Hive窗口函数小结

一.窗口函数作用

1.1.测试数据

首先我们看下面一组数据: business表中有一组消费流水记录。

1.2.应用场景

需求：求在2017年4月份购买过的顾客及总人数 从上面数据可以看出，4月份mart和jack购买过，所以总人数是2人。

此时通过传统的group by显然无法实现：

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

或者需要用下面这样一个复杂的子查询才能完成：

select t_name.name,t_count.count from
(
select name
from business
where substring(orderdate,1,7) = '2017-04'
group by name
) as t_name
,
(
select count(*) as count from 
(
select name
from business
where substring(orderdate,1,7) = '2017-04'
group by name
)as t3
)as t_count

• 用窗口函数实现上述需求sql代码：

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

这里的over()函数就是窗口函数，这里没有传其他参数，默认窗口范围是所有数据。可以理解为，整个表先按照group by 聚合，然后where条件再限定，得到的结果为：

jack

mart

然后计算count()的作用范围就是over()函数指定的范围，这里范围就是上面结果的全部数据，所以count()得到2.

下面将详细介绍窗口函数，并通过蚂蚁金服的hive面试题进行深入理解。

二.窗口函数概念

2.1.语法结构

[分析函数] over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置))

窗口函数over一般紧跟在分析函数后面，over中的partition by 等用于确定窗口的范围，下面将详细介绍。

2.2.分析函数

分析函数的特点是多进一出，常见的有下面一些。

• 聚合类

  avg() -- 求平均
  sum() -- 求和
  max() -- 最大值
  min() -- 最小值
  

• 排名类

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

• 其他类

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

2.3.over函数的参数

over()函数中包括三个函数：包括

• 分区partition by 列名、

• 排序order by 列名、

• 指定窗口范围rows between 开始位置 and 结束位置。

范围参数说明：

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

我们在使用over()窗口函数时，over()函数中的这三个函数可组合使用也可以不使用。partition by也可以用distribute by代替

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

其中partition by 类似于group by，这样窗口大小就是每个分组大小。

order by 这个比较难理解，采用了order by,其效果和 rows between UNBOUNDED PRECEDING AND CURRENT ROW 一样，窗口大小都是从起点到当前行。如果同时指定了分区，该规则限定在分区内。

三.窗口函数入门

3.1.over(partition by)

需求：查询顾客的购买明细及购买总额

因为是要求购买明细，所以每条记录都需要返回，显然group by不能满足，所以用窗口函数就非常合适。

每条记录最后一列就是对应用户的购买总金额。

3.2.over(order by)

需求：查询顾客购买明细，以及截止前日期的购买综合

因为购买是有日期顺序，所以是一个累加过程，窗口是移动的，每条数据的最后一列计算的是当前日期之前的购买总和，所以可以看出order by的窗口范围

是当前行之前的所有数据。而且这里用partition by 限定了order by的范围，这是最常用的用法。

这个需求也可以用下面的语句实现，结果一样：

select 
bs.name,bs.orderdate,bs.cost,sum(bs.cost) over(partition by bs.name rows between UNBOUNDED PRECEDING AND CURRENT ROW)
from business bs;

rows between UNBOUNDED PRECEDING AND CURRENT ROW表示从起点开始到当前行，和order by是一样的。当然，范围参数更加灵活，参加上面范围参数。

3.3.lag函数求上次购买时间

需求：查询顾客明细和上次购买时间

lag用于统计窗口内往上第n行值

3.4.ntile函数查前20%订单

需求：查询前日期排名前20%的明细

​ 这里ntile将数据分为5个组，图中只执行了选中的部分，可以看出结果会落在五个组，用字段sorted标识，然后执行完整sql过滤出sorted=1的就可以计算20%的订单，当然了如果数量不是5的倍数，结果只是约等于。

3.5.lead统计下次购买时间

需求：查询用户购买明细和下次购买时间。

强行为了使用该函数，场景并不典型，凑合看吧。这里当没有下一次时，用“最后一次” 填充

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

3.6.fist_value和last_value

需求：查询每个用户购买明细和首次购买花费的金额。last_value正好相反取窗口内最后一个值。

3.7.rank排名函数

create table score(
	name string,
	subject string,
	score int
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/test.db/score';

load data local inpath '/opt/soft/hiveData/score.txt' into table score;
use db_test;
show tables;
select * from score;

select 
sc.name,sc.subject,sc.score,
RANK() over(partition by sc.subject order by sc.score desc) as rk,
DENSE_RANK () over(partition by sc.subject order by sc.score desc) as d_rk,
row_number() over(partition by sc.subject order by sc.score desc) as rn

from score sc;

show functions;
desc function get_json_object;

四.窗口函数进阶

这里用蚂蚁金服的一道hive面试题作为小结。

4.1.测试数据

这里涉及两张表:

表1：植物换购表。

create table if not exists plant_carbon(
	plant_id string comment '植物编号',
	plant_name string comment '植物名称',
	low_carbon int comment '换购所需碳量'
)comment '植物换购表'
row format delimited fields terminated by '\t';

表2：用户减碳量领取表 下面数据截图显示了一部分。需要原始数据的记得留言。

create table if not exists user_low_carbon(
	user_id string comment '用户id',
	data_dt string comment '日期',
	low_carbon int comment '减少碳排放量(g)'
) comment '碳领取流水表'
row format delimited fields terminated by '\t';

4.2.需求1描述和实现

问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p001-梭梭树” 。
统计在10月1日累计申领“p001-梭梭树” 排名前10的用户信息；以及他比后一名多领了几颗梭梭树。
得到的统计结果如下表样式：
user_id plant_count less_count(比后一名多领了几颗梭梭树)
u_101 1000 100
u_088 900 400
u_103 500 …

解读：这里如果能量够买胡杨，会先买一棵胡杨，所以计算梭梭树时需要减掉。

实现效果:

sql:

--10月1各个用户累计碳量
select 
user_id,
sum(low_carbon)
from user_low_carbon 
group by user_id;

--10月1号累计总量>=215的用户会领一棵胡杨。所以可以减去胡杨的消耗，看剩余的购买梭梭树（需要17能量）的能量有多少。
select 
	t1.user_id,
	t1.total-t2.low_carbon
from 
(
select 
user_id,
sum(low_carbon) as total
from user_low_carbon 
group by user_id
)as t1,plant_carbon t2 where t2.plant_name='胡杨';


--在上一步基础上，计算能购买的梭梭树数量，并进行排名并利用窗口函数进行计算差值。
select 
t5.user_id,
t5.count_ss as plant_count,
max(t5.count_ss)-min(t5.count_ss) OVER(order by t5.count_ss desc ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) as less_count
from 
(
select t3.user_id,FLOOR(t3.total_ss/t4.low_carbon) as count_ss
from 
(
select 
	t1.user_id,
	t1.total-t2.low_carbon as total_ss
from 
(
select 
user_id,
sum(low_carbon) as total
from user_low_carbon 
group by user_id
)as t1,plant_carbon t2 where t2.plant_name='胡杨'
)as t3,plant_carbon t4 where t4.plant_name='梭梭树' 
)as t5 
group by t5.user_id,t5.count_ss;

4.3.需求2描述和实现

问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g：
seq（key） user_id data_dt low_carbon
xxxxx10 u_002 2017/1/2 150
xxxxx11 u_002 2017/1/2 70
xxxxx12 u_002 2017/1/3 30
xxxxx13 u_002 2017/1/3 80
xxxxx14 u_002 2017/1/4 150
xxxxx14 u_002 2017/1/5 101

实现效果：这里就是分步骤逐渐找到最终答案，分解问题。

sql:

--第一步：先合并同一天收集多次的记录，然后找到2017每天碳值>=100的记录,并按日期排名

select 
user_id,
data_dt,
sum(low_carbon) as low_carbon ,
rank() over(partition by user_id order by data_dt) as rk
from user_low_carbon 
where low_carbon >=100 and data_dt BETWEEN '2017/1/1' and '2027/12/31'
group by user_id ,data_dt;


--第二步：在上面基础，计算日期和排名的差值 diff

select 
t1.user_id,
t1.data_dt,
t1.low_carbon,
t1.rk,
date_sub(concat_ws('-',split(t1.data_dt,'/')),t1.rk) as diff
from 
(
select 
user_id,
data_dt,
sum(low_carbon) as low_carbon,
rank() over(partition by user_id order by data_dt) as rk
from user_low_carbon 
where low_carbon >=100 and data_dt BETWEEN '2017/1/1' and '2027/12/31'
group by user_id ,data_dt
) as t1

--第三步：上面基础上，按user_id和diff分组：则分组内就是连续的数据集。并过滤出连续>=3日的数据。

select 
t2.user_id,
t2.diff
from 
(
select 
t1.user_id,
t1.data_dt,
t1.low_carbon,
t1.rk,
date_sub(concat_ws('-',split(t1.data_dt,'/')),t1.rk) as diff
from 
(
select 
user_id,
data_dt,
sum(low_carbon) as low_carbon,
rank() over(partition by user_id order by data_dt) as rk
from user_low_carbon 
where low_carbon >=100 and data_dt BETWEEN '2017/1/1' and '2027/12/31'
group by user_id ,data_dt
) as t1
) as t2
group by t2.user_id,t2.diff
having count(*)>=3;


--第四步：用二、三两步结构进行join

select 
t_left.user_id,
t_left.data_dt,
t_left.low_carbon
from
(
select 
t1.user_id,
t1.data_dt,
t1.low_carbon,
t1.rk,
date_sub(concat_ws('-',split(t1.data_dt,'/')),t1.rk) as diff
from 
(
select 
user_id,
data_dt,
sum(low_carbon) as low_carbon,
rank() over(partition by user_id order by data_dt) as rk
from user_low_carbon 
where low_carbon >=100 and data_dt BETWEEN '2017/1/1' and '2027/12/31'
group by user_id ,data_dt
) as t1
) as t_left
join 
(
select 
t2.user_id,
t2.diff
from 
(
select 
t1.user_id,
t1.data_dt,
t1.low_carbon,
t1.rk,
date_sub(concat_ws('-',split(t1.data_dt,'/')),t1.rk) as diff
from 
(
select 
user_id,
data_dt,
sum(low_carbon) as low_carbon,
rank() over(partition by user_id order by data_dt) as rk
from user_low_carbon 
where low_carbon >=100 and data_dt BETWEEN '2017/1/1' and '2027/12/31'
group by user_id ,data_dt
) as t1
) as t2
group by t2.user_id,t2.diff
having count(*)>=3
)as t_right
on t_left.user_id=t_right.user_id and t_left.diff=t_right.diff;

五.小结

5.1.全文小结

hive的函数使用关键就是实践，动手写必不可少。上述几个需求，其实对应了具体的业务场景，非常实用。