题目描述

本题要求从听歌流水数据中筛选出18-25岁用户在2022年每个月播放次数排名前3的周杰伦歌曲。涉及三张表：

• 流水表play_log：记录了日期（fdate）、用户ID（user_id）、歌曲ID（song_id）等信息。
• 歌曲表song_info：包含歌曲ID（song_id）、歌曲名称（song_name）、歌手名称（singer_name）等字段。
• 用户表 user_info：主要有user_id和age两个字段，用于表示用户ID和年龄。

题目分析

本题的核心和难点在于窗口函数和多表连接的正确运用。首先我们需要将三张表连接起来，关联条件分别是play_log表的song_id与song_info表的 song_id，以及play_log表的user_id与user_info表的 user_id。然后根据年龄、年份和歌手名称进行数据筛选，最后通过窗口函数对每个月的歌曲播放次数进行排名，取出排名前3的歌曲。

代码详解

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

关键点

• 多表连接：利用LEFT JOIN将play_log表、song_info表和user_info表连接。以play_log表为基础，通过song_id连接song_info表，通过user_id连接user_info表。
• 条件筛选：使用WHERE子句筛选出年龄在18至25岁之间，年份为2022年，且歌手为周杰伦的记录，精准定位所需数据。
• 分组统计：运用GROUP BY对每个月、每首歌曲进行分组，然后通过COUNT(song_name)统计每个分组内的播放次数，得到每首歌在每月的播放量。
• 窗口函数：采用ROW_NUMBER () OVER ()窗口函数，按照每个月PARTITION BY MONTH (fdate)对歌曲的播放次数ORDER BY COUNT (song_name) DESC进行降序排名，当播放次数相同时，按照歌曲IDpl.song_id升序排列，生成排名字段ranking。
• 最终筛选：在外层查询中，通过WHERE ranking <= 3筛选出每个月排名前3的歌曲记录，得出最终结果。