SQL分组TopN实现：窗口函数与DATE_FORMAT在每月歌曲排行中的应用-北京尧图网络

这道题在牛客网的SQL练习里算是个小经典我前前后后刷过好几遍每次重新做都能挖出点新东西。题目本身一句话就能讲完——统计每个月播放量Top3的周杰伦歌曲但就是这平平无奇的分组TopN把日期函数、聚合、JOIN、窗口函数、子查询这些SQL核心知识点全串了起来。上周我用DeepSeek帮我把整道题的解题思路重新梳理了一遍发现几个平时容易忽略的细节正好写出来跟同样在刷题、准备面试的朋友们聊聊。无论你是刚学SQL的新手还是已经工作想回头补基础的老手这篇文章都会让你对分组TopN这个套路有个更立体的认识。1. 拿到题目先别急着写SQL把三个关键点拆明白1.1 题目翻译成人话每个月的前三名不是全表前三名每个月Top3的周杰伦歌曲这句话看着简单落在SQL里是两个动作的组合先按月份分组再在每个分组内部按播放量排序取前3条。注意这个前3是每个月的前3不是全年总榜的前3。很多人第一反应是直接ORDER BY play_count DESC LIMIT 3那拿到的只是全表最大的三首歌跟题目要求差了十万八千里。这道题我让DeepSeek帮我做的第一件事就是把它翻译成更机器友好的描述对播放记录按DATE_FORMAT(play_time, %Y-%m)分组组内按歌曲总播放量降序排名保留每组排名小于等于3的记录。翻译完了之后整个解题路径就基本浮出水面了。1.2 表结构还原没有表结构一切SQL都是空中楼阁牛客网不同批次的题目表名和字段可能略有差异我按最常见的双表设计来还原逻辑是通用的CREATE TABLE music_info ( song_id INT PRIMARY KEY COMMENT 歌曲ID, song_name VARCHAR(50) COMMENT 歌曲名, singer VARCHAR(20) COMMENT 歌手 ); CREATE TABLE play_log ( log_id INT PRIMARY KEY COMMENT 播放记录ID, song_id INT COMMENT 歌曲ID, play_time DATETIME COMMENT 播放时间, play_count INT COMMENT 本次播放量 );有些版本会把两张表合成一张大宽表字段里直接带上song_name和singer。这都不影响解题逻辑只要心里有数要统计的是周杰伦这个歌手的歌曲维度是月份度量是播放量之和。如果你拿到的表字段名不一样照着这个语义去替换就成。我习惯在动手前先往表里塞一批测试数据确认SQL结果能对上预期。随便造几条INSERT INTO music_info VALUES (1, 晴天, 周杰伦), (2, 七里香, 周杰伦), (3, 稻香, 周杰伦), (4, 夜曲, 周杰伦), (5, 青花瓷, 周杰伦), (6, 孤勇者, 陈奕迅); INSERT INTO play_log (log_id, song_id, play_time, play_count) VALUES (1, 1, 2024-01-03 10:00:00, 500), (2, 2, 2024-01-05 11:00:00, 400), (3, 3, 2024-01-08 09:00:00, 600), (4, 4, 2024-01-12 14:00:00, 300), (5, 5, 2024-01-20 20:00:00, 200), (6, 6, 2024-01-21 18:00:00, 9999), (7, 1, 2024-02-02 10:00:00, 300), (8, 4, 2024-02-04 12:00:00, 900), (9, 2, 2024-02-10 16:00:00, 500), (10, 3, 2024-02-15 22:00:00, 400);可以看到我故意放了一首陈奕迅的《孤勇者》播放量还特别高用来验证歌手过滤有没有生效。理想输出应该是月份歌曲总播放量排名2024-01稻香60012024-01晴天50022024-01七里香40032024-02夜曲90012024-02七里香50022024-02稻香40031.3 这题到底在考什么一张考点地图这道题的考察点非常集中我说几个关键词GROUP BY聚合、日期格式化、窗口函数、子查询、JOIN、排序与过滤顺序。它不像某些难题专考一个冷门函数而是把日常开发里最常用的一批技能打包在一起。我用DeepSeek把这道题的考点和对应的MySQL知识点拉了个清单月份分组要用DATE_FORMAT还是MONTH、分组内排名要用ROW_NUMBER还是RANK、聚合前的过滤该放WHERE还是HAVING。任何一个点理解不到位出来的结果都会有问题。把这些点逐个吃透比死记这条SQL本身有价值得多——因为同一套思路换个场景就能复用。2. 分组TopN的核心思路为什么不能一把梭2.1 直接ORDER BY LIMIT为什么不行先看错误示范很多人第一版是这样写的SELECT ... FROM play_log WHERE song_id IN (SELECT song_id FROM music_info WHERE singer 周杰伦) ORDER BY play_count DESC LIMIT 3;这条SQL做的是全表周杰伦歌曲播放量Top3跟题目要求的每个月Top3完全是两回事。LIMIT在SQL执行计划里是最后一步生效的它只作用于整个结果集的尾部无法感知分组这个维度。我习惯用一个生活化类比来理解LIMIT 3像是全班选成绩前三而题目要的是每个小组选前三——你得先按小组把人分开在小组内部排序再从每个小组各取前三。这就是分组TopN和普通TopN的本质区别。分组TopN的核心矛盾是排序的粒度是整个数据集但取数的粒度是每个分组SQL必须用某种方式让排序感知分组边界。2.2 两层查询框架先算总量再组内排名最后过滤分组TopN最标准的解题框架是子查询窗口函数外层过滤三层结构第一层按月份歌曲分组用SUM(play_count)算出每个歌曲每个月的总播放量第二层在同一层或紧接的窗口函数里用ROW_NUMBER() OVER (PARTITION BY 月份 ORDER BY 播放量 DESC)给每组歌曲打上组内序号第三层外层WHERE rn 3过滤掉排名靠后的记录。为什么必须套一层子查询因为WHERE子句的执行顺序在窗口函数之前你没法直接在WHERE里写rn 3——MySQL根本还不认识rn这个别名。所以得先让窗口函数算完、把结果当成一张派生表再对这张表的列做过滤。这个先算再滤的意识是解这类题的关键门槛。2.3 ROW_NUMBER、RANK、DENSE_RANK三兄弟怎么选窗口函数里负责排名的有三个常用函数它们的区别我在实际刷题时吃过亏这里直接给结论函数并列处理排名是否跳跃每组返回行数ROW_NUMBER并列时随机/按ORDER BY决定先后不跳跃名次连续固定N行RANK并列同名次跳跃如1、1、3可能超过N行DENSE_RANK并列同名次不跳跃如1、1、2可能超过N行牛客网这道题如果没特别说明并列怎么处理默认用ROW_NUMBER最稳因为判题通常按严格Top3的行数来比对。如果题目要求播放量相同的歌曲都算进Top3那要改用RANK或DENSE_RANK具体用哪个取决于跳跃是否影响后续行。我在调试时会让DeepSeek帮忙生成一些并列数据的测试用例用实际输出来确认函数语义比自己脑内推演快得多。3. MySQL 8.0标准解法窗口函数一条SQL拿下3.1 完整SQL与可复现代码如果你的MySQL版本是8.0以上现在大部分云数据库默认都是8.0直接用窗口函数这是最简洁也最容易读懂的方案SELECT play_month, song_name, play_total, rn FROM ( SELECT DATE_FORMAT(p.play_time, %Y-%m) AS play_month, m.song_id, m.song_name, SUM(p.play_count) AS play_total, ROW_NUMBER() OVER ( PARTITION BY DATE_FORMAT(p.play_time, %Y-%m) ORDER BY SUM(p.play_count) DESC, MAX(p.log_id) ASC ) AS rn FROM play_log p INNER JOIN music_info m ON p.song_id m.song_id WHERE m.singer 周杰伦 GROUP BY DATE_FORMAT(p.play_time, %Y-%m), m.song_id, m.song_name ) t WHERE rn 3 ORDER BY play_month ASC, rn ASC;把前面那段建表和插入数据的代码跑完再执行上面这条SQL得到的结果应该和我列出的预期输出完全一致。为了方便自己验证我还习惯在最后加一行SELECT COUNT(*)对比行数——总行数应该是月份数 × 3。3.2 窗口函数逐段拆解PARTITION BY和ORDER BY各管什么很多人第一次看窗口函数觉得玄乎其实拆开就两件事。PARTITION BY负责划组把数据按月份切分成一块一块的独立区域ORDER BY负责组内排序在每个区域里按播放量从高到低排。ROW_NUMBER()就是按这个排序顺序给每行发一个从1开始的序号。有一个细节很多人会踩GROUP BY和窗口函数同时出现时窗口函数是在分组之后才计算的。所以我在OVER()里可以直接写SUM(p.play_count)这个聚合是在GROUP BY完成后得到的每行汇总值。这里要特别注意的是若要在排序里加平局打破条件不能直接引用p.log_id这种非聚合列ONLY_FULL_GROUP_BY模式下直接报错必须用MAX(p.log_id)包一层。我用这个trick保证同样播放量的歌曲排名是稳定的不会因为执行计划不同而飘。3.3 DATE_FORMAT的边界问题为什么不能省掉%Y-统计月份最容易犯的错是用MONTH(play_time)而不是DATE_FORMAT(play_time, %Y-%m)。MONTH()只返回月数字2024年1月和2025年1月会被算成同一个分组跨年数据直接串味。DATE_FORMAT带上%Y才能保证2024-01和2025-01是不同组。如果你喜欢用LEFT(play_time, 7)这种字符串截取也行效果一样。但无论用哪种原则只有一个分组键里必须包含年份除非题目明确只按自然月不分年。这一点我在第5章的踩坑实录里还会细讲因为它造成的错误非常隐蔽从单看某个月的结果根本发现不了。4. MySQL 5.7兼容方案老版本也得能打4.1 方案一关联子查询数前面有几个人如果你的环境是MySQL 5.7不少公司的老库仍然是5.7牛客网上也存在老版本判题环境用不了窗口函数就得绕路。最稳妥的思路是关联子查询对每个月的每首歌去数一数同月里有几首歌的播放量严格大于它。如果这个数字小于3那它就在Top3里。SELECT t.play_month, t.song_name, t.play_total FROM ( SELECT DATE_FORMAT(p.play_time, %Y-%m) AS play_month, m.song_id, m.song_name, SUM(p.play_count) AS play_total FROM play_log p INNER JOIN music_info m ON p.song_id m.song_id WHERE m.singer 周杰伦 GROUP BY DATE_FORMAT(p.play_time, %Y-%m), m.song_id, m.song_name ) t WHERE ( SELECT COUNT(*) FROM ( SELECT DATE_FORMAT(p2.play_time, %Y-%m) AS play_month2, p2.song_id, SUM(p2.play_count) AS play_total2 FROM play_log p2 INNER JOIN music_info m2 ON p2.song_id m2.song_id WHERE m2.singer 周杰伦 GROUP BY DATE_FORMAT(p2.play_time, %Y-%m), p2.song_id ) t2 WHERE t2.play_month2 t.play_month AND t2.play_total2 t.play_total ) 3 ORDER BY t.play_month ASC, t.play_total DESC;这套写法逻辑上是RANK的语义并列的歌曲都会被保留可能在边界上让某个月出现超过3行。它的优点是对MySQL版本没有任何要求缺点也明显——内层子查询要对每一行重复执行数据量一大性能就难看。刷题没问题生产环境慎用。4.2 方案二用户变量模拟行号MySQL 5.7的另一个经典招数是用户变量。思路是先把数据按月份播放量降序排好序然后用两个变量模拟上一行的月份和累计序号逐行扫描时发现月份变了就重置序号。SELECT play_month, song_name, play_total, rn FROM ( SELECT play_month, song_name, play_total, rn : IF(prev_month play_month, rn 1, 1) AS rn, prev_month : play_month AS dummy_col FROM ( SELECT DATE_FORMAT(p.play_time, %Y-%m) AS play_month, m.song_name, SUM(p.play_count) AS play_total FROM play_log p INNER JOIN music_info m ON p.song_id m.song_id WHERE m.singer 周杰伦 GROUP BY DATE_FORMAT(p.play_time, %Y-%m), m.song_id, m.song_name ORDER BY play_month ASC, play_total DESC ) a CROSS JOIN (SELECT rn : 0, prev_month : ) b ) c WHERE rn 3 ORDER BY play_month ASC, rn ASC;这里有个非常关键的坑内层派生表a的ORDER BY不能省用户变量是按行扫描顺序递增的顺序乱了排名就是错乱。同时rn的赋值表达式必须写在prev_month赋值之前保证先判断再更新上一个月。我在5.7上实测过这个写法结果和窗口函数版一致。不过说实话这种变量写法过于tricky可读性差如果是在面试中我建议优先讲关联子查询的思路因为更容易证明你理解原理。4.3 三种方案横向对比对比维度窗口函数(8.0)关联子查询(5.7)用户变量(5.7)可读性高中低性能最好最差较好版本要求8.05.7均兼容5.7并列语义按需选择函数RANK语义ROW_NUMBER语义推荐场景生产/新项目面试讲思路老库救急我的建议是新环境一律用窗口函数老库能升级尽量升级实在升不了数据量小用关联子查询数据量大才考虑用户变量。刷题阶段把三种都写一遍对理解SQL执行顺序的帮助是巨大的。5. 实操踩坑实录这些问题我真遇到过5.1 用MONTH()统计导致跨年串月这事发生在一次我给真实业务写周报SQL的时候当时要统计每个月Top3活动页点击歌曲。我图省事用了MONTH(play_time)分组跑出来1月、12月的数据总感觉不对劲后来一排查才发现前一年的12月和当年12月被合并成了一组播放量是两年前求和的结果。改用DATE_FORMAT(play_time, %Y-%m)之后问题立刻消失。做牛客网这题的时候也是同理判题数据如果包含跨年月份用MONTH()必挂。5.2 并列排名到底取谁判题环境默认不带并列我在本地测试时用过RANK()发现某个月有两首歌播放量一样结果那个月输出了4行。牛客网这道题的判题逻辑按我的经验是严格按行数比对的它期望每组固定3行。所以默认答案必须用ROW_NUMBER()万一并列靠MAX(p.log_id) ASC兜底排序保证每组不多不少正好3行。如果你自己探索时想看看并列场景可以让DeepSeek生成一段含同分数据的测试SQL然后分别跑ROW_NUMBER、RANK、DENSE_RANK三个版本对比输出差异。这种动手对比的方式比背文档印象深刻得多。5.3 GROUP BY漏字段ONLY_FULL_GROUP_BY教你做人MySQL 5.7之后默认开了ONLY_FULL_GROUP_BY模式SELECT里的非聚合列必须全部出现在GROUP BY里。我刚接触这道题时写过GROUP BY DATE_FORMAT(p.play_time, %Y-%m), m.song_name结果在本地直接报错因为SELECT里还带了m.song_id而它没在GROUP BY中出现。就算不报错如果两张不同歌曲同名概率低但不是零汇总也会串。正确姿势是GROUP BY里带上song_id作为唯一键song_name只是跟随展示。这个习惯要尽早养成到了生产环境数据量大、脏数据多的时候这个细节能救命。5.4 过滤条件放错位置WHERE和HAVING差出数量级有个优化细节值得单讲过滤歌手周杰伦应该放WHERE而不是HAVING。WHERE在分组聚合之前执行意味着只有周杰伦的歌进入聚合计算HAVING在分组之后执行所有歌手都先算完一遍再丢弃非周杰伦的行。数据量一大这两者的性能差距是指数级的。写完SQL之后我习惯用EXPLAIN看一眼执行计划。一个合格的分组TopN查询WHERE m.singer 周杰伦应该能在music_info表上走索引然后play_log只JOIN出需要聚合的少量行。如果你看到执行计划里先全表扫了play_log再做过滤那多半是索引缺失或者JOIN顺序有问题。这个检查习惯比刷一百道题都值。6. 从牛客网SQL40延伸出去一套通用TopN模板6.1 改需求怎么改每个歌手Top3、每周Top5都是同一套模板做透一道题的意义在于沉淀模板。把SQL40的核心骨架抽出来其实就四个可替换的变量分组维度、排名维度、过滤条件、TopN的N。需求变体PARTITION BYWHERE过滤N 值每个歌手每月Top3DATE_FORMAT(play_time,%Y-%m)无不滤歌手3周杰伦每周Top5DATE_FORMAT(play_time,%x-%v)singer周杰伦5每个城市销售额Top10city无10每门课成绩Top2course_id无2比如每个歌手每个月Top3只需要把WHERE删掉、PARTITION BY改成DATE_FORMAT singer的复合分组键每周Top5只需要把%Y-%m换成%x-%vISO周格式注意跨年问题。理解模板之后再刷牛客的其他SQL题你会发现很多题目都是换汤不换药。6.2 生产环境下的索引建议刷题不考虑性能但真要复用这套SQL到业务上索引得跟上。我给的建索引建议是music_info表(singer, song_id)联合索引。singer用于过滤song_id用于覆盖JOIN字段。play_log表(song_id, play_time, play_count)联合索引。song_id用于JOINplay_time用于日期范围/格式化分组play_count用于覆盖聚合计算避免回表。这样三列全在索引里查询就是一个覆盖索引扫描分组排序压测下来比裸表快一个数量级。实践时先建索引再看执行计划别凭感觉。6.3 用AI助手刷SQL题的正确姿势这个话题放在最后聊因为我觉得它比单道题更重要。现在很多人用DeepSeek这类AI助手刷题但用法天差地别。我最推荐的做法有三个第一让AI先讲解思路而不是直接要答案。比如我会问分组TopN为什么必须用子查询包一层窗口函数让它把执行顺序讲清楚比自己瞎试高效得多。第二让AI生成边界测试数据。SQL题的坑往往藏在边角数据里跨年、并列、空值、重复记录让AI按你的要求生成一份刁钻的数据集然后用标准答案去跑能快速验证自己对题目的理解。第三把AI当code reviewer。写完SQL后把自己的版本和标准答案一起丢给它让它指出逻辑差异和隐患。我在写第4章用户变量版本时就是靠它帮我排查了变量赋值顺序的坑。有一点要提醒AI偶尔会编造不存在的函数或错误语法尤其是涉及具体MySQL版本特性时。任何SQL都要自己跑一遍验证AI只能帮你加速思考不能替代判题环境。把AI当陪练而不是代打刷题收获会完全不同。这套分组TopN的思路我后来在业务里用到了好几个地方排行榜、热点内容精选、库存预警TopN……每次都能很快套上模板改出来。牛客网SQL40作为入门练习确实是好题但更值得带走的是它背后的解题框架。下次再看到每个XX的TopN类题目你也能一眼看穿它的结构了。

SQL分组TopN实现：窗口函数与DATE_FORMAT在每月歌曲排行中的应用

相关推荐

OLAP查询预测：事前治理慢查询与资源调度的实战指南

SpringBoot+Vue图书馆座位预约系统：从占座大战到高并发抢座实战

iOS 小米手环6第三方表盘刷写：auth_key提取与BLE鉴权

更多资讯

Diginex为什么收购碳核算平台Plan A · 青绿蓝

基于SpringBoot的新乡市流浪动物救助系统数据分析可视化大屏_5dpcz21t

蓝牛仔与百褶裙：亚洲成年时装的九种光线练习

HarmonyOS 7 FlexDesk：FoldStatus折叠态与编辑草稿保持

基于SpringBoot的减肥减脂训练营管理系统设计与实现_2nld341d

免费转换Word软件推荐！零基础轻松搞定各类文档转换

今日更新

基于sEMG与IMU的手语手势识别：从数据采集到实时部署避坑指南

装配车间MES落地指南：SimpleMES工单流转、BOM与齐套检查实战

AI获客怎样减少重复线索？意客AI的原文复用与版本筛选

本周精选

CMOS设计三大物理效应：体效应、沟道长度调制与亚阈值导电

微信小程序在线阅读毕设全解析：Java后端+MySQL+避坑实战

Android音频问题五层分析法：从App到Codec的系统化诊断

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？