Hive窗口函数

Hive窗口函数

一般格式为：function + over(范围)
个人理解开窗函数就是对每一行开一个窗口，窗口的范围可以自己指定，然后在这个窗口范围中进行一些统计

over()

指定分析函数工作的窗口大小，这个窗口大小可能随着行的变化而改变
over()中可以指定窗口的范围

• current row：当前行
• n preceding：往前n行数据
• n following：往后n行数据
• unbounded：起点
• unbounded preceding：从前面的起点
• unbounded following：从后面的终点
• partition by：根据某个字段来划分窗口的范围
• LAG(col,n,default_val)：往前第n行数据，如果为null，就为默认值
• LEAD(col,n,default_val)：往后第n行数据，如果为null，就为默认值
• NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对
  于每一行，NTILE 返回此行所属的组的编号，n 必须为 int 类型。

实例操作

数据建表如下：
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

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

1. 查询在 2017 年 4 月份购买过的顾客及总人数
   分析
   此需求中4月份购买过的顾客，可以通过where过滤，group by name得到，但是group by后，总人数又没有办法求了，此时在group by后给每一行（4月份购买过的顾客）开一个窗，窗口的大小就是group by后的大小，这样就可以通过sum()计算总人数了
   代码结果如下

   select name,count(*) over ()
   from business
   where substring(orderdate,1,7) = '2017-04'
   group by name;
   name	count_window_0
   
   jack	2
   mart	2
   

2. 查询顾客的购买明细及月购买总额
   分析
   顾客购买明细好求，主要是月购买总额怎样和顾客购买明细这些字段联系起来，其实很简单，用sum(cost)求，然后在每一行over()开窗，窗口的范围跟你月份来划分
   代码结果如下

   select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;
   
   name	orderdate	cost	sum_window_0
   jack	2017-01-01	10	205
   tony	2017-01-02	15	205
   tony	2017-01-04	29	205
   jack	2017-01-05	46	205
   tony	2017-01-07	50	205
   jack	2017-01-08	55	205
   jack	2017-02-03	23	23
   mart	2017-04-13	94	341
   mart	2017-04-08	62	341
   mart	2017-04-09	68	341
   mart	2017-04-11	75	341
   jack	2017-04-06	42	341
   neil	2017-05-10	12	12
   neil	2017-06-12	80	80
   

3. 将每个顾客的 cost 按照日期进行累加
   分析
   还是用开窗函数，只是这次窗口的范围是根据姓名分组，在根据日期排序，从窗口内的第一行到当前行
   (如果order by 后面没有窗口范围那么它的范围默认是 between UNBOUNDED PRECEDING and current row)即从起点到当前行
   代码结果如下

   select name,orderdate,cost,
   sum(cost) over(partition by name order by orderdate)
   from business;
   
   name	orderdate	cost	sum_window_0
   jack	2017-01-01	10	10
   jack	2017-01-05	46	56
   jack	2017-01-08	55	111
   jack	2017-02-03	23	134
   jack	2017-04-06	42	176
   mart	2017-04-08	62	62
   mart	2017-04-09	68	130
   mart	2017-04-11	75	205
   mart	2017-04-13	94	299
   neil	2017-05-10	12	12
   neil	2017-06-12	80	92
   tony	2017-01-02	15	15
   tony	2017-01-04	29	44
   tony	2017-01-07	50	94
   
   

4. 查看顾客上次的购买时间
   分析
   此需求中需要用到LAG(orderdate,1,‘1900-01-01’)，表示取当前窗口中orderdate列的上一行数据，如果是第一行那就用1900-01-01代替
   代码结果如下

   select name,orderdate,cost,
   lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate) as time1
   from business;
   
   name	orderdate	cost	time1
   jack	2017-01-01	10	1900-01-01
   jack	2017-01-05	46	2017-01-01
   jack	2017-01-08	55	2017-01-05
   jack	2017-02-03	23	2017-01-08
   jack	2017-04-06	42	2017-02-03
   mart	2017-04-08	62	1900-01-01
   mart	2017-04-09	68	2017-04-08
   mart	2017-04-11	75	2017-04-09
   mart	2017-04-13	94	2017-04-11
   neil	2017-05-10	12	1900-01-01
   neil	2017-06-12	80	2017-05-10
   tony	2017-01-02	15	1900-01-01
   tony	2017-01-04	29	2017-01-02
   tony	2017-01-07	50	2017-01-04
   

5. 查询前 20%时间的订单信息
   分析
   用NTILE(5)函数分成5组，取组id为1（前20%）
   代码结果如下

   select * from 
   (select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business) t where sorted = 1;
   
   t.name	t.orderdate	t.cost	t.sorted
   jack	2017-01-01	10	1
   tony	2017-01-02	15	1
   tony	2017-01-04	29	1
   
   # 为了更好的理解ntile(n)函数，单独执行以下语句，查看结果
   select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business;
   
   name	orderdate	cost	sorted
   jack	2017-01-01	10	1
   tony	2017-01-02	15	1
   tony	2017-01-04	29	1
   jack	2017-01-05	46	2
   tony	2017-01-07	50	2
   jack	2017-01-08	55	2
   jack	2017-02-03	23	3
   jack	2017-04-06	42	3
   mart	2017-04-08	62	3
   mart	2017-04-09	68	4
   mart	2017-04-11	75	4
   mart	2017-04-13	94	4
   neil	2017-05-10	12	5
   neil	2017-06-12	80	5