mysql-over 窗口函数

MySQL 的 OVER() 窗口函数（也称为分析函数）是 MySQL 8.0 及以上版本引入的高级特性，用于在一组与当前行相关的行上执行计算，同时保留每行的独立结果（区别于 GROUP BY 聚合后压缩行数）。它的核心是定义一个“窗口”（即一组相关行），并在这个窗口内对数据进行计算。

一、窗口函数的原理

窗口函数的语法结构通常为： 

1
<函数> OVER ([PARTITION BY 列名] [ORDER BY 列名] [ROWS/RANGE 窗口范围])

其核心原理可拆解为 3 个部分：

1. 窗口的定义（OVER() 子句）

OVER() 子句用于定义“窗口”的范围，即计算时参考的行集合，主要通过以下参数控制：

  • PARTITION BY 列名：将数据按指定列分组（类似 GROUP BY），每个分组形成一个独立的窗口。如果不指定，则整个表作为一个窗口。
  • ORDER BY 列名：对每个窗口内的行进行排序，决定计算的顺序（如排名、累计求和的顺序）。
  • ROWS/RANGE 窗口范围：进一步限制窗口内的行范围（可选），例如“当前行及前 2 行”“当前行到分组末尾”等。常见用法：
    • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从分组第一行到当前行（默认）。
    • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：当前行及前 2 行。
    • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：当前行到分组最后一行。

2. 与聚合函数的核心区别

  • GROUP BY 聚合：将分组后的多行数据压缩为一行结果（如 SUM() 后每个分组只留一行总和）。
  • 窗口函数：不压缩行数，每行都会保留，同时在每行后附加窗口内的计算结果（如每行都显示所在分组的总和）。

3. 执行顺序

窗口函数在 SQL 执行流程中位于 GROUP BY、HAVING 之后，ORDER BY 之前。即：先分组聚合，再计算窗口函数，最后排序输出。

二、常用窗口函数及使用场景

窗口函数分为两类：专用窗口函数（如排名函数）和聚合窗口函数（如 SUM()、AVG() 结合 OVER()）。以下是典型使用场景：

1. 排名场景（专用窗口函数）

常用函数：ROW_NUMBER()、RANK()、DENSE_RANK()，用于对数据进行排名。

示例表（student_score）：

id class name score
1 1 张三 90
2 1 李四 85
3 1 王五 90
4 2 赵六 88

需求：按班级分组，对学生成绩排名。 

1
2
3
4
5
6
SELECT 
  id, class, name, score,
  ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS row_num,  -- 唯一排名（即使分数相同）
  RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rnk,            -- 并列排名（跳过后续名次）
  DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS dense_rnk -- 并列排名（不跳过后续名次）
FROM student_score;

结果：

id class name score row_num rnk dense_rnk
1 1 张三 90 1 1 1
3 1 王五 90 2 1 1
2 1 李四 85 3 3 2
4 2 赵六 88 1 1 1

场景适用：班级排名、销售业绩排名、考试成绩排名等。

2. 分组内聚合统计（聚合窗口函数）

将 SUM()、AVG()、MAX() 等聚合函数与 OVER() 结合，计算每行所在分组的聚合值（不压缩行数）。

需求：显示每个学生的成绩，同时显示其所在班级的平均分和最高分。 

1
2
3
4
5
SELECT 
  id, class, name, score,
  AVG(score) OVER (PARTITION BY class) AS class_avg,  -- 班级平均分
  MAX(score) OVER (PARTITION BY class) AS class_max   -- 班级最高分
FROM student_score;

结果：

id class name score class_avg class_max
1 1 张三 90 88.33 90
2 1 李四 85 88.33 90
3 1 王五 90 88.33 90
4 2 赵六 88 88 88

场景适用：在保留明细数据的同时，展示分组统计信息（如电商订单明细中显示用户的总消费额）。

3. 累计计算（移动聚合）

通过 ROWS/RANGE 定义窗口范围，实现累计求和、累计平均等。

示例表（sales）：

date amount
2023-01-01 100
2023-01-02 200
2023-01-03 150
2023-01-04 300

需求：计算每日销售额的累计总和（从月初到当天）。 

1
2
3
4
SELECT 
  date, amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM sales;

结果：

date amount cumulative_sum
2023-01-01 100 100
2023-01-02 200 300
2023-01-03 150 450
2023-01-04 300 750

场景适用：累计销售额、累计用户数、月度达成率等时间序列累计指标。

4. 移动平均值（滑动窗口）

通过 ROWS 定义固定大小的滑动窗口，计算最近 N 行的平均值（如最近 3 天的平均销售额）。

需求：计算每日销售额及最近 3 天的移动平均值（包含当天）。 

1
2
3
4
SELECT 
  date, amount,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3d
FROM sales;

结果：

date amount moving_avg_3d
2023-01-01 100 100.00
2023-01-02 200 150.00
2023-01-03 150 150.00
2023-01-04 300 216.67

场景适用：平滑数据波动（如股票价格移动平均、销售额趋势分析）。

5. 分组内取首尾值或指定位置值

使用 FIRST_VALUE()、LAST_VALUE() 或 NTH_VALUE() 获取分组内的首行、末行或第 N 行数据。

需求：按班级分组，显示每个学生的成绩及班级内的最高分（首行，按成绩降序）。 

1
2
3
4
SELECT 
  id, class, name, score,
  FIRST_VALUE(score) OVER (PARTITION BY class ORDER BY score DESC) AS class_top_score
FROM student_score;

结果：

id class name score class_top_score
1 1 张三 90 90
3 1 王五 90 90
2 1 李四 85 90
4 2 赵六 88 88

场景适用：展示分组内的标杆值（如每个部门的最高工资、每个地区的首单时间）。

三、总结

  • 原理：窗口函数通过 OVER() 定义“窗口”（一组相关行），在不压缩行数的前提下，对每行执行窗口内的计算。
  • 核心优势：同时保留明细数据和聚合结果，避免了 GROUP BY 后需要 JOIN 才能关联明细的繁琐。
  • 典型场景：排名、分组统计、累计计算、滑动窗口分析、分组内首尾值获取等。

窗口函数特别适合报表生成、数据分析等场景，能极大简化复杂统计逻辑的 SQL 编写。