SQL 窗口函数原理与应用

一、引言

在日常 SQL 开发中，我们经常会用到 聚合函数（如 SUM、AVG、COUNT），但它们有一个明显的局限：只能汇总数据，无法同时保留明细。

而 窗口函数 (Window Function) 的出现，正好弥补了这一点。
它能让你在 不丢失行的情况下，对一组数据进行计算。

换句话说，窗口函数让我们既能看到全局汇总，又能保留每一行的细节。

本文将带你一步步理解窗口函数的原理，并通过实例掌握常见应用。

---

二、窗口函数的基本原理

1. 窗口函数 vs 聚合函数

• 聚合函数：把多行“压缩”为一行。

• 窗口函数：保留每一行，再把“整体结果”附加到每行上。

示例：

sales 表：

dept	employee	sales
A	Alice	100
A	Bob	200
A	Carol	150
B	David	300
B	Eva	250

聚合函数写法：

 

SELECT dept, SUM(sales) AS total_sales FROM sales GROUP BY dept;

结果（明细丢失）：

dept	total_sales
A	450
B	550

窗口函数写法：

 

SELECT dept, employee, sales, SUM(sales) OVER (PARTITION BY dept) AS total_sales FROM sales;

结果（保留明细）：

dept	employee	sales	total_sales
A	Alice	100	450
A	Bob	200	450
A	Carol	150	450
B	David	300	550
B	Eva	250	550

👉 聚合函数是“压缩”，窗口函数是“附加”。

---

2. OVER 子句的结构

一个窗口函数通常这样写：

 

func(...) OVER ( PARTITION BY ... -- 定义分组 ORDER BY ... -- 定义排序 ROWS BETWEEN ... -- 定义窗口范围 )

其中：

• PARTITION BY：按什么字段分组（类似 GROUP BY，但不合并行）

• ORDER BY：定义计算顺序

• ROWS BETWEEN：限定窗口大小，比如累计和、移动平均

---

三、常见窗口函数分类

1. 排名函数

• ROW_NUMBER()：严格顺序编号

• RANK()：并列排名，跳号

• DENSE_RANK()：并列排名，不跳号

示例：

name	salary	ROW_NUMBER	RANK	DENSE_RANK
Alice	10000	1	1	1
Bob	9000	2	2	2
Carol	9000	3	2	2
David	8000	4	4	3

---

2. 聚合类

• SUM() OVER：累计和

• AVG() OVER：移动平均

---

3. 分析类

• LAG() / LEAD()：取前一行 / 后一行的值

• FIRST_VALUE() / LAST_VALUE()：取窗口内首/尾值

---

四、典型应用场景

1. 累计和

 

SELECT month, sales, SUM(sales) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales FROM sales;

结果：逐月累计销售额。

---

2. 环比分析

 

SELECT month, sales, LAG(sales, 1) OVER (ORDER BY month) AS prev_sales, sales - LAG(sales, 1) OVER (ORDER BY month) AS diff FROM sales;

结果：当前月销售额与上月对比。

---

3. 移动平均

 

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

结果：3 日移动平均，平滑趋势。

---

五、业务案例：电商用户消费分析

综合运用窗口函数，可以完成常见的电商分析任务：

1. 累计消费（SUM OVER）

2. 分组排名（RANK）

3. 环比变化（LAG）

示例结果：

user_id	month	amount	cumulative	rank_in_month	pct_change
U1	Jan	100	100	3	NULL
U1	Feb	200	300	2	100%
U1	Mar	150	450	1	-25%

👉 窗口函数让 SQL 直接完成用户行为分析，而无需复杂的多表 join。

---

六、总结

1. 原理：窗口函数不会压缩数据，而是附加计算结果。

2. 函数：排名、累计、环比、移动平均是最常用的场景。

3. 实战：电商、金融、运营分析里都有大量应用。

一句话记忆：

聚合函数回答“整体情况”，窗口函数回答“每一行在整体中的位置与关系”。

掌握窗口函数，你就能把 SQL 从“查数”升级为“分析”。