定制型营销网站建设,安卓模仿网站开发详细教程,临沂网站建设做网站,企业如何打造自己的品牌hive窗口函数详情总结 解释语法hive开窗函数排序开窗函数样例数据RANK()DENSE_RANK()ROW_NUMBER() 分析开窗函数样例数据#xff1a;last_valuefirst_valuelaglead 其他窗口函数cume_distpercent_rank 解释
开窗函数用于为行定义一个窗口#xff08;指运算将要操作的行的集合… hive窗口函数详情总结 解释语法hive开窗函数排序开窗函数样例数据RANK()DENSE_RANK()ROW_NUMBER() 分析开窗函数样例数据last_valuefirst_valuelaglead 其他窗口函数cume_distpercent_rank 解释
开窗函数用于为行定义一个窗口指运算将要操作的行的集合它对一组值进行操作不需要使用 Group By 子句对数据进行分组能够在同一行中同时返回基础行的列和聚合列。
语法
函数() over(partition by 列名1 order by 列名2 rows between [[unbounded|num] preceding | current row]and [[unbounded|num] following | current row]) rows between作用为划分表中窗口边界
unbounded preceding表示表中窗口无上边界
num preceding表示表中窗口上界到距离当前行向上num行
current row表示当前行
num following表示表中窗口下界到距离当前行向下num行
unbounded following表示表中窗口无下边界
rows between unbounded preceding and unbounded followinghive开窗函数
排序开窗函数
样例数据
select * from test
name score subjectA 90 语文A 90 数学A 98 英语B 93 语文B 90 数学B 94 英语RANK()
在计算排序时若存在相同位次会跳过之后的位次。有3条排在第1位时排序为1114······ 示例
#按姓名分组排序每个人的分数从低到高
select name , score , subject ,rank()over(partition by name order by score ) rk from test;
name score subject rk A 90 语文 1A 90 数学 1A 98 英语 3B 90 数学 1B 93 语文 2B 94 英语 3DENSE_RANK()
在计算排序时若存在相同位次不会跳过之后的位次。有3条排在第1位时排序为1112······ 示例
#按姓名分组排序每个人的分数从低到高
select name , score , subject ,rank()over(partition by name order by score ) rk from test;
name score subject rk A 90 语文 1A 90 数学 1A 98 英语 2B 90 数学 1B 93 语文 2B 94 英语 3ROW_NUMBER()
这个函数赋予唯一的连续位次。例如有3条排在第1位时排序为1234······ 示例
#按姓名分组排序每个人的分数从低到高
select name , score , subject ,rank()over(partition by name order by score ) rk from test;
name score subject rk A 90 语文 1A 90 数学 2A 98 英语 3B 90 数学 1B 93 语文 2B 94 英语 3分析开窗函数
样例数据
select * from test;RN ADDRESS ARRIVAL_TIME USERID ------ ---------- ------------------- --------- 1 A1 2012-7-9 下午12:03:21 1 (null) A2 2012-7-9 下午12:04:21 2 (null) A3 2012-7-9 下午12:05:21 3 2 A1 2012-7-9 下午12:08:21 4 (null) A2 2012-7-9 下午12:09:21 5 (null) A3 2012-7-9 下午12:10:21 6 3 A1 2012-7-9 下午12:13:21 7 (null) A3 2012-7-9 下午12:15:21 8 4 A1 2012-7-9 下午12:18:23 9 5 A1 2012-7-9 下午12:19:21 10 (null) A2 2012-7-9 下午12:20:21 11 (null) A3 2012-7-9 下午12:21:21 12 6 A1 2012-7-9 下午12:23:23 13 (null) A2 2012-7-9 下午12:24:21 14 last_value
取开窗最后一个值 第一个参数是列名第二个参数可选布尔值,默认值为FALSEtrue可以忽略null值
select rn,address,arrival_time,userid,last_value(rn,true) over(order by userid) group_t from test查询结果如下RN ADDRESS ARRIVAL_TIME USERID GROUP_T ------ ---------- ------------------- --------- ---------- 1 A1 2012-7-9 下午12:03:21 1 1 (null) A2 2012-7-9 下午12:04:21 2 1 (null) A3 2012-7-9 下午12:05:21 3 1 2 A1 2012-7-9 下午12:08:21 4 2 (null) A2 2012-7-9 下午12:09:21 5 2 (null) A3 2012-7-9 下午12:10:21 6 2 3 A1 2012-7-9 下午12:13:21 7 3 (null) A3 2012-7-9 下午12:15:21 8 3 4 A1 2012-7-9 下午12:18:23 9 4 5 A1 2012-7-9 下午12:19:21 10 5 (null) A2 2012-7-9 下午12:20:21 11 5 (null) A3 2012-7-9 下午12:21:21 12 5 6 A1 2012-7-9 下午12:23:23 13 6 (null) A2 2012-7-9 下午12:24:21 14 6 first_value
取开窗第一个值 第一个参数是列名第二个参数可选布尔值,默认值为FALSEtrue可以忽略null值
select rn,address,arrival_time,userid,first_value(rn,true) over(order by userid) group_t from test查询结果如下RN ADDRESS ARRIVAL_TIME USERID GROUP_T ------ ---------- ------------------- --------- ---------- 1 A1 2012-7-9 下午12:03:21 1 1 (null) A2 2012-7-9 下午12:04:21 2 1 (null) A3 2012-7-9 下午12:05:21 3 1 2 A1 2012-7-9 下午12:08:21 4 1 (null) A2 2012-7-9 下午12:09:21 5 1 (null) A3 2012-7-9 下午12:10:21 6 1 3 A1 2012-7-9 下午12:13:21 7 1 (null) A3 2012-7-9 下午12:15:21 8 1 4 A1 2012-7-9 下午12:18:23 9 1 5 A1 2012-7-9 下午12:19:21 10 1 (null) A2 2012-7-9 下午12:20:21 11 1 (null) A3 2012-7-9 下午12:21:21 12 1 6 A1 2012-7-9 下午12:23:23 13 1 (null) A2 2012-7-9 下午12:24:21 14 1 lag
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 ,第三个参数指的是往上n个weinull的默认值不是指开窗那列的值为null的默认值示例
select rn,address,arrival_time,userid,lag(rn,2,0) over(order by userid) group_t from test查询结果如下RN ADDRESS ARRIVAL_TIME USERID GROUP_T ------ ---------- ------------------- --------- ---------- 1 A1 2012-7-9 下午12:03:21 1 0 (null) A2 2012-7-9 下午12:04:21 2 0 (null) A3 2012-7-9 下午12:05:21 3 1 2 A1 2012-7-9 下午12:08:21 4 null (null) A2 2012-7-9 下午12:09:21 5 null (null) A3 2012-7-9 下午12:10:21 6 2 3 A1 2012-7-9 下午12:13:21 7 null (null) A3 2012-7-9 下午12:15:21 8 null 4 A1 2012-7-9 下午12:18:23 9 3 5 A1 2012-7-9 下午12:19:21 10 null (null) A2 2012-7-9 下午12:20:21 11 4(null) A3 2012-7-9 下午12:21:21 12 5 6 A1 2012-7-9 下午12:23:23 13 null (null) A2 2012-7-9 下午12:24:21 14 nulllead
LEAD(col,n,DEFAULT)用于统计窗口内往下第n行值
select rn,address,arrival_time,userid,lead(rn,2,0) over(order by userid) group_t from test
查询结果如下RN ADDRESS ARRIVAL_TIME USERID GROUP_T ------ ---------- ------------------- --------- ---------- 1 A1 2012-7-9 下午12:03:21 1 null (null) A2 2012-7-9 下午12:04:21 2 2 (null) A3 2012-7-9 下午12:05:21 3 null 2 A1 2012-7-9 下午12:08:21 4 null (null) A2 2012-7-9 下午12:09:21 5 3(null) A3 2012-7-9 下午12:10:21 6 null 3 A1 2012-7-9 下午12:13:21 7 4(null) A3 2012-7-9 下午12:15:21 8 54 A1 2012-7-9 下午12:18:23 9 null 5 A1 2012-7-9 下午12:19:21 10 null (null) A2 2012-7-9 下午12:20:21 11 6(null) A3 2012-7-9 下午12:21:21 12 null 6 A1 2012-7-9 下午12:23:23 13 0(null) A2 2012-7-9 下午12:24:21 14 0其他窗口函数
cume_dist
这个函数不太常用 小于等于当前值的行数/分组内总行数
select r, a ,cume_dist() over( order by a ) col from (
select cc r, 1 a union all select aa,2 union all select bb, 3
) tr a col
c 1 0.3333333333333333 #1/3
aa 2 0.6666666666666666 #2/3
b 3 1 #3/3percent_rank
percent_rank 窗口内当前行的RANK值-1/窗口内总行数-1(这里的rank值就是指的是rank 函数的的返回值)
select r, a ,percent_rank() over( order by a ) col from (
select cc r, 1 a union all select aa,2 union all select bb, 3
) tr a col
c 1 0 #1-1/3-1
aa 2 5 #2-1/3-1
b 3 1 #3-3/3-1
