题解 | 每个月Top3的周杰伦歌曲
每个月Top3的周杰伦歌曲
https://www.nowcoder.com/practice/4ab6d198ea8447fe9b6a1cad1f671503
WITH tongji AS
(
SELECT
b.song_id,
a.user_id ,
MONTH(b.fdate) AS month,
c.song_name
FROM user_info as a
LEFT JOIN play_log as b
ON a.user_id = b.user_id
LEFT jOIN song_info as c
ON b.song_id = c.song_id
WHERE a.age BETWEEN 18 AND 25
AND c.singer_name = "周杰伦"
)
,tongji_2 AS
(
SELECT
month,
song_id,
song_name,
COUNT(*) AS play_pv
FROM tongji
GROUP BY month,song_name,song_id
)
,tongji_3 AS
(
SELECT
month,
ROW_NUMBER() OVER(PARTITION BY month ORDER BY play_pv DESC,song_id ) AS ranking,
song_name,
play_pv
FROM tongji_2
)
SELECT * FROM tongji_3 WHERE ranking <=3
## 我的思路就是通过cte结构来完成每一步的拆解，首先是合并表格并且进行年龄和歌手筛选
第二步是进行分组聚合，通过各月份不同歌曲的听歌次数
第三部是进行窗口函数打标签来进行排序（注意，相同次数的歌曲需要进行按song_id进行升序排序）
第四步就是进行筛选排序前三的
#sql刷题#
查看30道真题和解析