目录
什么是窗口函数？
基本语法结构
为什么要用窗口函数？
常见的窗口函数分类
1️⃣ 排名类函数
2️⃣ 聚合类函数（不影响原始行）
3️⃣ 值访问函数
窗口范围说明（ROWS / RANGE）
什么是窗口函数？
窗口函数是一类 SQL 函数，在不分组的情况下，可以对查询结果中的某一“窗口”范围内的数据进行计算。
窗口函数 = 能在每一行数据上“看见”其他相关行的函数。
它允许你在不聚合（不合并行）的前提下，对一组相关行进行计算，并把结果加回到原来的每一行上。
不同于聚合函数（如 SUM, AVG），窗口函数不会压缩行，而是为每一行返回一个计算值。
基本语法结构
<窗口函数>([参数]) OVER ([PARTITION BY 子句][ORDER BY 子句][ROWS 或 RANGE 子句]
)函数名(...) OVER (PARTITION BY ... -- 按谁分组（可选）ORDER BY ... -- 按什么顺序（常用）ROWS BETWEEN ... -- 控制范围（高级用法）
)
-
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;
为什么要用窗口函数？
在传统的 SQL 聚合函数（如 SUM, AVG, COUNT）中，如果你写：
SELECT person_id, SUM(weight)
FROM Queue
GROUP BY person_id;
你得到的是每个 person_id 的总重，但你没法看到其他人的情况 —— 一条记录一条记录独立计算。
而窗口函数就像给每一行配了一个“望远镜”，能看到前面的、后面的或整组内的数据，然后基于这些数据算出“每一行自己的视角”。
举个通俗例子（排队上车）：
有一张表：
| turn | person_name | weight |
|---|---|---|
| 1 | Alice | 250 |
| 2 | Bob | 300 |
| 3 | Charlie | 200 |
| 4 | David | 400 |
你想知道：每个人上车时，前面所有人（包括自己）累计多重了？
👉 用窗口函数就可以这样写：
SELECT person_name,weight,SUM(weight) OVER (ORDER BY turn) AS cumulative_weight
FROM Queue;
输出结果：
| person_name | weight | cumulative_weight |
|---|---|---|
| Alice | 250 | 250 |
| Bob | 300 | 550 (250+300) |
| Charlie | 200 | 750 (250+300+200) |
| David | 400 | 1150 |
🚀 神奇的是，你没有把这些行合并，而是在每行里加上了“前面和自己的累积情况”。
常见的窗口函数分类
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 |
)
