hive窗口函数详情总结
解释
开窗函数用于为行定义一个窗口（指运算将要操作的行的集合），它对一组值进行操作，不需要使用 Group By 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
语法
函数()over(partitionby 列名1orderby 列名2rowsbetween[[unbounded|num]preceding|currentrow]and[[unbounded|num]following|currentrow])rowsbetween：作用为划分表中窗口边界
unboundedpreceding：表示表中窗口无上边界
num preceding：表示表中窗口上界到距离当前行向上num行
currentrow：表示当前行
num following：表示表中窗口下界到距离当前行向下num行
unboundedfollowing：表示表中窗口无下边界
rowsbetweenunboundedprecedingandunboundedfollowing：
hive开窗函数
排序开窗函数
样例数据
select*from test ；
>name score subject
A 90 语文
A 90 数学
A 98 英语
B 93 语文
B 90 数学
B 94 英语
RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。有3条排在第1位时，排序为：1，1，1，4······
示例：
#按姓名分组，排序每个人的分数从低到高select name , score , subject ,rank()over(partitionby name orderby score ) rk from test;>name score subject rk
> A 90 语文 1> A 90 数学 1> A 98 英语 3> B 90 数学 1> B 93 语文 2> B 94 英语 3
DENSE_RANK()
在计算排序时，若存在相同位次，不会跳过之后的位次。有3条排在第1位时，排序为：1，1，1，2······
示例：
#按姓名分组，排序每个人的分数从低到高select name , score , subject ,rank()over(partitionby name orderby score ) rk from test;>name score subject rk
> A 90 语文 1> A 90 数学 1> A 98 英语 2> B 90 数学 1> B 93 语文 2> B 94 英语 3
ROW_NUMBER()
这个函数赋予唯一的连续位次。例如，有3条排在第1位时，排序为：1，2，3，4······
示例：
#按姓名分组，排序每个人的分数从低到高select name , score , subject ,rank()over(partitionby name orderby score ) rk from test;>name score subject rk
> A 90 语文 1> A 90 数学 2> A 98 英语 3> B 90 数学 1> B 93 语文 2> B 94 英语 3
分析开窗函数
样例数据：
select*from test;
RN ADDRESS ARRIVAL_TIME USERID
------ ---------- ------------------- --------- 1 A1 2012-7-9 下午12:03:211(null) A2 2012-7-9 下午12:04:212(null) A3 2012-7-9 下午12:05:2132 A1 2012-7-9 下午12:08:214(null) A2 2012-7-9 下午12:09:215(null) A3 2012-7-9 下午12:10:2163 A1 2012-7-9 下午12:13:217(null) A3 2012-7-9 下午12:15:2184 A1 2012-7-9 下午12:18:2395 A1 2012-7-9 下午12:19:2110(null) A2 2012-7-9 下午12:20:2111(null) A3 2012-7-9 下午12:21:21126 A1 2012-7-9 下午12:23:2313(null) A2 2012-7-9 下午12:24:2114
last_value
取开窗最后一个值
第一个参数是列名，第二个参数可选布尔值,默认值为FALSE，true可以忽略null值
select rn,address,arrival_time,userid,last_value(rn,true)over(orderby userid) group_t from test
查询结果如下：
RN ADDRESS ARRIVAL_TIME USERID GROUP_T
------ ---------- ------------------- --------- ---------- 1 A1 2012-7-9 下午12:03:2111(null) A2 2012-7-9 下午12:04:2121(null) A3 2012-7-9 下午12:05:21312 A1 2012-7-9 下午12:08:2142(null) A2 2012-7-9 下午12:09:2152(null) A3 2012-7-9 下午12:10:21623 A1 2012-7-9 下午12:13:2173(null) A3 2012-7-9 下午12:15:21834 A1 2012-7-9 下午12:18:23945 A1 2012-7-9 下午12:19:21105(null) A2 2012-7-9 下午12:20:21115(null) A3 2012-7-9 下午12:21:211256 A1 2012-7-9 下午12:23:23136(null) A2 2012-7-9 下午12:24:21146
first_value
取开窗第一个值
第一个参数是列名，第二个参数可选布尔值,默认值为FALSE，true可以忽略null值
select rn,address,arrival_time,userid,first_value(rn,true)over(orderby userid) group_t from test
查询结果如下：
RN ADDRESS ARRIVAL_TIME USERID GROUP_T
------ ---------- ------------------- --------- ---------- 1 A1 2012-7-9 下午12:03:2111(null) A2 2012-7-9 下午12:04:2121(null) A3 2012-7-9 下午12:05:21312 A1 2012-7-9 下午12:08:2141(null) A2 2012-7-9 下午12:09:2151(null) A3 2012-7-9 下午12:10:21613 A1 2012-7-9 下午12:13:2171(null) A3 2012-7-9 下午12:15:21814 A1 2012-7-9 下午12:18:23915 A1 2012-7-9 下午12:19:21101(null) A2 2012-7-9 下午12:20:21111(null) A3 2012-7-9 下午12:21:211216 A1 2012-7-9 下午12:23:23131(null) A2 2012-7-9 下午12:24:21141
lag
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 ,第三个参数指的是往上n个weinull的默认值，不是指开窗那列的值为null的默认值，示例：
select rn,address,arrival_time,userid,lag(rn,2,0)over(orderby userid) group_t from test
查询结果如下：
RN ADDRESS ARRIVAL_TIME USERID GROUP_T
------ ---------- ------------------- --------- ---------- 1 A1 2012-7-9 下午12:03:2110(null) A2 2012-7-9 下午12:04:2120(null) A3 2012-7-9 下午12:05:21312 A1 2012-7-9 下午12:08:214null(null) A2 2012-7-9 下午12:09:215null(null) A3 2012-7-9 下午12:10:21623 A1 2012-7-9 下午12:13:217null(null) A3 2012-7-9 下午12:15:218null4 A1 2012-7-9 下午12:18:23935 A1 2012-7-9 下午12:19:2110null(null) A2 2012-7-9 下午12:20:21114(null) A3 2012-7-9 下午12:21:211256 A1 2012-7-9 下午12:23:2313null(null) A2 2012-7-9 下午12:24:2114null
lead
LEAD(col,n,DEFAULT)用于统计窗口内往下第n行值
select rn,address,arrival_time,userid,lead(rn,2,0)over(orderby userid) group_t from test
查询结果如下：
RN ADDRESS ARRIVAL_TIME USERID GROUP_T
------ ---------- ------------------- --------- ---------- 1 A1 2012-7-9 下午12:03:211null(null) A2 2012-7-9 下午12:04:2122(null) A3 2012-7-9 下午12:05:213null2 A1 2012-7-9 下午12:08:214null(null) A2 2012-7-9 下午12:09:2153(null) A3 2012-7-9 下午12:10:216null3 A1 2012-7-9 下午12:13:2174(null) A3 2012-7-9 下午12:15:21854 A1 2012-7-9 下午12:18:239null5 A1 2012-7-9 下午12:19:2110null(null) A2 2012-7-9 下午12:20:21116(null) A3 2012-7-9 下午12:21:2112null6 A1 2012-7-9 下午12:23:23130(null) A2 2012-7-9 下午12:24:21140
其他窗口函数
cume_dist
这个函数不太常用， 小于等于当前值的行数/分组内总行数
select r, a ,cume_dist()over(orderby a ) col from(select'cc' r,1 a unionallselect'aa',2unionallselect'bb',3) t
r a col
>c 10.3333333333333333#1/3>aa 20.6666666666666666#2/3>b 31#3/3
percent_rank
percent_rank ：窗口内当前行的RANK值-1/窗口内总行数-1(这里的rank值就是指的是rank 函数的的返回值)
select r, a ,percent_rank()over(orderby a ) col from(select'cc' r,1 a unionallselect'aa',2unionallselect'bb',3) t
r a col
>c 10#1-1/3-1>aa 25#2-1/3-1>b 31#3-3/3-1
本文转载自: https://blog.csdn.net/jokertiger/article/details/120140267
版权归原作者 jokertiger 所有， 如有侵权，请联系我们删除。
版权归原作者 jokertiger 所有， 如有侵权，请联系我们删除。