SQL40 每个月Top3的周杰伦歌曲

题目描述

从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲。 排名先后由两者的song_id先后顺序决定

数据表结构

• 流水表 play_log:

• 歌曲表song_info：

• 用户表user_info

解题思路

① 根据题目条件取出数据

Where usr.age between 18 and 25
and Year(log.fdate) = 2022
and song.singer_name = '周杰伦'

② 根据月份，歌曲名，歌曲id聚合播放次数，使用开窗函数统计排名

with rst as(
select month(log.fdate) as month
, song.song_id
, song.song_name
, count(log.song_id) as play_pv
 
-- 连续值的排序，使用row_number() over() -- 播放次数相同，再使用song_id排序
, row_number() over(partition by month(log.fdate) order by count(log.song_id) desc, song_id asc) as ranking
from play_log as log
left join song_info as song on log.song_id = song.song_id
left join user_info as usr on log.user_id = usr.user_id
-- 第①步的条件
Where usr.age between 18 and 25
and Year(log.fdate) = 2022
and song.singer_name = '周杰伦'
 
group by month(log.fdate)
, song.song_id
, song.song_name
)

③ 从步骤②结果集取出top 3

select month
, ranking
, song_name
, play_pv
from rst
where ranking <= 3

小结

解题时，将group by和开窗函数分了两步，实际group by和开窗函数可以一步完成