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 2
   吕布 北京 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实现：

select name,province,age,
--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聚合：

select name,province,age,
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求排序后（组内）值大于等于当前值的行数占整个（有分组字段就分组内，没有则全表）组内的比例：

select name,province,age,
--大于等于当前age值的数据所占比例
round(cume_dist(age) over(order by age desc)) as order_rate
from student;