Hive之聚合类开窗函数 + 滑动求和

为什么要写这样子一个主题文章呢，开窗函数毫无疑问，超级重要，但是很多人可能不会遇到一些稍微小众的业务需求，比如说滑动求和。这里带着业务，一并让大家熟悉一些，高阶函数的应用。

0，开窗函数和聚合函数的不同之处是什么？

◎普通的聚合函数用group by分组，
◎每个分组返回一个统计值，
◎而分析函数采用partition by分组，
◎并且每组每行都可以返回一个统计值。

一，聚合类开窗函数

1.1，基础语法

--语法格式是

 SUM  (字段1)   OVER  ( PARTITION BY 字段2  ORDER BY  字段3  desc/asc )

1.2，数据如下

A公司各部门员工的工资（24年6月）

部门 edptno	员工 empno	工资（元）SAL
A	A001	6K
A	A002	4K
A	A003	5K
C	C111	9K
C	C112	6.5K
C	C113	8K
C	C114	8.5K

1.3，代码如下

SELECT  
 E.DEPTNO    --部门
,E.EMPNO     --员工
,E.SAL       --工资
,SUM(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL )  AS S1 --按部门累加 
,SUM(E.SAL) OVER(PARTITION BY E.DEPTNO)  AS S2 --部门总和
,SUM(E.SAL) OVER(ORDER BY E.SAL)  AS S3  --全公司工资累加
,SUM(E.SAL) OVER()  AS S4  --公司工资总和 
FROM EMP E ;

1.4，结果展示

A公司各部门员工的工资（24年6月）

部门 edptno	员工 empno	工资（元）SAL	S1 按部门累加	S2 部门总和	S3 全公司工资累加	S4 公司工资总和
A	A001	4K	4K	15K	4K	47K
A	A002	5K	9K	15K	9K	47K
A	A003	6K	15K	15K	15K	47K
C	C111	6.5K	6.5K	32K	21.5K	47K
C	C112	8K	14.5K	32K	29.5K	47K
C	C113	8.5K	23K	32K	38K	47K
C	C114	9K	32K	32K	47K	47K

二，聚合类开窗函数(全)

2.1，语法介绍

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

PRECEDING：往前

FOLLOWING：往后

CURRENT ROW：当前行

UNBOUNDED：起点

UNBOUNDED PRECEDING ：表示从前面的起点

UNBOUNDED FOLLOWING：表示到后面的终点

FIRST_VALUE (col,true/ false)：当前窗口下的第一个值，第二个参数 为 true ，跳过空值

LAST_VALUE (col,true/ false)： 当前窗口下的最后一个值 ，第二个参数 为 true ， 跳过空值

LAG(col,n,default_val)：往前第n行数据

LEAD(col,n, default_val)：往后第n行数据

2.2，代码示例

Select
id
,order_num
,sum(order_num) over (partition by id)  `分组内所有行`
,sum(order_num) over (partition by id order by dt) `从起点到当前行`
,sum(order_num) over (partition by id order by dt rows between unbounded preceding and current row) `默认为从起点到当前行`
,sum(order_num) over (partition by id order by dt rows between 3 preceding and current row) `当前行+往前3行`
,sum(order_num) over (partition by id order by dt rows between current row and 3 following) `当前行+往后3行`
,sum(order_num) over (partition by id order by dt rows between 3 preceding and 1 following) `当前行+往前3行+往后1行`
,sum(order_num) over (partition by id order by dt rows between current row and unbounded following) `当前行+往后所有行`
,max(order_num) over (partition by id order by dt rows between 3 preceding and 1 following) `当前行+往前3行+往后1行里面取最大`
,count(1) over (partition by id order by dt rows between 3 preceding and 1 following)    `当前行+往前3行+往后1行里面计数`
,avg(order_num) over (partition by id order by dt rows between 3 preceding and 1 following)    `当前行+往前3行+往后1行里面求平均数`
,first_value(order_num) over (partition by id order by dt rows between 3 preceding and 1 following) `当前行+往前3行+往后1行第1个值`
,last_value(order_num) over (partition by id order by dt rows between 3 preceding and 1 following ) `当前行+往前3行+往后1行第最后1个值` 
from tmp.table_test;

 2.3，结果展示

ok，如果你sql技能比较强，那么看完上面几张图，你大概就能懂得很多的技巧了。

三，滑动求和

关于滑动求和，涉及业务性强，很重要，上面2.2代码也展示到了，还是接着再分享些案例。

那么滑动求和，在实际的业务之中是什么样子的呢？？？

3.1，滑动求和代码如下

3.2，代码解析

很显然，上述代码先套个子查询，求出在最近一年的时间里，每天的公募私募产品的总保有量；

然后对其使用max()over() 开窗函数：

rows betwwen 29 perceding and current row ,是求出在前30天（包括当天），各保有量的最大值。

rows betwwen 354 perceding and current row ,是求出在前365天（包括当天），各保有量的最大值。

3.3，业务场景：

就是把公募私募产品，最近一个月，和最近一年的最大保有量，开窗展示出来。

=====================================================

欢迎一键三连！