score:2
Accepted answer
This is not what a weighted average is. It sounds like you are trying to get at a Bayesian average where you penalize a small set by moving its observed average towards some meta-average. There is no built in way to do this in PostgreSQL.
Compute the sum and count separately, and then use some mechanism to implement the penalty based on those values. You could do that in the client, or you could write an outer query which takes the results of the subquery and applies the formula.
select id, (the_sum + 10* <metaaveerage>)/(the_count+10) from (
SELECT tv.id, sum(ut.rating) as the_sum, count(ut.rating) as the_count FROM user_tvshow AS ut
LEFT JOIN tvshows AS tv ON tv.id = ut.tvshow
WHERE "user" IN (
SELECT follows FROM user_follows WHERE "user" = 1 -- List of users the current user follows
) AND rating IS NOT NULL GROUP BY tv.id
) foobar
How you decide what values to plug in for the 10 and for the <metaaverage> are questions of statistics, not programming.
More questions
- Is there a way to use postgresql function to calculate weighted average in Rails?
- POSTGRESQL: Weighted average instead of average?
- PostgreSQL calculate weighted average based on user input
- calculate weighted average for each day and id based on time intervals in PostgreSQL
- Is it better to use tables instead of arrays field type in PostgreSql when arrays do not exceed 50 elements?
- Moving average in postgresql
- Select random row from a PostgreSQL table with weighted row probabilities
- Moving Average based on Timestamps in PostgreSQL
- In Postgresql how to get base64 decoded values as string instead of hex representation?
- Celery: Use PostgreSQL instead of RabbitMQ
- What are the benefits of using Postgresql Daterange type instead of two Date fields?
- Output "yes/no" instead of "t/f" for boolean data type in PostgreSQL
- Create an empty array in an SQL query using PostgreSQL instead of an array with NULL inside
- Why is Postgresql using filter instead of index?
- PostgreSQL psycopg2 returns a tuple of strings instead of tuple of tuples?
- Calculating the Weighted Average Cost of products stock
- SQL weighted average
- Create PostgreSQL dump with ONE INSERT statement instead of INSERT per row
- postgresql function: Final statement returns bigint instead of integer
- PostgreSQL How to get average of sum of certain rows
- Return multiple columns and rows from a function PostgreSQL instead of record
- Meteor reactivity with postgreSQL instead of MongoDB
- How to get an average of timestamps? PostgreSQL
- How to manually mapping String to postgresql text instead of just varchar(254)?
- How to invoke PostgreSQL function using spring jdbctemplate instead of direct insert query in Java?
- Moving window average in postgreSQL
- Conditional Cumulative Average in PostgreSQL
- Using LEFT JOIN instead of NOT IN in PostgreSQL
- Return NULL instead of composite value from PostgreSQL function
- Last three months average for each month in PostgreSQL query
More questions with similar tag
- postgres memory allocation tuning
- How to fix problems in docker postgres connection?
- Postgresql: Query 10x slower in a different client
- Get ID for Postgres Server Installation
- SQLAlchemy Joining a Session into an External Transaction Not Working as Expected
- Error "org.postgresql.Driver is not a valid javax.sql.DataSource implementation" that doesn`t affect anything. How to remove it?
- Sequelize authenticate() does not return any result
- Drop sequence and cascade
- Filtering joins with conditions
- SQL : How can i join different tables from competition of football?
- Lower sort renamed column
- Can we make correlated queries with SQLAlchemy
- PostgreSQL function to select max values of split record
- How can I assign pre-determined codes (1,2,3, etc,) to a JSON-type column in PostgreSQL?
- Ordering column values when concatenating to a single column
- PostgreSQL: Combine repeating window function (OVER / PARTITION BY) in one statement
- Kompose for docker-compose not use volumes
- Postgresql - Use selected column from one table as json select statements
- Rest Api application can't connect to postgres db in the same Kubernetes pod
- ID column with generated uniqe values in view (with union all)
- Eloquent json field- get records higher/lower than some value
- Modify the response of a SQL query involving junction table
- Merging two tables into one with the same column names
- Encrypt/Decrypt between Postgres and Node
- Distributed postgresql ID collision handling
- Getting this SQL Statement right with aliases
- How do I make strftime and GROUP and WHERE work in Postgres
- Record returned from function has columns concatenated
- SQL select value from one column based on values in another column
- Data type of $1 vs. coalesce($1)
Source:
stackoverflow.com
