Hive分析函数

分析函数

1、窗口函数（开窗函数），关键字：over

（0）基础知识

2 preceding ====== 前两行
2 following ====== 后两行
current row ====== 当前行
unbounded preceding ====== 无上限
unbounded following ====== 无下限

（1）表user_par的结构和数据如下图

（2）以行定义窗口界限

（2-1）按id排序，并计算当前行和以下两行的年龄之和

select id, name, age, sum(age)over(order by id rows between current row and 2 following) from user_par;

（2-2）按id排序，并计算当前行和以上两行的年龄之和

select id, name, age, sum(age)over(order by id rows between current row and 2 following) from user_par;

（3）以值定义窗口界限，必须和排序一起使用，否则没有意义

（3-1）按age排序，并计算当前的年龄比它大10岁的所有年龄之和

select id, name, age, sum(age)over(order by age range between current row and 10 following) from user_par;

（3-2）不加order by时计算的是所有年龄的总和，值定义窗口界限没有意义

select id, name, age, sum(age)over(range between current row and 10 following) from user_par;

2、排名函数

（0）表user_nopar的结构和数据如下图

（1）并列跳跃排名：按省份分区，并按年龄大小排序

select id, name, province, age, rank()over(partition by province order by age asc) from user_nopar;

（2）并列不跳跃：按省份分区，并按年龄大小排序

select id, name, province, age, dense_rank()over(partition by province order by age asc) from user_nopar;

（3）顺序排名：按省份分区，并按年龄大小排序

select id, name, province, age, row_number()over(partition by province order by age asc) from user_nopar;

3、最大值函数

select id, name, province, age, first_value(age)over(partition by province order by age desc) from user_nopar;

select id, name, province, age, last_value(age)over(partition by province order by age asc range between unbounded preceding and unbounded following) from user_nopar;

4、最小值函数

select id, name, province, age, first_value(age)over(partition by province order by age asc) from user_nopar;

select id, name, province, age, last_value(age)over(partition by province order by age desc range between unbounded preceding and unbounded following) from user_nopar;

5、三六九等函数

select id, name, age, ntile(3)over(order by age) from user_nopar;

6、上提和下沉函数

（1）按province分区，并将age字段向上提一行

select id, name, province, age, lead(age)over(partition by province order by age asc) from user_nopar;

（2）按province分区，并将age字段向上提两行

select id, name, province, age, lead(age,2)over(partition by province order by age asc) from user_nopar;

（3）按province分区，并将age字段向下沉两行

select id, name, province, age, lag(age,2)over(partition by province order by age asc) from user_nopar;

7、指定值占总数的百分比

（1）年龄按降序排列，统计年龄大于等于当前值的人占所有人的百分比

 select id, name, age, cume_dist()over(order by age desc) from user_nopar;

（2）按省份分区，并按年龄升序排列，统计每个分区内年龄小于等于当前值的人占所有人的百分比

select id, name, province, age, cume_dist()over(partition by province order by age asc) from user_nopar;