如何编写SQL查询获取各类型评分最高电影及平均评分？

说明 #

先纠正一个字段笔误：reviews表中的movide_id应为movie_id，以下SQL将按正确字段名编写。

完整SQL查询 #

WITH movie_avg_ratings AS (
    SELECT 
        m.id,
        m.title,
        m.genre,
        AVG(r.rating) AS stars
    FROM movies m
    JOIN reviews r ON m.id = r.movie_id
    GROUP BY m.id, m.title, m.genre
),
ranked_movies AS (
    SELECT 
        genre,
        title,
        stars,
        RANK() OVER (PARTITION BY genre ORDER BY stars DESC) AS rating_rank
    FROM movie_avg_ratings
)
SELECT genre, title, stars
FROM ranked_movies
WHERE rating_rank = 1
ORDER BY genre ASC;

代码拆解 #

1. 计算单部电影平均评分
   用CTE movie_avg_ratings关联两张表，按电影ID分组，通过AVG(r.rating)计算平均评分并命名为stars，得到每部电影的基础评分数据。

2. 按类型筛选最高评分电影
   用CTE ranked_movies结合RANK()窗口函数：按genre分组，组内按stars降序排名。如果同类型有多个电影评分并列最高，RANK()会让它们都保留排名1；若只想保留其中一部，可替换为ROW_NUMBER()。

3. 最终筛选与排序
   筛选出排名为1的电影，再通过ORDER BY genre ASC实现按类型升序排列。

执行结果 #

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