# 窗口函数

> 对行进行排名，并计算累计值、相邻值和窗口帧。

---

LLMS 索引： [llms.txt](/llms.txt)

---

窗口函数使用一个*窗口*为每一行计算一个值——窗口由 `OVER (...)` 子句定义，是一组相关行。`GROUP BY` 会折叠行，而窗口函数会在原始列旁边为每一行添加一个结果。排名、累计值、移动平均、百分位区间、与“上一行”比较等场景，都适合使用窗口函数；凡是过去需要在应用代码中通过排序和迭代完成的任务，窗口函数都可以胜任。

## 本章要点 {#learn}

- 窗口函数的形式：`f() OVER (PARTITION BY ... ORDER BY ...)`
- 最有用的函数：`row_number`、`rank`、`dense_rank`、`lag`、`lead`、`sum`、`avg`、`first_value`、`last_value`
- `OVER` 内的分区与排序
- 使用 `WINDOW name AS (...)` 复用窗口
- 窗口帧子句：`ROWS BETWEEN ... AND ...`

## 示例 {#example}

```sql {num="39.1" caption="窗口函数"}
-- Numbering rows within each partition
SELECT customer_id, id AS order_id, total,
       row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
       rank()       OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk
FROM orders_example
ORDER BY customer_id, rn;

-- "Most expensive order per customer" — keep rn = 1
WITH ranked AS (
  SELECT *,
         row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders_example
)
SELECT customer_id, id, total FROM ranked WHERE rn = 1;

-- lag / lead: look at previous / next row in the partition
SELECT customer_id, id, total,
       lag(total)  OVER (PARTITION BY customer_id ORDER BY id) AS prev_total,
       lead(total) OVER (PARTITION BY customer_id ORDER BY id) AS next_total
FROM orders_example
ORDER BY customer_id, id;

-- Running total (a sum is also a window function)
SELECT customer_id, id, total,
       sum(total) OVER (PARTITION BY customer_id ORDER BY id) AS running_total
FROM orders_example
ORDER BY customer_id, id;

-- Reusable window
SELECT id, total,
       sum(total) OVER w AS running,
       avg(total) OVER w AS running_avg
FROM orders_example
WINDOW w AS (PARTITION BY customer_id ORDER BY id)
ORDER BY customer_id, id;

-- Frame clause: 3-row trailing average
SELECT id, total,
       avg(total) OVER (
         PARTITION BY customer_id
         ORDER BY id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS trailing_avg
FROM orders_example
ORDER BY customer_id, id;
```

窗口由三个部分定义：

1. **`PARTITION BY`** ——将行拆分成相互独立的组。与 `GROUP BY` 类似，但每个组都会保留其中的行。
2. **`ORDER BY`** ——对每个分区内的行排序。它决定 `row_number`、`lag`、累计值等结果。
3. **窗口帧子句**（可选）——`ROWS BETWEEN ... AND ...` 或 `RANGE BETWEEN ...`。它定义当前行周围哪些行属于窗口。不同函数族的默认值不同：排名函数会忽略窗口帧；带有 `ORDER BY` 时，默认窗口帧包含从分区开头到当前行所在同等行组末尾的所有行；不带 `ORDER BY` 时，窗口覆盖整个分区。

`row_number`、`rank` 和 `dense_rank` 在存在并列时的差异很重要。`row_number` 总是分配 1、2、3、……，并列时任意决定先后。`rank` 分配 1、2、2、4（并列后有间断）。`dense_rank` 分配 1、2、2、3（没有间断）。

“每组取前 N 行”模式（`PARTITION BY group_col ORDER BY rank_col DESC + filter rn = 1`）是 `row_number` 的典型用法。它替代了繁琐的自连接和子查询。

> [!NOTE] 示例数据
> 第 38 章归档了第 3 个订单。本章输出沿用该章归档后的状态，目前剩下两个订单。重新运行第 29 章会恢复原来的三个订单。

## 运行示例 {#run}

运行方式（需要 `orders_example`）：

```text
$ psql -f source/window-functions.sql postgres
 customer_id | order_id | total | rn | rnk
-------------+----------+-------+----+-----
           1 |        1 | 50.00 |  1 |   1
           1 |        2 | 30.00 |  2 |   2
(2 rows)
 ...
```

## 常见陷阱 {#pitfalls}

- 窗口函数不能出现在 `WHERE` 或 `HAVING` 中（它们在这些子句*之后*计算）。请把查询包在 CTE 或子查询中，再在外层过滤。
- `sum() OVER (ORDER BY ...)` 的默认窗口帧是 `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`——`ORDER BY` 值相等的同等行会被计入同一个累计值。若要严格按行累计，请使用 `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`。
- 如果 `OVER` 内没有 `ORDER BY`，`lag` / `lead` 会使用未指定的行顺序，PostgreSQL 也不会发出警告。请指定排序顺序和任何用于打破并列的排序键。
- 性能：窗口函数会对分区排序。如果在 `(partition_col, order_col)` 上有匹配的索引，就可以跳过排序。

> [!TIP] 提示
> “显示相对于上一行的变化”可写为 `total - lag(total) OVER (ORDER BY ...)`。“显示占总数的百分比”可写为 `100.0 * total / sum(total) OVER ()`。两者都用一个易读的表达式替代自连接或子查询。

> [!NOTE] 动手练习
> 使用 `ntile(4)` 把每个订单分到四分位组：`ntile(4) OVER (ORDER BY total)`。然后在时间序列表中，用 `lag(daily_total) OVER (ORDER BY day)` 计算“今天的总额与昨天的总额”。最后试试 `first_value(total) OVER (PARTITION BY customer_id ORDER BY id)`，看看每一行中显示的该客户首笔订单金额。

## 下载 {#downloads}

SQL 文件：[window-functions.sql](/examples/source/window-functions.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)