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

select 
    sub.month,
    sub.ranking,
    sub.song_name,
    sub.play_pv
from(
    select
        month(p.fdate) as month,
        row_number() over (partition by month(p.fdate) order by count(*) desc, s.song_id asc) as ranking,
        s.song_name,
        count(*) as play_pv

    from
        play_log p
    join
        song_info s on s.song_id = p.song_id
    join
        user_info u on u.user_id = p.user_id
    where
        s.singer_name = '周杰伦'
        and u.age between 18 and 25
        and year(p.fdate) = 2022
    group by
        month(p.fdate), s.song_name, s.song_id

) as sub

where 
    sub.ranking <= 3
order by
    sub.month asc,
    play_pv desc;

• 题目快速记住
• 聚合函数和group by 一起使用
• asc 升序，desc 降序
• 构建子表 sub
• 窗口函数 row_number over (parptition by 分类依据)， order by 排序依据