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

with a as(
    select 
        month(fdate)  as month
        ,play_log.user_id user_id
        ,play_log.song_id song_id
        ,song_name
        ,singer_name
        from play_log 
        left join song_info on play_log.song_id = song_info.song_id
        left join user_info on play_log.user_id = user_info.user_id
        where age >=18 and age <= 25
        and singer_name = "周杰伦"
        )

select
month	
,ranking	
,song_name	
,play_pv
from 
(
  select* from(
    select 
    month
    , row_number()over(partition by month order by play_pv desc) ranking
    ,song_id
    ,play_pv
    from 
    ( select 
    month
    ,song_id
    ,count(song_id) play_pv
    from a
    group by month,song_id
    order by month,song_id ) b   
    # 这一层子查询必须加上song_id排序,不然后面排不上
     ) c
    where ranking <= 3
    # over窗口函数ranking加一层子查询才能where
    order by month,ranking
) d left join song_info on d.song_id = song_info.song_id 
   
# 这个题目还有几个盲区：一个用户重复播放算不算多次播放？升高难度：1首歌1个用户播放计1次求排序，或每个用户的TOP3播放
#    select * from song_info where  song_name in ("爱情废柴" ,"伊斯坦堡")