Hive窗口函数

Hive窗口函数学习

1、over()窗口函数的语法结构
分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置 ）
partition by 可以理解为 group by 分组 分析函数按照每一组的数据开始进行计算的
row between 开始位置 and 结束位置 是指窗口函数的范围，从当前第一行到当前行
比较常用的是 rows between unbounded preceding and current row 常用该方法来进行累加操作
2、常与over()一起使用的分析函数
2.1 排名类
row_number()按照值排序时产生一个自增编号，不会重复（如：1、2、3、4、5、6）
rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位（如：1、2、3、3、3、6）
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（如：1、2、3、3、3、4）
2.2 排名类
lag(列名,往前的行数,[行数为null时的默认值，不指定为null])，可以计算用户上次购买时间，或者用户下次购买时间。
lead(列名,往后的行数,[行数为null时的默认值，不指定为null])
ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号
3、hive具体练习题
练习一
测试数据

20191020,11111,85
20191020,22222,83
20191020,33333,86
20191021,11111,87
20191021,22222,65
20191021,33333,98
20191022,11111,67
20191022,22222,34
20191022,33333,88
20191023,11111,99
20191023,22222,33

建表并且导入数据

create table test_window(
   logday string,
   userid string,
   score int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

加载数据

load data local inpath '文件路径' into table test_window;

查询下test_window表

1、使用 over() 函数进行数据统计, 统计每个用户及表中数据的总数

select logday,userid,score,count() over() from test_window;

结果如下图所示

logday	userid	score	count_window_0
20191023	22222	33	11
20191023	11111	99	11
20191022	33333	88	11
20191022	22222	34	11
20191022	11111	67	11
20191021	33333	98	11
20191021	22222	65	11
20191021	11111	87	11
20191020	33333	86	11
20191020	22222	83	11
20191020	11111	85	11

2、求用户明细并统计每天的用户总数

select logday,userid,score,count() over(partition by logday) as daysum from test_window;

结果如下图所示

logday	userid	score	daysum
20191020	33333	86	3
20191020	22222	83	3
20191020	11111	85	3
20191021	33333	98	3
20191021	22222	65	3
20191021	11111	87	3
20191022	33333	88	3
20191022	22222	34	3
20191022	11111	67	3
20191023	22222	33	2
20191023	11111	99	2

3、计算从第一天到现在的所有 score 大于80分的用户总数

select logday,userid,score,count() over(order by logday rows between unbounded preceding
and current row) as total 
from test_window t
where t.score > 80;

结果如下图所示

logday	userid	score	total
20191020	33333	86	1
20191020	22222	83	2
20191020	11111	85	3
20191021	33333	98	4
20191021	11111	87	5
20191022	33333	88	6
20191023	11111	99	7

4、计算每个用户到当前日期分数大于80的天数

select logday,userid,score,count() over(partition by userid order by logday rows between unbounded preceding and current row) as total
from test_window t where t.score > 80

结果如下图所示

logday	userid	score	total
20191020	11111	85	1
20191021	11111	87	2
20191023	11111	99	3
20191020	22222	83	1
20191020	33333	86	1
20191021	33333	98	2
20191022	33333	88	3

练习二
测试数据

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

建表并加载数据

create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
#加载数据
load data local inpath '你的路径' into table business;

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

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

结果如下图所示

name	orderdate	cost	total
mart	2017-04-13	94	5
mart	2017-04-11	75	5
mart	2017-04-09	68	5
mart	2017-04-08	62	5
jack	2017-04-06	42	5

2、查询每位顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by name,substr(orderdate,1,7)) total_amount from business;

结果如下图所示

name	orderdate	cost	total_amount
jack	2017-01-05	46	111
jack	2017-01-08	55	111
jack	2017-01-01	10	111
jack	2017-02-03	23	23
jack	2017-04-06	42	42
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	80
tony	2017-01-04	29	94
tony	2017-01-02	15	94
tony	2017-01-07	50	94

3、查询每位顾客的购买明细及到目前为止每个顾客购买总金额

select *,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) total 
from business; 

结果如下图所示

business.name	business.orderdate	business.cost	total
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

4、查询顾客上次的购买时间----lag()over()偏移量分析函数的运用

select * ,lag(orderdate,1) 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

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

select * from(
  select * ,ntile(5) over(order by orderdate) sortOrderdate_num from business) t
where t.sortOrderdate_num =1 

结果如下图所示

t.name	t.orderdate	t.cost	t.sortorderdate_num
jack	2017-01-01	10	1
tony	2017-01-02	15	1
tony	2017-01-04	29	1

练习三
测试数据

孙悟空 语文  87
孙悟空 数学  95
孙悟空 英语  68
大海  语文  94
大海  数学  56
大海  英语  84
宋宋  语文  64
宋宋  数学  86
宋宋  英语  84
婷婷  语文  65
婷婷  数学  85
婷婷  英语  78

建表

create table score3
(
name string,
subject string, 
score int
) row format delimited fields terminated by ",";
#加载
load data local inpath '你的路径' into table score3;

1、每门学科学生成绩排名(是否并列排名、空位排名三种实现)

select *,
row_number() over(partition by subject order by score desc),
rank() over(partition by subject order by score desc),
dense_rank() over(partition by subject order by score desc)
from score3;

结果如下图所示

score3.name	score3.subject	score3.score	row_number_window_0	rank_window_1	dense_rank_window_2
悟空	数学	95	1	1	1
宋宋	数学	86	2	2	2
婷婷	数学	85	3	3	3
大海	数学	56	4	4	4
宋宋	英语	84	1	1	1
大海	英语	84	2	1	1
悟空	英语	68	3	3	2
大海	语文	94	1	1	1
悟空	语文	87	2	2	2
婷婷	语文	65	3	3	3
宋宋	语文	64	4	4	4

2、每门学科成绩排名top 3的学生

select * from (
   select *,rank() over(partition by subject order by score desc) rmp
   from score3
) t
where t.rmp <= 3

结果

t.name	t.subject	t.score	t.rmp
悟空	数学	95	1
宋宋	数学	86	2
婷婷	数学	85	3
大海	英语	84	1
宋宋	英语	84	1
悟空	英语	68	3
大海	语文	94	1
悟空	语文	87	2
婷婷	语文	65	3