Hive窗口函数详解—及3套案例练习

可爱的猫

目录：
一、hive窗口函数语法
----over()窗口函数的语法结构
----常与over()一起使用的分析函数
二、hive窗口函数练习28道题

视频录制好了，建议大家边看视频边阅读文章，文章中的窗口函数练习题也有相应的视频。点击蓝色字体可跳转到视频。

前言：我们在学习hive窗口函数的时候，一定要先了解窗口函数的结构。而不是直接百度sum() over()、row_number() over()、或者count() over()的用法，如果这样做，永远也掌握不到窗口函数的核心，当然我刚开始的时候也是这样做的，包括去年自己在接触ORACLE分析函数时也是这样搜索。

还好我比较顽强，在HIVE窗口函数问题上折腾了半个月、看了很多文章后才知道over()才是窗口函数，而sum、row_number、count只是与over()搭配的分析函数，当然除了这三个函数还有其他的函数。

一、hive窗口函数语法

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

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

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

1）partition by分区：
partition by可理解为group by 分组。over(partition by 列名)搭配分析函数时，分析函数按照每一组每一组的数据进行计算的。

2）order by排序：
order by是排序的意思，针对该窗口

3）窗口帧
窗口帧用于从分区中选择指定的多条记录，也就是对分区中的数据范围进行限定，供分析函数处理。Hive 提供了两种定义窗口帧的形式：ROWS 和 RANGE。两种类型都需要配置上界和下界。

例如：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（表示当前行到终点）

因为rows between工作中使用比较少，比较难理解，这里举几个案例。

案例1：求当前日期销售额和后面所有日期的销售额总和、以及当前日期销售额和前面所有日期的销售额总和

SELECT  
  sid,  
  day_time,  --时间
  sales_volume,   --销售额
--1）当前日期销售额和后面所有日期的销售额总和
-- unbounded following 最后一行
  sum(sales_volume) over(rows between current row and unbounded following) sum_sales ，

--2）求当前日期销售额和前面所有日期的销售额总和
-- unbounded preceding就是第一行数据,current row当前行
-- 这个是累计求和，好像通过 sum()over(partition by 字段 order by 字段)也可以实现，验证后补充
sum(sales_volume) over(rows between unbounded preceding and current row) sum_sales2  
FROM 
dw_sale_data;

rows between current row and unbounded following
这里面current row就是指当前行,unbounded following就是指最后一行,unbounded preceding就是第一行数据。
and前面和后面的关系就是范围,从and后面的到and前面,但是and前面是不可以使用unbounded following的,这样使用是错误的。

案例2：求当前日期和后面两天销售额的总和，以及当前日期和前两天销售额的总和

SELECT  
sid,   --订单id
day_time,   --时间
sales_volume,   --销售额
-- 1）求当前日期和后面两天销售额的总和,
-- current row是指当前行,2 following就是后两行数据
sum(sales_volume) over(rows between current row and 2 following) sum_sales ，

-- 2）求当前日期和前两天销售额的总和
-- 1 preceding 就是指上一行数据,current row就是指当前行数据
sum(sales_volume) over(rows between 1 preceding and current row) sum_sales2 ，
FROM dw_sale_data;

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

2.1、聚合类

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

2.2、排名类

row_number()按照值排序时产生一个自增编号，不会重复（如：1、2、3、4、5、6）
rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位（如：1、2、3、3、3、6）
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（如：1、2、3、3、3、4）

2.3、其他类

** lag() 、lead() 、ntile(n) 、first_value() 、last_value()**
1) lag()
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

lag(列名,往前第几行的数据,[行数为null时的默认值，不指定为null]),可以计算用户上次购买时间，或者用户下次购买时间。

2)lead()
与LAG相反，LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

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

lead(列名,往后第几行的数据,[行数为null时的默认值，不指定为null])

3)ntile(n):
NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值

把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号.
NTILE不支持ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

SELECT   
cookieid,  
createtime,  
pv,  
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,    --分组内将数据分成2片  
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,  --分组内将数据分成3片  
NTILE(4) OVER(ORDER BY createtime) AS rn3        --将所有数据分成4片  
FROM dw_window_data   
ORDER BY cookieid,createtime;

4)first_value()
取分组内排序后，截止到当前行，第一个值

SELECT cookieid,  
createtime,  
url,  
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1   
FROM dw_window_data_02;

5) last_value()
取分组内排序后，截止到当前行，最后一个值

SELECT cookieid,  
createtime,  
url,  
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1   
FROM dw_window_data_02;

3、窗口函数总结：

其实窗口函数逻辑比较绕，我们可以把窗口理解为对表中的数据进行分组，排序等计算。要真正的理解HIVE窗口函数，还是要结合练习题才行。下面我们开始HIVE窗口函数的练习吧！

二、hive窗口函数练习28道题

第一套练习：hive之简单窗口函数 over()

1、使用 over() 函数进行数据统计, 统计每个用户及表中数据的总数
2、求用户明细并统计每天的用户总数
3、计算从第一天到现在的所有 score 大于80分的用户总数
4、计算每个用户到当前日期分数大于80的天数

测试数据

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,    #logday时间
userid string, 
score int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

#加载数据
load data local inpath '/home/xiaowangzi/hive_test_data/test_window.txt' into table test_window;

我们先看下表中的数据：

select  *  from test_window;

test_window

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

select *, count(userid)over() as total  from  test_window;

这里使用 over() 与 select count(*) 有相同的作用，好处就是，在需要计算总数时不用再进行一次关联。

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

可以使用 partition by 按日期列对数据进行分区处理，如：over(partition by logday)

select  *,count()over(partition by logday)as day_total from  test_window;

求每天的用户数可以使用select logday, count(userid) from recommend.test_window group by logday，但是当想要得到 userid 信息时，这种用法的优势就很明显。

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

此时简单的分区不能满足需求，需要将 order by 和窗口定义结合使用。

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

通过 over() 计算出按日期的累加值。
其实自己刚开始的时候就计算我思路是错了，我就想的是不用累加，直接select *,count()over()as total from test_window where score > 80;这样计算，如果这样计算的话只会显示表中所有大于80的人数，如果我想看20191021或者看20191022的人数看不见。

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

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

第二套练习

1、查询在2017年4月份购买过的顾客及总人数
2、查询顾客的购买明细及月购买总额
3、查询顾客的购买明细及到目前为止每个顾客购买总金额
4、查询顾客上次的购买时间----lag()over()偏移量分析函数的运用
5、查询前20%时间的订单信息

测试数据

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 "/home/xiaowangzi/hive_test_data/business.txt" into table business;

查看表数据

select   *  from  business;

查看表数据

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

分析：按照日期过滤、分组count求总人数(分组为什么不是用group by？自己思考)

select   *,count()over() as total  from  business 
where substr(orderdate,1,7) = '2017-04';

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

是计算每月每个用户的消费金额

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

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

分析：按照顾客分组、日期升序排序、组内每条数据将之前的金额累加

select 
*,
sum(cost) over(partition by name order  by  orderdate 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_amount
from 
business;

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

lag()over()偏移量分析函数的运用

select 
name,
orderdate,
cost,
lag(orderdate,1) over(partition by name order by orderdate) last_date
from 
business;

image.png

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

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

第三套练习：

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

原始数据(学生成绩信息)

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