数据库基础体系 · 第 71/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQL 窗口函数：分区、排序、Frame、排名、累计和间隔分析

窗口函数（window function）用于：在保留明细行的同时，针对“当前行可见的一组相关行”进行计算。

它与普通聚合函数的根本区别是：

-- 普通聚合：多行变成一行
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

-- 窗口聚合：仍然保留每一行
SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

第一条语句按客户压缩结果集；第二条语句把客户总额附加到每一笔订单上。

窗口函数最容易出现的问题，不是函数名称记错，而是没有区分以下几个概念：

1. 分区（partition）：哪些行互相参与计算；
2. 排序（order）：当前行在分区中的位置；
3. Frame：当前行实际看到的连续窗口范围；
4. 排名规则：并列值如何处理；
5. 累计和移动计算：Frame 如何决定累计或滑动范围；
6. 间隔分析：当前行与前后行之间的关系如何建立。

下文以 PostgreSQL 当前稳定版本和 MySQL 8.4 的公开语义为范围。除特别说明外，示例使用两者都支持的基础窗口语法；涉及 GROUPS、EXCLUDE 等差异时会明确标注。

---

一、先建立窗口函数的形式化模型

对一个查询结果集，窗口计算可以抽象为：

$$W(r) = F(\text{Frame}(r))$$

其中：

• $r$ 是当前行；
• PARTITION BY 决定当前行所属的分区；
• ORDER BY 决定分区内部的顺序；
• Frame(r) 是当前行对应的 Frame；
• $F$ 是窗口函数，例如 SUM、AVG、ROW_NUMBER 或 LAG。

更具体地说，窗口定义：

OVER (
    PARTITION BY partition_columns
    ORDER BY order_columns
    frame_clause
)

可以拆成三个问题：

1. 当前行属于哪个分区？

PARTITION BY customer_id

表示不同客户之间互不影响。客户 A 的订单不会参与客户 B 的累计金额。

如果省略 PARTITION BY，所有结果行属于同一个分区。

2. 当前行在分区内排第几？

ORDER BY order_date, order_id

它决定：

• ROW_NUMBER() 的编号顺序；
• LAG() 和 LEAD() 的前后关系；
• 累计和的推进顺序；
• Frame 中“当前行”的位置。

3. 当前行能看到哪些行？

这由 Frame 决定。例如：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

表示从分区第一行到当前行。

而：

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

表示当前行及前两行。

注意：排序决定位置，Frame 决定可见范围；二者不是同一个概念。

---

二、窗口函数与普通聚合、子查询、CTE 的关系

2.1 普通聚合会改变结果粒度

假设有以下订单：

order_id	customer_id	order_date	amount
101	A	2024-01-01	100
102	A	2024-01-02	80
103	B	2024-01-01	50

普通聚合：

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

结果只有两行：

customer_id	total_amount
A	180
B	50

窗口聚合：

SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS total_amount
FROM orders;

结果仍有三行：

order_id	customer_id	amount	total_amount
101	A	100	180
102	A	80	180
103	B	50	50

因此，窗口函数特别适合：

• 明细行与分组汇总同时展示；
• 每行占比；
• 组内排名；
• 累计值；
• 与上一行、下一行比较。

2.2 窗口函数的逻辑位置

简化后的逻辑处理顺序可以理解为：

FROM / JOIN
WHERE
GROUP BY
HAVING
窗口函数计算
DISTINCT
ORDER BY
LIMIT

这带来一个重要限制：窗口函数通常不能直接出现在同一层查询的 WHERE 中。

错误示例：

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;

WHERE 阶段发生在窗口函数计算之前，rn 尚不存在。

正确做法是使用子查询或 CTE：

WITH ranked AS (
    SELECT
        employee_id,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT employee_id, salary
FROM ranked
WHERE rn <= 3;

这也是窗口函数与 CTE 的典型结合方式：先产生窗口结果，再在外层筛选。

2.3 聚合可以作为窗口函数的输入

下面的查询先按客户和日期聚合，再对每日金额做累计：

WITH daily AS (
    SELECT
        customer_id,
        order_date,
        SUM(amount) AS daily_amount
    FROM orders
    GROUP BY customer_id, order_date
)
SELECT
    customer_id,
    order_date,
    daily_amount,
    SUM(daily_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM daily;

这里有两个不同层次：

1. GROUP BY 把订单明细变成客户-日期粒度；
2. 窗口 SUM 在每日粒度上进行累计。

如果业务要求“每日累计”，必须先明确是否需要这个聚合层。直接在订单明细上累计，结果含义是“每笔订单累计”，不是“每天累计”。

---

三、准备一个完整示例数据集

以下示例在 PostgreSQL 16+ 和 MySQL 8.4 中使用的基础语法相同。示例假设：

• PostgreSQL 使用普通事务和 MVCC 快照；
• MySQL 使用 InnoDB 表；
• 查询在一个语句快照内读取数据；
• 不讨论跨多个查询的实时一致性；
• 生产环境应显式确认时区、日期类型和事务隔离级别。

建表示例：

CREATE TABLE sales (
    sale_id     INTEGER PRIMARY KEY,
    customer_id VARCHAR(10) NOT NULL,
    sale_date   DATE NOT NULL,
    amount      DECIMAL(10, 2) NOT NULL
);

插入数据：

INSERT INTO sales (sale_id, customer_id, sale_date, amount) VALUES
(1, 'A', DATE '2024-01-01', 10.00),
(2, 'A', DATE '2024-01-01', 20.00),
(3, 'A', DATE '2024-01-02', 5.00),
(4, 'A', DATE '2024-01-04', 40.00),
(5, 'B', DATE '2024-01-01', 7.00),
(6, 'B', DATE '2024-01-03', 8.00);

在 MySQL 中，DATE '2024-01-01' 这种标准日期字面量在常见场景下可用；为了避免客户端 SQL 模式或兼容设置造成差异，也可以写成：

'2024-01-01'

---

四、PARTITION BY：分区不是物理分区表

4.1 分区的含义

SUM(amount) OVER (
    PARTITION BY customer_id
)

表示每个客户单独计算总额。

查询：

SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_total
FROM sales
ORDER BY customer_id, sale_date, sale_id;

结果：

sale_id	customer_id	sale_date	amount	customer_total
1	A	2024-01-01	10.00	75.00
2	A	2024-01-01	20.00	75.00
3	A	2024-01-02	5.00	75.00
4	A	2024-01-04	40.00	75.00
5	B	2024-01-01	7.00	15.00
6	B	2024-01-03	8.00	15.00

这里的“分区”是窗口计算中的逻辑分组，不等于：

• PostgreSQL 的表分区；
• MySQL 的分区表；
• 操作系统中的物理分片；
• 执行计划中必然产生的独立并行任务。

PARTITION BY 不会自动把数据存储为多个物理分区。它只是规定了窗口函数不能跨越哪些边界。

4.2 分区列可以有多个

PARTITION BY customer_id, product_id

表示按照客户和产品的组合划分窗口。

组合键中的任意一列变化，都会进入新的窗口分区。

4.3 分区为空时的语义

如果写：

SUM(amount) OVER ()

则整个输入结果集是一个分区，且没有窗口排序。对每一行而言，窗口聚合通常看到整个分区。

---

五、ORDER BY：窗口内部排序与最终输出排序是两件事

窗口中的排序：

SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
)

决定窗口计算顺序。

查询末尾的排序：

ORDER BY customer_id, sale_date, sale_id

决定最终结果展示顺序。

两者相互独立。下面的查询中，窗口按金额排序，但最终结果按日期展示：

SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, sale_id
    ) AS amount_rank_order
FROM sales
ORDER BY customer_id, sale_date, sale_id;

5.1 排序不唯一会产生并列行

如果只写：

ORDER BY sale_date

则同一客户同一天的两笔销售属于同一个排序值。它们是 peers，即排序键相同的并列行。

对于某些函数，并列关系是定义的一部分；对于另一些函数，结果顺序可能不确定。

生产查询通常应该在需要稳定顺序时追加唯一键：

ORDER BY sale_date, sale_id

这不是为了改变业务排名，而是为了明确同一天内部的处理顺序。

5.2 NULL 排序具有引擎差异

空值参与窗口排序时，必须注意默认规则：

• PostgreSQL：升序默认将 NULL 排在非空值之后，降序默认排在前面；
• MySQL：升序通常将 NULL 视为小于非空值，因而排在前面。

需要统一语义时，应显式写排序规则。例如 PostgreSQL 支持：

ORDER BY event_time ASC NULLS LAST

MySQL 没有完全相同的 NULLS LAST 语法，通常使用表达式：

ORDER BY (event_time IS NULL), event_time

---

六、Frame：窗口函数真正看到的行范围

6.1 Frame 的基本结构

Frame 通常写成：

ROWS BETWEEN frame_start AND frame_end

例如：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

边界含义：

• UNBOUNDED PRECEDING：分区开始；
• n PRECEDING：当前行之前的第 n 个位置；
• CURRENT ROW：当前行；
• n FOLLOWING：当前行之后的第 n 个位置；
• UNBOUNDED FOLLOWING：分区结束。

常见形式：

-- 从分区开始到当前行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 当前行及前两行
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

-- 当前行前后各一行
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- 整个分区
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

BETWEEN 省略时，常见简写是：

ROWS UNBOUNDED PRECEDING

它等价于：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

6.2 ROWS、RANGE、GROUPS 的区别

这是窗口函数中最需要精确区分的部分。

ROWS：按物理行位置

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

它按排序后的行位置计算。即使两行的排序值相同，它们仍然是两行。

RANGE：按排序值范围

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

它按排序键的值确定范围，并且通常会把与当前行排序值相同的 peer 行一起纳入。

GROUPS：按 peer group 数量

GROUPS 按排序值相同的 peer group 计数，而不是按行计数。

例如：

GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW

表示当前 peer group 加上前一个 peer group。

PostgreSQL 支持 GROUPS Frame。MySQL 8.4 的窗口 Frame 语法主要支持 ROWS 和 RANGE，不能直接照搬 PostgreSQL 的 GROUPS 写法。跨引擎 SQL 应优先使用两者共同支持的语法，或为不同引擎提供不同实现。

6.3 同一数据下 ROWS 与 RANGE 的差异

查询：

SELECT
    sale_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date, sale_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rows_running_total
FROM sales
WHERE customer_id = 'A'
ORDER BY sale_date, sale_id;

结果：

sale_id	sale_date	amount	rows_running_total
1	2024-01-01	10.00	10.00
2	2024-01-01	20.00	30.00
3	2024-01-02	5.00	35.00
4	2024-01-04	40.00	75.00

如果按日期排序，并使用 RANGE：

SELECT
    sale_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS range_running_total
FROM sales
WHERE customer_id = 'A'
ORDER BY sale_date, sale_id;

结果：

sale_id	sale_date	amount	range_running_total
1	2024-01-01	10.00	30.00
2	2024-01-01	20.00	30.00
3	2024-01-02	5.00	35.00
4	2024-01-04	40.00	75.00

原因是 RANGE ... CURRENT ROW 的“当前值”是 2024-01-01，同一天的两行属于同一个 peer group，因此两行都看到当天两笔销售。

这两个结果都可能正确：

• 如果业务定义是“每笔交易按确定顺序累计”，使用 ROWS，并提供唯一排序键；
• 如果业务定义是“截至某天的累计”，使用按日期的 RANGE，让同一天的记录得到相同累计值。

6.4 默认 Frame 为什么危险

当窗口包含 ORDER BY，但没有显式 Frame 时，PostgreSQL 和 MySQL 的常见默认语义可以理解为：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

这意味着默认 Frame 可能包含当前行的 peer 行。

因此：

SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
)

不是简单等价于“按物理行逐行累计”。如果日期重复，它可能一次纳入同一天的所有记录。

累计计算最好显式写出 Frame：

SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date, sale_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

显式 Frame 同时表达了业务意图，也降低了因默认值、排序键变化而产生的误读风险。

---

七、排名函数：ROW_NUMBER、RANK 和 DENSE_RANK

窗口排名函数通常不依赖普通聚合意义上的 Frame，而是依赖分区和窗口排序。

7.1 三种排名的定义

设某分区按分数降序排列：

score
100
90
90
80

三种排名如下：

score	ROW_NUMBER()	RANK()	DENSE_RANK()
100	1	1	1
90	2	2	2
90	3	2	2
80	4	4	3

区别：

• ROW_NUMBER()：每一行都有唯一连续编号，不保留并列名次；
• RANK()：并列行名次相同，后续名次跳过并列数量；
• DENSE_RANK()：并列行名次相同，但后续名次不跳号。

查询示例：

SELECT
    sale_id,
    customer_id,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, sale_id
    ) AS row_number_by_amount,
    RANK() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC
    ) AS rank_by_amount,
    DENSE_RANK() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC
    ) AS dense_rank_by_amount
FROM sales
ORDER BY customer_id, amount DESC, sale_id;

7.2 是否加入唯一键会改变排名含义

对于 RANK()：

ORDER BY amount DESC

表示金额相同就是并列。

如果写成：

ORDER BY amount DESC, sale_id

由于 sale_id 唯一，通常不会再有并列排名。

因此：

• 需要“金额相同并列”时，不要把唯一键加入排名业务排序键；
• 需要“并列内部稳定选一行”时，可以在 ROW_NUMBER() 中加入唯一键；
• 需要“既保留并列，又稳定展示”时，可以让排名按业务键计算，最终结果再用唯一键排序。

7.3 Top-N 与 Top-N-with-ties

“每个客户金额最高的一笔”：

WITH ranked AS (
    SELECT
        sale_id,
        customer_id,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC, sale_id
        ) AS rn
    FROM sales
)
SELECT sale_id, customer_id, amount
FROM ranked
WHERE rn = 1;

这会严格返回每个客户一行。

“每个客户金额并列最高的所有记录”：

WITH ranked AS (
    SELECT
        sale_id,
        customer_id,
        amount,
        RANK() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC
        ) AS rnk
    FROM sales
)
SELECT sale_id, customer_id, amount
FROM ranked
WHERE rnk = 1;

两者的业务语义不同，不能只把 ROW_NUMBER() 和 RANK() 当作语法替换。

---

八、累计分析：从累计总额到累计占比

8.1 累计和

每个客户按交易顺序计算累计金额：

SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date, sale_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM sales
ORDER BY customer_id, sale_date, sale_id;

对客户 A：

1. 第一行累计：10
2. 第二行累计：10 + 20 = 30
3. 第三行累计：30 + 5 = 35
4. 第四行累计：35 + 40 = 75

这是一个递推过程：

$$C_i = C_{i-1} + a_i$$

其中：

• $a_i$ 是第 $i$ 行金额；
• $C_i$ 是截至第 $i$ 行的累计金额；
• $C_0 = 0$。

窗口 SUM 不需要手写递归 CTE，但其结果仍然遵循这个递推关系。

8.2 计算分区内占比

SELECT
    sale_id,
    customer_id,
    amount,
    amount / NULLIF(
        SUM(amount) OVER (PARTITION BY customer_id),
        0
    ) AS customer_amount_ratio
FROM sales;

NULLIF(..., 0) 用于避免总额为零时发生除零错误。

如果金额为整数类型，某些数据库或表达式类型可能导致整数除法。使用 DECIMAL 或显式转换可以避免比例被截断：

CAST(amount AS DECIMAL(18, 6))
/
NULLIF(SUM(amount) OVER (PARTITION BY customer_id), 0)

8.3 累计占比与首次达到阈值

WITH calculated AS (
    SELECT
        sale_id,
        customer_id,
        sale_date,
        amount,
        SUM(amount) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
            ROWS UNBOUNDED PRECEDING
        ) AS cumulative_amount,
        SUM(amount) OVER (
            PARTITION BY customer_id
        ) AS customer_total
    FROM sales
)
SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    cumulative_amount,
    cumulative_amount / NULLIF(customer_total, 0) AS cumulative_ratio
FROM calculated
ORDER BY customer_id, sale_date, sale_id;

如果要找每个客户累计贡献首次达到 80% 的交易，可以在外层继续筛选：

WITH calculated AS (
    SELECT
        sale_id,
        customer_id,
        sale_date,
        amount,
        SUM(amount) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
            ROWS UNBOUNDED PRECEDING
        ) AS cumulative_amount,
        SUM(amount) OVER (
            PARTITION BY customer_id
        ) AS customer_total
    FROM sales
),
qualified AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
        ) AS sequence_no
    FROM calculated
    WHERE cumulative_amount >= customer_total * 0.8
)
SELECT *
FROM qualified
WHERE sequence_no = 1;

这里需要注意：窗口函数的嵌套不能直接写成：

SUM(SUM(amount) OVER (...)) OVER (...)

如果确实需要多层窗口计算，应使用 CTE 或子查询分层。

---

九、移动窗口：ROWS 与时间范围不是一回事

9.1 最近三行移动平均

SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date, sale_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3_rows
FROM sales
ORDER BY customer_id, sale_date, sale_id;

在客户 A 的四行数据上：

• 第 1 行：只有第 1 行，平均值为 10；
• 第 2 行：第 1、2 行，平均值为 15；
• 第 3 行：第 1、2、3 行，平均值为 35/3；
• 第 4 行：第 2、3、4 行，平均值为 65/3。

它是“最近三条记录”，不是“最近三天”。

9.2 按时间值定义范围

如果业务要求“过去 2 天内的金额”，概念上更接近：

RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW

PostgreSQL 示例：

SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
        RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
    ) AS amount_in_recent_2_days
FROM sales
ORDER BY customer_id, sale_date, sale_id;

MySQL 的时间间隔 Frame 使用其自身的语法形式，例如：

RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW

具体可用形式取决于排序列的数据类型和 MySQL 的窗口 Frame 语法约束。跨 PostgreSQL 和 MySQL 发布 SQL 时，不应未经验证直接复用时间间隔写法。

更重要的是，“过去两天”需要明确边界：

• 是 [当前时间 - 2 天, 当前时间]；
• 还是包含当前日期在内的三个自然日；
• 时间戳是否带时区；
• 同一天多条记录是否全部纳入。

ROWS 解决行数问题，RANGE 解决排序值范围问题，它们不能互相替代。

---

十、LAG 与 LEAD：建立相邻行关系

10.1 LAG：读取前一行

SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date, sale_id
    ) AS previous_amount
FROM sales
ORDER BY customer_id, sale_date, sale_id;

对每个客户：

• 第一行没有前一行，因此 previous_amount 为 NULL；
• 第二行读取第一行金额；
• 第三行读取第二行金额。

可以继续计算金额变化：

SELECT
    sale_id,
    customer_id,
    amount,
    amount - LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date, sale_id
    ) AS amount_delta
FROM sales;

第一行的差值为 NULL，这是“没有可比较的前一行”的正确表示，不应默认当成零。

10.2 LEAD：读取后一行

SELECT
    sale_id,
    customer_id,
    sale_date,
    LEAD(sale_date) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date, sale_id
    ) AS next_sale_date
FROM sales
ORDER BY customer_id, sale_date, sale_id;

LEAD 常用于：

• 计算下一次事件时间；
• 生成区间结束时间；
• 判断当前状态持续到何时；
• 检测连续事件之间的空档。

10.3 LAG/LEAD 的 offset 和默认值

语法示意：

LAG(amount, 2, 0) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date, sale_id
)

含义是：

• 向前偏移两行；
• 如果不存在，则返回 0。

但默认值是否应为零取决于业务。对于金额差异分析，把“没有前两行”替换为零可能掩盖数据边界。很多场景应保留 NULL，在外层显式处理。

10.4 窗口排序必须定义事件顺序

如果只按 sale_date 排序，而同一天有多笔记录，那么“前一笔”没有唯一含义。

应根据业务选择：

ORDER BY sale_date, sale_id

或先按天聚合：

WITH daily AS (
    SELECT customer_id, sale_date, SUM(amount) AS amount
    FROM sales
    GROUP BY customer_id, sale_date
)
SELECT
    customer_id,
    sale_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS previous_day_amount
FROM daily;

前者分析“上一笔交易”，后者分析“上一营业日或上一有数据日期”，二者不能混淆。

---

十一、间隔分析：计算事件之间的时间差

11.1 计算相邻销售间隔

PostgreSQL：

SELECT
    sale_id,
    customer_id,
    sale_date,
    sale_date
      - LAG(sale_date) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
        ) AS days_since_previous_sale
FROM sales
ORDER BY customer_id, sale_date, sale_id;

PostgreSQL 中两个 DATE 相减得到整数天数。

MySQL 可以使用：

SELECT
    sale_id,
    customer_id,
    sale_date,
    DATEDIFF(
        sale_date,
        LAG(sale_date) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
        )
    ) AS days_since_previous_sale
FROM sales
ORDER BY customer_id, sale_date, sale_id;

对于客户 A：

sale_id	sale_date	前一笔日期	间隔天数
1	2024-01-01	NULL	NULL
2	2024-01-01	2024-01-01	0
3	2024-01-02	2024-01-01	1
4	2024-01-04	2024-01-02	2

11.2 找出超过阈值的间隔

窗口结果不能直接放进同层 WHERE，因此先放进 CTE：

WITH intervals AS (
    SELECT
        sale_id,
        customer_id,
        sale_date,
        sale_date
          - LAG(sale_date) OVER (
                PARTITION BY customer_id
                ORDER BY sale_date, sale_id
            ) AS days_since_previous_sale
    FROM sales
)
SELECT *
FROM intervals
WHERE days_since_previous_sale >= 2;

这会找出客户两次销售之间至少间隔两天的记录。

11.3 识别连续分组：间隔分析的进一步应用

窗口函数还可以把事件划分为连续会话。假设同一客户相邻两次事件间隔超过 1 天，就开始新会话。

第一步，计算与前一事件的间隔：

WITH ordered AS (
    SELECT
        sale_id,
        customer_id,
        sale_date,
        LAG(sale_date) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
        ) AS previous_date
    FROM sales
)
SELECT
    *,
    CASE
        WHEN previous_date IS NULL THEN 1
        WHEN sale_date - previous_date > 1 THEN 1
        ELSE 0
    END AS starts_new_session
FROM ordered;

第二步，对“开始新会话”的标记做累计：

WITH ordered AS (
    SELECT
        sale_id,
        customer_id,
        sale_date,
        LAG(sale_date) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
        ) AS previous_date
    FROM sales
),
marked AS (
    SELECT
        *,
        CASE
            WHEN previous_date IS NULL THEN 1
            WHEN sale_date - previous_date > 1 THEN 1
            ELSE 0
        END AS starts_new_session
    FROM ordered
)
SELECT
    sale_id,
    customer_id,
    sale_date,
    SUM(starts_new_session) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date, sale_id
        ROWS UNBOUNDED PRECEDING
    ) AS session_no
FROM marked
ORDER BY customer_id, sale_date, sale_id;

中间状态以客户 A 为例：

sale_id	sale_date	previous_date	starts_new_session	session_no
1	2024-01-01	NULL	1	1
2	2024-01-01	2024-01-01	0	1
3	2024-01-02	2024-01-01	0	1
4	2024-01-04	2024-01-02	1	2

这段逻辑的因果关系是：

1. LAG 找到上一事件；
2. CASE 判断当前事件是否开启新组；
3. 对标记值累计；
4. 累计值成为稳定的组编号。

---

十二、FIRST_VALUE、LAST_VALUE 与 Frame 陷阱

12.1 FIRST_VALUE

SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date, sale_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_amount
FROM sales;

它返回每个客户按指定顺序的第一笔金额。

12.2 LAST_VALUE 常被误用

下面的写法看起来像是“最后一笔金额”：

LAST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date, sale_id
)

但在默认 Frame 下，Frame 通常只到当前行。因此它得到的往往是“当前 Frame 的最后一行”，也就是当前行，而不是整个分区的最后一行。

如果要得到整个分区最后一行，应明确扩大 Frame：

LAST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date, sale_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_amount

这正好说明：窗口排序和 Frame 必须同时理解。LAST_VALUE 的结果不是由 ORDER BY 单独决定的。

在某些场景中，使用反向排序的 FIRST_VALUE 也更直观：

FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date DESC, sale_id DESC
) AS last_amount

不过这改变了“第一”的排序方向，仍然应明确写出唯一排序键。

---

十三、命名窗口与多个窗口计算

当多个函数使用相同的分区和排序条件时，可以定义命名窗口：

SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    ROW_NUMBER() OVER w AS row_no,
    LAG(amount) OVER w AS previous_amount,
    SUM(amount) OVER (
        w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM sales
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY sale_date, sale_id
)
ORDER BY customer_id, sale_date, sale_id;

命名窗口可以减少重复，但要注意：

• 不能把一个窗口函数的结果直接作为另一个窗口函数的输入；
• 不同 Frame 的函数仍应明确写出 Frame；
• PostgreSQL 和 MySQL 对命名窗口的具体扩展能力应以目标版本语法为准。

---

十四、窗口函数不能改变行数，也不能替代所有聚合

窗口函数对每一行产生一个值，但通常不会：

• 删除重复行；
• 把多行压成一行；
• 自动过滤结果；
• 自动补齐缺失日期；
• 自动建立跨表关系。

例如，想得到每个客户的最后一笔销售，窗口函数可以筛选：

WITH numbered AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY sale_date DESC, sale_id DESC
        ) AS rn
    FROM sales
)
SELECT *
FROM numbered
WHERE rn = 1;

但如果只是要每个客户的最大日期：

SELECT customer_id, MAX(sale_date)
FROM sales
GROUP BY customer_id;

普通聚合更直接。

如果要保留完整行，同时关联最大日期，可以使用窗口函数，也可以使用聚合后回连。选择哪种方式取决于是否需要处理并列、是否需要取整行以及执行计划。

---

十五、常见失败表现与诊断方法

15.1 累计值跳跃或同一天相同

现象：

同一天两笔数据的累计值相同

可能原因是默认 RANGE 包含 peer 行。

检查：

1. 窗口是否写了 ORDER BY；
2. 是否省略了 Frame；
3. 排序键是否有重复值；
4. 业务需要按行累计还是按值累计。

需要逐行累计时改为：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

并补充唯一排序键。

15.2 ROW_NUMBER() 每次结果不稳定

原因通常是排序键不唯一：

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
)

如果多行金额相同，数据库可以在满足排序条件的前提下采用不同的内部顺序。

改为：

ORDER BY amount DESC, sale_id

但要确认这不会改变“并列排名”的业务定义。

15.3 在 WHERE 中引用窗口别名报错

错误模式：

WHERE rn <= 10

同层窗口别名不能在 WHERE 阶段使用。用 CTE 或子查询包一层。

15.4 LAST_VALUE() 返回当前值

检查是否使用了默认 Frame。需要全分区最后值时写：

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

15.5 间隔天数异常

可能原因包括：

• 使用了日期而不是时间戳，丢失了时分秒；
• 时区转换前后日期发生变化；
• 同一日期多条事件没有唯一顺序；
• LAG 的分区列不完整；
• 第一行的 NULL 被错误地转换为零。

如果数据跨时区，事件时间应明确使用带时区或统一 UTC 的存储策略，并在窗口排序前确认转换后的时间值。

15.6 不同数据库结果不一致

优先检查：

• NULL 排序规则；
• 默认 Frame；
• 时间间隔 RANGE 的语法和数据类型；
• 日期相减返回类型；
• 整数除法与数值类型；
• 是否使用了某一引擎独有的 GROUPS 或 EXCLUDE。

---

十六、Frame 的边界和空值行为

16.1 Frame 到达分区边界时不会报错

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

在分区第一行上没有两行前置记录，Frame 会自然缩短到分区开始。

因此第一行的三行移动平均实际上只对一行计算，而不是返回错误。

16.2 空 Frame 与 NULL

聚合函数在没有输入行时通常返回 NULL，而不是零。例如：

SUM(value)

没有可计算值时返回 NULL。如果业务希望显示零：

COALESCE(
    SUM(value) OVER (...),
    0
)

但不要无条件使用 COALESCE(..., 0)。在“没有前一事件”和“前一事件金额确实为零”需要区分时，NULL 是重要信息。

16.3 IGNORE NULLS 不是跨引擎通用能力

不同数据库对窗口函数跳过空值的支持不同。PostgreSQL 当前常见语义没有直接提供 Oracle 风格的 IGNORE NULLS 选项；MySQL 8.4 对相关 null_treatment 也存在限制，不能简单假设：

LAG(value) IGNORE NULLS

在两个引擎中都可用。

需要跳过空值时，通常要改写数据集、使用条件聚合，或通过额外的排序和分组逻辑实现，并针对目标数据库验证。

---

十七、性能：窗口计算通常需要分区内排序

窗口函数的典型执行过程可以抽象为：

1. 读取 FROM、JOIN、WHERE 产生的输入；
2. 按 PARTITION BY 和 ORDER BY 组织数据；
3. 在每个分区内维护当前 Frame；
4. 计算窗口函数；
5. 将窗口结果交给外层排序、过滤或连接。

当窗口需要排序时，数据库可能进行：

• 内存排序；
• 外部排序；
• 临时表或磁盘溢出；
• 多个窗口定义之间的排序复用或重复排序。

同一查询中有多个不同窗口：

SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date, sale_id
    ROWS UNBOUNDED PRECEDING
),
ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC, sale_id
)

它们的排序需求不同，执行时可能需要不同排序过程。

应使用目标引擎的计划工具确认实际行为：

• PostgreSQL：EXPLAIN (ANALYZE, BUFFERS) ...
• MySQL：EXPLAIN ANALYZE ...

索引可能帮助减少读取、过滤或提供部分有序输入，但不能假设“存在 (customer_id, sale_date, sale_id) 索引，就一定不需要排序”。最终是否利用索引、是否发生物化或溢出，应以执行计划为准。

窗口函数的性能还受分区大小影响。一个极大的分区会使排序和 Frame 维护成本集中到少量分区上。必要时可以考虑：

• 先过滤无关数据；
• 先聚合到正确粒度；
• 缩小 PARTITION BY 的业务范围；
• 将一次复杂分析拆成明确的 CTE 层；
• 检查是否因为隐式类型转换导致索引或排序失效。

这些不是窗口函数语义的改变，而是对输入规模和执行计划的控制。

---

十八、事务与部署边界

窗口函数只对当前语句看到的输入计算。它不会：

• 锁定所有参与计算的行；
• 自动阻止其他事务插入新数据；
• 让多个独立查询共享同一个结果快照；
• 跨越事务边界保存累计状态。

例如，先执行“查询当前累计总额”，再执行另一条查询读取新订单，这两条语句在默认事务设置下可能看到不同的数据状态。

如果报表要求多个查询基于同一一致性视图，应根据数据库和业务要求使用显式事务，并选择合适的隔离级别。PostgreSQL 的 MVCC 快照和 MySQL InnoDB 的一致性读都不能被简单概括为“永远读到最新数据”或“永远锁住数据”。

窗口查询本身通常是只读的。它计算的是查询输入的逻辑结果，不会把累计值写回表。若把累计结果持久化，必须额外处理：

• 重跑是否幂等；
• 新旧数据是否会导致历史累计变化；
• 并发写入是否产生竞态；
• 迟到数据是否需要重新计算；
• 物化结果与源表的一致性。

---

十九、一个综合示例：排名、累计、前值和间隔同时分析

下面的查询在每个客户内部同时计算：

• 按交易金额降序的排名；
• 按日期的交易序号；
• 累计金额；
• 占客户总额的比例；
• 上一笔交易金额；
• 与上一笔交易的日期间隔。

WITH analyzed AS (
    SELECT
        sale_id,
        customer_id,
        sale_date,
        amount,

        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC, sale_id
        ) AS amount_row_number,

        RANK() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC
        ) AS amount_rank,

        SUM(amount) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_amount,

        SUM(amount) OVER (
            PARTITION BY customer_id
        ) AS customer_total,

        LAG(amount) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
        ) AS previous_amount,

        LAG(sale_date) OVER (
            PARTITION BY customer_id
            ORDER BY sale_date, sale_id
        ) AS previous_sale_date
    FROM sales
)
SELECT
    sale_id,
    customer_id,
    sale_date,
    amount,
    amount_row_number,
    amount_rank,
    cumulative_amount,
    cumulative_amount / NULLIF(customer_total, 0)
        AS cumulative_ratio,
    previous_amount,
    previous_sale_date,
    sale_date - previous_sale_date
        AS days_since_previous_sale
FROM analyzed
ORDER BY customer_id, sale_date, sale_id;

这条查询中，窗口定义承担了不同职责：

• PARTITION BY customer_id：客户之间隔离；
• ORDER BY amount DESC, sale_id：金额排名顺序；
• ORDER BY sale_date, sale_id：交易事件顺序；
• ROWS ... CURRENT ROW：逐笔累计；
• 没有显式 Frame 的 LAG：按排序位置读取前一行，Frame 不决定它读取哪一行；
• 外层查询：使用窗口结果计算比例和日期差，并负责最终展示。

如果把金额排名和时间累计错误地共用同一个排序，就会得到语义错误的结果。窗口函数不是“给查询加一个排序”这么简单，而是每个分析指标都需要独立确认其分区、顺序和范围。

---

二十、选择窗口定义时的判断路径

面对一个窗口分析需求，可以按以下顺序推导：

第一步：确定结果粒度

问自己：

• 是每笔订单一行？
• 每个客户每天一行？
• 每个客户一行？
• 是否需要先用 GROUP BY 聚合？

如果粒度没有确定，窗口结果很容易被误解。

第二步：确定分区边界

问：

• 哪些实体之间不能互相影响？
• 是客户、账户、设备、商品，还是客户与商品的组合？

把这些列放进 PARTITION BY。

第三步：确定事件顺序

问：

• “前一行”按什么定义？
• 同一时间是否可能有多个事件？
• 是否需要唯一键打破并列？

把业务排序键和稳定性排序键区分开。

第四步：确定 Frame 类型

问：

• 按行数计算，还是按排序值范围计算？
• 同一日期的记录是否应该同时纳入？
• 是累计、最近 N 行，还是过去 N 天？
• 是否需要整个分区？

然后选择 ROWS、RANGE 或目标数据库支持的 GROUPS。

第五步：确定并列规则

问：

• 并列记录是否应该共享名次？
• 是否允许结果返回多行？
• “第一名”是严格一行，还是包括并列第一？

分别选择 ROW_NUMBER()、RANK() 或 DENSE_RANK()。

第六步：确认外层过滤和执行计划

如果要筛选窗口结果，使用 CTE 或子查询。数据量较大时，再用目标数据库的执行计划确认排序、临时空间和物化行为。

窗口函数的核心不是记忆几十个函数，而是能明确回答：

当前行属于哪个分区，按什么顺序定位，Frame 覆盖哪些行，函数对这些行做什么计算，最后是否需要在外层继续筛选或聚合。

只要这五个问题能够逐一落到 SQL 语法上，排名、累计、移动统计和间隔分析通常都可以从同一套模型自然推导出来。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：SQL 子查询与 CTE：相关子查询、递归、物化和优化边界
• 下一篇：SQL 聚合与集合：GROUPING SETS、ROLLUP、CUBE、UNION 和去重

官方资料

• PostgreSQL SQL Language
• MySQL Reference Manual

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。