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

这道题目要求我们从听歌流水中找出18-25岁用户在2022年每个月播放次数最多的前三首周杰伦的歌曲。下面是这个SQL查询的思路和实现步骤。

题目描述

我们需要从三个表中提取数据：

• play_log：记录了用户的听歌流水，包括日期、用户ID和歌曲ID。
• song_info：记录了歌曲的信息，包括歌曲ID、歌曲名称和歌手名称。
• user_info：记录了用户的信息，包括用户ID和年龄。

目标是找出2022年每个月18-25岁用户播放次数最多的前三首周杰伦的歌曲。

拆解题目

1. 过滤数据

首先，我们需要过滤出符合条件的数据：

• 歌手是“周杰伦”。
• 用户年龄在18到25岁之间。
• 日期在2022年。

这通过WHERE子句实现：

WHERE 
    s.singer_name = '周杰伦'
    AND u.age BETWEEN 18 AND 25
    AND YEAR(p.fdate) = 2022

2. 计算每个月的播放次数

我们需要按月份和歌曲进行分组，并计算每首歌的播放次数：

GROUP BY 
    MONTH(p.fdate), s.song_name, s.song_id

使用COUNT(*)来计算播放次数：

COUNT(*) AS play_pv

3. 为每个月的歌曲排名

使用ROW_NUMBER()窗口函数为每个月的歌曲按播放次数进行排名：

ROW_NUMBER() OVER (PARTITION BY MONTH(p.fdate) ORDER BY COUNT(*) DESC, s.song_id ASC) AS ranking

• PARTITION BY MONTH(p.fdate)：按月份分区。
• ORDER BY COUNT(*) DESC, s.song_id ASC：按播放次数降序排列，如果播放次数相同，则按歌曲ID升序排列。

4. 选择排名前3的歌曲

在外层查询中，选择排名在前三的歌曲：

WHERE 
    sub.ranking <= 3

5. 排序输出

最后，按月份和播放次数排序输出结果：

ORDER BY 
    sub.month ASC, sub.play_pv DESC;

完整代码

SELECT 
    sub.month,
    sub.ranking,
    sub.song_name,
    sub.play_pv
FROM (
    SELECT 
        MONTH(p.fdate) AS month,
        ROW_NUMBER() OVER (PARTITION BY MONTH(p.fdate) ORDER BY COUNT(*) DESC, s.song_id ASC) AS ranking,
        s.song_name,
        COUNT(*) AS play_pv
    FROM 
        play_log p
    JOIN 
        song_info s ON s.song_id = p.song_id
    JOIN 
        user_info u ON u.user_id = p.user_id
    WHERE 
        s.singer_name = '周杰伦'
        AND u.age BETWEEN 18 AND 25
        AND YEAR(p.fdate) = 2022
    GROUP BY 
        MONTH(p.fdate), s.song_name, s.song_id
) AS sub
WHERE 
    sub.ranking <= 3
ORDER BY 
    sub.month ASC, sub.play_pv DESC;