窗口函数总结

常用的窗口函数

函数 作用描述
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE TEMP VIEW t1 AS
SELECT
id
, name
, modify_ts
, dt
, ROW_NUMBER() OVER(PARTITION BY id ORDER BY modify_ts desc) AS rn
FROM table_name
WHERE dt = 'xxx'
;

-- 在使用时候(关联或者直接使用),通过 条件rn = 1 来保证获取的数据没有重复
SELECT
id
, name
, modify_ts
FROM t1
WHERE rn = 1
;

2. 求窗口内的累计值

1
2
3
4
5
6
SELECT
id
,name
,SUM(amount) OVER (PARTITION BY id ORDER BY dt) as total_amt
FROM table_name
;

一般用于在窗口内,求某个时刻的累积值。比如张三2025年经常在沃尔玛超市购物,想要知道张三在每个时刻点,当年累计花的钱数。
窗口是2025年,分区是张三的id,排序是购物时间

3. 求窗口内的累计数

类似2,求某个时刻的累计数,即窗口内,累计购物次数,这里不再赘述。

4. 动态窗口

这个比较有意思,窗口可以是动态的。比如在商店场景下,分析每一位顾客在最近的7次购物中,累计购物金额。

用法如下:

1
2
3
4
5
SELECT
id
,SUM(amount) OVER(PARTITION BY id ORDER BY dt ROWS 6 PRECEDING) last_7times_amount
FROM table_name
;

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 来控制升降序。