目录
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:建表指定分桶字段