CODEWITHSUNDEEP
mysql
Conner
Conner

Reputation: 677

MySQL display top rated items?

Given the following tables:

---movie---
id int(11)
description varchar(200)

---movie_has_rating---
movie_id int(11)
user varchar(100)
stars int(11) <-- A 1-5 star rating

How would I select the top 50 rated movies using the IMDb weighted average

(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

C = the mean vote across the whole report

Assuming m is something like 5 or whatever?

Upvotes: 0

Views: 48

Answers (1)

Ralph Ritoch
Ralph Ritoch

Reputation: 3440

Here is a query which solves your problem. I reduced the C out of your equation since it is only a scaler and will not effect the order. Each zero (0) in this query is your m value which is a parameter that cannot be derived from your provided schema.

SELECT * from movie 
LEFT JOIN 
    (SELECT v.movie_id AS movie_id, v.v AS v, r.r AS r 
     FROM (select movie_id, count(*) as v 
           FROM movie_has_rating 
           GROUP BY movie_id) v 
     LEFT JOIN (SELECT movie_id, avg(stars) as r 
                FROM movie_has_rating          
                GROUP BY movie_id) r ON v.movie_id = r.movie_id
    ) ratings   ON movie.id = ratings.movie_id 
ORDER BY (v/(v+0) * r + (0/(v+0))) DESC
LIMIT 50

Upvotes: 1

Related Questions