Hive窗口函数

窗口函数介绍

hive:官网地址
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
聚和函数：多行数据按照规则进行聚和为一行
窗口函数：不仅要做聚和，而且返回的结果不仅仅是一行
over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化，不加参数默认全表，和当前行没有关系，跟在聚和函数后面，只对聚和函数生效。

相关函数说明

1. CURRENT ROW: 当前行

2. n PRECEDING: 往前n行数据

3. n FOLLOWING: 往后n行数据

4. UNBOUNDED:起点，UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDED FOLLOWING 表示到后面的终点

5. LAG(col,n):往前第n行数据

6. LEAD(col,n):往后第n行数据

7. NTILE(n):把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型
   准备数据：name,orderdate,cost

   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
   

   创建hive表并导入数据

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

需求：

1. 查询在2017年4月份购买过的顾客以及总人数(注意：不是购买次数)

   select name,count(*) over() from business where substring(orderdate,1,7)='2017-04' group by name;
   这里是人数,相同的人去掉(2人)
   ---------------------------------
   name	_wcol0
   mart	2
   jack	2
   ----------------------------------
   select name,count(*)  from business where substring(orderdate,1,7)='2017-04' group by name;
   这个是每个人购买次数
   ----------------------------------
   name	_c1
   jack	1
   mart	4
   

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

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

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

   select name,orderdate,cost, 
   sum(cost) over() as sample1,--所有行相加 
   sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
   sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
   sum(cost) over(partition by name order by orderdate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
   sum(cost) over(partition by name order by orderdate rows BETWEEN 1 PRECEDING AND CURRENT ROW) as sample5, --当前行和前面一行做聚合 
   sum(cost) over(partition by name order by orderdate rows BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
   sum(cost) over(partition by name order by orderdate rows BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
   from business;
   

   运行结果如下
   

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

   select name,orderdate,cost, 
   lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate) as time1, --按name分组，查找前一次时间，没有默认1900-01-01
   lag(orderdate,2) over (partition by name order by orderdate) as time2 --按name分组，查找前两次时间
   from business;
   

   运行结果如下
   

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

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

   运行如下
   
   分成5组，第二个sql是查询前20%时间的订单信息

RANK

1. RANK() 排序相同时会重复，总数不会变（名次会跳）

2. DENSE_RANK()排序相同时会重复，总数会减少（名次不跳）

3. ROW_NUMBER() 会根据顺序计算 （不会重复）
   数据准备name,subject,score

   create table score(
   name string,
   subject string, 
   score int) 
   row format delimited fields terminated by "\t";
   
   load data local inpath '/home/hadoop/data/window/score.txt' into table score;
   
   select name,subject,score,rank() over(partition by subject order by score desc) rp,
   dense_rank() over(partition by subject order by score desc) drp,
   row_number() over(partition by subject order by score desc) rmp
   from score;
   

   运行结果如下