# 窗口函数 · 不合并行的分组统计

> 窗口函数为每一行计算「它在所属分组里的排名 / 相对值 / 累计」，却不像 GROUP BY 那样把行合并掉。排名、环比同比、累计销售、每组 Top N、滑动平均——这些「以为得写代码循环」的活，一条 SQL 就干完了。

- 作者：David（道雾轩）
- 专栏：PostgreSQL 特性详解（https://daiw.org/manual/postgresql.md）
- 最后更新：2026-07-13
- 原文：https://daiw.org/manual/postgresql/window-functions
- 转载与引用：请注明出处并附原文链接（https://daiw.org/about/copyright）

# 窗口函数 · 不合并行的分组统计

`GROUP BY` 有个天生的限制：它把每一组**压成一行**。所以你要么拿明细、要么拿汇总，没法在一行里**既看到明细，又看到它在组里的位置**。

窗口函数(Window Function)就是来补这个缺口的：它对每一行计算「在一个窗口（分组）里，这一行的排名 / 相对值 / 累计」，但**保留所有明细行**。排名、环比、累计、每组 Top N、滑动平均——这些经典需求，它一招通吃。

## OVER：窗口函数的核心语法

窗口函数 = 一个函数 + 一个 `OVER (...)` 子句。`OVER` 里两件事最关键：`PARTITION BY`（按什么分组）和 `ORDER BY`（组内按什么排序）。

```sql
-- 每个 region 内,按金额从高到低编号
SELECT region, month, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales;
```

```text
East  m3 140 rn=1     North m2 150 rn=1     South m3 220 rn=1
East  m2 120 rn=2     North m3 130 rn=2     South m1 200 rn=2
East  m1  80 rn=3     North m1 100 rn=3     South m2 180 rn=3
```

注意：每个 region 各自从 1 开始编号，而**所有明细行都还在**。这就是窗口函数和 `GROUP BY` 的根本区别。

## 四类常用窗口函数

**① 排名类** —— `ROW_NUMBER`（严格 1,2,3）、`RANK`（并列跳号 1,1,3）、`DENSE_RANK`（并列不跳 1,1,2）、`NTILE(n)`（分成 n 桶）。

**② 偏移类** —— `LAG` / `LEAD` 取前 / 后 N 行的值，做环比同比的利器：

```sql
SELECT region, month, amount,
       LAG(amount)  OVER w AS prev,
       LEAD(amount) OVER w AS next
FROM sales WINDOW w AS (PARTITION BY region ORDER BY month);
```

```text
North m1 amount=100 prev=∅   next=150
North m2 amount=150 prev=100 next=130
North m3 amount=130 prev=150 next=∅
```

**③ 聚合当窗口** —— 把 `SUM` / `AVG` / `MAX` 加上 `OVER`，就成了「累计 / 分组内聚合但不合并行」：

```sql
-- 累计销售额(从组首累加到当前行)
SUM(amount) OVER (PARTITION BY region ORDER BY month
                  ROWS UNBOUNDED PRECEDING)
-- North: 100 → 250 → 380
```

**④ 滑动窗口** —— 用**框架(frame)** 限定「参与计算的行范围」，算移动平均：

```sql
-- 3 个月滑动平均(前 1 行 + 当前 + 后 1 行)
AVG(amount) OVER (PARTITION BY region ORDER BY month
                  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
-- North: 125.0 → 126.7 → 140.0
```

<Callout type="info">
  框架有 `ROWS` 和 `RANGE` 两种，别混：`ROWS` 按**物理行数**算（「前 1 行」就是紧邻的上一行）；`RANGE` 按 `ORDER BY` 列的**值**算（「前 1」指值差在范围内的所有行，并列值会被算作一体）。做「精确的 N 行滑窗」用 `ROWS`；做「按金额 / 时间的值区间累计」用 `RANGE`。不写框架时，有 `ORDER BY` 的默认框架是 `RANGE UNBOUNDED PRECEDING`——这常常不是你以为的那个，踩过坑的人都记得。
</Callout>

## 经典套路：每组 Top N

「每个 region 卖得最好的那一单」，用窗口函数 + 子查询是标准解法——先编号，再过滤：

```sql
SELECT region, month, amount FROM (
  SELECT region, month, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
) t WHERE rn = 1;
-- East: m3=140 | North: m2=150 | South: m3=220
```

把 `rn = 1` 改成 `rn <= 3` 就是「每组 Top 3」。这个「窗口编号 + 外层过滤」的套路，是日常查询里出现频率极高的一招，值得背下来。（数据量大时，PG 还有更快的 `DISTINCT ON` 或 `LATERAL` 写法，见 [LATERAL 一章](https://daiw.org/manual/postgresql/lateral-grouping)。）

## 横向对比

| 能力 | PostgreSQL | MySQL | Oracle | SQL Server |
| --- | --- | --- | --- | --- |
| 窗口函数 | PG 8.4 起完整支持 | **8.0 才有**（5.x 没有） | 很早就有 | 2005 起 |
| 框架 `ROWS` / `RANGE` | 齐全 | 8.0+ | 齐全 | 齐全 |
| `GROUPS` 框架模式 | PG 11+ | 8.0+ | 有 | 部分 |

窗口函数是 SQL 标准，如今主流库都支持。值得记住的是：**MySQL 直到 8.0(2018)才有窗口函数**——在那之前，MySQL 用户算个「组内排名」都得靠用户变量玩 hack，而 PostgreSQL 从 8.4(2009)就完整支持了。这十年差距，是很多老 MySQL 项目里那些绕来绕去的排名 SQL 的由来。

## 应用场景

- **排行榜 / 分组排名**：每个品类销量前十、每个班级成绩排名。
- **环比同比**：`LAG` / `LEAD` 取上期数据，当场算增长率。
- **累计与占比**：累计销售额、`amount / SUM(amount) OVER ()` 算每行占总量的比例。
- **去重保留每组最新**：`ROW_NUMBER() ... ORDER BY updated_at DESC` 编号后取 `rn = 1`。
- **滑动平均 / 趋势平滑**：监控指标、股价、传感器读数的移动平均。

## 小结与下一章

窗口函数的精髓是「**算出每行在组里的位置，但不丢明细**」。掌握 `PARTITION BY` + `ORDER BY` + 框架这三件套，排名、环比、累计、Top N、滑窗就都是一条 SQL 的事，不用把数据捞到应用层再循环。

下一章看两个「进一步撑大查询表达力」的特性：**LATERAL** 让子查询能引用左表的列（每组 Top N 的另一种更快写法），**GROUPING SETS** 让一条 SQL 同时输出多种粒度的小计与总计。👉 [LATERAL 与 GROUPING SETS：把「每组取 N」和「透视表」写进一条 SQL](https://daiw.org/manual/postgresql/lateral-grouping)。

## 参考文献

- [1] PostgreSQL 官方文档.「3.5. Window Functions(Tutorial).」https://www.postgresql.org/docs/current/tutorial-window.html —— 概念入门。
- [2] PostgreSQL 官方文档.「4.2.8. Window Function Calls.」https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS —— `OVER`、`PARTITION`、框架语法。
- [3] PostgreSQL 官方文档.「9.22. Window Functions.」https://www.postgresql.org/docs/current/functions-window.html —— `row_number` / `rank` / `lag` / `lead` / `ntile` 等内置函数。