GROUP BY 与窗口函数：为什么每组前几名必须换一种写法

上一节结尾留了一个没解决的问题：LIMIT 3 只能对整张结果表裁剪，取不出“每个城市金额最高的前 3 笔”。取不出来的原因，其实不是 LIMIT 不够用，而是 在它之前的那一步，你想留的行已经被压掉了。这篇把这一步讲清楚：GROUP BY 和窗口函数（OVER (PARTITION BY …)）看着都在“分组”，但一个会减少行数，一个不会；分清了这一点，“每组前几名”该怎么写、什么时候根本不用窗口函数，就有了判断依据。

材料还是那 9 行 orders（MySQL 8.0）：

先看清楚：两个查询各吐出几行

想拿“每个城市金额最高的那一笔订单，要 order_id、city、amount 三列”，第一反应是拿聚合函数硬凑：

1SELECT order_id, city, MAX(amount)
2FROM orders
3GROUP BY city;

MySQL 8.0 直接拒绝，报 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.orders.order_id' which is not functionally dependent on columns in GROUP BY clause 3。

很多人把这条报错当成语法洁癖，觉得数据库太死板。其实它拦住的东西就是本篇的核心：GROUP BY 的输出单位是“组”，一行代表一组。 9 行按 city 分完只有 4 组，输出就只能是 4 行。这 4 行里，city 还有意义（它就是分组依据），MAX(amount) 还有意义（它是一个算出来的数），但 order_id 没有意义了——“杭州那一组”里三笔订单的 order_id 是 1001、1003、1005，压成一行之后该显示哪一个？数据库没法替你选，所以干脆报错。

换句话说：GROUP BY 想要每组一个数，就必须放弃组内每一行的身份。

换一条路：不压缩，而是给每行记一个名次

窗口函数的做法完全不同。它不压缩，而是 在每一行旁边另开一列，写上“你这一堆里排第几”：

1SELECT order_id, city, amount,
2       ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
3FROM orders
4ORDER BY city, rn;

输出还是 9 行，只是多了一列 rn：

现在有了名次，“每组第一名”就是一个普通的筛选条件：留下 rn = 1 的行。1003、1006、1009、1004 正好四行，每城一笔，明细字段一个没丢。

PARTITION BY 到底切了什么

OVER (PARTITION BY city ORDER BY amount DESC) 里两个部分各干一件事：

• PARTITION BY city 决定 把行切成哪几堆，切法和 GROUP BY city 一模一样——同城的一堆，city 为 NULL 的自成一堆。
• ORDER BY amount DESC 决定 堆内怎么排队，名次就按这个队形数。

这条名次只在本堆内有效：上海的第一名是 210.00，不是全表第一；杭州的名次和上海的互不干扰。如果想看全表排名，把 PARTITION BY 整段去掉、只留 ORDER BY amount DESC，那时 9 行就是同一堆。

rn 因此不是“某组的属性”，而是“某一行的属性”。这正是它能留在 9 行里的原因，也是它和 MAX(amount) 的本质区别：MAX 的答案属于组，rn 的答案属于行。

顺带说清执行顺序，它解释了你马上会碰到的那个报错。窗口计算发生在 WHERE、GROUP BY、HAVING 之后，ORDER BY、LIMIT 之前 1。也就是说，窗口函数看到的行，已经是筛选、分组之后剩下的行了。

名次不能直接写在 WHERE 里

直觉上想这么写：

1SELECT order_id, city, amount,
2       ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
3FROM orders
4WHERE rn = 1;   -- 报错

MySQL 报 ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.。原因就是上面那条顺序：WHERE 在窗口之前执行，那时候 rn 还不存在；而且 MySQL 明确规定窗口函数只能出现在 SELECT 列表和 ORDER BY 里 1。

正确做法是先把带名次的结果当成一张表，再在外面筛。这正是派生表和 WITH 的用处：

1WITH ranked AS (
2  SELECT order_id, city, amount,
3         ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
4  FROM orders
5)
6SELECT order_id, city, amount
7FROM ranked
8WHERE rn = 1
9ORDER BY amount DESC;

结果四行：1003 杭州 320.00、1006 上海 210.00、1009 北京 199.00、1004 城市为空 76.00。

注意最后这一行：city 为空的那 2 行也会被当成一个正常的分组，拿到自己的“第一名”。这跟 GROUP BY 把 NULL 归成一组的做法一致。如果“城市未知”根本不该算一个城市，就在内层加 WHERE city IS NOT NULL 筛掉，不要指望窗口函数替你处理这个问题。

并列时要几个人：ROW_NUMBER、RANK、DENSE_RANK

上面这张表里金额没有重复，所以三个排名函数结果一样。真实数据一定有并列，此时它们就分道扬镳了。假设把 1001 的金额改成 320.00，杭州这堆变成 320.00、320.00、45.00：

• ROW_NUMBER() 是按队形发的序号，永远 1、2、3，重复不了。并列的两行谁拿 1 谁拿 2，没有约定——这和上一篇讲的 LIMIT 1 碰上并列时“拿到哪一行不稳定”是同一件事 3。
• RANK() 是名次，并列占同一个名次，但会跳号，所以下一行是 3。
• DENSE_RANK() 也是名次，并列同名次，但不跳号，下一行接着数 2。

选哪个取决于业务要几个人。只要一个代表，用 ROW_NUMBER()，但要在 OVER 里补一个能唯一区分行的排序键（比如再加 , order_id DESC），否则同样不稳定。并列都算第一、都要交出去，用 RANK()。只是想按金额分档看层级，用 DENSE_RANK()。

这也给出一个容易踩的坑：用 WHERE rn = 1 说“每城第一笔”，碰上并列时会悄悄丢掉一笔；如果报表要“所有并列第一”，就必须换成 WHERE rk = 1。

什么时候根本用不着窗口函数

这是两个方法真正重叠的地方，得如实说。

如果你要的只是 每城最高金额这个数，不需要那笔订单的 order_id，GROUP BY 就够了，而且更简单：

1SELECT city, MAX(amount) AS max_amount
2FROM orders
3GROUP BY city;

四行，每城一个数，没有信息损失，因为你要的本来就只有组级别的数字。

判断依据只有一句：你需要不需要带着那一行的其它字段。 需要 order_id、下单时间、客户名这些“属于某一行而不是某一组”的东西，就只能让行活下来，用窗口函数；只需要一个汇总值，就用 GROUP BY，别为了显得高级硬套窗口。

还有一种折中写法：先用 GROUP BY 求出每城最大值，再按 city 和 amount 把它 JOIN 回原表找对应的行。结果能拿到，但多写一层，还要额外处理并列时一个城市匹配出多行的问题。窗口函数把“算名次”和“拿明细”放在同一次扫描里，这是它值得单独学一遍的理由。

至于“每组前 3 名”，只要把条件从 rn = 1 改成 rn <= 3，代码不用重写。这一点 LIMIT 3 永远做不到，因为它只认识整张结果表的前 3 行，不知道有“组”这回事。

排名之外，窗口函数还常用来在每行旁边显示本城合计（SUM(amount) OVER (PARTITION BY city)）和拿上一行算环比（LAG）。它们和排名共用同一个 OVER 骨架，下一篇接着写。