29. 窗口函数

29.1 什么是窗口函数

前面学的聚合函数（COUNT、SUM、AVG 等）会把多行”压缩”成一行。比如 GROUP BY status 后，每种状态只有一行结果。

窗口函数不同。它也在多行上做计算，但不压缩行数—每一行都有自己的计算结果。

说白了，窗口函数就是”在不减少行数的前提下，给每行附加一个计算值”。这个计算值是基于一个”窗口”（一组相关行）算出来的。

一个直观的例子：给每个订单加上”这个用户所有订单的平均金额”：

-- 聚合函数 + GROUP BY：每个用户一行，丢失了订单明细
SELECT user_id, AVG(amount) FROM orders GROUP BY user_id;

-- 窗口函数：每行订单都保留，同时附带该用户的平均金额
SELECT
    id,
    user_id,
    amount,
    AVG(amount) OVER (PARTITION BY user_id) AS 用户平均金额
FROM orders;

结果每行都保留了原始数据，同时多了一列”用户平均金额”。这就是窗口函数的价值。

29.2 OVER() 子句

OVER() 是窗口函数的核心，它定义了”窗口”—在哪些行上做计算。

空 OVER()：全局窗口

SELECT
    id,
    amount,
    AVG(amount) OVER() AS 全局平均金额
FROM orders;

空的 OVER() 表示窗口是整张表。每行的”全局平均金额”都是所有订单的平均值。

PARTITION BY：分区

PARTITION BY 把数据分成多个区（类似 GROUP BY），每个区独立计算：

SELECT
    id,
    user_id,
    amount,
    AVG(amount) OVER(PARTITION BY user_id) AS 用户平均金额
FROM orders;

按 user_id 分区，每个用户的订单各自计算平均值。张三的行显示张三的平均金额，李四的行显示李四的平均金额。

ORDER BY：区内排序

ORDER BY 在分区内排序，通常配合序号函数和累计计算使用：

SELECT
    id,
    user_id,
    amount,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) AS 金额排名
FROM orders;

按 user_id 分区，区内按 amount 降序排，给每行编号。结果就是每个用户的订单按金额从大到小排名。

29.3 序号函数：ROW_NUMBER / RANK / DENSE_RANK

这三个是最常用的窗口函数，都用于排名，但处理并列的方式不同。

ROW_NUMBER：行号

给每行分配一个连续的序号，不管有没有并列：

SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;

每个用户的订单按金额降序排，从 1 开始编号。即使两行金额相同，序号也不同（1、2、3…）。

RANK：排名（跳号）

有并列时，相同值给相同排名，下一个排名跳过：

SELECT
    user_id,
    amount,
    RANK() OVER(PARTITION BY user_id ORDER BY amount DESC) AS rank_num
FROM orders;

如果第一名有两个（金额相同），都是 rank 1，下一个是 rank 3（跳过 2）。

DENSE_RANK：密集排名（不跳号）

有并列时，相同值给相同排名，下一个排名不跳：

SELECT
    user_id,
    amount,
    DENSE_RANK() OVER(PARTITION BY user_id ORDER BY amount DESC) AS dense_rank_num
FROM orders;

如果第一名有两个（金额相同），都是 dense_rank 1，下一个是 dense_rank 2（不跳）。

三者对比

假设金额从大到小是：500, 500, 300, 200

金额	ROW_NUMBER	RANK	DENSE_RANK
500	1	1	1
500	2	1	1
300	3	3	2
200	4	4	3

29.4 取值函数：LAG / LEAD

LAG 和 LEAD 用于在分区内取”前一行”或”后一行”的值，常用于计算环比、同比。

LAG：取前一行

SELECT
    user_id,
    created_at,
    amount,
    LAG(amount) OVER(PARTITION BY user_id ORDER BY created_at) AS 上一笔金额
FROM orders;

按用户分区，按时间排序，每行显示上一笔订单的金额。第一行没有”上一行”，显示 NULL。

可以指定偏移量和默认值：

-- 取前 2 行的值，没有时默认显示 0
LAG(amount, 2, 0) OVER(PARTITION BY user_id ORDER BY created_at)

LEAD：取后一行

SELECT
    user_id,
    created_at,
    amount,
    LEAD(amount) OVER(PARTITION BY user_id ORDER BY created_at) AS 下一笔金额
FROM orders;

取后一行的值。最后一行没有”后一行”，显示 NULL。

实际应用：计算环比增长

SELECT
    user_id,
    created_at,
    amount AS 当前金额,
    LAG(amount) OVER(PARTITION BY user_id ORDER BY created_at) AS 上次金额,
    amount - LAG(amount) OVER(PARTITION BY user_id ORDER BY created_at) AS 增减,
    ROUND(
        (amount - LAG(amount) OVER(PARTITION BY user_id ORDER BY created_at))
        / LAG(amount) OVER(PARTITION BY user_id ORDER BY created_at) * 100,
        2
    ) AS 环比增长率
FROM orders
WHERE status = 'paid';

29.5 聚合函数作为窗口函数

前面学的聚合函数（SUM、AVG、COUNT、MAX、MIN）都可以作为窗口函数使用，加 OVER() 即可：

SELECT
    id,
    user_id,
    amount,
    -- 每个用户的订单总数
    COUNT(*) OVER(PARTITION BY user_id) AS 用户订单数,
    -- 每个用户的总消费
    SUM(amount) OVER(PARTITION BY user_id) AS 用户总消费,
    -- 每个用户的最大单笔金额
    MAX(amount) OVER(PARTITION BY user_id) AS 用户最大金额
FROM orders;

每行都保留原始数据，同时附带该用户的统计值。

累计求和

配合 ORDER BY，聚合窗口函数可以做累计计算：

SELECT
    user_id,
    created_at,
    amount,
    SUM(amount) OVER(
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS 累计消费
FROM orders;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示窗口范围是”从分区的第一行到当前行”。每行的”累计消费”是当前行及之前所有行的金额之和。

+---------+-----------+--------+----------+
| user_id | created_at| amount | 累计消费 |
+---------+-----------+--------+----------+
|    1    | 07-01     |  100   |   100    |  -- 100
|    1    | 07-05     |  200   |   300    |  -- 100 + 200
|    1    | 07-10     |  150   |   450    |  -- 100 + 200 + 150
+---------+-----------+--------+----------+

29.6 帧框架（Frame Clause）

帧（Frame）定义了窗口函数的计算范围。在 ORDER BY 后面用 ROWS 或 RANGE 指定。

语法

ROWS BETWEEN 起点 AND 终点
RANGE BETWEEN 起点 AND 终点

起点和终点可以是：

关键字	含义
UNBOUNDED PRECEDING	分区的第一行
N PRECEDING	前面第 N 行
CURRENT ROW	当前行
N FOLLOWING	后面第 N 行
UNBOUNDED FOLLOWING	分区的最后一行

ROWS vs RANGE

• ROWS：按物理行数计算
• RANGE：按逻辑值范围计算

-- 前一行到当前行的移动平均
AVG(amount) OVER(
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
)

-- 前一行到后一行的移动平均（3行窗口）
AVG(amount) OVER(
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

默认帧

如果指定了 ORDER BY 但没写帧：

• 大多数函数的默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• 等价于”从分区开头到当前行（含同值行）”

如果没指定 ORDER BY：

• 默认帧是整个分区（ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING）

29.7 其他窗口函数

NTILE：分桶

把数据分成 N 等份，返回每行属于第几桶：

-- 把用户按年龄分成 4 组
SELECT
    username,
    age,
    NTILE(4) OVER(ORDER BY age) AS 年龄分组
FROM users;

常用于数据分析中的四分位数、十分位数。

FIRST_VALUE / LAST_VALUE

取分区内第一个/最后一个值：

SELECT
    user_id,
    created_at,
    amount,
    FIRST_VALUE(amount) OVER(PARTITION BY user_id ORDER BY created_at) AS 第一笔金额,
    LAST_VALUE(amount) OVER(
        PARTITION BY user_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS 最后一笔金额
FROM orders;

NTH_VALUE

取分区内第 N 个值：

-- 每个用户第二笔订单的金额
SELECT
    user_id,
    created_at,
    amount,
    NTH_VALUE(amount, 2) OVER(PARTITION BY user_id ORDER BY created_at) AS 第二笔金额
FROM orders;

CUME_DIST 和 PERCENT_RANK

-- 累积分布和百分比排名
SELECT
    username,
    age,
    CUME_DIST() OVER(ORDER BY age) AS 累积分布,
    PERCENT_RANK() OVER(ORDER BY age) AS 百分比排名
FROM users;

完整窗口函数列表

函数	作用
ROW_NUMBER()	行号
RANK()	排名（跳号）
DENSE_RANK()	密集排名（不跳号）
LAG(列, n, 默认值)	前第 n 行的值
LEAD(列, n, 默认值)	后第 n 行的值
FIRST_VALUE(列)	分区第一个值
LAST_VALUE(列)	分区最后一个值
NTH_VALUE(列, n)	分区第 n 个值
NTILE(n)	分成 n 桶，返回桶号
CUME_DIST()	累积分布值
PERCENT_RANK()	百分比排名
SUM/AVG/COUNT/MIN/MAX(列) OVER()	聚合窗口函数

29.8 命名窗口（WINDOW 子句）

如果多个窗口函数用相同的 OVER() 定义，可以用 WINDOW 子句命名，避免重复：

SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER w AS rn,
    RANK() OVER w AS rnk,
    SUM(amount) OVER w AS total
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC);

WINDOW w AS (...) 定义了一个名为 w 的窗口，后面直接用 OVER w 引用。当 SQL 里有多个相同 OVER 的窗口函数时，这能让代码简洁很多。

29.9 窗口函数的执行顺序

窗口函数在 SQL 执行流程中的位置：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT(窗口函数) -> ORDER BY -> LIMIT

窗口函数在 SELECT 阶段执行，在 GROUP BY 和 HAVING 之后。这意味着：

1. 窗口函数能用到 WHERE 和 GROUP BY 的结果
2. 窗口函数不能用 WHERE 来过滤（它在 WHERE 之后执行）
3. 窗口函数的结果可以在 ORDER BY 中使用

-- 窗口函数结果不能用在 WHERE 里（会报错）
SELECT *, ROW_NUMBER() OVER(ORDER BY amount DESC) AS rn
FROM orders
WHERE rn <= 3;
-- ERROR: Unknown column 'rn'

-- 正确做法：用子查询或 CTE 包一层
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY amount DESC) AS rn
    FROM orders
) AS t
WHERE rn <= 3;

29.10 实战示例

示例一：查每个用户金额最高的 3 笔订单

SELECT * FROM (
    SELECT
        id,
        user_id,
        amount,
        status,
        created_at,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) AS rn
    FROM orders
) AS t
WHERE rn <= 3;

这是经典的”分组取 Top N”问题，窗口函数是最佳解法。

示例二：查金额排名前 3 的所有用户（含并列）

SELECT * FROM (
    SELECT
        id,
        user_id,
        amount,
        DENSE_RANK() OVER(ORDER BY amount DESC) AS dr
    FROM orders
) AS t
WHERE dr <= 3;

用 DENSE_RANK 不会跳号，并列的都能查出来。

示例三：计算每个用户连续 3 笔订单的移动平均

SELECT
    user_id,
    created_at,
    amount,
    ROUND(
        AVG(amount) OVER(
            PARTITION BY user_id
            ORDER BY created_at
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS 三笔移动平均
FROM orders;

示例四：查每个用户第一笔和最后一笔订单

SELECT DISTINCT
    user_id,
    FIRST_VALUE(amount) OVER w AS 第一笔金额,
    FIRST_VALUE(created_at) OVER w AS 第一笔时间,
    LAST_VALUE(amount) OVER(
        PARTITION BY user_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS 最后一笔金额
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

29.11 性能注意事项

窗口函数虽然强大，但也有性能考量：

1. 需要排序：大多数窗口函数需要排序（PARTITION BY + ORDER BY），大数据量时消耗内存和 CPU。
2. 无法走索引优化：窗口函数在结果集上计算，不直接利用索引。
3. 内存消耗：窗口函数需要缓存分区数据，分区越大内存消耗越多。

29.12 小结

窗口函数是 MySQL 8.0+ 最重要的新特性之一。它在不减少行数的前提下给每行附加计算值，解决了排名、累计、环比等复杂查询需求。核心记住：OVER() 定义窗口、PARTITION BY 分区、ORDER BY 排序、ROWS BETWEEN 定义帧范围。ROW_NUMBER/RANK/DENSE_RANK 做排名，LAG/LEAD 做前后行比较，聚合函数 OVER 做分组统计。窗口函数结果不能直接在 WHERE 过滤，要包一层子查询。下一章学 CTE（公用表表达式），跟窗口函数是好搭档。