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

select *
from (
    select 
        month(p.fdate) as month,
        row_number() over (partition by month(p.fdate) order by count(*) desc,s.song_id) 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,sub.play_pv desc;

三表连接

使用窗口函数，不可用group by因为会最终展示一行

row_number() over (partition by month(p.fdate) order by count(*) desc,s.song_id) as ranking

count(*) 可以表示歌播放次数 关键在于根据月和歌名和歌id分组了

count(*) as play_pv

group by

month(p.fdate),s.song_name,s.song_id

where条件筛选

最后order by 排序