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

with
    play_info as (
        select
            month (pl.fdate) as month,
            si.song_name,
            si.song_id,
            count(pl.song_id) as play_pv
        from
            play_log pl
            inner join song_info si on pl.song_id = si.song_id
            inner join user_info ui on pl.user_id = ui.user_id
        where
            ui.age between 18 and 24
            and singer_name = '周杰伦'
        group by
            si.song_name,
            si.song_id,
            month (pl.fdate)
    ),
    rank_info as (
        select
            month,
            ROW_NUMBER() over (
                PARTITION BY
                    month
                order by
                    play_pv desc,
                    song_id asc
            ) as ranking,
            song_name,
            play_pv,
            song_id
        from
            play_info
    )
select
    month,
    ranking,
    song_name,
    play_pv
from
    rank_info
where
    ranking <= 3
order by
    month,
    ranking

用子查询的方式：

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