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

##有月份
##group by 每个月每首歌的pv
##分组每个月，按照pv排序
##筛选rk《=3的
##pv相同按照id决，先月份，再排名再id
with a as(
    select p.user_id
    ,month(p.fdate) as month
    ,p.song_id
    ,s.song_name
    from play_log p left join song_info s on p.song_id=s.song_id
    left join user_info u on p.user_id=u.user_id
    where year(p.fdate)=2022
    and u.age between 18 and 25
    and s.singer_name='周杰伦')
    ,b as (
        select 
        month
        ,song_name
        ,song_id
        ,count(1) as play_pv
        from a 
        group by month,song_name,song_id
    )
    ,c as (
        select *
        ,row_number()over(partition by month order by play_pv desc,song_id) as ranking
        from b
    )
    select month
    ,ranking
    ,song_name
    ,play_pv
    from c 
    where ranking<=3
    order by month,play_pv desc
	做的时候出现的几个问题是：
	1、引用错了
	2、连接表后，select里面不能直接用*，要一个字段一个字段选
	3、打错字段名