Hive窗口函数详解

发布日期: 2021-06-30

一、hive窗口函数语法

在前言中我们已经说了avg()、sum()、max()、min()是分析函数，
而over()才是窗口函数，下面我们来看看over()窗口函数的语法结构、
及常与over()一起使用的分析函数

1
2
3

1、over()窗口函数的语法结构
2、常与over()一起使用的分析函数
3、窗口函数总结

1、over()窗口函数的语法结构

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
over()函数中包括三个函数：包括分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置。我们在使用over()窗口函数时，over()函数中的这三个函数可组合使用也可以不使用。

over()函数中如果不使用这三个函数，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据。

1.1、over()函数中的三个函数讲解

order by
order by是排序的意思，是该窗口中的

A、partition by
partition by可理解为group by 分组。over(partition by 列名)搭配分析函数时，分析函数按照每一组每一组的数据进行计算的。
B、rows between 开始位置 and 结束位置
是指定窗口范围，比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时，分析函数按照这个范围进行计算的。
窗口范围说明：
我们常使用的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行），常用该窗口来计算累加。

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）
比如说：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

2、常与over()一起使用的分析函数：

2.1、聚合类

avg()、sum()、max()、min()

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.1、其他类

lag(列名,往前的行数,[行数为null时的默认值，不指定为null])，可以计算用户上次购买时间，或者用户下次购买时间。
lead(列名,往后的行数,[行数为null时的默认值，不指定为null])
ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号

3、练习

练习1

建表

-- 创建表
create table izk_tmpdb.test_window_01_scx
(logday string COMMENT '日期', 
userid string COMMENT '用户id', 
score int COMMENT '分数')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

插入数据

-- 创建数据
insert into   izk_tmpdb.test_window_01_scx values
('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)

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

1	select *, count(userid)over() as total from izk_tmpdb.test_window_01_scx ;

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

1	select *,count()over(partition by logday)as day_total from izk_tmpdb.test_window_01_scx ;

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

-- 筛选数据
select  *,count(*)over(order by logday rows between unbounded preceding and unbounded following )as total 
from  izk_tmpdb.test_window_01_scx 
where score > 80
and userid='11111';
-- 全部数据
select * 
from  izk_tmpdb.test_window_01_scx 
where score > 80
and userid='11111';

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

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

练习2

建表

-- 创建表
create table izk_tmpdb.test_window_02_scx
(
name string  COMMENT '客户', 
orderdate string  COMMENT '下单日期', 
cost int  COMMENT '费用'
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

插入数据

-- 创建数据
insert into   izk_tmpdb.test_window_02_scx values
('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月份购买过的顾客及总人数

1 2	select ,count()over() as total from izk_tmpdb.test_window_02_scx where substr(orderdate,1,7) = '2017-04';

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

1	select *,sum(cost)over(partition by name,substr(orderdate,1,7)) as total from izk_tmpdb.test_window_02_scx

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

1 2	select *,sum(cost) over(partition by name order by orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_amount from izk_tmpdb.test_window_02_scx

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

select 
name,
orderdate,
cost,
lag(orderdate,1) over(partition by name order by orderdate) last_date
from   izk_tmpdb.test_window_02_scx

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

select  *  
from 
(select   *,
ntile(5)over(order  by  cost)sortgroup_num from  izk_tmpdb.test_window_02_scx  )t 
where t.sortgroup_num = 1;

练习3

建表

-- 创建表
create table izk_tmpdb.test_window_03_scx
(
name string  COMMENT '姓名', 
subject string  COMMENT '学科', 
score int  COMMENT '分数'
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

插入数据

-- 创建数据
insert into   izk_tmpdb.test_window_03_scx values
('孙悟空', '语文',  87),
('孙悟空', '数学',  95),
('孙悟空', '英语',  68),
('大海',  '语文',  94),
('大海',  '数学',  56),
('大海',  '英语',  84),
('宋宋',  '语文',  64),
('宋宋',  '数学',  86),
('宋宋',  '英语',  84),
('婷婷',  '语文',  65),
('婷婷',  '数学',  85),
('婷婷',  '英语',  78)

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

select  *,
row_number()over(partition by subject order by score desc) row_number,
rank()over(partition by subject order by score desc) rank,
dense_rank()over(partition by subject order by score desc) dense_rank
from  izk_tmpdb.test_window_03_scx

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

select 
*
from 
(
select 
*,
row_number() over(partition by subject order by score desc) rmp
from izk_tmpdb.test_window_03_scx 
) t
where t.rmp<=3;

select logday,score,
sum(score)  over(order by logday rows between unbounded preceding and current row) uc01,-- 从最开始到当前。
sum(score)  over(order by logday ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) uc02, -- 当前到后两个（共3个值累加）
sum(score)  over(order by logday ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) uc03, -- 当前到钱两个（共3个值累加） 
sum(score)  over(order by logday ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) uc04 -- 从当前加到最后
 from izk_tmpdb.test_window_01_scx
where userid='22222'