本文介绍窗口函数的基本语法及示例。
普通的聚合函数只能用来计算一行内的结果或把所有行聚合成一行结果,而窗口函数支持为每一行或多行分区生成一个结果。
window_function over (
[partition by partition_expr]
[order by order_expr]
)
- 分区:由partition by子句定义。partition by子句用于划分窗口分区,如果没有指定partition by子句,则整个统计分析结果集作为一个窗口分区。
- 排序:由order by子句定义。order by子句用于对窗口分区内的行进行排序。
函数列表
函数名称 | 语法 | 说明 |
---|---|---|
dense_rank | dense_rank() | 无间隙排序。 |
ntile | ntile(n) | 将一个数据集分成n个分组,每个分组中包含尽可能相等数量的行。 |
rank | rank() | 有间隙排序。 |
row_number | row_number() | 为每条分组记录返回一个数字。 |
first_value | first_value(x) | 返回各个窗口分区内第一个值。 |
last_value | last_value(x) | 返回各个窗口分区内最后一个值。 |
nth_value | nth_value(x, offset) | 返回窗口分区中第x行开始的第一个非null值。 |
lag | lag(x) | 返回窗口分区内位于当前行上方第x行的值。 |
lead | lead(x) | 返回窗口分区内位于当前行下方第x行的值。 |
dense_rank函数
无间隙排名。相同值拥有相同的排名,排名是连续的。
语法
dense_rank() over ( [partition by partition_expr] [order by order_expr] )
参数说明
无
返回值类型
integer类型
示例
类型 |
示例 |
---|---|
统计分析语句 |
SELECT method, ua, req_cnt, dense_rank() OVER (PARTITION BY ua ORDER BY req_cnt desc) AS denseRank |
ntile函数
将一个数据集分成n个分组,每个分组中包含尽可能相等数量的行。
语法
ntile(n) over ( [partition by partition_expr] [order by order_expr] )
参数说明
参数名称 | 说明 | 类型 | 是否必选 |
---|---|---|---|
n | 分组个数 | integer类型 | 是 |
返回值类型
integer类型
示例
类型 |
示例 |
---|---|
统计分析语句 |
level:error |
rank函数
有间隙排序。相同值拥有相同的排名,排名不一定是连续的。
语法
rank() over ( [partition by partition_expr] [order by order_expr] )
参数说明
无
返回值类型
integer类型
示例
类型 |
示例 |
---|---|
统计分析语句 |
SELECT method, ua, req_cnt, rank() OVER (PARTITION BY ua ORDER BY req_cnt desc) AS rank_num |
row_number函数
为每条分组记录返回一个数字。
语法
row_number() over ( [partition by partition_expr] [order by order_expr] )
参数说明
参数名称 | 说明 | 类型 | 是否必选 |
---|
返回值类型
integer类型
示例
类型 |
示例 |
---|---|
统计分析语句 |
select level, time, caller, row_number() over (partition by caller) |
first_value函数
返回各个窗口分区内第一个值。
语法
first_value(x) over ( [partition by partition_expr] [order by order_expr] )
参数说明
参数名称 | 说明 | 类型 | 是否必选 |
---|---|---|---|
x | 列名 | 任意数据类型 | 是 |
返回值类型
与x数据类型一致
示例
类型 |
示例 |
---|---|
统计分析语句 |
select level, time, caller, first_value(time) over (partition by caller) |
last_value函数
返回各个窗口分区内最后一个值。
语法
last_value(x) over ( [partition by partition_expr] [order by order_expr] )
参数说明
参数名称 | 说明 | 类型 | 是否必选 |
---|---|---|---|
x | 列名 | 任意数据类型 | 是 |
返回值类型
与x数据类型一致
示例
类型 |
示例 |
---|---|
统计分析语句 |
select level, time, caller, last_value(time) over (partition by caller) |
nth_value函数
返回各个窗口分区内最后一个值。
语法
nth_value(x, offset) over ( [partition by partition_expr] [order by order_expr] )
参数说明
参数名称 | 说明 | 类型 | 是否必选 |
---|---|---|---|
x | 列名 | 任意数据类型 | 是 |
offset |
偏移量 | int类型 | 是 |
返回值类型
与x数据类型一致
示例
类型 |
示例 |
---|---|
统计分析语句 |
select level, time, caller, nth_value(time, 100) over (partition by caller) |
lag函数
返回在有序帧内当前行之前的指定物理偏移行上的求值。
语法
lag(x, offset) over ( [partition by partition_expr] [order by order_expr] )
参数说明
参数名称 | 说明 | 类型 | 是否必选 |
---|---|---|---|
x | 列名 | 任意数据类型 | 是 |
offset | 偏移量 | int类型 | 是 |
返回值类型
与x数据类型一致
示例
类型 |
示例 |
---|---|
统计分析语句 |
select level, time, caller, lag(time, 3) over (partition by caller) |
lead函数
返回在有序行中当前行之后偏移行上的求值。
语法
lead(x, offset) over ( [partition by partition_expr] [order by order_expr] )
参数说明
参数名称 | 说明 | 类型 | 是否必选 |
---|---|---|---|
x | 列名 | 任意数据类型 | 是 |
offset | 偏移量 | int类型 | 是 |
返回值类型
与x数据类型一致
示例
类型 |
示例 |
---|---|
统计分析语句 |
select level, time, caller, lead(time, 3) over (partition by caller) |