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

WITH filtered_plays AS (
    SELECT 
        MONTH(p.fdate) AS month,
        p.song_id,
        COUNT(*) AS play_pv
    FROM 
        play_log p
    JOIN 
        user_info u ON p.user_id = u.user_id 
        AND u.age BETWEEN 18 AND 25
    WHERE 
        YEAR(p.fdate) = 2022
    GROUP BY 
        MONTH(p.fdate), p.song_id
),

songs AS (
    SELECT * FROM song_info WHERE singer_name = '周杰伦'
)

SELECT 
    month,
    ranking,
    song_name, 
    play_pv
FROM (
    SELECT 
        f.month,
        s.song_name,
        f.play_pv,
        ROW_NUMBER() OVER(PARTITION BY f.month ORDER BY f.play_pv DESC) AS ranking
    FROM 
        filtered_plays f
    JOIN 
        songs s ON f.song_id = s.song_id
) ranked
WHERE 
    ranking <= 3
ORDER BY 
    month asc, ranking asc;

拆解成两个CTE清晰一些，

以及这里不能用rank()over() 不然就跳跃排序了~