1. 先别急着背语法先分清它们在解决哪两类问题干了这么多年数据相关工作我见过太多人在 ROW_NUMBER() OVER(PARTITION BY ...) 和 GROUP BY 之间摇摆不定。面试题里出现的频率也足够高SQL 窗口函数、SQL 面试题、数据库 sql 这些热词背后基本都逃不开这两个知识点。但你如果只是把语法背下来换一个场景照样出错。真正要把它们“吃透”第一步不是背而是先搞清楚它们各自在解决哪类问题。举两个业务问题你就明白了。第一个问题统计每个部门的平均工资这是典型的“按部门汇总”答案应该是每个部门一行总结记录比如“研发部 avg_salary18000”。第二个问题列出每个部门里工资最高的那个人注意这里要的不是平均值而是具体到某个人输出里要有 emp_name、salary 这些原始明细字段。同样是“每个部门”第一个问题的结果行数会大幅减少第二个问题的结果行数会等于甚至超过原始数据量。到底是用 GROUP BY 还是用 ROW_NUMBER() OVER(PARTITION BY ...)本质上就是在回答“你最终是要聚合后的组统计值还是要带着全员明细的分组序号”。这个定位准了后面所有细节都是锦上添花。1.1 找准核心差异行折叠与行保留我给你一个一直用的口诀GROUP BY 是“行折叠”ROW_NUMBER() OVER(PARTITION BY ...) 是“行保留”。怎么理解“行折叠”你把一张员工表按部门分组每个组里本来有 20 个人GROUP BY 会把这一组压成一条摘要记录。你只能通过聚合函数来表达组内信息比如 COUNT(*)、SUM(salary)、MAX(salary)那些没有被分组的原始列像 emp_name直接消失因为它们不是组级别的属性。而 ROW_NUMBER() OVER(PARTITION BY ...) 做的事情完全不同。PARTITION BY 的意思是“按某个维度切窗口”它切了窗口但绝不会把窗口里的行合并成一行。它只是在每个窗口内部给每一行编一个序号原始数据一条不少地留在结果集里。所以你在 SELECT 里仍然能输出 emp_id、emp_name、salary 这些明细字段只是额外多了一列组内序号。这就解释了为什么“分组取前 N”这种需求几乎都要靠窗口函数你要先分组又不想丢明细只有“切窗口 编号”能同时满足两个条件。1.2 从业务语言到 SQL 语言的翻译我建议你把“分组”这个词暂时从脑子里拿走换成两套更精确的描述如果你听到“按某维度统计”“汇总”“合计”“均值”核心诉求是得到一个压缩后的汇总表优先考虑 GROUP BY。如果你听到“按某维度排序编号”“每个部门的 Top 3”“每个用户最近一条订单”“取每组最大 id 的那行”核心诉求是“保留原始行 组内排序”此时就要上 ROW_NUMBER() OVER(PARTITION BY ...)。语言翻译对新手格外重要。很多时候不是不会写 SQL而是没搞清楚业务要的是“组”还是“行”。举个例子运营要“每个渠道的付费转化人数”这是 GROUP BY 的活产品要“每个渠道付费转化率最高那一名用户”这是 ROW_NUMBER 的活。同一个数据源同一个“每个渠道”写法完全不同这就是 SQL 最容易产生“看似会了、动手就错”的地方。2. 深入 GROUP BY把多行压成一行并且必须让聚合函数说话2.1 GROUP BY 的分组逻辑与 SQL 边界先看一段常见的部门工资统计SELECT dept, COUNT(*) AS emp_cnt, ROUND(AVG(salary), 2) AS avg_salary FROM emp GROUP BY dept;执行时数据库会先按照 dept 把源数据分成若干个“桶”每个桶内部有多少行都没关系输出时每个桶只贡献一行。那 SELECT 子句里到底能放哪些列规则很死要么是你写在 GROUP BY 里的列要么是被聚合函数包裹的列。这条边界很多人在刚学的时候都犯迷糊。其实背后的逻辑很自然部门“研发部”这个桶里有张三、李四、王五三个人如果 SELECT 里直接写 emp_name数据库根本不知道该显示哪一个人的名字所以它只能拒绝执行。你只能通过 MAX(emp_name)、COUNT(emp_name) 这种聚合函数把三个人“变成一个值”。这里还要补充一个常见场景GROUP BY 跟多个字段一起用。比如GROUP BY dept, channel它不再按单个维度分组而是按“部门 渠道”的组合维度分组。分组粒度越细输出组数就可能越多但每组仍然是一行。很多人把“GROUP BY 多个字段”误解成“按多个字段分别分组”这是不对的它本质上是按一个复合键去分组两个字段共同决定一个桶。2.2 曾经流行的“GROUP BY 去重”是怎么来的又有哪些坑在老一点的 MySQL 版本里存在一种很野的写法SELECT * FROM emp GROUP BY dept;这种写法在没有开启 ONLY_FULL_GROUP_BY 的 MySQL 5.7 上是能跑出结果的。dept 相同的多条记录里数据库随手挑一条展示剩下的全部丢弃。很多人把它当成“SQL 语句去重”的快捷键来用但这是典型的错误示范。第一挑出来的那条记录不是你可以控制的查了三次可能三次结果都不一样第二从 SQL 标准的角度看这种行为根本没有被规范定义过属于数据库网开一面。如果你确实想实现“按部门去重后保留每组某一条”稳妥的做法是先找到每组的“目标主键”再回表取明细。比如保留每个部门里 emp_id 最小的那条记录SELECT e.* FROM emp e JOIN ( SELECT dept, MIN(emp_id) AS keep_id FROM emp GROUP BY dept ) t ON e.emp_id t.keep_id;这里面真正用到的 GROUP BY 是“找每组最小的 emp_id”而不是直接拿着源表瞎分组。用这种思路做去重结果可预测行为也规范。等到你学了 ROW_NUMBER() OVER(PARTITION BY ...)会发现同样的场景还能有更优雅的解法这部分我放到后面讲。3. 拆穿 ROW_NUMBER() OVER(PARTITION BY ...)不删行只给每行发序号3.1 语法拆开看三个部分各管什么事窗口函数其实是一块很容易入门但很难用好的内容。ROW_NUMBER() 的完整写法一般长这样ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY salary DESC )三个部分各有分工ROW_NUMBER() 是函数本体它负责生成一个从 1 开始递增的整数PARTITION BY dept 定义“窗口边界”意思是按 dept 把整个结果集切成多个互不相干的小窗口每个窗口里的编号都从 1 重新开始ORDER BY salary DESC 定义窗口内部的排序规则排序越靠前的行拿到的编号越小。PARTITION BY 也可以省略。不写它就是把全部结果集当成一个整体的大窗口ROW_NUMBER() 会对全表数据统一编号。比如ROW_NUMBER() OVER(ORDER BY create_time DESC)就是给全表按创建时间倒序编号这在很多分页、取全表最新一条场景里很常用。有一个细节很多人会忽略ORDER BY 决定编号顺序但它同时也会引入排序成本。你在写窗口函数时partition 和 order by 两部分的字段直接影响数据库要不要为这次计算额外排序。后面聊性能时我会展开这里先记住一个原则窗口里的 ORDER BY 不只是语法摆设它真的会让数据库去排序。3.2 用一张员工表演示窗口编号的过程假设我们有这样一张 emp 表CREATE TABLE emp ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), dept VARCHAR(10), salary INT ); INSERT INTO emp VALUES (1, 张三, 研发部, 20000), (2, 李四, 研发部, 18000), (3, 王五, 研发部, 15000), (4, 赵六, 市场部, 16000), (5, 钱七, 市场部, 12000);查询每个部门内部按工资从高到低的编号SELECT emp_name, dept, salary, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn FROM emp;结果是emp_namedeptsalaryrn张三研发部200001李四研发部180002王五研发部150003赵六市场部160001钱七市场部120002注意看数据行数没有变化还是 5 行。只是在“研发部”这个窗口里张三排第 1李四排第 2王五排第 3到了“市场部”这个新窗口编号又从 1 开始。这种“窗口内独立编号”的行为就是 PARTITION BY 和 GROUP BY 最直观的区别。如果你想找每个部门工资最高的人只要在这条查询外面包一层过滤掉 rn 1 的行就行。如果你直接写 WHERE rn 1那就会触发“窗口函数不能用在 WHERE”的报错至于为什么下一章我会拿 SQL 的执行顺序专门讲。3.3 顺带认识它的兄弟RANK 和 DENSE_RANK讲 ROW_NUMBER 时经常会被问到它和 RANK、DENSE_RANK 的区别。这三兄弟长得像但面对并列值时行为完全不同。函数并列时如何处理示例工资 200/100/100/80 的编号ROW_NUMBER并列值也要强行分先后1, 2, 3, 4RANK并列值占同一个名次后续名次跳过1, 2, 2, 4DENSE_RANK并列值占同一个名次后续名次不跳过1, 2, 2, 3实际使用中ROW_NUMBER 更适合用来做“每组保留一行”这种行级筛选因为你总能拿到 1、2、3……这样连续不重复的编号。RANK 和 DENSE_RANK 更适合做“真实排行榜”因为并列的两个人本来就该排在同一个位置。4. 两者真正在一起工作时三种高频实战组合4.1 场景一取每组最新一条记录这是我在实际项目里遇到最多的需求。订单表里一个客户有多条订单现在要取每个客户最近的一笔订单也就是“每个分区的最新行”。标准做法是先用 ROW_NUMBER() 给每个客户窗口内的订单按时间倒序编号再过滤出 rn 1。WITH ranked AS ( SELECT order_id, customer_id, order_amount, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_time DESC) AS rn FROM orders ) SELECT order_id, customer_id, order_amount FROM ranked WHERE rn 1;这类写法在订单、日志、流水表里非常常见比如“每个渠道最近一次投放”“每个商品最近一次调价”“每个会员最近一期账单”。它本质上是在做“先分组排序再取组内第一名”但数据行一条都不会被 GROUP BY 折叠掉。这里有个关键点rn 这个列是在 SELECT 阶段生成的所以你不能直接在它所在的同一层 WHERE 里用。必须先把它放到子查询或 CTE 里再在上一层过滤。这就是我常说的“包一层”套路。很多初学者在这里反复报错报错信息一般类似“Unknown column rn in where clause”本质就是没理解窗口函数的作用时机。4.2 场景二先 GROUP BY 聚合再对聚合结果做窗口编号这两个函数真正的配合点在于窗口函数可以在 GROUP BY 做完之后才执行。也就是说你可以对聚合后的结果再进行排名。比如先算每个部门的工资总额再按总额排名SELECT dept, SUM(salary) AS total_salary, ROW_NUMBER() OVER(ORDER BY SUM(salary) DESC) AS dept_rank FROM emp GROUP BY dept;这条 SQL 的执行顺序是先把 emp 表按部门 GROUP BY得到每个部门一行总计记录然后窗口函数再针对这堆“部门汇总行”做全表编号。所以你会看到每个部门只有一条汇总记录上面带上一个按总额排列的序号。这种写法的妙处在于窗口函数可以直接引用 SUM(salary) 这个聚合表达式因为它执行时聚合已经算完了。如果你把 PARTITION BY dept 也加进去反而会出问题因为 GROUP BY 之后每个部门只剩一行部门窗口里只有一个编号 1排名瞬间失去意义。4.3 场景三用窗口函数优雅地去掉重复数据传统 SQL 语句去重除了 DISTINCT最常见的就是“保留重复组内指定的一行”。比如用户表按 email 重复了需要保留 emp_id或 user_id最小的那条其余删除。用窗口函数可以写得非常干净DELETE FROM users WHERE user_id NOT IN ( SELECT keep_id FROM ( SELECT user_id AS keep_id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY user_id) AS rn FROM users ) t WHERE rn 1 );内层查询为每个 email 分区里的用户按 user_id 从小到大编号rn 1 的就是需要保留的那一个。外层拿到这些保留 id再把不在列表里的删掉。相比“GROUP BY 取 min(id) 再 JOIN”的老办法这个写法更直接也更容易扩展成“保留最大 id”“保留最新创建的那条”。不过要提醒一句如果你的排序列或字段里有 NULLNOT IN可能会带来意外结果实际工程里我更推荐先把保留的 id 集合查出来确认一遍再用DELETE ... JOIN去处理。SQL 语句去重这块的坑十有八九出在“你以为删掉了结果 NULL 把整个条件带崩了”。4.4 到底该用谁一张决策速查表需求类型首选方案原因统计每组行数、总额、均值GROUP BY需要组级别的聚合值列出每组明细并给组内排名ROW_NUMBER() OVER(PARTITION BY ...)保留原始行同时分组编号取每个分组的最新/最大/最小一条ROW_NUMBER() OVER(PARTITION BY ...)窗口编号后过滤 rn1先统计汇总再对汇总排名GROUP BY 外包 ROW_NUMBER窗口函数在聚合之后执行简单去重只看不删SELECT DISTINCT不涉及原始明细保留去重且保留某一特定行ROW_NUMBER 包一层过滤可精确控制保留规则可以看出来两者根本不是二选一的替代关系更像是“先折叠”和“先编号”两种不同思路。复杂报表里经常是把它们串成一条流水线先用 GROUP BY 做汇总再用窗口函数对汇总做排名最后用 WHERE 过滤出需要的名次区间。5. 原理深挖执行顺序、分区键、排序键这些细节决定成败5.1 SQL 的书写顺序不等于执行顺序SQL 里最容易让人栽跟头的就是书写顺序和逻辑执行顺序不一致。一个 SELECT 语句的逻辑处理顺序大致是这样的FROM / JOIN确定数据源WHERE过滤行GROUP BY分组HAVING过滤分组SELECT计算投影列窗口函数在这里执行DISTINCT去除重复ORDER BY排序LIMIT/TOP限制返回行数窗口函数排在 WHERE 之后、ORDER BY 之前执行这就回答了很多人的困惑为什么 WHERE 里不能用别名 rn为什么 ORDER BY 里却可以因为执行到 WHERE 的时候rn 还没生成执行到 ORDER BY 的时候SELECT 已经算完rn 已经存在了。这条顺序也解释了另一个常见错误。有些人想“先排除掉部分行再编号”直接把过滤条件加到 WHERE 里这是对的但如果你在 WHERE 里用 rn 1就会报错。正确的姿势永远是内层完成编号外层用 WHERE 过滤编号结果。这个“包一层”的模式是窗口函数用得熟不熟的试金石。5.2 分区键和排序键选不好编号就是一堆随机数PARTITION BY 后面的字段决定了“在哪里重新开始编号”ORDER BY 后面的字段决定了“窗口内谁先谁后”。这两组键选错了结果会很奇怪。先说分区键。如果你需要每个部门一个排名就必须 PARTITION BY dept如果你把分区键定得太细比如加上 emp_id 这种几乎每条记录都不同的字段每个窗口基本只有一行数据那 ROW_NUMBER() 永远返回 1整个功能就丧失了。反过来如果分区键定得太粗又会出现本应分开排名的记录被混在一起编号。再说排序键。只写 ORDER BY salary DESC 时如果两条记录的 salary 完全一致数据库会给出什么样的先后顺序标准答案是“不确定”。不同数据库可能按物理存储顺序、索引顺序甚至并行线程完成顺序来决定谁拿 1 谁拿 2。业务上如果不允许这种随机性你就要给 ORDER BY 补充唯一键比如ROW_NUMBER() OVER( PARTITION BY dept ORDER BY salary DESC, emp_id ASC )这样即使工资并列也能根据 emp_id 让结果变得确定且可复现。这个细节在数据清洗任务里特别重要因为你删除的是“排在后面”的行一旦排序不稳定你都不知道自己到底删掉的是哪条。5.3 NULL 值排序、数据库方言这些隐性差异还有一个容易忽略的点NULL 在排序时到底排前还是排后不同数据库实现不一样。Oracle 里默认 NULL 最大倒序时会排在最后SQL Server 里默认 NULL 最小正序时会排在最前PostgreSQL 还额外提供了 NULLS FIRST / NULLS LAST 让你自己控制。如果你在一个包含 NULL 的排序字段上使用 ROW_NUMBER()编号结果可能跟预期差别巨大。比如你要“按最后登录时间倒序给每个用户排名”没有登录过的用户login_time 为 NULL在 SQL Server 里可能直接排到最前面得到编号 1这通常不是你想要的。处理办法很简单要么先 WHERE 过滤掉 NULL要么在 ORDER BY 里显式写NULLS LAST如果你的数据库支持要么用COALESCE(login_time, 1900-01-01)这类函数把 NULL 兜成一个极小值。窗口函数的跨数据库差异虽然不致命但会让你在 MySQL、SQL Server、Hive SQL、Oracle 之间来回切换时踩上莫名其妙的坑。我的原则是凡是涉及排名、分组保留一行的场景一定先确认目标数据库对窗口函数和 NULL 排序的默认行为再动手。6. 高频报错场景与我的排查套路6.1 这些报错信息你大概率见过报错/现象根因正确处理包含 group by 的查询报错提示 select 中某列不是聚合列GROUP BY 之后还想直接输出非分组明细列把那列加入 GROUP BY或用聚合函数包裹提示找不到 rn / 未知列WHERE 里用了 SELECT 阶段才生成的窗口函数别名子查询或 CTE 包一层再在外面过滤MySQL 5.7 不支持窗口函数版本过低没有 ROW_NUMBER 能力升级到 MySQL 8.0或用户变量模拟窗口编号删除重复数据时误删大量记录排序字段有 NULL或 NOT IN 子查询结果含 NULL先查保留集合确认再改用 JOIN 删除同一分区的并列值编号每次执行都变ORDER BY 缺少唯一性字段补充一个主键或高基数业务字段作次级排序键加了窗口函数后查询变得特别慢分区排序产生额外排序文件评估是否需要先缩小区间以及检查能否借用索引这张表算是我过去这些年把常见问题浓缩出来的版本。实际排查时先看报错信息里提到的“字段名”在 SQL 的执行顺序中是否已经存在如果用这个思路至少能解决一半的语法问题。6.2 一个印象深刻的排序性能陷阱最后分享一个我实际遇到的案例。当时在一个千万级订单表上做“每个用户最近一笔有效订单”我顺手就写了SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_time DESC) AS rn FROM orders WHERE status 有效 ) t WHERE rn 1;本地测试数据量小速度很快。上线后一跑数据库就出现明显的临时文件和文件排序整个查询慢了几倍。原因并不难理解窗口函数要对亿级数据按 customer_id 分区、再按 order_time 排序这个操作几乎无法用普通索引直接覆盖数据库只能大量排序和落盘。我的调整思路不是避开窗口函数而是尽量在进入窗口计算前减少数据规模。比如先把清洗条件status、时间范围等全部下沉到内层 WHERE如果业务上能接受再加一个时间窗口限制把分区数量控制在合理范围内。这类优化做下来查询耗时通常会成倍下降。其实窗口函数本身不是洪水猛兽它只是要求你付出排序成本。理解它的执行时机、分区边界、排序稳定性这三点再配合长期积累的排查意识基本就能把这张 SQL 技能板上最常考的钉子钉牢了。