Hive 窗口函数（开窗函数）

开窗函数

1）相关函数说明

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

CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，
- UNBOUNDED PRECEDING 表示从前面的起点，
- UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

2）数据准备：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

3）需求

需求1：
- 查询在2017年4月份购买过的顾客及总人数
需求2：
- 查询顾客的购买明细及所有顾客的月购买总额
- 查询顾客的购买明细及每个顾客的月购买总额
需求3：
- 将每个顾客的cost按照日期进行累加
- 将所有顾客的cost按照日期进行累加
需求4：
- 求所有顾客的购买明细及按照日期进行排序后
- 求所有顾客的cost 第一行到当前行累加和
- 求所有顾客的cost 上一行到当前行的累加和
- 求所有顾客的cost 上一行到下一行的累加和
- 求所有顾客的cost 当前行到下一行的累加和
- 求所有顾客的cost 当前行到最后一行的累加和
需求5：
- 查询每个顾客上次的购买时间及下一次的购买时间
需求6：
- 查询前20%时间的订单信息

4）创建hive表并导入数据


create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/home/hadoop/data/business.txt" into table business;

5）按需求查询数据

需求1：
- 查询在2017年4月份购买过的顾客及总人数


select 
   t2.name, count(t2.name) over()
from 
(select
  distinct(t1.name)
from 
  (select
  name, orderdate, cost 
from 
  business 
where substring(orderdate,0,7) = '2017-04')t1) t2;

需求2：
- 查询顾客的购买明细及所有顾客的月购买总额


select
  name, 
  orderdate, 
  cost , 
  sum(cost) over(partition by substring(orderdate,0,7))  month_cost
from
  business;

需求2：
- 查询顾客的购买明细及每个顾客的月购买总额


select
  name, 
  orderdate,
  cost, 
  sum(cost) over(partition by name, substring(orderdate,0,7)) name_month_cost
from 
  business;

需求3：

将每个顾客的cost按照日期进行累加


select 
  name, 
  orderdate,
  cost,
  sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) lj
from 
  business;

需求3：
- 将所有顾客的cost按照日期进行累加


select 
  name, 
  orderdate,
  cost,
  sum(cost) over(order by orderdate ) lj
from 
  business;

需求4：
- 求所有顾客的购买明细及按照日期进行排序后
- 求所有顾客的cost 第一行到当前行累加和
- 求所有顾客的cost 上一行到当前行的累加和
- 求所有顾客的cost 上一行到下一行的累加和
- 求所有顾客的cost 当前行到下一行的累加和
- 求所有顾客的cost 当前行到最后一行的累加和


select
  name,
  orderdate,
  cost,
  sum(cost) over(order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) f_c,
  sum(cost) over(order by orderdate rows between 1 PRECEDING and CURRENT ROW ) p_c,
  sum(cost) over(order by orderdate rows between 1 PRECEDING and 1 FOLLOWING ) p_n,
  sum(cost) over(order by orderdate rows between CURRENT ROW and 1 FOLLOWING ) c_n,
  sum(cost) over(order by orderdate rows between CURRENT ROW and UNBOUNDED FOLLOWING ) c_l
from
  business;

需求5：
- 查询每个顾客上次的购买时间及下一次的购买时间


select
   name,
   cost, 
   orderdate c_orderdate,
   lag(orderdate ,1 ,'1970-01-01') over(partition by name  order by orderdate) p_orderdate,
   lead(orderdate ,1 ,'9999-01-01') over(partition by name  order by orderdate) p_orderdate
from 
  business;

需求6：
- 查询前20%时间的订单信息


select 
  t1.name, 
  t1.orderdate,
  t1.cost ,
  t1.gid
from 
(select
  name, 
  orderdate,
  cost, 
  ntile(5) over(order by orderdate ) gid
from 
  business) t1
where t1.gid = 1;

5）总结

over()：会为每条数据都开启一个窗口。默认的窗口大小就是当前数据集的大小。
over(partition by ….)：会按照指定的字段进行分区，将分区字段的值相同的数据划分到相同的区。每个区中的每条数据都会开启一个窗口。每条数据的窗口大小默认为当前分区数据集的大小。
over(order by ….)：会在窗口中按照指定的字段对数据进行排序，会为每条数据都开启一个窗口，默认的窗口大小为从数据集开始到当前行。
over(partition by …. order by ….)：会按照指定的字段进行分区，将分区字段的值相同的数据划分到相同的区，在每个区中会按照指定的字段进行排序，会为每条数据都开启一个窗口,默认的窗口大小为当前分区中从数据集开始到当前行。
over(partition by … order by … rows between … and ….)：指定每条数据的窗口大小。

6）关键字整理

order by：全局排序或者窗口函数中排序.
distribute by：分区
sort by：区内排序
cluster by：分区排序
partition by：窗口函数中分区
partitioned by：建表指定分区字段
clustered by：建表指定分桶字段

Hive 教程

Hive 窗口函数（开窗函数）

目录

开窗函数