hive所有窗口函数详情总结

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