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

WITH t1 AS (  
    SELECT  
        month(fdate) AS month,  
        DATE_FORMAT(fdate, '%Y') AS year, 
        song_id,   
        COUNT(user_id) AS play_pv  
    FROM play_log pl 
    where user_id in 
        (SELECT user_id
        FROM user_info  
        WHERE age BETWEEN 18 AND 25 )
    GROUP BY month, year, song_id 
    HAVING year = 2022 --查符合年龄的用户听的所有歌曲及歌曲每月被听的频次
),  
t2 AS (  
    SELECT song_id, song_name, singer_name  
    FROM song_info  
    WHERE singer_name = '周杰伦'  --查周杰伦的所有歌曲
),  
t3 AS (  
    SELECT  
        t1.month,  
        t2.song_id,  
        t2.song_name,  
        t1.play_pv  
    FROM 
    t1 JOIN t2 ON t1.song_id = t2.song_id  
)    --链接成1张表
SELECT b.month,b.ranking,b.song_name,b.play_pv from  
    (select
    t3.month,  
    ROW_NUMBER() OVER (PARTITION BY t3.month ORDER BY t3.play_pv DESC) AS ranking,
    t3.song_id,  
    t3.song_name,  
    t3.play_pv 
FROM t3)b  --按频次每月排名
    where b.ranking<=3
    ORDER BY b.month asc,b.ranking asc,b.song_id asc,b.play_pv DESC