基于电影推荐数据库，如何按类型获取Top电影？

核心思路 #

要按类型选出排名靠前的电影，核心是把存电影类型的Movies表和存用户评分的Users表关联起来，先算出每部电影的评分参考值（一般用平均评分更合理），再按类型分组，在每个组里对电影按评分排序，取前N位即可。

具体实现步骤 #

1. 关联表并计算电影平均评分 #

先通过电影名称关联两张表，聚合计算每部电影的平均评分——单条评分可能存在个人偏好，平均评分更能反映整体口碑。

示例SQL：

SELECT 
    m.movie,
    m.genre,
    AVG(u.rating) AS avg_rating
FROM Movies m
JOIN Users u ON m.movie = u.movieName
GROUP BY m.movie, m.genre

2. 按类型分组取Top N电影 #

用窗口函数实现“按类型分组排序”，常用的有两种函数：

• ROW_NUMBER()：同评分电影会按顺序排，每个排名唯一，不会出现并列
• RANK()：同评分电影会获得相同排名，比如两部电影都是5分，都会排第1名

示例：取每个类型评分Top 3的电影（无并列）

WITH MovieRatings AS (
    SELECT 
        m.movie,
        m.genre,
        AVG(u.rating) AS avg_rating
    FROM Movies m
    JOIN Users u ON m.movie = u.movieName
    GROUP BY m.movie, m.genre
),
RankedMovies AS (
    SELECT 
        movie,
        genre,
        avg_rating,
        ROW_NUMBER() OVER (PARTITION BY genre ORDER BY avg_rating DESC) AS rank_num
    FROM MovieRatings
)
SELECT movie, genre, avg_rating
FROM RankedMovies
WHERE rank_num <= 3
ORDER BY genre, rank_num;

示例：允许同评分并列的情况

把上面的ROW_NUMBER()换成RANK()即可：

WITH MovieRatings AS (
    SELECT 
        m.movie,
        m.genre,
        AVG(u.rating) AS avg_rating
    FROM Movies m
    JOIN Users u ON m.movie = u.movieName
    GROUP BY m.movie, m.genre
),
RankedMovies AS (
    SELECT 
        movie,
        genre,
        avg_rating,
        RANK() OVER (PARTITION BY genre ORDER BY avg_rating DESC) AS rank_num
    FROM MovieRatings
)
SELECT movie, genre, avg_rating
FROM RankedMovies
WHERE rank_num <= 3
ORDER BY genre, rank_num;

3. 特殊场景处理 #

• 如果有些电影没有用户评分，上面的JOIN会自动过滤掉它们。要是想把这些电影也列出来，可以改用LEFT JOIN，并把AVG(u.rating)改成COALESCE(AVG(u.rating), 0)，给无评分的电影默认0分。
• 本次需求用不到Actors表，要是需要同时展示电影的演员信息，可以在最终查询里关联Actors表。

内容的提问来源于stack exchange，提问作者Ariel Fisher