Hive_窗口函数(开窗函数)

1．相关函数说明

OVER()：

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

CURRENT ROW：

　　当前行

n PRECEDING：

　　往前n行数据

n FOLLOWING：

　　往后n行数据

UNBOUNDED：

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

LAG(col,n,DEFAULT) ：

　　往前第n行数据

　　第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

LEAD(col,n,DEFAULT)：

　　往后第n行数据

　　第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

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按照日期进行累加

（4）查询每个顾客上次的购买时间

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

4．创建本地business.txt，导入数据

[hadoop@hadoop102 datas]$ vi business.txt

5．创建hive表并导入数据

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

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

6．按需求查询数据

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

　　①不使用开窗函数，就是普通的分组统计查询

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

　　　得出的结果显现不是我们想要的，这个结果是每个人四月份出现的次数

+-------+------+--+
| name  | _c1  |
+-------+------+--+
| jack  | 1    |
| mart  | 4    |
+-------+------+--+

　　②使用开窗函数，即在查询的后面加上over()，这题上我理解的开窗函数就是把整张表当做一个窗口，没有添加限制，统计的是整张表中name出现的次数

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

　　这里运行了两次mapreduce，因为暂时不知道，但是得出的结果符合题意

+-------+-----------------+--+
| name  | count_window_0  |
+-------+-----------------+--+
| mart  | 2               |
| jack  | 2               |
+-------+-----------------+--+

 

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

　　原本打算做两次查询，但是发现over()函数的前面字段必须做点什么事情，故发现了over()函数是针对前面一个字段进行开窗，所以sun(cost)后接开窗函数，并且在开窗函数中指定分区是按月份即可

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           |
| 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按照日期进行累加

　　使用开窗函数对sum(cost)开窗，按照名字分组，时间排序，首行到当前行的一个累加

select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as cost_sum
from business;

　　结果符合题意

+-------+-------------+-------+-----------+--+
| name  |  orderdate  | cost  | cost_sum  |
+-------+-------------+-------+-----------+--+
| 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        |
+-------+-------------+-------+-----------+--+

注意：rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

（4）查看顾客上次的购买时间

　　使用LAG函数并配合开窗函数中指定按名字分区和按时间排序即可得到上一次顾客购买的时间

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%时间的订单信息

　　把订单信息拆分成5组，开窗函数内按时间排序，where语句中取第一组即可，因为要用到where语句，所以分两次写

select name,orderdate,cost
from (select name,orderdate,cost,NTILE(5) over(order by orderdate) as group_
from business) t
where t.group_=1;

 

　　结果符合题意

+-------+-------------+-------+--+
| name  |  orderdate  | cost  |
+-------+-------------+-------+--+
| jack  | 2017-01-01  | 10    |
| tony  | 2017-01-02  | 15    |
| tony  | 2017-01-04  | 29    |
+-------+-------------+-------+--+

为什么要分两次写而不能组合一次写？

　　因为开窗函数的执行顺序几乎是最后执行的，在开窗函数后面执行的只有一个limit，如果组合写的话，其他地方要用到开窗函数中的字段，会找不到，所以只能把开窗函数单独写一条命令。