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

select *
from(
    select month(p.fdate) as month,
        row_number() over(partition by month(fdate) 
        order by count(s.song_id) DESC,
        p.song_id ASC) as ranking,
        song_name,
        count(s.song_id) as play_pv
    from user_info u 
    left join play_log p on u.user_id = p.user_id
    left join song_info s on p.song_id = s.song_id
    where
        year(fdate) = 2022 and (age between 18 and 25)
        and singer_name = '周杰伦'
    group by 
        month(p.fdate), song_name, p.song_id
) t1
where ranking in(1,2,3);

SELECT *

FROM (

...

) t1

WHERE ranking IN (1, 2, 3);

外层查询从一个子查询（别名为 t1）中选择所有列，但只保留 ranking 列值为 1、2 或 3 的行。这意味着它将提取每个月播放次数排名前三的歌曲。

子查询：

• MONTH(p.fdate) AS month ：提取 play_log 表中 fdate 字段的月份部分，并将其命名为 month。
• ROW_NUMBER() OVER (PARTITION BY MONTH(fdate) ORDER BY COUNT(s.song_id) DESC, p.song_id ASC) AS ranking ：这是一个窗口函数，用于为每个月的歌曲播放次数生成一个排名。
• PARTITION BY MONTH(fdate) ：将数据按月份分组，确保每个月的排名是独立计算的。
• ORDER BY COUNT(s.song_id) DESC, p.song_id ASC ：首先按歌曲播放次数降序排列，播放次数多的歌曲排名靠前。如果播放次数相同，则按 song_id 升序排列，确保排名的唯一性。
• ROW_NUMBER() ：为每个分组中的行生成一个唯一的序号，即排名。
• ranking ：将生成的排名命名为 ranking。
• song_name ：从 song_info 表中提取歌曲名称。
• COUNT(s.song_id) AS play_pv ：计算每首歌的播放次数，并将其命名为 play_pv。
• FROM user_info u LEFT JOIN play_log p ON u.user_id = p.user_id LEFT JOIN song_info s ON p.song_id = s.song_id ：从 user_info 表开始，通过 LEFT JOIN 将 play_log 和 song_info 表连接起来，以便获取用户的播放记录和歌曲信息。
• WHERE YEAR(fdate) = 2022 AND (age BETWEEN 18 AND 25) AND singer_name = '周杰伦' ：筛选条件如下：
• YEAR(fdate) = 2022 ：只考虑 2022 年的播放记录。
• age BETWEEN 18 AND 25 ：只考虑年龄在 18 到 25 岁之间的用户。
• singer_name = '周杰伦' ：只考虑周杰伦演唱的歌曲。
• GROUP BY MONTH(p.fdate), song_name, p.song_id ：按月份、歌曲名称和歌曲 ID 进行分组，以便对每首歌的播放次数进行统计。