一、窗口函数分类
1、序号函数
-
row_number()
-
rank()
-
dense_rank()
2、分布函数
-
percent_rand()
-
cume_dist()
3、前后函数
4、头尾函数
-
first_value(expr)
-
last_value(expr)
5、聚类窗口函数
-
min() over()
-
max() over()
-
count() over()
-
sum() over()
-
avg() over()
6、其他函数
-
nth_value(expr,n)
-
ntile(n)
7、语法
function over(partition by 字段a order by 字段b range|rows between start and end);
二、序号函数
序号函数 | 功能 |
row_number() | 顺序排序:对数据中的序号进行顺序显示,不管其排序结构是否出现重复值,排序结果为1,2,3,4,5 |
rank() | 并列排序:相同字段数值并列排序,且跳过重复序号,如1,1,3,4,5;rank函数没有参数,但需要指定按照那个字段进行排名,所以使用rank函数必须用order by参数,order by的排序字段就是排名字段 |
dense_rank() | 并列排序:相同字段数值并列排序,且不跳过重读序号,如1,1,2,3,4 |
三、分布函数
分布函数 | 功能 |
percent_rank() | 累计百分比,函数计算结果为小于该条记录值的所有记录的行数/该分组的总行数-1,所以该记录的返回值为[0,1]。和之前的RANK()函数相关,每行按照如下公式进行计算: (rank - 1) / (rows - 1) 其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数。 |
cume_dist() | 累计分布值。分组值小于等于当前值的行数与分组总行数的比值 (0,1]。 分组内大于等于当前rank值的行数/分组内总行数。(常用) |
四、前后函数
前后函数 | 功能 |
lead(字段,n) | 分组中位于当前行后n行的记录值 |
lag(字段,n) | 分组中位于当前行前n行的记录值 |
五、头尾函数
头尾函数 | 功能 |
first_val(expr) | 得到分区中的第一个指定参数的值 |
last_val(expr) | 得到分区中的最后一个指定参数的值 |
六、聚类窗口函数
函数 | 功能 |
min() | 计算最小值 |
max() | 计算最大值 |
count() | 计数 |
sum() | 求和 |
avg() | 求平均值 |
七、其他函数
函数 | 功能 |
nth_value(expr,n) | 返回窗口中第N个expr的值,expr可以是表达式,也可以是列名 |
ntile() | 将分区中的有序数据分为n个桶,记录桶号 |