Hive 中的window函数

1. 概念

        hive中的窗口函数和sql中的窗口函数相类似，一般用于OLAP分析(在线分析处理)。有时我们想要既显示聚集前的数据，又要显示聚集后的数据，这便要用到窗口函数了。注意：

在SQL处理中，窗口函数(over 关键字) 都是最后一步执行，且仅位于Order by字句之前。

应用场景：

1. 用于分区排序；

2. 动态Group By；

3. Top N；

4. 累计计算；

5. 层次查询。

实验数据

uid,dt,pv

A,2018-04-10,1
A,2018-04-11,5
A,2018-04-12,7

A,2018-04-13,3

A,2018-04-14,2

A,2018-04-15,4

A,2018-04-16,4
B,2018-04-11,3
B,2018-04-12,5

2. 窗口函数 part1

2.1 ROW_NUMBER()

        常用于分组内topN 场景，从1开始按照顺序，生成分组内记录的序列。

2.2 RANK & DENSE_RANK

        rank() 生成数据项在分组中的排名，排名相等会在名次中留下空位；而dense_rank() 在排名相等时则不会在名次中留下空位。

select uid,dt,pv,
rank() over(partition by uid order by pv desc) as rn1,
dense_rank() over(partition by uid order by pv desc) as rn2,
row_number() over(partition by uid order by pv desc) as rn3 
from XX;

2.3 NTILE

        ntile 把有序的数据集合平均分配到指定的num个桶中，将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。之后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

        统计一个uid，pv数最多的前1/3的天，即其中rn2=1 的记录：

select uid,dt,pv,
ntile(2) over(partition BY uid order by dt) as rn1,
ntile(3) over(partition BY uid order by dt) as rn2,
ntile(4) over(order by dt) AS rn3
from XX 
order by uid,dt;

  where 过滤，即可选出想要“排序后的” 几分之几 。

3. 窗口函数 part2 -- 常用

3.1 SUM

SUM(结果和ORDER BY相关,默认为升序) -- 比如统计每个用户的累积访问量

select uid,dt,pv,
sum(pv) over(partition by uid order by dt) as pv1,
sum(pv) over(partition by uid order by dt rows between 
    unbounded preceding and current row) as pv2  
-- 与pv1 等价，累加函数默认从将当前和前面所有行的某个字段值累加在一起
from XX;

         如果没有order by 语句，默认把分组内的所有数据进行sum操作。

指定累加范围:

1. 累加指定数量的行 -- 当前行前面的行

select uid,dt,pv,
sum(pv) over(partition by uid order by dt rows between 3 preceding 
    and current row) as pv4 
-- rows between 用来指定累加的范围，上面的语句是当前行和前面3行的总和(4行)索引的 0 1 2 3 
from XX;

2. 还可以指定当前行后多少个

select *,
sum(pv) over(partition by uid order by dt rows between 3 preceding 
    and 1 following) as pv5,
sum(pv) over(partition by uid order by dt rows between current row 
    and unbounded following) as pv6  -- 当前到后边所有  从第一条 记录开始算
from XX;

总结：

1. 如果不指定rows between，默认为从起点到当前行；
2. 如果不指定order by，则将分组内所有值累加；
3. 关键是理解rows between含义， 也叫做window子句

preceding: 往前，                    following: 往后， 

current row: 当前行，            unbounded: 起点，

unbounded preceding: 表示从前面的起点， 

unbounded following: 表示 到后面的起点。

 3.2 AVG & MIN & MAX

用法类似 sum

select *,
avg(pv) over(partition by uid order by dt rows between unbounded preceding 
    and current row) as pv2,
avg(pv) over(partition by uid order by dt) as pv3,
max(pv) over(partition by uid order by dt) as pv4,
min(pv) over(partition by uid order by dt) as pv5
from XX;

4. 窗口函数part3

4.1 GROUPING SETS

         根据不同维度的字段组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL。本质上来看，grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

select A,B,C,count(A),
grouping_id  -- 表示这一组结果属于哪个分组集合
from XX
group by A,B,C
grouping sets((A,C), (A,B), (B,C), (C));

        其中 grouping sets 中的(A,C),   (A,B),   (B,C),   (C) 代表4个group by 组合， 相当于写了4个sql查询语句使用了四个不同的 group by 策略。

4.2 GROUPING__ID

         表示结果属于哪一个分组集合，属于虚字段，即区分每条输出结果是属于哪一个group by的数据。它是根据group by后面声明的顺序字段是否存在于当前group by中的一个二进制位组合数据。 比如（A,C）的group_id： group_id(A,C) = grouping(A)+grouping(B)+grouping (C) 的结果就是：二进制：101 也就是5.

         select中的字段是完整的A,B,C，但是我们知道由于group by的存在，select 字段本不应该出现非 group by 字段的，所以这里我们要特别说明，如果解释器发现group by A,C 但是select A,B,C 那么运行时会将所有from 表取出的结果复制一份，B都置为null，也就是在结果中，B都为null。

4.3 CUBE

        数据立方体，用于多维数据分析，根据 group by 维度的所有组合进行聚合。举个栗子：某个事情有A、B、C三个维度，根据这三个维度进行组合分析，共有多少种情况？这些情况加起来就是所谓多维分析中数据立方体。 多维度的预处理 框架 kylin 的计算模型就是 cube。

没有维度：[]
一个维度：[A]  [B]  [C]
两个维度：[AB] [AC] [BC]
三个维度：[ABC]

select month,day,count(distinct uid) as uv,
grouping__id 
from XX 
group by month,day 
with cube 
order by grouping__id;

规律：假如有n个维度 所有的维度组合情况是2的n次方

4.4 ROLLUP

        为cube的子集，以最左侧的维度为主，从该维度进行层级聚合。

5. 其他

5.1 LAG & LEAD

        lag() 和 lead() 都是基于窗口的函数，常用于时间序列数据，它们将被处理的数据集分成窗口，并为每个窗口中的记录返回一个结果，以便比较当前记录与先前或后续记录之间的值。lag() 函数返回在当前行之前指定偏移量的行的列值。而 lead() 函数返回在当前行之后指定偏移量的行的列值。

1. LAG(col，n，DEFAULT) 用于取窗口内列col 往前第N行的值；

2. LEAD(col, n, DEFAULT) 与函数LAG类似，方向相反，用于去窗口内列col往后第n行的值。

计算每天与前一天销售额的差异：

dt,amount
1,10
2,20
3,30
4,40

select dt,
amount - lag(amount, 1) over (order by dt) as amount_diff
from XX;

        使用 lag() 和 lead() 可以轻松地比较记录之间的值。注意：如果不指定排序方式，则无法确定计算顺序，并且结果可能会不准确。

5.2 FIRST_VALUE & LAST_VALUE

1. FIRST_VALUE(col) 表示分组排序后，截止到当前行排名第一的那一行对应的列col的值；

2. LAST_VALUE(col)，分组排序后，截止到当前行排名最后一名的那一行对应的列col的值。