牛客网SQL40题要求我们从2022年的听歌记录中，找出18-25岁用户每月播放量排名前三的周杰伦歌曲。这道题的核心是考察分组TopN查询，需要综合运用多表连接、数据过滤、分组聚合以及窗口函数。

以下是基于题目要求的详细解法和说明。

解题思路

1. 多表连接：题目数据分布在三个表中，需要将它们连接起来。
   • play_log 表通过 user_id 连接 user_info 表，以获取用户年龄。
   • play_log 表通过 song_id 连接 song_info 表，以获取歌曲名称和歌手信息。
2. 数据过滤：在连接后的数据集中，筛选出符合所有条件的数据。
   • 用户年龄在18岁到25岁之间（包含18和25）。
   • 歌曲的演唱者是周杰伦。
   • 播放日期在2022年之内。
3. 分组统计播放次数：以月份和歌曲为维度进行分组，统计每首歌在每个月的总播放量（play_pv）。这里需要使用MONTH()函数从日期中提取月份，并用COUNT()函数统计播放记录。
4. 窗口函数排名：使用ROW_NUMBER()窗口函数，对每个月份分区，在分区内按照播放量（play_pv）进行降序排列，生成每个月份内部的排名（ranking）。
5. 筛选Top 3：在外层查询中，筛选出排名小于或等于3的记录，得到最终结果。

参考代码

SELECT 
    sub.month,
    sub.ranking,
    sub.song_name,
    sub.play_pv
FROM (
    -- 子查询：先完成连接、过滤、分组统计和排名
    SELECT 
        MONTH(pl.fdate) AS month,
        ROW_NUMBER() OVER (
            PARTITION BY MONTH(pl.fdate) 
            ORDER BY COUNT(*) DESC, si.song_id ASC   -- 先按播放量降序，若相同则按歌曲ID升序（题目隐含规则）
        ) AS ranking,
        si.song_name,
        COUNT(*) AS play_pv
    FROM 
        play_log pl
    -- 连接用户表，筛选年龄
    INNER JOIN user_info ui ON pl.user_id = ui.user_id AND ui.age BETWEEN 18 AND 25
    -- 连接歌曲表，筛选周杰伦歌曲
    INNER JOIN song_info si ON pl.song_id = si.song_id AND si.singer_name = '周杰伦'
    WHERE 
        YEAR(pl.fdate) = 2022   -- 筛选2022年的记录
    GROUP BY 
        MONTH(pl.fdate), si.song_name, si.song_id  -- 按月份和歌曲分组
) AS sub
WHERE 
    sub.ranking <= 3   -- 筛选每个月份的前三名
ORDER BY 
    sub.month ASC, sub.ranking ASC;  -- 按月份和排名排序输出

关键点解析

• 为什么用 ROW_NUMBER() 而不是 RANK() 或 DENSE_RANK()？
  题目要求取Top 3，且从提供的示例输出来看，排名列（ranking）是连续且不重复的（1,2,3）。ROW_NUMBER() 会为每一行分配一个唯一且连续的整数排名，即使播放量相同，也会根据 ORDER BY 中的次要条件（如歌曲ID）强制分出先后，确保每个月稳定地输出3条记录 。如果使用 RANK()，在播放量并列时会产生相同的排名（如1,1,3），可能导致结果超过3条 。

• GROUP BY 中为什么同时有 song_name 和 song_id？
  虽然在这个业务场景下，song_name 和 song_id 应该是一一对应的，但在SQL的 GROUP BY 逻辑中，song_name 的取值依赖于 song_id。为了保证分组结果的唯一性和准确性，避免潜在的错误，最佳的实践是将所有非聚合的查询列都包含在 GROUP BY 子句中 。

• 为什么不能直接用 LIMIT 3？
  LIMIT 子句是对整个结果集进行限制，只能取出全局的前3条记录。要得到“每个月的Top 3”，必须使用窗口函数配合 PARTITION BY 按月分区，才能实现“分组内排序并取前N”的效果 。

总结

本题是窗口函数在分组TopN场景下的经典应用，主要包含以下几个步骤：

1. 连接与过滤：通过 INNER JOIN 关联多表，并用 WHERE 筛选出18-25岁用户、周杰伦歌曲、2022年记录。
2. 分组聚合：用 GROUP BY 按月、歌曲分组，用 COUNT(*) 计算每月每首歌的播放次数。
3. 窗口计算与排名：使用 ROW_NUMBER() OVER(PARTITION BY 月份 ORDER BY 播放量 DESC) 为每月歌曲生成排名。
4. 外层筛选：在子查询外，用 WHERE 排名 <= 3 获取最终结果。