HIVE 开窗函数

在开窗函数出现之前，存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在2003年ISO SQL标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决 ;

SQL标准允许将所有聚合函数用做开窗函数，只需要在聚合函数后加over()即可

案例演示

     1.hive环境下建表与加载数据

vi t1.txt
u01	2017/1/21	5
u02	2017/1/23	6
u03	2017/1/22	8
u04	2017/1/20	3
u01	2017/1/23	6
u01	2017/2/21	8
U02	2017/1/23	6
U01	2017/2/22	4

create table if not exists tb1(
userId string,
visitDate string,
visitCount int
) 
row format delimited fields terminated by '\t';

load data local inpath '/home/hadoop/test/t1.txt' into table tb1;

2. SQL脚本实现

select
tmp.userId,
tmp.visitDate,
tmp.visitCount,
sum(visitCount) over(partition by tmp.userId order by tmp.visitDate  rows between unbounded preceding and current row) sumsum 
from(select
  lower(userId) userId,
  trim(from_unixtime(unix_timestamp(visitDate,'yyyy/MM/dd'),'yyyy-MM')) visitDate,
  sum(visitCount) visitCount 
  from tb1 
  group by lower(userId),trim(from_unixtime(unix_timestamp(visitDate,'yyyy/MM/dd'),'yyyy-MM'))) tmp;

3.实现效果

开窗函数(over())

开窗函数(over())包含三个分析子句：
　分组子句(partition by)
　排序子句(order by)
　窗口子句(rows)

开窗函数(over())使用形式如下：
　　　over(partition by xxx order by yyy rows between zzz)

开窗函数里的"窗"，即"窗口"，表示分析函数分析时要处理的数据范围
　　比如分析函数sum()，它所针对的数据范围为窗口中的记录，而不是整个表的记录
　　要获取某个范围的sum值，则需在窗口指定具体的数据范围
　　比如指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和

窗口子句除了rows方式的窗口,还包括range方式和滑动窗口

　　排序子句(order by)

　　排序子句的使用方法跟sql中的order by一样，如：order by colA desc, colB asc nulls first, colC  nulls last
　　开窗函数的order by和sql语句的order by的执行时机
　　分析及开窗函数是在整个sql查询结束后再进行的, 即sql语句的order by也会影响分析函数的执行结果，有以下两种情况：
　　1) 两者一致，即sql语句中的order by语句与开窗函数的order by一致，则sql语句中的order by先执行,分析函数在分析时就不必再排序
　　2) 两者不一致，即sql语句中的order by语句与开窗函数的order by不一致，则分析及开窗函数先分析排序，sql语句中的order by再最后执行

窗口子句(rows)
　　如果没有窗口子句(rows),则默认当前组的第一行到当前行
　　无论是否省略分组子句，都有：
　　　　窗口子句(rows)不能单独存在，必须有order by子句时才能出现
　　　　相反，有order by子句，可以没有窗口子句(rows)

　　　　当省略窗口子句时
　　　　　　如果存在order by，则默认的窗口是unbounded preceding and current row，即当前组的第一行到当前行
　　　　　　如果不存在order by，则默认的窗口是unbounded preceding and unbounded following，即整个组

　　　　例如：lag(sal) over(order by sal)
　　　　over(order by salary)表示意义如下：
　　　　　　1) 由于省略分组子句，所以当前组的范围为整个表的数据行
　　　　　　2) 在当前组(此时为整个表的数据行)这个范围里执行排序，即order by salary
　　　　　　3) 分析函数lag(sal)在当前组(此时为整个表的数据行)这个范围里的窗口范围为当前组的第一行到当前行，即分析函数lag(sal)在这个窗口范围执行　　　  

　　　　窗口子句(rows)的相关关键字：
　　　　preceding：表示在...之前
　　　　　　1 preceding：表示当前记录的前1条记录
　　　　　　2 preceding：表示当前记录的前2条记录
　　　　　　n preceding：表示当前记录的前n条记录
　　　　　　unbounded preceding：不受控制的，无限的,
　　　　　　　　　　　　　　　　   若无分组，则表示所有记录的第1条记录
　　　　　　　　　　　　　　　　   若有分组，则表示分组后，组内的第1条记录

　　　　following：表示在...之后
　　　　　　1 following：表示当前记录的后一条记录
　　　　　　2 following：表示当前记录的后两条记录
　　　　　　n following：表示当前记录的后n条记录
　　　　　　unbounded following：不受控制的，无限的,
　　　　　　　　　　　　　　　　若无分组，则表示所有记录的最后一条记录
　　　　　　　　　　　　　　　　若有分组，则表示分组后，组内的最后一条记录

　　　　相关用例：
　　　　　　rows between unbounded preceding and unbounded following：针对所有记录
　　　　　　rows between unbounded preceding and current row：针对第一条记录到当前记录
　　　　　　rows between current row and unbounded following：针对当前记录到最后一条记录
　　　　　　rows between 1 preceding and current row：针对当前记录的上一行记录到当前行记录
　　　　　　rows between current row and 3 following：针对当前记录到当前行记录的后三条记录
　　　　　　rows between 1 preceding and 2 following：针对当前记录的上一条记录 ~~ 当前行记录的后两条记录

　　　　当开窗函数over()出现分组(partition by)子句时
　　　　　　unbounded preceding即表中一个分组里的第一行，unbounded following即表中一个分组里的最后一行
　　　　当开窗函数over()省略了分组(partition by)子句时 
　　　　　　unbounded preceding即表中的第一行， unbounded following即表中的最后一行