hive 开窗函数 partition by 多列 hive分析函数窗口函数

sql里有聚合函数sum，avg等，这些函数配合group分组将多行数据聚集为一行，但是有时候我们想要显示聚集前的数据，又想要聚集后的数据，因此在hive中，我们引入了窗口函数
窗口函数包含两个部分，第一是分析函数，第二是over子句

一、over从句

1、over从句规范：over(partition by ??? order by ??? row|range between ??? and ???)，里面三个字段，2-4分别介绍三个字段的意义

2、group by不能用在over从句；

3、order by做全局排序，有partition by分组内排序；当order by缺少窗口条件从句，默认规范是组内 range between unbounded preceding and current row

4、row|range between ??? and ???，也叫windows子句，可以省略，省略的话有分区时默认分区内头尾范围，无分区默认表内头尾范围

5、行窗口：根据当前行之前或之后的行号确定的窗口，语法：rows between <start_expr> and <end_expr>

• <start_expr>可以为下列值
  unbounded preceding: 窗口起始位置（分组第一行）
  current row：当前行
  N preceding/following：当前行之前/之后n行
• <end_expr>可以为下列值
  unbounded FOLLOWING : 窗口结束位置（分组最后一行）
  current row：当前行
  N preceding/following：当前行之前/之后n行
  6、行窗口案例：

select
name, dept_num as dept, salary as sal,
max(salary) over (partition by dept_num order by name rows between 2 preceding and current row) win1,
max(salary) over (partition by dept_num order by name rows between 2 preceding and unbounded following) win2,
max(salary) over (partition by dept_num order by name rows between 1 preceding and 2 following) win3,
max(salary) over (partition by dept_num order by name rows between 2 preceding and 1 preceding) win4,
max(salary) over (partition by dept_num order by name rows between 1 following and 2 following) win5,
max(salary) over (partition by dept_num order by name rows between current row and current row) win6,
max(salary) over (partition by dept_num order by name rows between current row and 1 following) win7,
max(salary) over (partition by dept_num order by name rows between current row and unbounded following) win8,
max(salary) over (partition by dept_num order by name rows between unbounded preceding and current row) win9,
max(salary) over (partition by dept_num order by name rows between unbounded preceding and 1 following) win10,
max(salary) over (partition by dept_num order by name rows between unbounded preceding and unbounded following) win11,
max(salary) over (partition by dept_num order by name rows 2 preceding) win12
from employee_contract  order by dept, name;

案例2：

select name, dept_num as dept, salary as sal,
max(salary) over (partition by dept_num order by name rows between 2 preceding and current row) win1,
salary - 1000 as sal_r_start,salary as sal_r_end,
max(salary) over (partition by dept_num order by name range between 1000 preceding and current row) win13
from employee_contract order by dept, name;

二、指定范围的窗口函数

1、first_value(col)：分组内截止到当前行排序后的第一个值

2、last_value(col)：分组内截止到当前行的最后一个值

不排序时，取的是整个分组内对应列的第一、最后值；排序时，取的是截止到当前行的第一、最后的值。图一图二分别为未排序和排序的：

3、lag(col,n,DF)：窗口内往前第n行col的值，如果没有，提供默认值DF

4、lead(col,n,DF)：窗口内往后第n行col的值，如果没有，提供默认值DF

5、如需控制上述函数的取值范围，需要指定 ... over( ... rows between ??? and ???)

三、序列的窗口函数

1、row_number()：就是行序列号，一般配合order by使用得到某字段的排名，如果要取某行或者某几行的数据，可以放在在外部查询里的where子句中加入条件即可。

2、rank(),dense_rank()：排名，rank是遇到重复的跳号，比如说三个并列第一的，则第四个数的排名就是4，而dense_rank则不跳号，第四个数的排名是2。要在over从句中配合order by使用才有效，否则输出的全是1。

select name,dept_num,salary
from(select name,dept_num,salary
	,dense_rank() over(partition by dept_num order by salary asc) rn
	from employee_contract)T
where t.rn=1

3、cume_dist()：小于等于当前值的行数 除以 分组内总行数，测试了下，有点像rank()的跳号百分比排序。使用场景：小于等于这个值所占的比例；截止到某天/月/年等，任务的完成进度；

4、percent_rank()：分组内当前行的rank值-1/分组内总行数-1，测试了下，有点像是rank()的跳号的百分比排序，和cume_dist()的区别在于分子分母都减了一；

5、ntile(n)：将分组数据按照顺序切成n片，返回当前切片值(对应的n的数字)(如果切片不均匀，默认按照第一个切片的分布)

6、sum/count/avg/max/min：五大聚合函数，在over()从句中不使用排序的话，就是整个分区的值求和/计数/平均值/最大值/最小值，每一行的值都一样。在over()从句中使用了排序后，得到的值都是截止到当前行的求和/计数/平均值/最大值/最小值；如果排序后的两个值是一个排名(比如说并列第一)，则两个值会被看作一个整体计算。

select name,dept_num,salary
,count(salary) over() sc
,sum(salary) over() ss
,avg(salary) over() sa
,max(salary) over() sma
,min(salary) over() smi
from employee_contract;

select name,dept_num,salary
,count(salary) over(order by salary asc) sc
,sum(salary) over(order by salary asc) ss
,avg(salary) over(order by salary asc) sa
,max(salary) over(order by salary asc) sma
,min(salary) over(order by salary asc) smi
from employee_contract;