窗口函数

约 923 字大约 3 分钟

布欧-Lewyon

2026-05-15

窗口函数（Window Function）在 MySQL 8.0 中引入，它不合并行，而是在每行上基于一个窗口（行集合）计算值。相比 GROUP BY，窗口函数能同时保留原始行细节和聚合结果。

与 GROUP BY 的对比

-- GROUP BY：行被合并，失去原始行
SELECT dept_id, AVG(salary) FROM employee GROUP BY dept_id;

-- 窗口函数：每行保留，额外增加平均薪资列
SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employee;

OVER 子句语法

函数名() OVER (
    [PARTITION BY 列名]    -- 按列分组（可选）
    [ORDER BY 列名]        -- 窗口内排序（可选）
    [ROWS/RANGE 帧定义]    -- 定义窗口范围（可选）
)

排名窗口函数

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,  -- 唯一连续排名
       RANK()       OVER (ORDER BY salary DESC) AS rnk,       -- 并列跳号
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk  -- 并列不跳号
FROM employee;
-- name   | salary | row_num | rnk | dense_rnk
-- 赵六    | 22000  | 1       | 1   | 1
-- 王五    | 18000  | 2       | 2   | 2
-- 张三    | 15000  | 3       | 3   | 3
-- 李四    | 12000  | 4       | 4   | 4
-- 孙七    | 11000  | 5       | 5   | 5
-- 周八    | 9000   | 6       | 6   | 6
-- 吴九    | NULL   | 7       | 7   | 7

RANK 和 DENSE_RANK 在出现并列时会体现差异：假设有两个 22000，RANK 给出 1, 1, 3，DENSE_RANK 给出 1, 1, 2。

分组排名

-- 各部门内按薪资排名
SELECT name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employee;

偏移窗口函数

-- LAG：上一行
-- LEAD：下一行
SELECT name, salary,
       LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary,
       LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employee;
-- LAG(salary, 1, 0) 第三个参数是默认值（若无上一行则用 0）

典型应用：环比增长计算

-- 月度订单金额环比
SELECT
    order_date,
    SUM(amount) AS monthly_amount,
    LAG(SUM(amount)) OVER (ORDER BY order_date) AS prev_month,
    (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY order_date))
        / LAG(SUM(amount)) OVER (ORDER BY order_date) * 100 AS pct_change
FROM orders
GROUP BY order_date;

值窗口函数

-- 窗口第一行值 / 最后一行值
SELECT name, salary,
       FIRST_VALUE(name)  OVER (ORDER BY salary DESC) AS highest_paid,
       LAST_VALUE(name)   OVER (ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_paid
FROM employee;

LAST_VALUE 的默认窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，因此常见用法需显式指定到末尾。

聚合窗口函数

常见的 SUM、AVG、COUNT、MAX、MIN 都可以做窗口函数：

SELECT
    id, name, dept_id, salary,
    SUM(salary)   OVER (PARTITION BY dept_id) AS dept_total,
    AVG(salary)   OVER (PARTITION BY dept_id) AS dept_avg,
    MAX(salary)   OVER (PARTITION BY dept_id) AS dept_max,
    COUNT(*)      OVER (PARTITION BY dept_id) AS dept_count,
    SUM(salary)   OVER (ORDER BY id) AS running_total    -- 累计求和
FROM employee;

NTILE（分桶函数）

将结果分为指定数量的组（近似均匀）：

-- 按薪资从高到低，分为 4 个分位
SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employee;
-- 常用于数据分桶分析或抽取样本

窗口帧（Frame）定义

-- 计算移动平均（最近 3 笔订单平均）
SELECT order_date, amount,
       AVG(amount) OVER (
           ORDER BY order_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM orders;

帧选项：

帧定义	含义
`ROWS UNBOUNDED PRECEDING`	从分区第一行到当前行
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	前一行 + 当前行 + 后一行
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	整个分区所有行
`RANGE BETWEEN ...`	按值范围（而非行数）定义帧

小结

窗口函数保留原始行，增加基于窗口的聚合/排名/偏移计算。
ROW_NUMBER / RANK / DENSE_RANK 用于排名；LAG / LEAD 用于访问同行。
SUM / AVG 等聚合函数可作为窗口函数使用（需 OVER 子句）。
窗口帧（ROWS BETWEEN）控制计算范围，无帧定义时默认帧取决于是否存在 ORDER BY。
窗口函数是分析查询和报表场景的核心工具。

上一节：集合操作下一节：公用表表达式（CTE）