MySQL窗口函数 OVER()全解析

自MySQL 8.0版本起，窗口函数（Window Functions）成为了SQL查询中极为强大且实用的一项特性。与传统的聚合函数（如 GROUP BY）不同，窗口函数在进行聚合计算时，不会将多行合并成一行，而是保留每一行的原始数据，同时能够访问与当前行相关的其他行的数据。本文将带你全面解析MySQL中的 OVER() 子句及其窗口函数的用法。

一、窗口函数基础语法

窗口函数的核心在于 OVER() 子句，它定义了函数的“窗口”范围。基础语法如下：

函数名([参数]) OVER (
  [PARTITION BY 分区字段]
  [ORDER BY 排序字段 [ASC|DESC]]
  [FRAME 子句]
)

• PARTITION BY：用于将结果集分区，窗口函数在每个分区内独立计算。类似于 GROUP BY，但不会压缩行数。

• ORDER BY：定义分区内的排序规则，某些函数（如 ROW_NUMBER、累计求和）强依赖于此子句。

• FRAME 子句：进一步限制窗口范围，如当前行前几行到后几行（如 ROWS BETWEEN ... AND ...）。

二、数据准备

为了更好地演示，我们创建一个员工表，并插入一些测试数据：

-- 创建表
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO employees (id, name, department, salary) VALUES 
(1, 'Alice', 'HR', 5000.00),
(2, 'Bob', 'IT', 8000.00),
(3, 'Charlie', 'IT', 7500.00),
(4, 'David', 'HR', 5500.00),
(5, 'Eve', 'IT', 9000.00),
(6, 'Frank', 'Sales', 6500.00);

三、常用窗口函数分类与实战

1. 排序函数

排序函数用于为分区内的行生成序号，最常用于TOP-N问题。

• ROW_NUMBER()：按顺序编号，相同值也会分配不同序号（1, 2, 3）。

• RANK()：相同值同序号，后续序号跳过（1, 1, 3）。

• DENSE_RANK()：相同值同序号，后续序号不跳过（1, 1, 2）。

示例：查询每个部门的员工薪资排名

SELECT 
  name, 
  department, 
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_num
FROM employees;

2. 聚合函数作为窗口函数

常见的 SUM()、AVG()、COUNT()、MAX()、MIN() 也可以配合 OVER() 使用，实现累计统计或分组带明细统计。

示例：查询员工信息，附带部门总薪资与全局累计薪资

SELECT 
  name, 
  department, 
  salary,
  -- 部门薪资总计（不包含排序则默认整个分区的聚合值）
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  -- 按薪资从低到高的全局累计求和
  SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees
WHERE salary > 4000;

注意：在上述代码中，筛选条件中的大于号已经转义为 >，以确保在Ueditor等富文本编辑器中显示正常，不会被误解析为HTML标签。

3. 偏移函数（LEAD / LAG）

用于获取当前行之前（LAG）或之后（LEAD）的第N行的值，常用于计算环比、同比增减。

示例：计算员工薪资与前一名员工的薪资差值

SELECT 
  name, 
  department, 
  salary,
  LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS prev_salary,
  salary - LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS salary_diff
FROM employees;

4. 取值函数（FIRST_VALUE / LAST_VALUE / NTH_VALUE）

用于获取窗口内指定排序后的第一个、最后一个或第N个值。

示例：查询部门内薪资最高与最低的值

SELECT 
  name, 
  department, 
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_max_salary,
  -- 注意：LAST_VALUE默认窗口范围是到当前行，必须指定ROWS BETWEEN才能取到整个分区的最后行
  LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS dept_min_salary
FROM employees;

四、深入理解 FRAME 子句（窗口帧）

当使用 ORDER BY 时，默认的窗口帧范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从分区第一行到当前行）。我们可以通过 FRAME 子句自定义这个范围。

语法：

ROWS BETWEEN frame_start AND frame_end
-- 或者
RANGE BETWEEN frame_start AND frame_end

示例：计算每个员工与前后各一名员工的薪资移动平均值

SELECT 
  name, 
  salary,
  AVG(salary) OVER (
    ORDER BY salary 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg
FROM employees;

ROWS 与 RANGE 的区别：ROWS 是物理偏移，指当前行的前几行/后几行；RANGE 是逻辑偏移，会包含与当前行 ORDER BY 值相等的所有行。

五、总结与在线实操

窗口函数极大简化了复杂SQL的编写，避免了以往需要通过自关联或用户变量来实现排名、累计计算的低效写法。掌握 PARTITION BY 与 ORDER BY 的组合，以及不同函数的特性，是提升SQL编写能力的关键。

MySQL窗口函数OVER子句PARTITION BY排序函数LEAD LAG