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

select month,ranking,song_name,play_pv
from
    (select pl_month month,row_number() over (partition by pl_month order by cnt DESC,song_id)  ranking,song_name,cnt play_pv from
        (
        select month(pl.fdate) pl_month,si.song_name song_name,si.song_id song_id,count(*) cnt
        from play_log pl left join song_info si 
        on pl.song_id=si.song_id
        left join user_info ui on pl.user_id=ui.user_id
        where ui.age>=18 and ui.age<=25 and si.singer_name='周杰伦' and year(pl.fdate)='2022'
        group by pl_month,si.song_name,si.song_id
        ) tmp1
    ) tmp2
where ranking<4

从输出中可以看到，有ranking排序，有play_pv播放次数，初步确定应该要用到窗口函数得到排序，group by聚合函数得到总播放次数

第一步：得到每月每首歌的播放次数，也即play_pv这一项。首先把play_log,song_info,user_info三表连接起来，然后用年龄、歌手、年份筛选需要的记录，之后考虑每月每首歌播放次数，所以用月份和歌名group by，用count计数。（group by里添加song_id是为了后续排名先后相同用song_id排序）

select month(pl.fdate) pl_month,si.song_name song_name,si.song_id song_id,count(*) cnt
from play_log pl left join song_info si 
on pl.song_id=si.song_id
left join user_info ui on pl.user_id=ui.user_id
where ui.age>=18 and ui.age<=25 and si.singer_name='周杰伦' and year(pl.fdate)='2022'
group by pl_month,si.song_name,si.song_id

第二步：得到排名ranking这一项。因为输出中即使play_pv相同，ranking也按1,2,3排列，所以考虑使用row_number()(比如针对4,4,2，如果用rank会得到1,1,3,如果用dense_rank会得到1,1,2）。要输出的是每月播放次数top3的，所以窗口函数用月份分组，按播放次数play_pv，和歌曲song_id排名。

select pl_month month,row_number() over (partition by pl_month order by cnt DESC,song_id)  ranking,song_name,cnt play_pv from
        (
        select month(pl.fdate) pl_month,si.song_name song_name,si.song_id song_id,count(*) cnt
        from play_log pl left join song_info si 
        on pl.song_id=si.song_id
        left join user_info ui on pl.user_id=ui.user_id
        where ui.age>=18 and ui.age<=25 and si.singer_name='周杰伦' and year(pl.fdate)='2022'
        group by pl_month,si.song_name,si.song_id
        ) tmp1

最后，仅输出播放次数最高的三首，用ranking进行限定。

select month,ranking,song_name,play_pv
from
    (select pl_month month,row_number() over (partition by pl_month order by cnt DESC,song_id)  ranking,song_name,cnt play_pv from
        (
        select month(pl.fdate) pl_month,si.song_name song_name,si.song_id song_id,count(*) cnt
        from play_log pl left join song_info si 
        on pl.song_id=si.song_id
        left join user_info ui on pl.user_id=ui.user_id
        where ui.age>=18 and ui.age<=25 and si.singer_name='周杰伦' and year(pl.fdate)='2022'
        group by pl_month,si.song_name,si.song_id
        ) tmp1
    ) tmp2
where ranking<4