窗口函数总结
常用的窗口函数
函数 | 作用描述 |
---|---|
ROW_NUMBER() | 分组内唯一递增序号 |
RANK() | 分组内排名(有并列,排名有跳跃) |
DENSE_RANK() | 分组内连续排名(无跳跃) |
NTILE(n) | 分桶编号(分位数) |
LEAD(expr, n, def) | 取当前行后第 n 行的值 |
LAG(expr, n, def) | 取当前行前第 n 行的值 |
FIRST_VALUE(expr) | 窗口内首值 |
LAST_VALUE(expr) | 窗口内尾值 |
NTH_VALUE(expr, n) | 窗口内第 n 个值 |
CUME_DIST() | 累计分布百分比 |
PERCENT_RANK() | 百分比排名(当前行排名在分组中的百分比) |
SUM(expr) | 求和(聚合/窗口均可) |
AVG(expr) | 平均值(聚合/窗口均可) |
COUNT(expr) | 计数(聚合/窗口均可) |
MIN(expr) | 最小值(聚合/窗口均可) |
MAX(expr) | 最大值(聚合/窗口均可) |
常见用法
1. 去重时使用row_number
1 | CREATE OR REPLACE TEMP VIEW t1 AS |
2. 求窗口内的累计值
1 | SELECT |
一般用于在窗口内,求某个时刻的累积值。比如张三2025年经常在沃尔玛超市购物,想要知道张三在每个时刻点,当年累计花的钱数。
窗口是2025年,分区是张三的id,排序是购物时间
3. 求窗口内的累计数
类似2,求某个时刻的累计数,即窗口内,累计购物次数,这里不再赘述。
4. 动态窗口
这个比较有意思,窗口可以是动态的。比如在商店场景下,分析每一位顾客在最近的7次购物中,累计购物金额。
用法如下:
1 | SELECT |
ROWS 关键字可以定义窗口的范围,常见用法有:
当前行的前一行到后一行:
1 | ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING |
单边写法,前三行及当前行:
1 | ROWS 3 PRECEDING |
等价于 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
后2行及当前行:
1 | ROWS 2 FOLLOWING |
等价于 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
从分组的第一行到当前行:
1 | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
这也是默认策略,可以省略不写。
从当前行到分组的最后一行:
1 | ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
这种用法比较少见,一般从上往下累计,通过 ORDER BY 来控制升降序。