MAX function and group by usage - postgresql

Question

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

http://stackoverflow.com/questions/1411199/what-is-a-better-way-to-sort-by-a-5-star-rating/1411268#1411268

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

where:

* 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?

Answer 1

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,
         reviews_count,
         rating, 
         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