SQL：窗口函数（Window Functions）
目录
什么是窗口函数？
基本语法结构
常见的窗口函数分类
1️⃣ 排名类函数
2️⃣ 聚合类函数（不影响原始行）
3️⃣ 值访问函数
窗口范围说明（ROWS / RANGE）
什么是窗口函数？
窗口函数是一类 SQL 函数，在不分组的情况下，可以对查询结果中的某一“窗口”范围内的数据进行计算。
不同于聚合函数（如 SUM, AVG），窗口函数不会压缩行，而是为每一行返回一个计算值。
基本语法结构
<窗口函数>([参数]) OVER ([PARTITION BY 子句][ORDER BY 子句][ROWS 或 RANGE 子句]
)
-
PARTITION BY: 把数据分组，每组内独立执行函数（类似 GROUP BY，但不合并行）。 -
ORDER BY: 确定组内数据顺序。 -
ROWS BETWEEN: 精确控制窗口范围（例如：过去3行）。
举例：
SELECT department_id,employee_id,salary,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;
常见的窗口函数分类
1️⃣ 排名类函数
| 函数 | 描述 |
|---|---|
ROW_NUMBER() | 每一组数据中按顺序分配唯一行号 |
RANK() | 同分并列，跳跃排名（如：1,1,3） |
DENSE_RANK() | 同分不跳（如：1,1,2） |
NTILE(n) | 将结果分为 n 个桶，每行给出所属桶编号 |
1.ROW_NUMBER()
意义：按顺序为每行分配一个“唯一编号”。
名称：ROW_NUMBER = 行号。
语法示例：
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
用途：
-
对每个部门中员工薪资进行唯一编号（常用于分页、去重等）
2.RANK()
意义：返回排名，相同值并列排名，后续名次跳跃。
名称：RANK = 排名。
语法示例：
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
| salary | RANK |
|---|---|
| 1000 | 1 |
| 1000 | 1 |
| 900 | 3 |
3.DENSE_RANK()
意义：与 RANK() 类似，但排名连续不跳跃。
名称：DENSE_RANK = 密集排名。
语法示例：
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
| salary | DENSE_RANK |
|---|---|
| 1000 | 1 |
| 1000 | 1 |
| 900 | 2 |
4. NTILE(n)
意义：将数据平均分成 n 个桶，每行返回桶编号。
名称：NTILE = "N Tile"，即“分桶”。
语法示例：
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
用途：
-
按工资水平将员工划分为四个档次（四分位分析）
2️⃣ 聚合类函数（不影响原始行）
| 函数 | 描述 |
|---|---|
SUM(), AVG(), MAX(), MIN() | 聚合函数 + 窗口：在窗口范围内计算 |
COUNT() | 窗口内的行数统计 |
5. SUM(expr)、AVG(expr)、MAX(expr)、MIN(expr)
意义：在窗口内执行聚合计算，但不影响原始行展示。
名称：
-
SUM= 总和 -
AVG= 平均 -
MAX= 最大值 -
MIN= 最小值
语法示例：
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_total
用途：
-
滚动汇总、组内对比等。
3️⃣ 值访问函数
| 函数 | 描述 |
|---|---|
LAG(expr, n, default) | 返回当前行前第 n 行的值 |
LEAD(expr, n, default) | 返回当前行后第 n 行的值 |
FIRST_VALUE(expr) | 窗口中的第一个值 |
LAST_VALUE(expr) | 窗口中的最后一个值 |
6. LAG(expr, offset, default)
意义：返回当前行的前 N 行的值。
名称：LAG = 滞后。
语法示例：
LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS prev_salary
用途：
-
分析趋势、比较环比。
7. LEAD(expr, offset, default)
意义：返回当前行的后 N 行的值。
名称：LEAD = 领先。
语法示例：
LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS next_salary
用途：
-
预测未来、构建时间序列对比。
8. FIRST_VALUE(expr)
意义：返回窗口中按排序后第一行的值。
名称：FIRST_VALUE = 第一个值。
语法示例：
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS first_salary
9. LAST_VALUE(expr)
意义：返回窗口中最后一行的值。
名称：LAST_VALUE = 最后一个值。
注意： LAST_VALUE 需要配合 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 才能获取整个分区最后一行值。
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_salary
窗口范围说明（ROWS / RANGE）
ROWS 是基于物理行号
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
RANGE 是基于值范围
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
常见用途总结
| 场景 | 推荐函数 |
|---|---|
| 排名 | ROW_NUMBER, RANK, DENSE_RANK |
| 对比趋势 | LAG, LEAD, FIRST_VALUE, LAST_VALUE |
| 滚动汇总 | SUM, AVG, COUNT + ROWS BETWEEN |
| 分段统计 | NTILE |
| 时间窗口 | RANGE BETWEEN |