MySQL中的窗口函数

深入理解窗口函数（Window Functions）

窗口函数确实经常用于分组后为行分配序号（如1,2,3…），但它的功能远不止于此。窗口函数是SQL中极其强大的分析工具，可以让你在不减少行数的情况下进行复杂计算。

---

窗口函数本质

窗口函数的核心特点是：

• 不折叠行：与GROUP BY不同，窗口函数会保留所有原始行
• 定义窗口：通过OVER()子句指定计算的数据范围（“窗口”）
• 逐行计算：为每一行返回一个基于其所在窗口的计算结果

---

窗口函数家族

1. 排名类函数

• ROW_NUMBER()：连续唯一序号（1,2,3,4…）
• RANK()：相同值同排名，后续跳过（1,2,2,4…）
• DENSE_RANK()：相同值同排名，后续不跳过（1,2,2,3…）

2. 分析类函数

• LEAD(列名, n)：获取当前行后第n行的值
• LAG(列名, n)：获取当前行前第n行的值
• FIRST_VALUE(列名)：窗口第一个值
• LAST_VALUE(列名)：窗口最后一个值
• NTH_VALUE(列名, n)：窗口第n个值

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

• SUM() OVER()
• AVG() OVER()
• COUNT() OVER()
• MAX() OVER()
• MIN() OVER()

---

窗口定义的三要素

窗口函数的强大之处在于可以精确控制"窗口"范围：

函数() OVER([PARTITION BY 分组列][ORDER BY 排序列][ROWS/RANGE 框架]
)

1. PARTITION BY

将数据分成多个组，函数在每个组内独立计算（类似GROUP BY但不合并行）

2. ORDER BY

定义分区内的排序方式，影响序号分配和滑动窗口计算

3. 窗口框架

窗口框架（Window Frame）​​ 是 SQL 窗口函数中一个 ​​高级但极其有用​​ 的功能，它允许你 ​​更精细地控制计算范围​​，而不仅仅是按 PARTITION BY 分组或按 ORDER BY 排序。

窗口框架的作用​​
在 OVER() 子句中，除了 PARTITION BY 和 ORDER BY，你还可以用 ​​ROWS 或 RANGE​​ 来定义：

1. ​​计算时包含哪些行​​（例如：当前行 + 前3行） ​​
2. 是否包含当前行​​
3. ​​是否包含未来的行（FOLLOWING）​​

​​典型应用场景​​
✅ ​​移动平均（Moving Average）​​
✅ ​​累计计算（Running Total）​​
✅ ​​前后行对比（Lag/Lead 分析）​​
✅ ​​滑动窗口统计（如最近5天的总和）​

基本语法

SUM(column) OVER([PARTITION BY ...][ORDER BY ...]ROWS|RANGE BETWEEN <start> AND <end>
)

• ​​ROWS​​ → 按 ​​物理行​​ 计算（固定行数）
• ​​RANGE​​ → 按 ​​逻辑范围​​ 计算（如相同值的行视为同一组）

窗口框架的边界选项

选项	含义
UNBOUNDED PRECEDING	从分区的第一行开始
n PRECEDING	当前行之前的 n 行
CURRENT ROW	当前行
n FOLLOWING	当前行之后的 n 行
UNBOUNDED FOLLOWING	直到分区的最后一行

---

3. 实际案例
案例1：计算3天移动平均（ROWS）

SELECT date,           -- 日期列revenue,        -- 当天的收入AVG(revenue) OVER(  -- 计算收入的移动平均值ORDER BY date   -- 按日期排序ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 包含当前行 + 前2行) AS moving_avg_3day  -- 结果列名
FROM sales;

关键点：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
• 2 PRECEDING = 当前行的前2行
• CURRENT ROW = 当前行
→ 合起来就是 当前行 + 前2行，共3行数据

假设数据是这样的：

date	revenue
2023-01-01	100
2023-01-02	150
2023-01-03	200
2023-01-04	250

查询结果会是：

date	revenue	moving_avg_3day	计算方式
2023-01-01	100	100.0	(100) / 1（只有1天数据）
2023-01-02	150	125.0	(100 + 150) / 2
2023-01-03	200	150.0	(100 + 150 + 200) / 3
2023-01-04	250	200.0	(150 + 200 + 250) / 3

前几行不够怎么办？
• 比如第1天（2023-01-01），前面没有数据，就只算它自己。
• 第2天（2023-01-02），只有前1天的数据，就只算2天的平均。

---

案例2：计算累计到当前行的总和（RANGE）

# 计算销售数据的​​累计收入（running total）​​，也就是从最早日期到当前日期的收入总和
SELECT date,revenue,SUM(revenue) OVER( #对 revenue 列求和ORDER BY date #按日期排序RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW# 计算范围是从最早日期（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）) AS running_total
FROM sales;

关键点解析

1. RANGE vs ROWS：
   • RANGE：按逻辑范围计算（相同日期的行会被合并统计）。
   • ROWS：按物理行计算（严格按行数计算，即使日期相同也会分开统计）。
2. UNBOUNDED PRECEDING：
   • 表示从分区的第一行开始计算（这里是按日期排序后的最早日期）。
3. CURRENT ROW：
   • 计算到当前行为止。

假设原始数据：

date	revenue
2023-01-01	100
2023-01-02	150
2023-01-03	200

查询结果：

date	revenue	running_total	计算逻辑
2023-01-01	100	100	100
2023-01-02	150	250	100 (前一天) + 150
2023-01-03	200	450	250 (前累计) + 200

---

案例3：计算当前行 + 前后各1行的总和（滑动窗口）

SELECT date,revenue,SUM(revenue) OVER(ORDER BY dateROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sliding_sum
FROM sales;

结果示例：

date	revenue	sliding_sum
2023-01-01	100	250
2023-01-02	150	450
2023-01-03	200	600
2023-01-04	250	450

---

4. ROWS vs RANGE 的区别

类型	行为	适用场景
ROWS	按 物理行 计算（固定行数）	移动平均、滑动窗口
RANGE	按 逻辑范围 计算（相同值的行视为同一组）	处理重复值（如相同日期的数据）

示例（RANGE 处理相同日期的数据）：

SELECT date,revenue,SUM(revenue) OVER(ORDER BY dateRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

如果 2023-01-01 有2行数据（100 和 150），RANGE 会 同时计算这两行，而 ROWS 会逐行计算。

---

5. 总结
• 窗口框架（Window Frame） 让你可以 更灵活地定义计算范围，而不仅仅是按分区或排序计算。
• ROWS → 适用于 固定行数 的计算（如移动平均）。
• RANGE → 适用于 逻辑范围 的计算（如相同日期的数据）。
• 常见用途：移动平均、累计计算、滑动窗口统计。

实际应用示例

示例1：计算累计和

# 计算销售数据的每日收入以及累计收入
SELECT date,revenue,SUM(revenue) OVER(ORDER BY date) AS running_total
FROM sales;

示例2：计算同部门薪资排名

SELECT employee_name,department,salary,RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

示例3：计算3个月移动平均

SELECT month,sales,AVG(sales) OVER(ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM monthly_sales;

与GROUP BY的关键区别

特性	窗口函数	GROUP BY
行数	保持原行数	合并行
计算方式	基于窗口计算	基于分组计算
结果	每行都有计算结果	每组一行结果
使用场景	排名、累计、移动平均等分析需求	汇总统计、分组聚合

总结

窗口函数远不止是为行分配简单序号，它是：

1. 一种强大的分析工具
2. 可以访问与当前行相关的其他行
3. 能够进行复杂计算而不减少行数
4. 现代数据分析不可或缺的功能

掌握窗口函数可以让你解决许多传统SQL难以处理的分析问题，如：

• 计算同比/环比增长率
• 识别数据趋势
• 处理复杂的排名和分组分析
• 计算各种滑动窗口指标

补充

（1）RANGE 和 ROWS 在窗口函数中的区别？

核心区别
• ROWS = 按物理行计算（数"行数"）
• RANGE = 按逻辑范围计算（看"值的大小"）

具体区别

特性	ROWS	RANGE
计算方式	按绝对行数	按排序字段的值范围
相同值处理	每行独立计算	相同值会被合并计算
性能	更快	较慢（需要额外计算）
典型用途	移动平均、固定行数计算	处理重复值、按实际范围计算

实际例子说明
假设有这样的销售数据：

-- 测试数据
INSERT INTO sales VALUES('2023-01-01', 100),('2023-01-02', 150),('2023-01-02', 200), -- 注意这里有重复日期('2023-01-03', 50);

使用ROWS的查询

SELECT date,revenue,SUM(revenue) OVER(ORDER BY dateROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_running_total
FROM sales;

结果：

date       | revenue | rows_running_total
-----------+---------+-------------------
2023-01-01 | 100     | 100    ← 第1行
2023-01-02 | 150     | 250    ← 100+150 (第1+2行)
2023-01-02 | 200     | 450    ← 250+200 (第1+2+3行) 
2023-01-03 | 50      | 500    ← 450+50

使用RANGE的查询

SELECT date,revenue,SUM(revenue) OVER(ORDER BY dateRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_running_total
FROM sales;

结果：

date       | revenue | range_running_total
-----------+---------+--------------------
2023-01-01 | 100     | 100    ← 只有这一天
2023-01-02 | 150     | 450    ← 100+150+200 (所有1月2日的数据)
2023-01-02 | 200     | 450    ← 同上（相同日期被合并计算）
2023-01-03 | 50      | 500    ← 450+50

关键区别图示

数据行： [2023-01-01/100] → [2023-01-02/150] → [2023-01-02/200] → [2023-01-03/50]ROWS计算：  行1      行1+2     行1+2+3      行1+2+3+4[100]    [250]     [450]       [500]RANGE计算： date<=1/1  date<=1/2          date<=1/3[100]      [100+150+200]       [100+150+200+50][450]重复两次       [500]

什么时候用哪个？
**用 ROWS **
• 需要计算固定行数（如"最近3行"）
• 数据没有重复排序值
• 需要更高性能

用 RANGE
• 排序字段可能有重复值（如相同日期）
• 需要按实际值范围计算（如"所有小于当前值的行"）
• 做时间序列分析时更准确

进阶技巧
可以组合使用：

-- 计算当前日期及前2天（按日期范围）
SUM(revenue) OVER(ORDER BY dateRANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW
)