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

select  * from (select  month(p1.fdate) as month,ROW_NUMBER() OVER(PARTITION BY month(p1.fdate) order by count(*) desc ,p1.song_id asc) as ranking, s1.song_name ,COUNT(*) as play_pv from play_log p1 join user_info s2 on p1.user_id = s2.user_id join song_info s1 on s1.song_id = p1.song_id
where s1.singer_name ='周杰伦' and s2.age BETWEEN 18 AND 25 and year(fdate)= 2022
group by month(p1.fdate),p1.song_id,s1.song_name,s1.singer_name )   as linshi
where ranking <4 

1.where用不了窗口函数和聚合函数，因为窗口函数执行是在select之后，但是可以使用日期函数，转换函数，数字函数等等