hive内置函数（四）窗口函数

这篇文字我们介绍下hive中的窗口函数，在hive中窗口函数主要涉及到3个要素，分别是：

1、over函数

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

2、current row（当前行）

这里涉及到两个参数：

n PRECEDING：往前n行数据

n FOLLOWING：往后n行数据

3、unbounded（无边界）

这里也涉及到两个参数：

UNBOUNDED PRECEDING 前无边界，表示从前面的起点

UNBOUNDED FOLLOWING后无边界，表示到后面的终点

4、lag(col,n,default-val)

往前第N行数据，没有的话default-val

5、lead(col,n,default-val)

往后第N行数据，没有的话default-val

6、first_value(col,true/false)

当前窗口下的第一个值，第二个参数为true，跳过空值。

7、last_value(col,true/false)

当前窗口下的最后一个值，第二个参数为true，跳过空值。

8、ntile(n)

把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行ntile返回慈航所属于的组的编号。（用于将分组数据按照顺序切分成那篇，返回当前切片子。）

下面我们根据这个窗口函数来演示几个sql案例：

1、由起点到当前行的聚合

select 
    sum(money) over(partition by user_id order by pay_time rows between UNBOUNDED PRECEDING and current row) 
from or_order;

2、当前行和前一行的聚合

select 
    sum(money) over(partition by user_id order by pay_time rows between 1 PRECEDING and current row) 
from or_order;

3、当前行和前面一行和后面一行的聚合

select 
    sum(money) over(partition by user_id order by pay_time rows between 1 PRECEDING AND 1 FOLLOWING )
from or_order;

4、当前行及后面后有行的聚合

select 
    sum(money) over(partition by user_id order by pay_time rows between current row and UNBOUNDED FOLLOWING  )
from or_order;

5、查询当前用户购买明细以及上次的购买时间和下次购买时间

select 
 user_id,,pay_time,money,
 
 lag(pay_time,1,'1970-01-01') over(PARTITION by name order by pay_time) prev_time,
 
 lead(pay_time,1,'1970-01-01') over(PARTITION by name order by pay_time) next_time
from or_order;

6、查询用户每个月第一次的购买时间和每个月的最后一次购买时间

select
 FIRST_VALUE(pay_time) 
     over(
         partition by user_id,month(pay_time) order by pay_time 
         rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
         ) first_time,
 
 LAST_VALUE(pay_time) 
     over(partition by user_id,month(pay_time) order by pay_time rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
     ) last_time
from or_order;

7、查询前25%时间的订单信息

select * from (
    select User_id,pay_time,money,
    
    ntile(4) over(order by pay_time) sorted
    
    from or_order
) t
where sorted = 1;