Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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