set hive.cli.print.header=true;
HIVE的窗口函数，对于每一条数据通过窗口滑动，对在窗口内的数据进行聚合等操作。
假设窗口大小为2，那么对每一条数据就以大小为2的窗口滑动。
第一条数据的窗口：
然后滑动窗口到第二条数据：
对每二条、第三条、第四条数据进行窗口滑动，进行相应的聚合等操作，将结果放入对应行的cum_money列。
OVER()函数是窗口函数。一般与聚合函数/lag/lead/ntile/row_number/rank/dense_rank等函数搭配使用。
这里通过示例加以说明：
假设table_sale表的数据如下：
orderdate | sale_money | clientName |
2020-01-01 | 2.0 | a |
2020-01-02 | 1.0 | b |
2020-01-01 | 3.0 | b |
2020-01-02 | 5.0 | a |
1 不加条件的OVER函数
问题：统计客户总消费金额
select *,sum(sale_money) over() as 'cum_money' from table_sale;执行结果如下：
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | 11.0 |
2020-01-02 | 1.0 | b | 11.0 |
2020-01-01 | 3.0 | b | 11.0 |
2020-01-02 | 5.0 | a | 11.0 |
而HIVE是如何实现累计的呢？
对于OVER()函数而言，它会为每一条数据进行开窗操作，不加参数时每一条数据的窗口大小是相同的。每一个窗口内部都会执行sum([窗口大小的数据集合])函数。
这里没有对OVER(）函数加任何条件，从而每一条数据的开窗大小都为4。进行sum()操作如下：
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | sum([2.0,1.0,3.0,5.0]) |
2020-01-02 | 1.0 | b | sum([2.0,1.0,3.0,5.0]) |
2020-01-01 | 3.0 | b | sum([2.0,1.0,3.0,5.0]) |
2020-01-02 | 5.0 | a | sum([2.0,1.0,3.0,5.0]) |
2 加排序条件的OVER函数
问题：统计客户累计消费情况
select *,sum(sale_money) over(order by orderdate) as 'cum_money' from table_sale;里面对OVER函数加了条件，首先会对数据进行排序操作，此时加了排序条件每一条数据的窗口大小是不同的。
对于第一条数据而言，它的窗口大小为1(因为它前面没有数据)，为第一条数据开窗后，就开始执行相应的聚合函数，然后赋予到第一行中。
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | sum([2.0]) |
2020-01-01 | 3.0 | b | |
2020-01-02 | 1.0 | b | |
2020-01-02 | 5.0 | a | |
对于第二条数据，因为它的前面已经有了a客户的数据，再加上自身的数据，从而当前窗口大小为2，然后就开启大小为2的窗口，进行聚合操作，即sum([2.0,,3.0])
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | sum([2.0]) |
2020-01-01 | 3.0 | b | sum([2.0,3.0]) |
2020-01-02 | 1.0 | b | |
2020-01-02 | 5.0 | a | |
对于第三条数据，则开启大小为3的窗口，第四条条数据则开启大小为4的窗口：
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | sum([2.0]) |
2020-01-01 | 3.0 | b | sum([2.0,3.0]) |
2020-01-02 | 1.0 | b | sum([2.0,3.0,1.0]) |
2020-01-02 | 5.0 | a | sum([2.0,3.0,1.0,5.0]) |
从而完成了累计操作。
3 加分区条件的OVER函数
问题：统计每一个客户总消费情况
select *,sum(sale_money) over(partition by clientName) as 'cum_money' from table_sale;执行sum(sale_money) over(parition by name)部分时，首先对数据集进行分区(分组)操作，此时每一条数据集的窗口大小为所在分区的大小。如对a客户分区，a客户有2条数据集，从而窗口大小为2。从而对于sum()函数执行情况如下：
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | sum([2.0,5.0]) |
2020-01-02 | 5.0 | a | sum([2.0,5.0]) |
2020-01-01 | 3.0 | b | sum([3.0,1.0]) |
2020-01-02 | 1.0 | b | sum([3.0,1.0]) |
4 加分区排序条件的OVER函数
问题：统计每一个客户累计消费情况
select *,
sum(sale_money) over(partition by clientName order by orderdate) as 'cum_money'
from table_sale;首先OVER函数对数据执行分区操作，然后对数据进行排序操作。而加了排序操作使得分区内部的每一条数据的窗口大小是不同的。对于sum()函数执行情况如下：
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | sum([2.0]) |
2020-01-02 | 5.0 | a | sum([2.0,5.0]) |
2020-01-01 | 3.0 | b | sum([3.0]) |
2020-01-02 | 1.0 | b | sum([3.0,1.0]) |
5 加窗口大小条件的OVER函数
这里先对OVER函数内部的若干参数进行说明：
- CURRENT ROW：当前行
- n PRECEDING：往前n行数据
- n FOLLOWING：往后n行数据
- UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
问题：统计相邻两条数据的消费总额
select *,
sum(sale_money) over(rows between 1 preceding and current row) as 'cum_money'
from table_sale;该问题的答案忽略了边界问题，请忽略。
1 preceding : 指的是前一行；current row : 当前行，从而限定了窗口大小为2。此时sum()函数执行如下：
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | 0 |
2020-01-02 | 5.0 | a | 2.0 |
2020-01-01 | 3.0 | b | 0 |
2020-01-02 | 1.0 | b | 3.0 |
6 LAG/LEAD函数的使用
- LAG(col,n)：往前第n行数据
- LEAD(col,n)：往后第n行数据
LAG和LEAD()要与OVER()函数一起使用。
问题：查看客户前一天消费金额。
select *,
lag(sale_money,1,0) over(partition by clientName order by orderdate) as 'yesterdayMoney'
from table_sale;
-- 也可以写成这样的
select *,
lag(sale_money,1,0) over(distirbution by clientName sort by orderdate) as 'yesterdayMoney'
from table_sale;lag(sale_money,1,0)：取窗口内的前1天sale_money数据，若无数据，则用0替代。
查询结果为：
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | 0 |
2020-01-02 | 5.0 | a | 2.0 |
2020-01-01 | 3.0 | b | 0 |
2020-01-02 | 1.0 | b | 3.0 |
对于LEAD函数也是一样的理解，如：
问题：查看客户下一天消费金额。
select *,
lead(sale_money,1,0) over(partition by clientName order by orderdate) as 'nextMoney'
from table_sale;lead(sale_money,1,0)：取窗口内的下1天sale_money数据，若无数据，则用0替代。
orderdate | sale_money | clientName | cum_money |
2020-01-01 | 2.0 | a | 5.0 |
2020-01-02 | 5.0 | a | 0 |
2020-01-01 | 3.0 | b | 1.0 |
2020-01-02 | 1.0 | b | 0 |
就这么多了，希望对大家有帮助。
若有错误，还请指正。
参考：
1 哔哩哔哩的尚硅谷视频：https://www.bilibili.com/video/BV1z4411y7C2?p=44
2 《Hive编程指南》
