如何用SQL三表联查按年龄分组统计高评分电影类型

原SQL存在的核心问题 #

• 语法错误：CASE WHEN后的判断条件不合法，genre and age < 40中genre是字符串类型字段不能直接作为布尔判断条件，第二个CASE分支甚至缺失逻辑连接符，直接写genre age < 40会直接报语法错误。
• 聚合函数选择错误：需求是对比评分高低，count()仅能统计评分记录条数，无法反映评分水平，需要用avg()计算分组平均评分才能衡量类型的评分表现。
• 缺失分组逻辑：没有写GROUP BY子句对电影类型、年龄维度分组，无法输出分维度的统计结果。
• 表关联逻辑不合理：对item表用RIGHT JOIN会引入大量无任何用户评分的无效电影数据，三张表用INNER JOIN关联取有有效评分的交集数据即可。另外user是SQL保留关键字，作为表名使用时需要加反引号转义避免报错。

正确实现代码 #

以下代码默认item表中genre字段为单值存储（即一部电影仅对应一个类型），年龄组按通用规则划分为4档，最终会输出每个年龄组平均评分最高的电影类型，同时附带评分人数过滤小样本误差：

WITH group_rating AS (
    SELECT
        i.genre,
        -- 划分年龄组
        CASE
            WHEN u.age < 18 THEN '18岁以下'
            WHEN u.age BETWEEN 18 AND 39 THEN '18-39岁'
            WHEN u.age BETWEEN 40 AND 59 THEN '40-59岁'
            ELSE '60岁及以上'
        END AS age_group,
        AVG(d.rating) AS avg_score,
        COUNT(d.rating) AS rating_user_count
    FROM `user` u
    INNER JOIN `data` d 
        ON u.userid = d.userid
    INNER JOIN `item` i 
        ON d.movieid = i.movieid
    GROUP BY i.genre, age_group
)
-- 取每个年龄组评分最高的类型
SELECT age_group, genre AS highest_rated_genre, avg_score, rating_user_count
FROM (
    SELECT
        *,
        -- 按年龄组分片，按平均评分降序、评分人数降序排序取第一
        ROW_NUMBER() OVER (
            PARTITION BY age_group 
            ORDER BY avg_score DESC, rating_user_count DESC
        ) AS rk
    FROM group_rating
) t
WHERE rk = 1
ORDER BY age_group;

补充说明：如果item表中genre字段是多值拼接存储（比如用|分隔一部电影的多个类型，是MovieLens这类公开数据集的常见存储格式），需要先对genre字段做拆分，将一部电影对应多个类型的记录拆成多行单类型记录，再执行上述聚合逻辑，否则统计的是多类型组合的评分，不是单类型的真实评分表现。

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