hive开窗函数使用,用案例演示
hive开窗函数使⽤，⽤案例演⽰
hive的开窗函数的使⽤
hive开窗函数，⼤致就是row_number()、sum()、coun()、avg()、max()、min()、first_value()、last_value()、lag()、lead()、
cume_dist()、rank()、dense_rank()、ntile()这些接over开窗⼝，over()中指定partition by，表⽰分组，order by表⽰排序；开窗⽅式可以是对分组内，或者分组后并排序之后获取当前⾏到有界或者⽆界的⾏数据的聚合，或者是对于分组后（是否排序看是否指定排序条件order by）组内⽐当前条件匹配的结果和当前组内所有数据之间的计算，或者相对于当前⾏存在前后⾏数差值为指定值的数据的计算。
通过hive SQL对下列数据加⼯得到⽬标数据。
有student表，字段有：name学⽣姓名、province学⽣归属地、age年龄，数据如下：
张三 四川 23
赵六 四川 38
关⽻ 北京 40
张飞 北京 37
李四 江苏 22
秦九 四川 28
周瑜 江苏 29
王五 江苏 25
周三 四川 38
曹操 江苏 45
吕布 北京 32
1. ⽤hive SQL查询，如何得到如下结果？(省份排序顺序按照⾸字母c-h-j-s)
周三 四川 38 1
赵六 四川 38 2
秦九 四川 28 3
张三 四川 23 4
曹操 江苏 45 1
周瑜 江苏 29 2
王五 江苏 25 3
李四 江苏 22 4
关⽻ 北京 40 1
张飞 北京 37 2rank函数的用法
吕布 北京 32 3
⽤row_number获取按照省份分组并按照年龄排序，开窗得到⾏号：
select name,province,age,
-
-开窗获取分组内的⾏号
row_number()over(partition by province order by age desc)as rownum
from student;
2. 如何⽤hive SQL查询得到如下结果？
周三 四川 38 1
赵六 四川 38 1
秦九 四川 28 3
张三 四川 23 4
曹操 江苏 45 1
周瑜 江苏 29 2
王五 江苏 25 3
李四 江苏 22 4
关⽻ 北京 40 1
张飞 北京 37 2
吕布 北京 32 3
组内排名（跳跃），是要通过dense_rank实现：
--dense_rank：跳跃排名（113）
dense_rank()over(partition by province order by age desc)as dense_rank from student;
3. 如何⽤hive SQL查询得到如下结果？
周三 四川 38 1
赵六 四川 38 1
秦九 四川 28 2
张三 四川 23 3
曹操 江苏 45 1
周瑜 江苏 29 2
王五 江苏 25 3
李四 江苏 22 4
关⽻ 北京 40 1
张飞 北京 37 2
吕布 北京 32 3
组内排名，是要通过rank实现：
select name,province,age,
--rank：排名（112）
rank()over(partition by province order by age desc)as rank
from student;
4. 如何⽤hive SQL查询得到如下结果？
周三 四川 38 31.75
赵六 四川 38 31.75
秦九 四川 28 31.75
张三 四川 23 31.75
曹操 江苏 45 30.25
周瑜 江苏 29 30.25
王五 江苏 25 30.25
李四 江苏 22 30.25
关⽻ 北京 40 36.33
张飞 北京 37 36.33
吕布 北京 32 36.33
通过分组后，求组内平均数，⽤函数avg聚合：
select name,province,age,
--求根据每个学⽣的科⽬在⾃⼰省份的平均数，四舍五⼊保留两位⼩数
round(avg(age)over(partition by province order by age desc),2)as avg_age from student;
4. 如何⽤hive SQL查询得到如下结果？
周三 四川 38 127
赵六 四川 38 127
秦九 四川 28 127
张三 四川 23 127
曹操 江苏 45 121
周瑜 江苏 29 121
王五 江苏 25 121
李四 江苏 22 121
关⽻ 北京 40 109
张飞 北京 37 109
吕布 北京 32 109
通过sum聚合：
sum(age)over(partition by province order by age desc)as sum_age
from student;
5. 如何⽤hive SQL查询得到如下结果？
周三 四川 38 38
赵六 四川 38 38
秦九 四川 28 38
张三 四川 23 38
曹操 江苏 45 45
周瑜 江苏 29 45
王五 江苏 25 45
李四 江苏 22 45
关⽻ 北京 40 40
张飞 北京 37 40
吕布 北京 32 40
⽤first_value获取组内最⼤值：
select name,province,age,
first_value(age)over(partition by province order by age desc)as first_value
from student;
6. 如何⽤hive SQL查询得到如下结果？
周三 四川 38 1
赵六 四川 38 1
秦九 四川 28 2
张三 四川 23 2
曹操 江苏 45 1
周瑜 江苏 29 1
王五 江苏 25 2
李四 江苏 22 2
关⽻ 北京 40 1
张飞 北京 37 1
吕布 北京 32 2
⽤369等分分级函数开窗获取级别字段：
select name,province,age,
--如果不够分，遵循排在前⾯级别的数据多于后⾯的原则划分级别
ntile(age)over(partition by province order by age desc)as level_age
from student;
7. 如何⽤hive SQL查询得到如下结果？
曹操 江苏 45 0.0909090909
关⽻ 北京 40 0.1818181818
赵六 四川 38 0.3636363636
周三 四川 38 0.3636363636
张飞 北京 37 0.4545454545
吕布 北京 32 0.5454545455
周瑜 江苏 29 0.6363636364
秦九 四川 28 0.7272727273
王五 江苏 25 0.8181818182
张三 四川 23 0.9090909091
李四 江苏 22 1.0000000000
⽤cume_dist求排序后（组内）值⼤于等于当前值的⾏数占整个（有分组字段就分组内，没有则全表）组内的⽐例：
--⼤于等于当前age值的数据所占⽐例
round(cume_dist(age)over(order by age desc))as order_rate from student;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。