题解 | 每个月Top3的周杰伦歌曲

WITH PlayStats AS (
    SELECT 
        YEAR(pl.fdate) AS year,
        MONTH(pl.fdate) AS month,
        si.song_id,        
        si.song_name,
        COUNT(pl.song_id) AS play_pv
    FROM 
        play_log pl
    JOIN 
        user_info ui ON pl.user_id = ui.user_id
    JOIN 
        song_info si ON pl.song_id = si.song_id
    WHERE 
        ui.age BETWEEN 18 AND 25
        AND si.singer_name = '周杰伦'
        AND YEAR(pl.fdate) = 2022
    GROUP BY 
        YEAR(pl.fdate), MONTH(pl.fdate), si.song_id, si.song_name
),
RankedStats AS (
    
    SELECT 
        month,
        song_id,
        song_name,
        play_pv,
        ROW_NUMBER() OVER (
            PARTITION BY month 
            ORDER BY play_pv DESC, song_id ASC  
        ) AS ranking
    FROM 
        PlayStats
)
SELECT 
    month,
    ranking,
    song_name,
    play_pv
FROM 
    RankedStats
WHERE 
    ranking <= 3
ORDER BY 
    month, ranking;

1. CTE（公共表表达式）PlayStats：从play_log表（播放日志表）、user_info表（用户信息表）和song_info表（歌曲信息表）中联合查询数据。选择年份（YEAR(pl.fdate)）、月份（MONTH(pl.fdate)）、歌曲 ID（si.song_id）、歌曲名（si.song_name）以及歌曲的播放量（COUNT(pl.song_id)命名为play_pv）。过滤条件为用户年龄在 18 到 25 岁之间（ui.age BETWEEN 18 AND 25），歌手名为 “周杰伦”（si.singer_name = '周杰伦'），且播放日期年份为 2022 年（YEAR(pl.fdate) = 2022）。按年份、月份、歌曲 ID 和歌曲名进行分组（GROUP BY YEAR(pl.fdate), MONTH(pl.fdate), si.song_id, si.song_name）。
2. CTE RankedStats：基于PlayStats的结果，对每个月的歌曲播放量进行排名。使用ROW_NUMBER()窗口函数，按每月（PARTITION BY month）内的播放量降序（ORDER BY play_pv DESC）和歌曲 ID 升序（song_id ASC）进行排名，排名结果命名为ranking。
3. 最终查询：从RankedStats中选择月份（month）、排名（ranking）、歌曲名（song_name）和播放量（play_pv）。过滤条件为排名小于等于 3（ranking <= 3）。最后按月份和排名升序排序（ORDER BY month, ranking）。