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.


Related Query

More Query from same tag