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

select
month,
ranking,
song_name,
play_pv
from
(
select
month(fdate) as month,
ROW_NUMBER() over (PARTITION BY month(fdate) ORDER BY count(play_log.song_id) DESC,song_info.song_id ASC) as ranking,
song_name,
count(play_log.song_id) as play_pv,
song_info.song_id
from 
play_log,song_info,user_info
where 
play_log.song_id = song_info.song_id
and
play_log.user_id = user_info.user_id
AND
year(fdate) = 2022
AND
age between 18 and 25
AND
singer_name = "周杰伦"
group by month(fdate),song_name,song_info.song_id)t
WHERE
ranking <= 3
ORDER BY
    month ASC, play_pv DESC;