I am building a ranking algorithm using IMDB's formula:


The formula for calculating the Top Rated 250 Titles gives a true Bayesian estimate:

weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C


* R = average for the movie (mean) = (Rating)
* v = number of votes for the movie = (votes)
* m = minimum votes required to be listed in the Top 250 (currently 1300)
* C = the mean vote across the whole report (currently 6.8)
for the Top 250, only votes from regular voters are considered.

The problem I am having is that I am not sure how to get the AVG and MAX from the whole result set. SQLFiddle here

I am guessing it has to do with the GROUP BY clause.

Also, how can I set "business 4" ranking to 0 instead of null?

  • If you don't use GROUP BY, you will get the aggregates for all the rows selected (as specified by the WHERE clause). In this case you can't specify other columns in the SELECT list. If you say GROUP BY id, aggregates will be computed for any values of id, in your case for single rows - that's useless. – dezso Jan 14 '13 at 21:46
  • What if I want to SELECT other columns though? – AlexBrand Jan 14 '13 at 22:22
  • If there are multiple values for a column within a group which value do you want to select? If you are certain that the columns you want to select have the same values when grouped by id add them to the group by clause – redguy Jan 14 '13 at 22:32
  • 1
    I suspect you either want a subquery to get the dataset-wide mean and max, or to use avg and max as window functions to do both resultset-wide and per-group results. – Craig Ringer Jan 15 '13 at 1:01

This might get you started:

select name,
       (reviews_count / reviews_count + max_reviews_count) * rating + (max_reviews_count / (reviews_count+max_reviews_count) * avg_rating) AS ranking
from (
  SELECT name,
         MAX(reviews_count) over () as max_reviews_count,
         AVG(rating) over() as avg_rating
  FROM businesses
) t;

Note that I removed the nullif to make it easier to read.

I'm not sure the use of the window functions is totally correct. I guess you need to do some grouping in there. If that is the case, then add this to the over() clause, e.g. over(partition by name)

The partition by in a window function is essentially a group by without the need to group the whole result.

For more details on window functions, please see the manual: http://www.postgresql.org/docs/current/static/tutorial-window.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.