HIVE中开窗函数的使用
HIVE中开窗函数的使用
相关函数说明 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类型。表：
+----------------+---------------------+----------------+--+
| business.name | business.orderdate | business.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 |
+----------------+---------------------+----------------+--+
1、查询在2017年4月份购买过的顾客及总人数
select distinct name,count(*) over() from business where substring(orderdate,1,7)="2017-04";
+-------+-----------------+--+
| name | count_window_0 |
+-------+-----------------+--+
| mart | 2 |
| jack | 2 |
+-------+-----------------+--+
2、查询顾客的购买明细及月购买总额
select *,sum(cost) over(partition by substring(orderdate,1,7)) from business;
+----------------+---------------------+----------------+---------------+--+
| business.name | business.orderdate | business.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按照日期进行累加
3.1
select *,sum(cost) over(partition by name order by substring(orderdate,1,7)) from business;
+----------------+---------------------+----------------+---------------+--+
| business.name | business.orderdate | business.cost | sum_window_0 |
+----------------+---------------------+----------------+---------------+--+
| jack | 2017-01-05 | 46 | 111 |
| jack | 2017-01-08 | 55 | 111 |
| jack | 2017-01-01 | 10 | 111 |
| jack | 2017-02-03 | 23 | 134 |
| jack | 2017-04-06 | 42 | 176 |
| mart | 2017-04-13 | 94 | 299 |
| mart | 2017-04-11 | 75 | 299 |
| mart | 2017-04-09 | 68 | 299 |
| mart | 2017-04-08 | 62 | 299 |
| neil | 2017-05-10 | 12 | 12 |
| neil | 2017-06-12 | 80 | 92 |
| tony | 2017-01-04 | 29 | 94 |
| tony | 2017-01-02 | 15 | 94 |
| tony | 2017-01-07 | 50 | 94 |
+----------------+---------------------+----------------+---------------+--+
3.2
select *,sum(cost) over(order by orderdate rows between unbounded preceding and current row) from business;
+----------------+---------------------+----------------+---------------+--+
| business.name | business.orderdate | business.cost | sum_window_0 |
+----------------+---------------------+----------------+---------------+--+
| jack | 2017-01-01 | 10 | 10 |
| tony | 2017-01-02 | 15 | 25 |
| tony | 2017-01-04 | 29 | 54 |
| jack | 2017-01-05 | 46 | 100 |
| tony | 2017-01-07 | 50 | 150 |
| jack | 2017-01-08 | 55 | 205 |
| jack | 2017-02-03 | 23 | 228 |
| jack | 2017-04-06 | 42 | 270 |
| mart | 2017-04-08 | 62 | 332 |
| mart | 2017-04-09 | 68 | 400 |
| mart | 2017-04-11 | 75 | 475 |
| mart | 2017-04-13 | 94 | 569 |
| neil | 2017-05-10 | 12 | 581 |
| neil | 2017-06-12 | 80 | 661 |
+----------------+---------------------+----------------+---------------+--+
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;
+-------+-------------+-------+----------+----------+----------+----------+----------+----------+----------+--+
| name | orderdate | cost | sample1 | sample2 | sample3 | sample4 | sample5 | sample6 | sample7 |
+-------+-------------+-------+----------+----------+----------+----------+----------+----------+----------+--+
| jack | 2017-01-01 | 10 | 661 | 176 | 10 | 10 | 10 | 56 | 176 |
| jack | 2017-01-05 | 46 | 661 | 176 | 56 | 56 | 56 | 111 | 166 |
| jack | 2017-01-08 | 55 | 661 | 176 | 111 | 111 | 101 | 124 | 120 |
| jack | 2017-02-03 | 23 | 661 | 176 | 134 | 134 | 78 | 120 | 65 |
| jack | 2017-04-06 | 42 | 661 | 176 | 176 | 176 | 65 | 65 | 42 |
| mart | 2017-04-08 | 62 | 661 | 299 | 62 | 62 | 62 | 130 | 299 |
| mart | 2017-04-09 | 68 | 661 | 299 | 130 | 130 | 130 | 205 | 237 |
| mart | 2017-04-11 | 75 | 661 | 299 | 205 | 205 | 143 | 237 | 169 |
| mart | 2017-04-13 | 94 | 661 | 299 | 299 | 299 | 169 | 169 | 94 |
| neil | 2017-05-10 | 12 | 661 | 92 | 12 | 12 | 12 | 92 | 92 |
| neil | 2017-06-12 | 80 | 661 | 92 | 92 | 92 | 92 | 92 | 80 |
| tony | 2017-01-02 | 15 | 661 | 94 | 15 | 15 | 15 | 44 | 94 |
| tony | 2017-01-04 | 29 | 661 | 94 | 44 | 44 | 44 | 94 | 79 |
| tony | 2017-01-07 | 50 | 661 | 94 | 94 | 94 | 79 | 79 | 50 |
+-------+-------------+-------+----------+----------+----------+----------+----------+----------+----------+--+
其中sample3和sample4是一样的，都是按name分组，组内数据累加
上面总共开了7个窗口函数，select执行完了之后（select不需要执行MapReduce程序），每多一个窗口，就多一个MapReduce执行函数，但是这个前提是窗口开的不一样，只有窗口开的不一样才有额外的MapReduce，
但是，sample3~sample7的窗口都是一样的，只不过他们各自加的行的范围不一样而已，但是窗口都是一个窗口。
4、查询每个顾客上次的购买时间
4.1
select *,lag(orderdate) over(partition by name order by orderdate) from business;
+----------------+---------------------+----------------+---------------+--+
| business.name | business.orderdate | business.cost | lag_window_0 |
+----------------+---------------------+----------------+---------------+--+
| jack | 2017-01-01 | 10 | NULL |
| 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 | NULL |
| 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 | NULL |
| neil | 2017-06-12 | 80 | 2017-05-10 |
| tony | 2017-01-02 | 15 | NULL |
| tony | 2017-01-04 | 29 | 2017-01-02 |
| tony | 2017-01-07 | 50 | 2017-01-04 |
+----------------+---------------------+----------------+---------------+--+
4.2
select *,lag(orderdate,1,"1970-01-01") over(partition by name order by orderdate) from business;
+----------------+---------------------+----------------+---------------+--+
| business.name | business.orderdate | business.cost | lag_window_0 |
+----------------+---------------------+----------------+---------------+--+
| jack | 2017-01-01 | 10 | 1970-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 | 1970-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 | 1970-01-01 |
| neil | 2017-06-12 | 80 | 2017-05-10 |
| tony | 2017-01-02 | 15 | 1970-01-01 |
| tony | 2017-01-04 | 29 | 2017-01-02 |
| tony | 2017-01-07 | 50 | 2017-01-04 |
+----------------+---------------------+----------------+---------------+--+
5、查询前20%时间的订单信息
5.1 先体验一下ntile函数的分组功能
select *,ntile(5) tgroup over(order by orderdate) from business;
+----------------+---------------------+----------------+-----------------+--+
| business.name | business.orderdate | business.cost | ntile_window_0 |
+----------------+---------------------+----------------+-----------------+--+
| 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 |
+----------------+---------------------+----------------+-----------------+--+
select * from (select *,ntile(5) tgroup over(order by orderdate) from business) t1 where t1.tgroup=1;
5.3 体验一下persent_rank()函数，求大致比例，不过是从0开始的。
select *,percent_rank() over(order by orderdate) pr from business;
+----------------+---------------------+----------------+----------------------+--+
| business.name | business.orderdate | business.cost | pr |
+----------------+---------------------+----------------+----------------------+--+
| jack | 2017-01-01 | 10 | 0.0 |
| tony | 2017-01-02 | 15 | 0.07692307692307693 |
| tony | 2017-01-04 | 29 | 0.15384615384615385 |
| jack | 2017-01-05 | 46 | 0.23076923076923078 |
| tony | 2017-01-07 | 50 | 0.3076923076923077 |
| jack | 2017-01-08 | 55 | 0.38461538461538464 |
| jack | 2017-02-03 | 23 | 0.46153846153846156 |
| jack | 2017-04-06 | 42 | 0.5384615384615384 |
| mart | 2017-04-08 | 62 | 0.6153846153846154 |
| mart | 2017-04-09 | 68 | 0.6923076923076923 |
| mart | 2017-04-11 | 75 | 0.7692307692307693 |
| mart | 2017-04-13 | 94 | 0.8461538461538461 |
| neil | 2017-05-10 | 12 | 0.9230769230769231 |
| neil | 2017-06-12 | 80 | 1.0 |
+----------------+---------------------+----------------+----------------------+--+
给定下表：
+-------------+----------------+--------------+--+
| score.name | score.subject | score.score |
+-------------+----------------+--------------+--+
| 孙悟空 | 语文 | 87 |
| 孙悟空 | 数学 | 95 |
| 孙悟空 | 英语 | 68 |
| 大海 | 语文 | 94 |
| 大海 | 数学 | 56 |
| 大海 | 英语 | 84 |
| 宋宋 | 语文 | 64 |
| 宋宋 | 数学 | 86 |
| 宋宋 | 英语 | 84 |
| 婷婷 | 语文 | 65 |
| 婷婷 | 数学 | 85 |
| 婷婷 | 英语 | 78 |
+-------------+----------------+--------------+--+
体验rank()函数
select *,rank() over(partition by subject order by score desc) r from score;
+-------------+----------------+--------------+----+--+
| score.name | score.subject | score.score | r |
+-------------+----------------+--------------+----+--+
| 孙悟空 | 数学 | 95 | 1 |
| 宋宋 | 数学 | 86 | 2 |
| 婷婷 | 数学 | 85 | 3 |
| 大海 | 数学 | 56 | 4 |
| 宋宋 | 英语 | 84 | 1 |
| 大海 | 英语 | 84 | 1 |
| 婷婷 | 英语 | 78 | 3 |
| 孙悟空 | 英语 | 68 | 4 |
| 大海 | 语文 | 94 | 1 |
| 孙悟空 | 语文 | 87 | 2 |
| 婷婷 | 语文 | 65 | 3 |
| 宋宋 | 语文 | 64 | 4 |
+-------------+----------------+--------------+----+--+
对比rank()、dense_rank()、row_number()差异
select *,rank() over(partition by subject order by score desc) r,
dense_rank() over(partition by subject order by score desc) dr,
row_number() over(partition by subject order by score desc) rr
from score;
+-------------+----------------+--------------+----+-----+-----+--+
| score.name | score.subject | score.score | r | dr | rr |
+-------------+----------------+--------------+----+-----+-----+--+
| 孙悟空 | 数学 | 95 | 1 | 1 | 1 |
| 宋宋 | 数学 | 86 | 2 | 2 | 2 |
| 婷婷 | 数学 | 85 | 3 | 3 | 3 |
| 大海 | 数学 | 56 | 4 | 4 | 4 |
| 宋宋 | 英语 | 84 | 1 | 1 | 1 |
| 大海 | 英语 | 84 | 1 | 1 | 2 |
| 婷婷 | 英语 | 78 | 3 | 2 | 3 |
| 孙悟空 | 英语 | 68 | 4 | 3 | 4 |
| 大海 | 语文 | 94 | 1 | 1 | 1 |
| 孙悟空 | 语文 | 87 | 2 | 2 | 2 |
| 婷婷 | 语文 | 65 | 3 | 3 | 3 |
| 宋宋 | 语文 | 64 | 4 | 4 | 4 |
+-------------+----------------+--------------+----+-----+-----+--+
浙公网安备 33010602011771号