窗口函数总结篇
一、基本语法及常见函数
SELECT column1,column2,窗口函数() OVER (PARTITION BY 分组列 ORDER BY 排序列 [ASC|DESC]ROWS/RANGE BETWEEN 起始位置 AND 结束位置) AS 别名
FROM table_name;| 分类 | 函数 | 作用 | 典型场景 |
|---|---|---|---|
| 排名函数 | ROW_NUMBER() | 为每行分配唯一序号（无并列） | 生成唯一行号 |
RANK() | 允许并列排名，跳过重复序号（如1,1,3） | 带并列的排名 | |
DENSE_RANK() | 允许并列排名，不跳过序号（如1,1,2） | 连续排名 | |
| 聚合函数 | SUM() OVER() | 计算窗口内总和 | 累计销售额、分区求和 |
AVG() OVER() | 计算窗口内平均值 | 移动平均、分组平均 | |
COUNT() OVER() | 统计窗口内行数 | 累计计数、分区计数 | |
| 偏移函数 | LAG(column, offset) | 访问当前行之前的第N行数据 | 环比增长、上期值对比 |
LEAD(column, offset) | 访问当前行之后的第N行数据 | 预测未来值、下期值对比 |
二、注意事项
0. rows between and 和 range between and的语法
必须写 between and！
| 前一行 + 当前行 | RANGE BETWEEN 1 PRECEDING AND CURRENT ROW | 必须明确指定起始和结束点 |
| 仅前一行 | RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING | 起始和结束点相同 |
| 默认窗口（未指定范围时） | 等效于 RANGE UNBOUNDED PRECEDING AND CURRENT ROW | 从分区第一行到当前行 |
1.rows between and 和 range between and 的区别
ROWS BETWEEN（基于实际物理行数的窗口）
- 定义：通过 物理行数 定义窗口范围，与排序后的实际行位置直接相关。
- 特点：
- 窗口大小固定（按行数计算）。
- 不受列值重复或间隔影响。
SUM(sales) OVER (ORDER BY dateROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_sum| date | sales |
|---|---|
| 2023-01-01 | 100 |
| 2023-01-02 | 200 |
| 2023-01-03 | 150 |
| 2023-01-04 | 300 |
结果：
| date | sales | moving_sum |
|---|---|---|
| 2023-01-01 | 100 | 100 |
| 2023-01-02 | 200 | 300 |
| 2023-01-03 | 150 | 450 |
| 2023-01-04 | 300 | 650 |
2. RANGE BETWEEN（基于值的窗口）
- 定义：通过 列值的逻辑范围 定义窗口边界，与排序后的列值相关。
- 特点：
- 窗口大小动态（按列值区间计算）。
- 受列值重复或间隔影响。
SUM(sales) OVER (ORDER BY dateRANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
) AS range_sum| date | sales |
|---|---|
| 2023-01-01 | 100 |
| 2023-01-03 | 200 |
| 2023-01-04 | 150 |
| 2023-01-06 | 300 |
输出
| date | sales | range_sum |
|---|---|---|
| 2023-01-01 | 100 | 100 |
| 2023-01-03 | 200 | 300 |
| 2023-01-04 | 150 | 350 |
| 2023-01-06 | 300 | 450 |
例2：
SUM(sales) OVER (ORDER BY salesRANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) AS sum_prev_only假设窗口函数按 ORDER BY sales 排序，则 RANGE BETWEEN 1 PRECEDING AND CURRENT ROW 的窗口范围定义为：
- 起始点：当前行的
sales值减去1。 - 结束点：当前行的
sales值。 - 窗口包含所有
sales值在区间[当前值 -1, 当前值]的行。
2.实际上 rows between 用的更多
3. 几种排名函数
在MySQL8.X或者hive中专用的窗口函数有3个：
rank：并列跳跃排名，相同的排名会跳过dense_rank：并列连续排名 ， 相同的排名不会跳过row_number：连续排名 ， 不管值的大小 ，都是从1，2，3，4依次排列下去（用的少）
通过一个例子来说明3个函数的排序差异体现在哪里。现在给定五个成绩：93，93，85，80，75，分别使用3个不同的开窗函数得到的结果分别是：
1、使用 DENSE_RANK() 进行排名会得到：1，1，2，3，4
2、使用 RANK() 进行排名会得到：1，1，3，4，5
3、使用 ROW_NUMBER() 进行排名会得到：1，2，3，4，5
这几种排名函数使用时必须跟着 order by ， 指定依据哪一列进行排行
4.LAG（） 和 Rows between 的区别
| 特性 | LAG()（单行偏移访问） | ROWS BETWEEN（窗口范围聚合） |
|---|---|---|
| 功能 | 直接访问 特定偏移行的单值 | 定义窗口范围，聚合多行数据（如求和、平均） |
| 返回值 | 单行的字段值 | 聚合结果（如总和、平均值） |
| 操作粒度 | 精确访问某一行（如前1行、前2行） | 操作一组行的聚合值 |
| 性能 | 高效（直接定位行） | 可能更耗资源（需遍历多行） |
三、相关例题
1. LC 185
注意事项：
partition by 后面的字段必须使用表的原名！！
2.体育馆的人流量 LC 601
本题需求：
编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
思路：
1.先把人流大于100的选出来，放在那
2.要求id 连续的三行 ， 可以注意到
id连续的行，它们与各自的row_number 之差都是一样的，如下所示
3.根据此 ， 我们在分组统计一下rk的数量大于3的 ， 就是至少3天连续的id了
4.后续就是一些繁琐的连表查询 ， 慢慢写即可
代码：
with s as(select id , visit_date , people from Stadiumwhere people >= 100
)
,
temp as(
select id - row_number() over(order by id asc) diff , id , visit_date , people
from s
)
select id ,visit_date , people from temp left join (
select diff , count(diff) cnt from temp
group by diff
)cur on temp.diff = cur.diff
where cnt >= 3