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

SELECT
    t.month,
    t.ranking,
    t.song_name,
    t.play_pv
FROM
    (SELECT
        MONTH(p.fdate) AS month,
        s.song_name,
        COUNT(*) AS play_pv,
        ROW_NUMBER() over (partition by MONTH(p.fdate) ORDER by COUNT(*) DESC, s.song_id ASC) AS ranking
    FROM
        play_log p 
        JOIN song_info s ON p.song_id=s.song_id
        JOIN user_info u ON p.user_id=u.user_id
    WHERE
        u.age BETWEEN 18 AND 25
        AND YEAR(p.fdate) = 2022
        AND s.singer_name = '周杰伦'
    GROUP BY
        MONTH(p.fdate),s.song_name,s.song_id
    ) t
WHERE
    t.ranking <= 3
ORDER BY 
    t.month ASC,
    t.ranking ASC

1. group by可以和窗口函数合用
2. 想清楚是在哪个维度上压缩，哪个维度上保留。本题是在用户收听次数上压缩
3. 用窗口函数时，每首歌的次数已经经过group聚合好，相同月份的条目中每首歌的记录只有一条，按照收听数排序就是在排序歌曲