窗口函数总结

发表于 更新于 分类于 Waline： 阅读次数：

常用的窗口函数

函数 作用描述
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 来控制升降序。

补充

动态窗口在使用时，应该确保ROWS的范围和业务范围一致。
例如按id+date 分区，需要确保date为连续且不重复的，否则往前xx行，不等于往前xx天。