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

WITH
    counted_songs AS (
        SELECT
            MONTH (play_log.fdate) AS month,
            song_info.song_id AS song_id,
            MIN(song_info.song_name) AS song_name,
            COUNT(1) AS play_pv
        FROM
            play_log
            JOIN user_info ON play_log.user_id = user_info.user_id
            JOIN song_info ON play_log.song_id = song_info.song_id
        WHERE
            YEAR (play_log.fdate) = 2022
            AND user_info.age BETWEEN 18 AND 25
            AND song_info.singer_name = '周杰伦'
        GROUP BY
            MONTH (play_log.fdate),
            song_info.song_id
    ),
    ranked_songs AS (
        SELECT
            *,
            ROW_NUMBER() OVER(
                PARTITION BY
                    month
                ORDER BY
                    play_pv DESC,
                    song_id
            ) AS ranking
        FROM
            counted_songs
    )
SELECT
    month,
    ranking,
    song_name,
    play_pv
FROM
    ranked_songs
WHERE
    ranking BETWEEN 1 AND 3
ORDER BY
    month ASC,
    ranking ASC

似乎只能计数-分组加序号-筛选排序？大神还有更简单的方法吗