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

GROUP BY 为什么只能输出一组一行、窗口函数为什么能保留明细，名次为什么不能写进 WHERE，以及并列时该用 ROW_NUMBER 还是 RANK

> 窗口函数 · GROUP BY · 聚合函数 · 约 7 分钟 · 11 月 01 日

## 本篇要点

1. `GROUP BY` 的输出单位是“组”，9 行按城市分成 4 组就只能输出 4 行；`order_id` 这类行级别的字段在压缩后失去意义，MySQL 8.0 的 ERROR 1055 拒绝的正是这件事。
2. 窗口函数不压缩行：`ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC)` 在 9 行旁边各加一个组内名次，行数不变。
3. `PARTITION BY` 负责切堆（和 `GROUP BY` 切法相同，NULL 自成一堆），`OVER` 内的 `ORDER BY` 负责堆内排队。
4. 窗口计算发生在 WHERE、GROUP BY、HAVING 之后，ORDER BY、LIMIT 之前，所以名次不能写进 WHERE，要套一层 CTE 或子查询再筛。
5. 并列时 `ROW_NUMBER` 给 1、2、3（谁先不确定），`RANK` 给 1、1、3，`DENSE_RANK` 给 1、1、2；用 `rn = 1` 会悄悄丢掉并列的那一笔，要全留就换 `RANK`。
6. 判断标准是需不需要带着那一行的其它字段：只需要组级别的汇总值就用 `GROUP BY`，要带着明细字段才用窗口函数。
7. 把 `rn = 1` 改成 `rn <= 3` 就是“每组前 3 名”，这是 `LIMIT` 结构性做不到的事。

---

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

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

| order_id | city | amount |
|---|---|---|
| 1001 | 杭州 | 129.00 |
| 1002 | 上海 | 58.50 |
| 1003 | 杭州 | 320.00 |
| 1004 | NULL | 76.00 |
| 1005 | 杭州 | 45.00 |
| 1006 | 上海 | 210.00 |
| 1007 | 上海 | 91.50 |
| 1008 | NULL | 19.90 |
| 1009 | 北京 | 199.00 |

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

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

```sql
SELECT order_id, city, MAX(amount)
FROM orders
GROUP 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` 想要每组一个数，就必须放弃组内每一行的身份。**

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

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

```sql
SELECT order_id, city, amount,
       ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
FROM orders
ORDER BY city, rn;
```

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

| order_id | city | amount | rn |
|---|---|---|---|
| 1004 | NULL | 76.00 | 1 |
| 1008 | NULL | 19.90 | 2 |
| 1009 | 北京 | 199.00 | 1 |
| 1006 | 上海 | 210.00 | 1 |
| 1007 | 上海 | 91.50 | 2 |
| 1002 | 上海 | 58.50 | 3 |
| 1003 | 杭州 | 320.00 | 1 |
| 1001 | 杭州 | 129.00 | 2 |
| 1005 | 杭州 | 45.00 | 3 |

现在有了名次，“每组第一名”就是一个普通的筛选条件：留下 `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 里

直觉上想这么写：

```sql
SELECT order_id, city, amount,
       ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
FROM orders
WHERE 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](https://pangzhengboyin.com/articles/derived-table-vs-cte-with-f6d774bc) 的用处：

```sql
WITH ranked AS (
  SELECT order_id, city, amount,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT order_id, city, amount
FROM ranked
WHERE rn = 1
ORDER 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()` | `RANK()` | `DENSE_RANK()` |
|---|---|---|---|---|
| 1003 | 320.00 | 1 | 1 | 1 |
| 1001 | 320.00 | 2 | 1 | 1 |
| 1005 | 45.00 | 3 | 3 | 2 |

- `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` 就够了，而且更简单：

```sql
SELECT city, MAX(amount) AS max_amount
FROM orders
GROUP BY city;
```

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

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

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

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

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

## 思维导图

```mermaid
flowchart LR
  A["每组前几名"] --> B["GROUP BY：输出组"]
  A --> C["窗口函数：输出行"]
  B --> B1["9 行按城市压成 4 行"]
  B --> B2["order_id 失去意义 → ERROR 1055"]
  B --> B3["只要汇总值就用它"]
  C --> C1["OVER (PARTITION BY city ORDER BY amount DESC)"]
  C1 --> C2["PARTITION BY：切堆，NULL 自成一堆"]
  C1 --> C3["ORDER BY：堆内排队"]
  C3 --> C4["ROW_NUMBER / RANK / DENSE_RANK 处理并列"]
  C --> D["名次不能写进 WHERE"]
  D --> D1["套一层 CTE 再筛 rn = 1 或 rn <= 3"]
```

## 术语表

- 窗口函数：在保留每一行的情况下，用一组“和这一行同堆的其它行”算出一个值，写在它旁边，不减少行数。
- OVER 子句：写在函数后面的括号，规定这一行的“同堆”怎么划、堆内怎么排队，是窗口函数的开关（聚合函数加上它就是窗口用法）。
- PARTITION BY：在 `OVER` 里把行切成若干堆，切法与 `GROUP BY` 相同；不写就整张结果算一堆。
- ROW_NUMBER：按堆内队形发序号，永远 1、2、3，并列也不例外，因此并列时谁在前没有约定。
- RANK 与 DENSE_RANK：名次函数，并列占同一名次；`RANK` 之后跳号，`DENSE_RANK` 不跳号。

## 来源

1. [MySQL 8.0 Reference Manual: Window Function Concepts and Syntax — 窗口函数只允许出现在 SELECT 列表和 ORDER BY 中，以及窗口计算的执行位置](https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html)
2. [MySQL 8.0 Reference Manual: Window Function Descriptions — ROW_NUMBER、RANK（有间隔）、DENSE_RANK（无间隔）的定义](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html)
3. [MySQL 8.0 Reference Manual: Server SQL Modes — ONLY_FULL_GROUP_BY 与 ERROR 1055 的来源](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html)
4. [PostgreSQL Documentation: Window Functions — 官方教程，可对照另一种数据库里同样的写法](https://www.postgresql.org/docs/current/tutorial-window.html)

---

原文：https://pangzhengboyin.com/articles/group-by-vs-window-functions-top-n-per-group-ce5379fc

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具：每篇 5–10 分钟，边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com