hive窗口函数

可以计算一定范围内，一定值域内或者一段时间内的累积和以及移动平均值等

语法：

分析函数over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

常用分析函数：

     聚合类：avg() sum() max() min()

     排名类 ：

     row_number() 按照值排序时产生一个自增编号，不会重复

     rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位

     dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位

     其他类：

     lag(列名,往前的行数,[行数为null时的默认值，不指定为null])

     lead(列名,往后的行数,[行数为null时的默认值，不指定为null])

     ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号

注意点：

     over()函数中的分区，排序，指定窗口范围可组合使用也可以不指定，根据不同的业务需求结合使用

     over()函数中如果不指定分区，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区中的数据

over()函数中的窗口范围说明:

     current row :当前行

     unbounded：起点，unbounded preceding 表示从前面的起点， unbounded following表示到后面的终点

     n preceding ：往前n行数据

     n following：往后n行数据

实战案例1：

原始数据(用户购买明细数据)

name，orderdate，cost
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


建表加载数据
vi business.txt

create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/data/business.txt" into table business;

需求

(1)查询在2017年4月份购买过的顾客及总人数

分析：按照日期过滤、分组count求总人数(分组为什么不是用group by？自己思考)

select 
name,
orderdate,
cost,
count(*) over() total_people
from 
business
where date_format(orderdate,'yyyy-MM')='2017-04';

(2)查询顾客的购买明细及月购买总额

分析：按照顾客分组、sum购买金额

select 
name,
orderdate,
cost,
sum(cost) over(partition by name) total_amount
from 
business;

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

分析：按照顾客分组、日期升序排序、组内每条数据将之前的金额累加

select 
name,
orderdate,
cost,
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) cumulative_amount
from 
business;

(4)查询顾客上次的购买时间

分析：查询出明细数据同时获取上一条数据的购买时间(肯定需要按照顾客分组、时间升序排序)

select 
name,
orderdate,
cost,
lag(orderdate,1) over(partition by name order by orderdate) last_date
from 
business;
查询连续7天登录的用户数

select count(distinct a.uid)
from
(
select t1.uid,datediff(t1.dt,lag(t1.dt,6) over(group by t1.uid order by t1.dt)) as t,t1.login_status
from (
select uid,dt,login_status from t0 group by uid where login_status=1) t1 ) t2

where t2.t=6

(5)查询前20%时间的订单信息

分析：按照日期升序排序、取前20%的数据

select
*
from
(
select 
name,
orderdate,
cost,
ntile(5) over(order by orderdate) sortgroup_num
from 
business
) t
where t.sortgroup_num=1;