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.
- 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 Query from same tag
- RFC3339 format in PostgreSQL
- Find IDs with only one observation in PostgreSQL
- Using PG Backups to import database to app on Heroku
- How to return rows of query result in PostgreSQL's function?
- query has no destination for result data in POSTGRESQL for stored procedure
- Rails: Order by associate, first not downloaded
- Architecture for fast globally distributed user quota management
- Getting results with the correct time zone shift (Postgresql, Django)
- PostgreSQL query extremely slow
- Unusual two tables join
- Can read/write/delete OS files with plpgsql?
- Limit pg_search to the associations of an individual User object
- How to use variables in PostgreSQL in Python
- Datadog: PostgreSQL custom_metrics returns a single row
- MySQL dump compatible is not working
- Update table column by comparing geometry columns from other table
- Table name prefix for columns in UPDATE query
- Two columns working with same data
- delete duplicate rows postgresql
- JAVA store string Array in postgres and retrieve a JSON data from it
- PHP/PostgreSQL: check if a prepared statement already exists
- delete rows if count bigger than
- How to select from table A and then insert selected id inside table B with one query?
- How to learn Postgresql and its internals?
- Scrapy crawler: Unable to store multiple urls into postgres
- PreparedStatement.setString on int/numeric columns in PostgreSQL JDBC
- Error deploying Rails app to Heroku: simple_form issue
- Postgres query over ODBC a order of magnitude slower?
- Looking in array with a big set of input values
- Spring JPA Crud Repository findAll() performacne improvement