深入解析SQL中ROW_NUMBER()与GROUP BY的本质区别及实战应用

1. 开头为什么我把这两个语法放在一起“吃透”做SQL开发的人几乎都会遇到一个诡异的场景明明只是想去个重用ROW_NUMBER()写出来的结果和用GROUP BY写出来的结果乍一看好像一样但细看却完全不同。更让人头疼的是面试的时候十个面试官有八个会问“ROW_NUMBER() OVER(PARTITION BY ...)和GROUP BY到底有什么区别”你要是只答一个“一个是窗口函数一个是聚合函数”基本等于白说。这篇内容不打算从教科书定义开始念经而是直接用业务场景、执行逻辑、实际案例把这两个语种的“底层思维”拆开揉碎。适合这几类人看刚入门想搞懂窗口函数的小白、写了好几年SQL但从来没深究过两者差异的开发、准备面试想把这题答得漂亮的学习者以及那些被“去重要不要用GROUP BY”“取每组前三条能不能用GROUP BY”这类问题反复折磨的实战派。先给一个最核心的结论GROUP BY是“压缩行数”的聚合操作ROW_NUMBER() OVER(PARTITION BY ...)是“不压缩行数”的窗口计算。这个结论后面我会用完整案例展开但请你先记住它因为整篇文章的所有细节都是在解释这句话到底意味着什么。2. 两种语法的底层执行逻辑一个“合并行”一个“标记行”要真正吃透区别必须先把这两个语法的执行逻辑掰开看。很多教程只给了语法格式没讲它们在数据库引擎内部到底对数据做了什么这才是很多人用错的关键原因。2.1 GROUP BY分组之后“多行变一行”GROUP BY执行的核心操作是“分组合并”。它对指定列中值相同的行进行分组然后在每个组内执行聚合函数比如SUM()、COUNT()、MAX()、MIN()、AVG()每个组最终只会输出一行结果。这里最关键的点是被GROUP BY的列之外的字段如果没被聚合函数包裹在标准的SQL语法下是不能直接出现在SELECT列表里的。MySQL有个著名的“宽容模式”允许你查出来但返回的值是随机的这在任何严谨的项目里都是隐患。想象一个订单表每个客户有多笔订单我们想统计每个客户的订单总额SELECT customer_id, SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id;这条SQL执行后每个customer_id只保留一行total_amount是组内所有订单金额的累加。原来那些订单明细数据在结果里已经“消失”了——它们被合并进了聚合值。所以GROUP BY的本质逻辑是三个字压缩、合并、汇总。2.2 ROW_NUMBER() OVER(PARTITION BY ...)分组之后“每行都在”ROW_NUMBER()是窗口函数家族的代表它做的事情完全不同。它根据PARTITION BY指定的列将数据划分为多个“窗口”也就是分区然后在每个分区内按ORDER BY指定的顺序为每一行分配一个从1开始的连续序号。划重点原始表的每一行在输出结果中都保留一条都不会丢失。窗口函数不改动行的数量只是在每一行旁边多了一列序号信息。比如还是那张订单表我们想给每个客户的订单按金额从高到低排名SELECT customer_id, order_id, order_amount, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_amount DESC) AS rn FROM orders;这条SQL执行后输出行数就等于订单表的行数每条订单都会有一行rn列显示的是“这条订单在其所属客户的所有订单中按金额排序是第几”。客户3有5笔订单就会得到5行rn分别可能是1、2、3、4、5。2.3 结构化对比一句话说清本质区别对比维度GROUP BYROW_NUMBER() OVER(PARTITION BY ...)函数类型聚合函数体系窗口函数体系对行数的影响每个分组压缩成一行行数完全不变能否访问明细数据不能明细已被合并可以所有明细保留执行阶段SQL逻辑顺序GROUP BY 在 SELECT 之前窗口计算在 SELECT 之后最终排序前和聚合函数配合本身是聚合操作可同时使用聚合函数典型用途分组统计、去重、汇总报表分组编号、Top N、去重保留一条、相邻行比较这个表格建议你收藏。不仅是背下来而是要理解背后的设计动机GROUP BY回答的是“每个组是什么样”ROW_NUMBER()回答的是“每一行在组里排第几”。3. 用真实业务场景看两者差异同一个需求两种SQL结果完全不同前面讲的是理论逻辑这一节我用一个完整的业务案例把差异“演”出来。这个案例覆盖了最常见的面试题和使用场景我建议你对着自己的数据库实际操作一遍比看十遍文章都管用。3.1 造一张测试表学生成绩表假设我们有一张学生考试成绩表student_scores字段如下student_id学生编号class_id班级编号subject科目score成绩先插入测试数据CREATE TABLE student_scores ( student_id INT, class_id INT, subject VARCHAR(20), score INT ); INSERT INTO student_scores VALUES (1, 1, 语文, 85), (2, 1, 语文, 92), (3, 2, 语文, 78), (4, 2, 语文, 88), (5, 3, 语文, 95), (1, 1, 数学, 90), (2, 1, 数学, 82), (3, 2, 数学, 91), (4, 2, 数学, 79), (5, 3, 数学, 88);现在我们有10条成绩记录分布在3个班级、2个科目、5个学生之间。3.2 需求一统计每个班级的平均分——必须用GROUP BY如果领导要的是“每个班级的平均成绩”这是一个典型的聚合需求。平均分必然把班级里的多条成绩合并成一个数值这只能用GROUP BYSELECT class_id, AVG(score) AS avg_score FROM student_scores GROUP BY class_id;执行结果class_idavg_score187.25284.00391.50注意看原来10行数据变成了3行。这就是GROUP BY的“压缩”特性。班级1有四条成绩两个学生各两科合并成了一个平均值87.25。3.3 需求二给每条成绩在班级内排名——必须用ROW_NUMBER()如果领导要的是“每条成绩在班级里的名次”注意这个需求——结果里还是要保留每一条成绩记录只是额外多了一列名次信息。这时GROUP BY根本不适用因为你没有要“合并”任何东西你要的是“给每一行编号”SELECT student_id, class_id, subject, score, ROW_NUMBER() OVER(PARTITION BY class_id ORDER BY score DESC) AS rank_in_class FROM student_scores;执行结果student_idclass_idsubjectscorerank_in_class21语文92111数学90211语文85321数学82453语文95153数学88232数学91142语文88210条数据依然全部在每条成绩旁边多了它在班级内的序号。这里有个很多人第一次用会惊讶的点班级1和班级3的rank编号都是从1重新开始的。这就是PARTITION BY的“分区独立编号”特性——每个分区单独排序、单独编号互不干扰。3.4 需求三找出每个班级分数最高的那条记录——经典的Row Number用法这个需求是面试高频题“取每个组的第一条/前N条”。很多新手第一反应是用GROUP BY MAX()然后发现拿不到完整记录的其他字段卡住了。GROUP BY的正确写法只能拿到最高分是多少拿不到“谁考了最高分”SELECT class_id, MAX(score) AS max_score FROM student_scores GROUP BY class_id;结果class_idmax_score192291395但你拿不到对应的student_id和subject。虽然可以用子查询或窗口函数来解决但最舒服的方式显然是ROW_NUMBER()SELECT student_id, class_id, subject, score FROM ( SELECT student_id, class_id, subject, score, ROW_NUMBER() OVER(PARTITION BY class_id ORDER BY score DESC) AS rn FROM student_scores ) t WHERE rn 1;先把每条记录在班级内排好序并编号再在外层过滤编号为1的记录——这就是“组内Top 1”的通用解法。班级2有两条记录都是最高分88这里只返回了其中一条因为ROW_NUMBER()的编号是唯一的不会出现并列。这个特性既好用也要注意如果你希望并列的都保留就要用RANK()或者DENSE_RANK()。3.5 差异复盘为什么同一个“分组”概念在两个语法里含义不同对比上面三个需求你会发现一个很有意思的现象GROUP BY和ROW_NUMBER()都涉及“分组”的概念但内涵完全不同。GROUP BY里的分组是“物理压缩”数据在组内被聚合组外字段消失结果是“每个组代表一行”ROW_NUMBER()里的PARTITION BY是“计算范围”它只是告诉窗口函数“在哪个范围内计算序号”数据本身没有任何变化结果还是“每一行都还在”。这就好比统计班级人数和给班级里的每个人发学号的区别前者最后只留下一个数字“45人”后者每个同学手里都拿到一张写着学号的纸条。这就是两者的本质差异。4. 到底什么是窗口函数不删行、不合并、逐行计算的“透视眼”既然ROW_NUMBER()是窗口函数的代表这一节把窗口函数的概念彻底讲清楚。理解了窗口函数就理解了为什么ROW_NUMBER()会有那些看起来“反直觉”的行为。4.1 窗口函数的工作方式窗口函数的标准执行流程是这样的在SQL的查询逻辑中窗口函数在WHERE、GROUP BY、HAVING都执行完之后在最终输出结果之前对当前的结果集做一次“扫描计算”。它可以看到三类范围的数据PARTITION BY分出的分区内的所有行当前行本身配合ORDER BY后可以按需扩大或缩小计算范围这就是滑动窗口不过本文不展开例如SUM(score) OVER(PARTITION BY class_id)这行代码的意思是不用GROUP BY而是直接在当前行旁边附加一列显示“这个班级所有成绩的合计”。每一行都能看到自己所属班级的合计值同时自己的数据还在。4.2 窗口函数家族的分类了解家族成员能帮你更好定位ROW_NUMBER()的位置。窗口函数主要分三类类别代表函数用途排序窗口函数ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()分组内排序、编号、分层聚合窗口函数SUM(), COUNT(), AVG(), MIN(), MAX() 搭配 OVER()在保持明细的同时显示聚合值分析窗口函数LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()前后行对比、首尾值提取ROW_NUMBER()属于第一类“排序窗口函数”它只做编号不做事后删除所以必须配合外层条件比如WHERE rn 1才能实现“取一条”的效果。4.3 为什么窗口函数在SQL逻辑顺序中排在最后要理解窗口函数为什么能保留所有行要回到SQL的逻辑执行顺序。标准SQL的逻辑执行顺序是FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT而窗口函数是在SELECT阶段计算的确切地说是在SELECT处理完普通的表达式之后、ORDER BY之前进行。这带来一个重要影响WHERE条件无法直接过滤窗口函数的结果。这就是为什么用ROW_NUMBER()取组内Top N时必须先把窗口函数写到子查询里再在外层用WHERE过滤而不是直接在WHERE后面写ROW_NUMBER() ... 1。因为执行WHERE的时候序号根本还没算出来。顺带说一个数据库兼容性提醒MySQL 8.0之后才完整支持窗口函数如果你的项目还在用MySQL 5.7想实现“组内Top N”就不得不走用户变量或者GROUP_CONCAT这类曲线救国方案。我见过很多老项目因为这个升级问题代码里写了一大堆极其绕的SQL就是在等8.0上线。如果你有机会做技术选型建议直接把MySQL 8.0作为底线版本否则窗口函数带来的简洁度红利都享受不到。5. 实战对比一去重场景GROUP BY 和 ROW_NUMBER() 到底该用谁去重是SQL开发中使用频率最高的需求之一也是GROUP BY和ROW_NUMBER()最容易让人纠结的场景。这里我把两种方案放在同一张表上来对比它们的优劣。5.1 场景设定订单表里有重复数据假设有一张订单表orders因为上游数据重复同步导致同一个订单号出现了多次SELECT order_id, customer_id, order_amount FROM orders;查询结果里有大量重复的order_id现在需要清理。5.2 方案一GROUP BY 去重如果你只需要“不重复的订单号”用GROUP BY非常简单SELECT order_id, MAX(customer_id) AS customer_id, MAX(order_amount) AS order_amount FROM orders GROUP BY order_id;这样每个order_id保留一行。但注意一个痛点如果三个字段需要保持同一行的关联关系比如某个order_id的重复行里customer_id和order_amount可能并不相同那么用MAX()拼出来的结果可能是“跨行”的——比如customer_id取了第一行的值order_amount取了第二行的值这在实际数据有差异时会造成逻辑错误。5.3 方案二ROW_NUMBER() 去重推荐如果你需要完整保留“每个订单号里最新的一条完整记录”ROW_NUMBER()是更稳的方案SELECT order_id, customer_id, order_amount FROM ( SELECT order_id, customer_id, order_amount, ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 1;这个写法的逻辑是先按order_id分区在每个分区里按create_time倒序排列给每一行标记序号序号为1的就是每组最新的一条记录。因为所有字段都来自同一行所以不会出现“跨行拼数据”的问题。5.4 选型建议判断维度GROUP BY 去重ROW_NUMBER() 去重去重后保留的字段只能保留分组列 聚合字段可以保留完整字段值的关联性可能跨行拼接一定是同一行数据执行效率通常更快有排序开销稍慢适用场景只要分组列去重、不关心其他字段需要保留完整明细或按某个规则选一条我的经验是去重的核心问题不只是“去掉重复”而是“保留哪一行”。如果业务没有特殊要求只是统计去重后的数量用GROUP BY最直接如果必须保留完整的某一行ROW_NUMBER()是这个场景下的首选。还有一个去重场景值得说单纯的SELECT DISTINCT在字段多时会生成笛卡尔式的组合去重容易在字段语义理解上出偏差。比如想按user_id去重但SELECT了user_id, user_name, user_email三个字段如果user_email有历史变更DISTINCT会把三个字段视为一个整体结果反而留下了重复的user_id。这是很多人踩过的坑。6. 实战对比二分组统计场景窗口函数的聚合能力也被低估了很多人以为窗口函数只能用来编号取Top N实际上把聚合函数和OVER()组合使用能做出不少GROUP BY做不到的报表效果。这一节专门讲这个点。6.1 普通聚合与窗口聚合的并存有一个非常经典的需求明细列表上同时展示“总数”。比如订单列表每行都是订单明细最后一列要显示“当前用户所有订单的总金额”。用GROUP BY的常规思路是先汇总再关联回明细SELECT o.*, t.total_amount FROM orders o LEFT JOIN ( SELECT customer_id, SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id ) t ON o.customer_id t.customer_id;这个写法没问题但要写子查询、要JOIN代码阅读起来不够直观。如果用窗口聚合函数一行搞定SELECT order_id, customer_id, order_amount, SUM(order_amount) OVER(PARTITION BY customer_id) AS user_total_amount FROM orders;每一行都保留明细同时在旁边显示出该客户所有订单的合计。这就是前面说的“不合并行的聚合”——明细和汇总出现在同一行而且不需要任何JOIN。这种写法的好处是当你同时需要明细、汇总、占比时窗口聚合函数可以避免多次子查询。比如再加一列“该订单占客户总额的百分比”SELECT order_id, customer_id, order_amount, SUM(order_amount) OVER(PARTITION BY customer_id) AS user_total_amount, order_amount * 1.0 / SUM(order_amount) OVER(PARTITION BY customer_id) AS pct FROM orders;6.2 GROUP BY表现最好的场景即便掌握了窗口函数GROUP BY依然有自己的主场。以下场景用GROUP BY更合适最终结果需要每个分组一行比如“每个类别的商品数”需要和HAVING配合过滤聚合结果比如“订单总额超过1000的客户”需要COUNT(DISTINCT ...)这类去重计数窗口函数做去重计数也可以但写法繁琐、性能开销大结果为结果集较小、需要喂给报表系统做维度汇总时6.3 一个“能同时用”的复合场景有些需求是两者结合才能发挥最大威力。比如查出每个班级总分最高的学生并且附带班级总人数。第一步用SUM()窗口函数算出每个学生的总分第二步用ROW_NUMBER()在班级内排序第三步再用COUNT()窗口函数统计班级人数。SELECT student_id, class_id, total_score, class_student_count FROM ( SELECT student_id, class_id, SUM(score) OVER(PARTITION BY student_id) AS total_score, ROW_NUMBER() OVER(PARTITION BY class_id ORDER BY SUM(score) OVER(PARTITION BY student_id) DESC) AS rn, COUNT(*) OVER(PARTITION BY class_id) AS class_student_count FROM student_scores ) t WHERE rn 1;注意这种写法嵌套较多实际生产环境可以先在子查询里把“每个学生的总分”算好再在上层做班级排名性能更稳定。不过这个例子说明了一个趋势窗口函数不是来取代GROUP BY的它是来补充GROUP BY做不到的那部分需求的。两者的关系不是“谁比谁强”而是“各自解决什么问题”。6.4 性能思考什么时候窗口函数会拖慢查询窗口函数虽然写起来爽但它不是免费的有两个主要的开销点需要心里有数PARTITION BY和ORDER BY需要排序或哈希操作大数据量下会消耗较多内存和临时磁盘空间窗口聚合的计算结果是在最终输出前“展开”的也就是说它会为每一行都重复计算一次分区聚合值所以遇到千万级以上的大表时用窗口函数要特别注意尽量在子查询里先用WHERE缩小数据范围再在外层做窗口计算。另外窗口函数的PARTITION BY列如果能与索引匹配排序代价会小很多。我处理过一个慢SQL加了窗口函数之后执行时间从200毫秒涨到2秒排查后发现是PARTITION BY的列没有索引创建索引后回到300毫秒。7. 其他窗口函数与 ROW_NUMBER() 的对比RANK、DENSE_RANK、LAG 到底怎么选既然讲到窗口函数这一节把ROW_NUMBER()的几个“兄弟”一并理清因为面试和实战里几乎总是连着问。如果你只知道ROW_NUMBER()不知道另外几个遇到“并列排名”只能拍脑袋。7.1 三种排序函数的差异同样是给成绩排序三个函数的处理方式完全不同函数相同值排名编号是否连续举例成绩90,90,85ROW_NUMBER()强制排定先后1, 2, 390→1, 90→2, 85→3RANK()相同值并列为同一名次之后跳号1, 1, 390→1, 90→1, 85→3DENSE_RANK()相同值并列为同一名次之后连续1, 1, 290→1, 90→1, 85→2实际SQLSELECT student_id, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS row_num, RANK() OVER(ORDER BY score DESC) AS rank_no_gap, DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank_no_gap FROM scores;选型建议数据库表主键或业务上不允许并列时用ROW_NUMBER()比赛排名这种“并列占两个名次”的语义用RANK()如果并列不占名次且希望编号连续用DENSE_RANK()。我这里举个最容易记的例子两个人都考了90分后面一个人考了85分用RANK()第三名是空缺的第二名被两位90分并列占了下一个直接是第三名但用DENSE_RANK()85分就是第二名。7.2 LAG/LEAD相邻行比较神器ROW_NUMBER()的核心是编号但如果你想要“和上一条数据比较”比如环比涨跌就要用LAG()和LEAD()。SELECT order_date, amount, LAG(amount, 1) OVER(ORDER BY order_date) AS prev_amount, amount - LAG(amount, 1) OVER(ORDER BY order_date) AS diff FROM daily_sales;这个函数组合用来做连续登录天数判断、同比环比计算都极其方便。这里只作延伸介绍不展开细讲但知道它的存在能帮你在设计查询时多一个候选方案。7.3 什么时候“排名”用不上窗口函数窗口函数的排序有一个隐含条件结果集必须保留所有行才能编号。如果你根本不需要明细行只需要“每个分组下的Top N的前N名是谁”建议配合子查询使用后外层再用LIMIT或者WHERE条件收敛结果。另外要说明一点ORDER BY放在OVER()里面和放在查询最外层语义完全不同前者是给窗口函数用的分区内排序后者是结果集的最终展示顺序。两者不是必须一致的但很多新手习惯性写成一致导致明明想要“按成绩排名的结果”却因为外层没有ORDER BY而看到了无序的数据继而怀疑是ROW_NUMBER()出了问题。8. 常见误区与避坑指南我把这些年踩过的坑集中告诉你写SQL窗口函数这些年我踩过不少坑也见过团队里很多人反复踩同样的坑。这一节把高频问题集中盘点每一个都有真实场景直接给结论和修正方案。8.1 误区一用 HAVING 过滤窗口函数结果有同事写了这样的SQLSELECT student_id, ROW_NUMBER() OVER(PARTITION BY class_id ORDER BY score DESC) AS rn FROM student_scores HAVING rn 1;结果直接报错。因为HAVING的执行顺序在窗口函数之前此时rn这个列根本不存在。正确做法一定是子查询包装一层再过滤。8.2 误区二PARTITION BY 列太多导致分区过碎PARTITION BY不是越多越好它会把数据切成很多小块每个小块独立排序开销是乘数级的。如果你的分组列选择不当比如用了一个存在大量唯一值的时间戳字段那么每个分区只有一行编号永远是1完全失去意义。反过来说如果你确实需要“每个小时每个用户”的粒度那分区别无选择但要注意控制数据范围。8.3 误区三混淆 COUNT() OVER() 和 GROUP BY 的 COUNT()SELECT COUNT(*) OVER() AS total_count FROM orders;这个写法会给每行都带上订单总数而不是把订单按某种维度合并。如果只想查总数写SELECT COUNT(*) FROM orders就够了不用窗口函数给自己增加没必要的复杂度。8.4 误区四忽略NULL排序ROW_NUMBER() OVER(ORDER BY score DESC)遇到score为NULL时NULL会被排到什么位置取决于数据库实现。MySQL里NULL在升序时排在最前降序时排在最后。如果你不希望NULL被当成第一名需要加上NULLS LAST之类的语法MySQL 8.0.30之后的版本支持NULLS LAST或者提前用COALESCE处理。8.5 误区五以为 ROW_NUMBER() 能直接“取前几条”再次强调窗口函数的结果是“附加列”而不是“过滤行”。取Top N必须借助外层子查询SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY class_id ORDER BY score DESC) AS rn FROM student_scores ) t WHERE rn 3;这是“每组前三条”的标准答案。没有其他更优雅的简写至少主流数据库里没有。8.6 效率陷阱窗口函数处理大表时的内存压力当PARTITION BY分区的数据量非常大时数据库可能把中间结果放到临时表或磁盘上导致SQL执行时间变长。我的习惯是在大表上使用窗口函数前先确认是否有必要用能不能用GROUP BY 关联代替能缩小数据范围就先用WHERE过滤把窗口计算的数据集压到可控范围针对PARTITION BY列建索引尤其是分区列与ORDER BY列组合索引收益明显我曾经优化过一个报表SQL原本对全量一年的订单数据约三千万行做ROW_NUMBER()排名查询跑了快十分钟。后来把数据范围先在子查询里压缩到最近一周加了组合索引后跑到了30秒以内。SQL优化没有银弹但减少数据量永远是最有效的方向。8.7 面试高频场景边答边演示面试官问“ROW_NUMBER()和GROUP BY”时最好的回答结构是先说本质区别窗口函数逐行编号不合并行聚合函数分组压缩行 再各举一个典型场景Top N vs 分组汇总 再说明两者可以配合使用先聚合再排名 最后用一句类比收尾GROUP BY是“把一群人变成一个平均值”ROW_NUMBER()是“给人群里的每个人发序号人还是那么多人”。9. 最后的实操心得写这篇文章时我翻了很多自己以前写过的SQL发现一个有意思的事早年间我几乎没怎么用过窗口函数所有“取每条记录在组内的序号”的需求都是用变量、子查询或者多条SQL拼出来的代码又长又脆改一个排序条件就要动好几处。后来彻底吃透了ROW_NUMBER() OVER(PARTITION BY ...)之后才意识到所谓的“难度”其实只在于没有先把“聚合”和“计算”这两个概念分开。如果你现在正在学习这块内容我的建议是不要只看文章一定要动手建一张小表插入个位数的行把这篇文章里的每个案例自己跑一遍然后试着改一改条件看看结果怎么变。等你亲眼看到“行数不变但多了编号列”和“行数被压缩成一个汇总值”的差别这个知识点就真正内化为你自己的东西了。另外如果你的项目还在用MySQL 5.7认真考虑一下升级到8.0或更高版本。窗口函数带来的代码可读性提升是巨大的很多需要子查询变量的老写法都可以用几行干净的窗口函数替代。等你习惯了这种写法再看回老的SQL会有一种“以前是怎么忍过来的”感觉。