MySQL 显示评分最高的项目？

Question

给出下表:

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

如何使用 IMDb 加权平均值选择评分最高的 50 部电影

(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

假设 m 是 5 之类的东西？

Answer 1

这是一个可以解决您的问题的查询。我从方程中减少了 C，因为它只是一个缩放器，不会影响顺序。此查询中的每个零 (0) 都是您的 m 值，该参数无法从您提供的架构中派生。

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