Hive-窗口函数详解

窗口函数和分析函数

应用场景

1）用于分区排序；
2）动态group by；
3）计算 top N；
4）累计计算；
5）层次查询；

1. 窗口函数

在查询时使用聚合函数，如sum，avg，max，这类函数会将多行数据聚集为一行，聚合后的行数时小于聚合前的行数。但是有时需要既显示聚合前的数据，也需要显示聚集后的数据，如下

-- 这是错误的做法， 执行会失败
select depno,count(*)
from tablename
group by member; 

为了实现效果，需要引入窗口函数。
注意： 窗口函数时最后一步执行的，而且仅位于order by字句之前。

1.1 需求案例

原始数据如下：

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

1.1.1 需求1

需求：查询在 2017 年 4 月份购买过的顾客及总人数

第一步：

select name
from y_ods.tmp_test_20200713
where substring(orderdate,1,7)='2015-04'
limit 1000 

-- 结果：
jack
mart
mart
mart
mart

第二步：

-- 指定日期下的，购买的人，以及总的购买次数，是jack	购买的次数 和mart购买的次数总和
select name,count(*) over()
from y_ods.tmp_test_20200713
where substring(orderdate,1,7)='2015-04'
limit 1000 

jack	5
mart	5
mart	5
mart	5
mart	5

第三步：

-- 查询2015-04 购买过的人，以及该人购买的次数
select name, count(*)
from y_ods.tmp_test_20200713
where substring(orderdate,1,7)='2015-04'
group by name
limit 1000 


-- 输出结果为：表示mart去了4次，jack去了1次
mart	4
jack	1

第四步 加上over函数：

select name, count(*) over()
from y_ods.tmp_test_20200713
where substring(orderdate,1,7)='2015-04'
group by name
limit 1000 


-- 输出结果为：一共有2个人去，mart是一个，jack是一个
mart	2
jack	2


执行过程大致为：
1. where筛选出2015-04的
2. 根据name分组，分组就分为了2个组，一个mart组，一个jack组，共2条数据
3. over函数开窗，并且因为over函数没有传参，是对整体数据集开窗，计算的就是所有的数据，即2条数据；
4. count函数计算的就是over函数开的整个窗口里的所有的条数，即2， 所以最后是每条数据后面都是2。 

1）over必须跟在聚合函数后面；over叫做开窗函数， 窗口就是数据集；开出来的数据集是给前面的聚合函数用的；
2）over()函数是可以传参的， 如果没有传参，则是最整个数据集开窗， 即对所有的数据进行聚合计算；
3）over函数是为每一条数据开窗；
4）group by 是一个组出来一条数据
5）over()函数是空函数体的时候，会对group by后的结果总体视为一个大窗口 。。。
6）over()函数是针对于每一条数据开的窗口，相当于每一条数据都是一个独立的组。
7）over函数窗口里面限定的是数据集的多少

1.1.2 需求2

需求： 查询顾客的购买明细及购买总额

select *, sum(cost) over()
from y_ods.tmp_test_20200713
limit 1000 

1. 需要购买明细，即每条数据的所有字段都需要展示出来，在每条数据最后都加上总额数；
2. 因为over没有参数，所以是对所有数据作为数据集，作为一个大的窗口， sum聚合函数是对整个窗口内的数据进行求和；

结果如下：

1.1.3 需求3

上述的场景,要将 cost 按照日期进行累加

select *, sum(cost) over(order by orderdate)
from y_ods.tmp_test_20200713
limit 1000 



执行过程如下：
在over函数中是按照orderdate排序，  并且over函数是对每一条数据进行开窗的
01-01	10  
01-02	15
01-04	29 
....
..
.

当执行到第一条，没有比01-01小的了，所以开窗的时候就第一条数据，sum函数计算的时候就10；
当执行到第二条，有一个01-01比01-02小，所以窗口中一共2条数据，sum计算的是10+15;
当执行到第三条，第一条和第二条的时间都比当前的小，所以窗口一共是3条数据，sum计算的是10+15+39
以此类推下面的数据

1.1.4 需求4

需求： 查出每个人的明细以及每个人购买总额

select *, sum(cost) over(distribute by name)
from y_ods.tmp_test_20200713
limit 1000 

要算每个人的购买总额，就要先对每个人进行分区；
每个分区内，相当于一个数据集，把这个结果给sum函数计算。

结果如下：

1.1.5 需求5

需求： 查出每个人的明细以及累加起来这个人的购买总额

select *, sum(cost) over(distribute by name sort by orderdate)
from y_ods.tmp_test_20200713
limit 1000 

先根据name进行分区，并且在分区内按照orderdate排序，在分区内（即在这个人）开窗口
注意：distribute by 和 sort by是连用的  不能分开用。

1.2 窗口函数相关函数

OVER()： 指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化
而变化；
CURRENT ROW：当前行；
n PRECEDING：往前 n 行数据；
n FOLLOWING：往后 n 行数据；
UNBOUNDED：起点， UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED
FOLLOWING 表示到后面的终点；

以上函数都是在over()函数中用的。【1.2.2 需7】
以下函数是在over函数外用的。【1.2.1 需求6】

LAG(col,n)：往前第 n 行数据；
LEAD(col,n)：往后第 n 行数据；
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，
对于每一行， NTILE 返回此行所属的组的编号。 注意： n 必须为 int 类型。【1.2.3 需求8】

1.2.1 需求6 【实现跨行计算】

需求：查询顾客上次的购买时间

select 
     name
    ,orderdate
    ,cost
    ,lag(orderdate,1, '-9999') over(distribute by name sort by orderdate) as last_time
from y_ods.tmp_test_20200713
limit 1000 

over(distribute by name sort by orderdate)：按照name分区，并按照时间排序
lag函数：在开的窗口内，取出上一条数据（即指定的数字1）中指定的字段orderdate, 作为last_time字段的值；
其中-9999是当lag函数的值为NULL的时候，用-9999代替。
LEAD函数和lag函数用法是一致的。

1.2.2 需求7

需求： 累计计算前2行到当前行的累加的购买额数

select
    name
    ,orderdate
    ,cost
    ,sum(cost) over(rows between 2 preceding and current row)
from 
    yiche_ods.tmp_test_20200713
limit 1999

-- rows between and 是固定词；替换的是 2 preceding current row 这些

看图，观察同颜色的框内的数据以及箭头指向的数值。 【指向98的应该是黄色箭头】
10+15+23累加的结果为48；
15+23+29累加的结果是67；
类似于滑动的窗口，每次的计算的是前两行到当前行的值的总和。以此类推下面的数据。

1.2.3 需求8【NTILE函数使用】

需求：查询前 20%时间的订单信息

select
    name
    ,orderdate
    ,cost
    ,ntile(5) over(order by orderdate)
from 
    y_ods.tmp_test_20200713
limit 1999


ntile：是将结果分为5份，即5个分区，并且用序号标注在后面。 

1.3 总结【敲黑板重点】

开窗本质是去一个范围的数据集；数据集怎么取是跟括号里面的参数有关；
每一条数据都会开窗口，但是会根据over函数里的参数，开的窗口大小是不一样的。
比如需求4中的，over中的参数distribute by name 是按照name分区，那在同一个分区中，mart分区的每条数据的窗口大小为4，jack分区的每条数据的窗口大小为5。前面的聚合函数会对该窗口函数中的值聚合计算。

1.4 使用搭配

select *, sum(cost) over(distribute by name sort by orderdate)
from yiche_ods.tmp_test_20200713
limit 1000 


select *, sum(cost) over(partition by name order by orderdate)
from yiche_ods.tmp_test_20200713
limit 1000 

上面2种写法都是可以的，
注意：distribute by 和sort by 搭配使用；partition by 和order by 搭配使用，不要混着用。

另一种替换方案：

select uid,
	   mn,
	   sum(visitcount) over(partition by uid, mn)
from tabel_name
或者
select uid,
	   mn,
	   sum(visitcount)
from tabel_name
group by uid, mn

over(partition by uid, mn)  是进行分组，group by也是分组。
同时使用相当于先使用group by进行分组，再使用over(partition by uid, mn) 进行分组；

可以使用over(partition by uid, mn) 替换 group by uid, mn