在窗口函数必知必会（Ⅰ）中，我们已经介绍了窗口函数的核心价值（聚合 + 保留明细）、语法结构、窗口帧规则以及事件流法。这些是理解窗口函数的"内功"。
本文将进入"招式"层面，系统讲解常用的窗口函数，分为三大类：
- 排序函数：ROW_NUMBER / RANK / DENSE_RANK / NTILE / PERCENT_RANK
- 偏移函数：LAG / LEAD / FIRST_VALUE / LAST_VALUE
- 聚合窗口函数：SUM / COUNT / AVG / MAX / MIN
每个函数都配有简例，帮助快速理解用法和典型场景。
第一章 排序函数
排序函数用于给数据"打排名"，是最常用的窗口函数之一。
1.1 ROW_NUMBER —— 强行编号
语法：
ROW_NUMBER()OVER(PARTITIONBY分区字段ORDERBY排序字段)特点：每行分配一个唯一的连续整数，即使值相同也不会并列。
简例：
SELECTname,score,ROW_NUMBER()OVER(ORDERBYscoreDESC)ASrnFROMt_score;| name | score | rn |
|---|---|---|
| 张三 | 95 | 1 |
| 李四 | 90 | 2 |
| 王五 | 90 | 3 |
| 赵六 | 85 | 4 |
李四和王五都是 90 分，但
ROW_NUMBER强行给了一个先后顺序（2 和 3），不会并列。
1.2 RANK —— 并列跳号
语法：
RANK()OVER(PARTITIONBY分区字段ORDERBY排序字段)特点：相同值并列排名，后续排名跳过已占的名次。
简例：
SELECTname,score,RANK()OVER(ORDERBYscoreDESC)ASrkFROMt_score;| name | score | rk |
|---|---|---|
| 张三 | 95 | 1 |
| 李四 | 90 | 2 |
| 王五 | 90 | 2 |
| 赵六 | 85 | 4 |
李四和王五并列第 2，下一个排名直接跳到第 4（没有第 3）。
1.3 DENSE_RANK —— 并列不跳号
语法：
DENSE_RANK()OVER(PARTITIONBY分区字段ORDERBY排序字段)特点：相同值并列排名，后续排名不跳号。
简例：
SELECTname,score,DENSE_RANK()OVER(ORDERBYscoreDESC)ASdrkFROMt_score;| name | score | drk |
|---|---|---|
| 张三 | 95 | 1 |
| 李四 | 90 | 2 |
| 王五 | 90 | 2 |
| 赵六 | 85 | 3 |
李四和王五并列第 2，下一个排名是第 3（不跳号）。
1.4 三者对比总结
用同一份数据（张三95、李四90、王五90、赵六85）对比：
| name | score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| 张三 | 95 | 1 | 1 | 1 |
| 李四 | 90 | 2 | 2 | 2 |
| 王五 | 90 | 3 | 2 | 2 |
| 赵六 | 85 | 4 | 4 | 3 |
| 函数 | 并列？ | 跳号？ | 适用场景 |
|---|---|---|---|
| ROW_NUMBER | 不并列 | — | 需要唯一排名，如取 Top-N 去重 |
| RANK | 并列 | 跳号 | 竞赛排名，如"第1名、第2名（并列）、第4名" |
| DENSE_RANK | 并列 | 不跳号 | 需要连续排名，如"一等奖、二等奖（并列）、三等奖" |
如何选择：
- 要唯一编号 →
ROW_NUMBER - 要并列且允许跳号 →
RANK - 要并列但不跳号 →
DENSE_RANK
1.5 NTILE —— 分桶
语法：
NTILE(N)OVER(PARTITIONBY分区字段ORDERBY排序字段)特点：将分区内的数据尽量均匀地分成 N 个"桶"（组），返回每行所在的桶编号。
简例：
SELECTname,score,NTILE(3)OVER(ORDERBYscoreDESC)ASbucketFROMt_score;| name | score | bucket |
|---|---|---|
| 张三 | 95 | 1 |
| 李四 | 90 | 1 |
| 王五 | 90 | 2 |
| 赵六 | 85 | 3 |
4 个人分 3 个桶：第1桶2人，第2桶1人，第3桶1人。尽量均匀分配。
典型场景：
- 将用户按消费金额分为高/中/低三档
- 将数据按百分比分段处理
分配规则：
- 如果不能整除，前面的桶多分 1 个
- 例如 10 个人分 3 组：4, 3, 3
1.6 PERCENT_RANK —— 百分位排名
语法：
PERCENT_RANK()OVER(PARTITIONBY分区字段ORDERBY排序字段)特点：返回当前行在分区中的百分位排名，值为[0, 1]之间的小数。
计算公式：
PERCENT_RANK = (当前行的RANK - 1) / (分区总行数 - 1)简例：
SELECTname,score,PERCENT_RANK()OVER(ORDERBYscoreDESC)ASpct_rankFROMt_score;| name | score | pct_rank |
|---|---|---|
| 张三 | 95 | 0.0 |
| 李四 | 90 | 0.333… |
| 王五 | 90 | 0.333… |
| 赵六 | 85 | 1.0 |
计算过程：
- 张三：RANK=1，(1-1)/(4-1) = 0.0
- 李四/王五：RANK=2，(2-1)/(4-1) = 0.333
- 赵六：RANK=4，(4-1)/(4-1) = 1.0
典型场景：
- 判断某个值处于什么百分位（如"你的薪资超过了 80% 的人"）
- 异常检测：百分位接近 0 或 1 的可能是异常值
第二章 偏移函数
偏移函数用于"跨行取值"——获取当前行的前一行或后一行的数据。
2.1 LAG —— 取前 N 行的值
语法：
LAG(字段名,N,默认值)OVER(PARTITIONBY分区字段ORDERBY排序字段)| 参数 | 说明 |
|---|---|
| 字段名 | 要取的字段 |
| N | 往前偏移几行（默认 1） |
| 默认值 | 超出边界时的返回值（默认 NULL） |
简例：
SELECTdate,amount,LAG(amount)OVER(ORDERBYdate)ASprev_amount,amount-LAG(amount)OVER(ORDERBYdate)ASdiffFROMt_daily_sales;| date | amount | prev_amount | diff |
|---|---|---|---|
| 2024-01-01 | 100 | NULL | NULL |
| 2024-01-02 | 200 | 100 | 100 |
| 2024-01-03 | 300 | 200 | 100 |
| 2024-01-04 | 400 | 300 | 100 |
第一行没有"上一行"，所以
prev_amount为 NULL。
典型场景：
- 环比计算：本月 vs 上月
- 登录间隔分析：本次登录 vs 上次登录
- 用户留存：次日是否回访
2.2 LEAD —— 取后 N 行的值
语法：
LEAD(字段名,N,默认值)OVER(PARTITIONBY分区字段ORDERBY排序字段)参数含义与 LAG 相同，只是方向相反——向后取值。
简例：
SELECTdate,amount,LEAD(amount)OVER(ORDERBYdate)ASnext_amountFROMt_daily_sales;| date | amount | next_amount |
|---|---|---|
| 2024-01-01 | 100 | 200 |
| 2024-01-02 | 200 | 300 |
| 2024-01-03 | 300 | 400 |
| 2024-01-04 | 400 | NULL |
最后一行没有"下一行"，所以
next_amount为 NULL。
典型场景：
- 预测/趋势：看下一个时间点的值
- 会话间隔：下次购买距本次多久
2.3 FIRST_VALUE —— 取分组内第一个值
语法：
FIRST_VALUE(字段名)OVER(PARTITIONBY分区字段ORDERBY排序字段)特点：返回分区内排序后的第一个值。
简例：
SELECTdept,name,salary,FIRST_VALUE(name)OVER(PARTITIONBYdeptORDERBYsalaryDESC)AStop_employeeFROMt_employee;| dept | name | salary | top_employee |
|---|---|---|---|
| 技术部 | 张三 | 25000 | 张三 |
| 技术部 | 李四 | 20000 | 张三 |
| 技术部 | 王五 | 18000 | 张三 |
| 市场部 | 赵六 | 22000 | 赵六 |
| 市场部 | 孙七 | 15000 | 赵六 |
每个部门都能看到薪资最高的员工是谁。
典型场景：
- 查看每个分组中的最大/最小/首条记录
- 拼接"组内最优"字段到每一行
2.4 LAST_VALUE —— 取分组内最后一个值
语法：
LAST_VALUE(字段名)OVER(PARTITIONBY分区字段ORDERBY排序字段ROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)注意：
LAST_VALUE必须显式指定窗口帧ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，否则默认窗口帧只到当前行，取到的不是真正的"最后一个值"。
简例：
SELECTdept,name,salary,LAST_VALUE(name)OVER(PARTITIONBYdeptORDERBYsalaryDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASbottom_employeeFROMt_employee;| dept | name | salary | bottom_employee |
|---|---|---|---|
| 技术部 | 张三 | 25000 | 王五 |
| 技术部 | 李四 | 20000 | 王五 |
| 技术部 | 王五 | 18000 | 王五 |
| 市场部 | 赵六 | 22000 | 孙七 |
| 市场部 | 孙七 | 15000 | 孙七 |
每个部门都能看到薪资最低的员工是谁。
踩坑提醒：
-- ❌ 错误：不加窗口帧，结果可能不符合预期LAST_VALUE(name)OVER(PARTITIONBYdeptORDERBYsalaryDESC)-- ✅ 正确：显式指定全分区窗口帧LAST_VALUE(name)OVER(PARTITIONBYdeptORDERBYsalaryDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)2.5 偏移函数对比
| 函数 | 方向 | 边界返回 | 典型场景 |
|---|---|---|---|
| LAG | 向前（上方） | NULL | 环比、间隔分析、留存 |
| LEAD | 向后（下方） | NULL | 趋势预测、下次购买 |
| FIRST_VALUE | 分组内第一个 | 始终有值 | 组内最优/首条 |
| LAST_VALUE | 分组内最后一个 | 需显式窗口帧 | 组内最差/末条 |
第三章 聚合窗口函数
将常见的聚合函数（SUM / COUNT / AVG / MAX / MIN）放在窗口中执行，实现"聚合 + 保留明细"。
3.1 三种聚合模式
聚合窗口函数的核心在于窗口帧的不同，决定了聚合的"范围"：
| 模式 | 写法 | 聚合范围 | 效果 |
|---|---|---|---|
| 全分区聚合 | SUM(col) OVER (PARTITION BY col) | 整个分区 | 每行都看到相同的汇总值 |
| 累计聚合 | SUM(col) OVER (ORDER BY col) | 从第一行到当前行 | 逐行累加 |
| 滑动窗口 | SUM(col) OVER (ORDER BY col ROWS BETWEEN N PRECEDING AND N FOLLOWING) | 当前行 ± N 行 | 移动求和 |
这三种模式的区别完全来自窗口帧的不同，回顾教程（一）2.2.3 节的默认窗口帧规则。
3.2 全分区聚合
语法：
聚合函数(字段)OVER(PARTITIONBY分区字段)不加 ORDER BY，窗口帧默认为整个分区。
简例：
SELECTname,dept,salary,SUM(salary)OVER(PARTITIONBYdept)ASdept_total,salary/SUM(salary)OVER(PARTITIONBYdept)ASsalary_ratioFROMt_employee;| name | dept | salary | dept_total | salary_ratio |
|---|---|---|---|---|
| 张三 | 技术部 | 25000 | 63000 | 0.397 |
| 李四 | 技术部 | 20000 | 63000 | 0.317 |
| 王五 | 技术部 | 18000 | 63000 | 0.286 |
| 赵六 | 市场部 | 22000 | 37000 | 0.595 |
| 孙七 | 市场部 | 15000 | 37000 | 0.405 |
dept_total每行都一样（整个部门的总薪资），可以用来算每个人的薪资占比。
典型场景：
- 计算占比（部分 / 整体）
- 计算每个人的贡献度
- 与整体平均值对比
3.3 累计聚合
语法：
聚合函数(字段)OVER(ORDERBY排序字段)有 ORDER BY，窗口帧默认为"从第一行到当前行"。
简例：
SELECTdate,amount,SUM(amount)OVER(ORDERBYdate)AScumulative_sumFROMt_daily_sales;| date | amount | cumulative_sum |
|---|---|---|
| 2024-01-01 | 100 | 100 |
| 2024-01-02 | 200 | 300 |
| 2024-01-03 | 300 | 600 |
| 2024-01-04 | 400 | 1000 |
每一行的
cumulative_sum都是从第一天到当天的累计总和。
典型场景：
- 累计销售额 / 累计用户数
- 累计占比（用于 ABC 分析、帕累托图）
- 商品等级划分（累计销售额占比 × 10）
3.4 滑动窗口聚合
语法：
聚合函数(字段)OVER(ORDERBY排序字段ROWSBETWEENNPRECEDINGANDNFOLLOWING)需要显式指定窗口帧。
简例（3日移动平均）：
SELECTdate,amount,AVG(amount)OVER(ORDERBYdateROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmoving_avgFROMt_daily_sales;| date | amount | moving_avg |
|---|---|---|
| 2024-01-01 | 100 | 150.0 |
| 2024-01-02 | 200 | 200.0 |
| 2024-01-03 | 300 | 300.0 |
| 2024-01-04 | 400 | 350.0 |
| 2024-01-05 | 500 | 450.0 |
第1行的移动平均 = (100+200)/2 = 150（前面没有数据）
第2行的移动平均 = (100+200+300)/3 = 200
第3行的移动平均 = (200+300+400)/3 = 300
典型场景：
- N日移动平均（平滑趋势线）
- 滑动窗口内的最大/最小值
- 短期波动分析
3.5 各聚合函数的窗口用法对比
| 函数 | 全分区 | 累计 | 滑动窗口 | 典型场景 |
|---|---|---|---|---|
| SUM | 总额、占比 | 累计求和 | 滑动求和 | 销售额分析 |
| COUNT | 总数、计数 | 累计计数 | 滑动计数 | 用户增长分析 |
| AVG | 整体均值 | 累计均值 | 移动平均 | 趋势平滑 |
| MAX | 全局最大值 | 截至目前的最大值 | 窗口内最大值 | 峰值检测 |
| MIN | 全局最小值 | 截至目前的最小值 | 窗口内最小值 | 谷值检测 |
附录：窗口函数速查表
| 函数 | 用途 | 语法模板 |
|---|---|---|
| ROW_NUMBER | 唯一行号 | ROW_NUMBER() OVER (PARTITION BY col ORDER BY col) |
| RANK | 并列跳号排名 | RANK() OVER (PARTITION BY col ORDER BY col) |
| DENSE_RANK | 并列不跳号排名 | DENSE_RANK() OVER (PARTITION BY col ORDER BY col) |
| NTILE(N) | 分桶 | NTILE(N) OVER (PARTITION BY col ORDER BY col) |
| PERCENT_RANK | 百分位排名 | PERCENT_RANK() OVER (PARTITION BY col ORDER BY col) |
| LAG(col, N) | 取前第 N 行 | LAG(col, N) OVER (PARTITION BY col ORDER BY col) |
| LEAD(col, N) | 取后第 N 行 | LEAD(col, N) OVER (PARTITION BY col ORDER BY col) |
| FIRST_VALUE(col) | 分组内第一个值 | FIRST_VALUE(col) OVER (PARTITION BY col ORDER BY col) |
| LAST_VALUE(col) | 分组内最后一个值 | LAST_VALUE(col) OVER (... ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) |
| SUM(col) | 窗口内求和 | SUM(col) OVER (PARTITION BY col [ORDER BY col]) |
| COUNT(col) | 窗口内计数 | COUNT(col) OVER (PARTITION BY col [ORDER BY col]) |
| AVG(col) | 窗口内平均 | AVG(col) OVER (PARTITION BY col [ORDER BY col]) |
| MAX(col) | 窗口内最大值 | MAX(col) OVER (PARTITION BY col [ORDER BY col]) |
| MIN(col) | 窗口内最小值 | MIN(col) OVER (PARTITION BY col [ORDER BY col]) |
写在最后
本文系统讲解了 14 个常用窗口函数的语法和用法。限于篇幅和笔者水平，部分函数的边界行为和高级用法可能未完全覆盖，示例也相对简化。如有疏漏或表述不当之处，欢迎指正交流。
学完"内功"（教程一）和"招式"（本文），接下来就是"实战"。推荐继续阅读《窗口函数教程（三）：面试考察重点》，通过 5 道面试题将所学知识串联起来，并附有面试官视角的考核要点与应对策略，帮组读者从"会写窗口"进阶到"能用窗口并应对面试做到胸中有数"。
