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

select final.month, final.ranking, final.song_name, final.play_pv from (
        select play_temp.month as month ,row_number() over(partition by play_temp.month order by play_temp.play_pv desc) as ranking,song.song_name as song_name, song.singer_name ,play_temp.play_pv from (
        select month, song_id, count(1) as play_pv from (
            select song_id,month(fdate) as month from play_log
            where user_id in (select user_id from user_info 
                        where age between 18 and 25) 
            and year(fdate) = '2022'
        ) temp
        group by temp.month,temp.song_id
    ) play_temp
    left join song_info song on play_temp.song_id = song.song_id
    where song.singer_name = '周杰伦'
) final
where ranking <= 3

分析：

1.首先找出2022年，18-25岁听歌的人 select user_id from user_info where age between 18 and 25 and year(fdate) = '2022'。

记为A表

2. 获取这些人听歌的月份和听的歌曲 select song_id,month(fdate) as month from play_log where user_id in ( A ) temp。

记为B表

3.计算出当前月听歌曲的次数 select month, song_id, count(1) as play_pv from ( B ) play_temp

记为C表

4.通过窗口函数，获取不同月份下的，听歌次数的排名 select play_temp.month as month ,row_number() over(partition by play_temp.month order by play_temp.play_pv desc) as ranking,song.song_name as song_name ,play_temp.play_pv from ( C ) play_temp left join song_info song on play_temp.song_id = song.song_id

where song .singer_name = '周杰伦'

注：在left join中，'周杰伦'这个条件只能放在where中，不能放在on的关联条件中。因为主表是play_info表，会出现这种情况，该首歌是林俊杰的歌；

或者去掉left，使用inner，此时，就可以把'周杰伦'这个条件放在on的关联条件中了。

记为D表

5.最终通过where条件过滤排名，得到结果 select final.month, final.ranking, final.song_name, final.play_pv from D where ranking <= 3