hive中的窗口函数详解

概念：

我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。

窗口函数与分析函数

应用场景：

1.用于分区排序
2.动态GROUP BY
3.TOP N
4.累计计算
5.层次查询

窗口函数：

函数	返回类型	描述
row_number()	bigint	在其分区中的当前行号，从1计
rank()	bigint	有间隔的当前行排名；与它的第一个相同行的row_number相同
dense_rank()	bigint	没有间隔的当前行排名；这个函数计数对等组。
percent_rank()	double precision	当前行的相对排名: (rank - 1) / (总行数 - 1)
cume_dist()	double precision	当前行的相对排名：(前面的行数或与当前行相同的行数)/(总行数)
ntile(num_buckets integer)	integer	从1到参数值的整数范围，尽可能相等的划分分区。
lag(value any [, offset integer [, default any ]])	类型同value	计算分区当前行的前offset 行，返回value 。如果没有这样的行， 返回default替代。 offset和default 都是当前行计算的结果。如果忽略了，则offset 默认是1，default默认是 null。
lead(value* any [, offset integer [, default any ]])	类型同value	计算分区当前行的后offset行， 返回value。如果没有这样的行， 返回default替代。 offset和default 都是当前行计算的结果。如果忽略了，则offset 默认是1，default默认是 null。
first_value(value any)	类型同value	返回窗口第一行的计算value值。
last_value(value any)	类型同value	返回窗口最后一行的计算value值。
nth_value(value any, nth integer)	类型同value	返回窗口第nth行的计算 value值（行从1计数）；没有这样的行则返回 null。

partition by子句

Over子句之后第一个提到的就是Partition By，Partition By子句也可以
称为查询分区子句，非常类似于Group By，都是将数据按照边界值分组，而Over
之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算。

order by子句

上述的场景,假如我们想要将cost按照月进行累加。这时我们引入order by子句。

order by子句会让输入的数据强制排序（文章前面提到过，窗口函数是SQL语句最后执行的函数，
因此可以把SQL结果集想象成输入数据）。Order By子句对于诸如Row_Number()，Lead()
，LAG()等函数是必须的，因为如果数据无序，这些函数的结果就没有任何意义。
因此如果有了Order By子句，则Count()，Min()等计算出来的结果就没有任何意义。

子查询语句：

window子句

我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分，我们就要引入window子句了。

我们首先要理解两个概念:
- 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合。
- 使用了order by子句,未使用window子句的情况下,默认从起点到当前行。

当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.

window子句：
- PRECEDING：往前
- FOLLOWING：往后
- CURRENT ROW：当前行
- UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

实例：

先准备 一张表：

hive (d6_hive)> select * from full
OK
full.user	full.month	full.count
A	2018-01	5
B	2018-01	18
C	2018-02	10
A	2018-04	21
B	2018-03	25
C	2018-04	3
A	2018-02	33
B	2018-02	15
C	2018-03	25
A	2018-05	18
A	2018-03	2
C	2018-01	10
C	2018-05	28

用窗口函数进行累加查询：

hive (d6_hive)> select 
              >   user,month,count, 
              >    sum(count) over (partition by user order by month rows between unbounded preceding and current row) as pv1, 
              >    sum(count) over (partition by user order by month) as pv2, 
              >    sum(count) over (partition by user) as pv3, 
              >    sum(count) over (partition by user order by month rows between 3 preceding and current row) as pv4, 
              >    sum(count) over (partition by user order by month rows between 3 preceding and 1 following) as pv5, 
              >    sum(count) over (partition by user order by month rows between current row and unbounded following) as pv6 
              > from full

查询结果：

user	month	count	pv1	pv2	pv3	pv4	pv5	pv6
A	2018-02		33	38	38	79	38	40	74
A	2018-01		5	5	5	79	5	38	79
A	2018-03		2	40	40	79	40	61	41
A	2018-04		21	61	61	79	61	79	39
A	2018-05		18	79	79	79	74	74	18
B	2018-02		15	33	33	58	33	58	40
B	2018-03		25	58	58	58	58	58	25
B	2018-01		18	18	18	58	18	33	58
C	2018-01		10	10	10	76	10	20	76
C	2018-02		10	20	20	76	20	45	66
C	2018-03		25	45	45	76	45	48	56
C	2018-04		3	48	48	76	48	76	31
C	2018-05		28	76	76	76	66	66	28

详细解释：

pv1: 分组内从起点到当前行的pv累积，如：A组2月的pv1=1月的pv+2月的pv, 3月=1月+2月+3月
pv2: 同pv1
pv3: 分组内(count)所有的pv累加
pv4: 分组内当前行+往前3行，如：2月=1月+2月， 3月=1月+2月+3月， 4月=1月+2月+3月+4月 
pv5: 分组内当前行+往前3行+往后1行，如：4月=1月+2月+3月+4月+5月
pv6: 分组内当前行+往后所有行，如：3月=3月+4月+5月

先通过 partition by 分区，然后 通过 order by 选择需要按指定字段排序累加的 字段。

order by 后跟的 字段 表示 ：你要按这个字段进行累加。