最全面的Hive开窗函数讲解和实战指南
窗口函数(Window Function)是 SQL2003 标准中定义的一项新特性,并在 SQL2011、SQL2016 中又加以完善,添加了若干拓展。
窗口函数不同于我们熟悉的常规函数及聚合函数,它为每行数据进行一次计算,特点是输入多行(一个窗口)、返回一个值。
在报表等数据分析场景中,你会发现窗口函数真的很强大,灵活运用窗口函数可以解决很多复杂问题,比如去重、排名、同比及环比、连续登录等等。
既然窗口函数这么强大,更要了解和灵活运用它了,本文将对窗口函数进行一个全面的整理,讲一讲窗口函数是什么, 有哪些分类,用法是什么,以及窗口函数的案例加深大家的理解。
那什么是窗口函数呢?
窗口函数出现在 SELECT 子句的表达式列表中,它最显著的特点就是 OVER 关键字。语法定义如下:
1 | Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] |
Function (arg1,…, argn) 可以是下面的函数:
Aggregate Functions: 聚合函数,比如:sum(…)、 max(…)、min(…)、avg(…)等.
Sort Functions: 数据排序函数, 比如 :rank(…)、row_number(…)等.
Analytics Functions: 统计和比较函数, 比如:lead(…)、lag(…)、 first_value(…)等.
OVER ([PARTITION BY <…>] [ORDER BY <….>] 其中包括以下可选项:
PARTITION BY 表示将数据先按 字段 进行分区
ORDER BY 表示将各个分区内的数据按 排序字段 进行排序
c1jWq8
window_expression 用于确定窗边界:
名词 | 含义 |
---|---|
preceding | 往前 |
following | 往后 |
current row | 当前行 |
unbounded | 起点 |
unbounded preceding | 从前面的起点 |
unbounded following | 到后面的终点 |
窗口边界使用详解
- 如果不指定 PARTITION BY,则不对数据进行分区,换句话说,所有数据看作同一个分区;
- 如果不指定 ORDER BY,则不对各分区做排序,通常用于那些顺序无关的窗口函数,例如 SUM()
- 如果不指定窗口子句,则默认采用以下的窗口定义:
a、若不指定 ORDER BY,默认使用分区内所有行 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
b、若指定了 ORDER BY,默认使用分区内第一行到当前值 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
窗口函数的计算过程(语法中每个部分都是可选的)
- 按窗口定义,将所有输入数据分区、再排序(如果需要的话)
- 对每一行数据,计算它的窗口范围
- 将窗口内的行集合输入窗口函数,计算结果填入当前行
数据准备
1 | -- 创建表 |
窗口聚合函数有哪些?
窗口函数 | 返回类型 | 函数功能说明 |
---|---|---|
AVG() | 参数类型为DECIMAL的返回类型为DECIMAL,其他为DOUBLE | AVG 窗口函数返回输入表达式值的平均值,忽略 NULL 值。 |
COUNT() | BIGINT | COUNT 窗口函数计算输入行数。 COUNT(*) 计算目标表中的所有行,包括Null值;COUNT(expression) 计算特定列或表达式中具有非 NULL 值的行数。 |
MAX() | 与传参类型一致 | MAX窗口函数返回表达式在所有输入值中的最大值,忽略 NULL 值。 |
MIN() | 与传参类型一致 | MIN窗口函数返回表达式在所有输入值中的最小值,忽略 NULL 值。 |
SUM() | 针对传参类型为DECIMAL的,返回类型一致;除此之外的浮点型为DOUBLE;传参类型为整数类型的,返回类型为BIGINT | SUM窗口函数返回所有输入值的表达式总和,忽略 NULL 值。 |
1 | select emp_name, |
hive sum窗口函数
1 | select emp_name, |
hive count窗口函数
1 | select emp_name, |
hive avg窗口函数
1 | select emp_name, |
max
1 | select emp_name, |
min
排名窗口函数
窗口函数 | 返回类型 | 函数功能说明 |
---|---|---|
ROW_NUMBER() | BIGINT | 根据具体的分组和排序,为每行数据生成一个起始值等于1的唯一序列数 |
RANK() | BIGINT | 对组中的数据进行排名,如果名次相同,则排名也相同,但是下一个名次的排名序号会出现不连续。 |
DENSE_RANK() dense是稠密的意思,可以引申记忆 | BIGINT | dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。当出现名次相同时,则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。 |
PERCENT_RANK() | DOUBLE | 计算给定行的百分比排名。可以用来计算超过了百分之多少的人;排名计算公式为:(当前行的rank值-1)/(分组内的总行数-1) |
CUME_DIST() | DOUBLE | 计算某个窗口或分区中某个值的累积分布。假定升序排序,则使用以下公式确定累积分布:小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中,x 等于 order by 子句中指定的列的当前行中的值 |
NTILE() | INT | 已排序的行划分为大小尽可能相等的指定数量的排名的组,并返回给定行所在的组的排名。如果切片不均匀,默认增加第一个切片的分布,不支持ROWS BETWEEN |
1 | select *, |
开窗排名函数
1 | select *, |
开窗函数CUME_DIST
1 | select *, |
开窗函数NTILE
值窗口函数
窗口函数 | 返回类型 | 函数功能说明 |
---|---|---|
LAG() | 与lead相反,用于统计窗口内往上第n行值。第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL. | |
LEAD() | 用于统计窗口内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL. | |
FIRST_VALUE | 取分组内排序后,截止到当前行,第一个值 | |
LAST_VALUE | 取分组内排序后,截止到当前行,最后一个值 |
注意: last_value默认的窗口是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
,表示当前行永远是最后一个值,需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
。
1 | select emp_name, dealer_id, sales, first_value(sales) over (partition by dealer_id order by sales) as dealer_low from q1_sales; |
1 | select emp_name, dealer_id, sales, `year`, last_value(sales) over (partition by emp_name order by `year`) as last_sale from emp_sales where `year` = 2013; |
开窗案例举例
如何使用开窗函数去重
1 | select * from (select *,row_number() over(partition by emp_mgr order by stat_date desc) rk from q1_sales) tmp where rk = 1; |
窗口函数去重
如何使用开窗函数进行排名
1 | select *,row_number() over(partition by dealer_id order by sales desc) rk from q1_sales; |
窗口函数排名
数仓增量数据合并
基于上述的排名和区中方法结合,可以实现数仓增量抽取的数据和历史数据合并去重。
环比
数据准备
1 | select * from temp_test12; |
需求描述
查询店铺上个月的营业额,结果字段如下:
| 月份 | 商铺 | 本月营业额 | 上月营业额|
不使用开窗函数实现方案
1 | 实现这个需求我们需要先使用row_number()over按商铺分组,按月份排序得出这样一个结果: |
lag 开窗函数实现环比
1 | SELECT month ,shop |
lag 其他用法演示
1 | SELECT month ,shop |
lead 求下月营业额
lead(col,n,default)与lag相反,统计分组内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,不填默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)。
1 | 新添一列每个商铺下个月的营业额,结果字段如下: 月份 商铺 本月营业额 下月营业额 |
first_value(col)
用于取分组内排序后,截止到当前行,第一个col的值。
1 | ELECT month |
last_value(col)
用于取分组内排序后,截止到当前行,最后一个col的值。
1 | SELECT month |
连续登录
数据准备
1 | 源数据,文件中是以,号隔开的 |
展现连续登陆两天的用户信息
1 | select |
统计连续登陆两天的用户个数
(n天就只需要把lead(date,2,-1)中的2改成n-1并且把date_sub(cast(b.date as date),2)中的2改成n-1)
1 | select |
特说说明:上文指出了连续登录2天的场景,针对其他连续登录场景,假设连续登录n天,可将lead(date,1,-1)中的1改成n-1,date_sub(cast(b.date as date),1)中的1改成n-1。
占比、同比、环比计算(lag函数,lead函数)
数据准备
1 | -- 创建表并插入数据 |
使用窗口函数实现占比
1 | SELECT |
Hive窗口函数占比结算
使用窗口函数实现环比计算
什么是环比、什么是同比?
与上年度数据对比称”同比”,与上月数据对比称”环比”。
相关公式如下:
同比增长率计算公式:(当年值-上年值)/上年值x100%
环比增长率计算公式:(当月值-上月值)/上月值x100%
1 | -- 环比增长率 |
其他案例
1 | -- 建表 |
间隔,最近两次间隔,登录间隔,出院间隔等等
1 | select |
扩展
一些优化思想
有时候,一个 SELECT 语句中包含多个窗口函数,它们的窗口定义(OVER 子句)可能相同、也可能不同。显然,对于相同的窗口,完全没必要再做一次分区和排序,我们可以将它们合并成一个 Window 算子。
那如何利用一次排序计算多个窗口函数呢?某些情况下,这是可能的。下面的例子如下:
1 | ROW_NUMBER() OVER (PARTITION BY dealer_id ORDER BY sales) AS rank, |
虽然这 2 个窗口并非完全一致,但是 AVG(sales) 不关心分区内的顺序,完全可以复用 ROW_NUMBER() 的窗口,这里提供了一种方式,尽一切可能利用能够复用的机会。
窗口函数 VS. 聚合函数
从聚合这个意义上出发,似乎窗口函数和 Group By 聚合函数都能做到同样的事情。但是,它们之间的相似点也仅限于此了!这其中的关键区别在于:
窗口函数仅仅只会将结果附加到当前的结果上,它不会对已有的行或列做任何修改。而 Group By 的做法完全不同:对于各个 Group 它仅仅会保留一行聚合结果。
有的读者可能会问,加了窗口函数之后返回结果的顺序明显发生了变化,这不算一种修改吗?因为 SQL 及关系代数都是以 multi-set 为基础定义的,结果集本身并没有顺序可言,ORDER BY 仅仅是最终呈现结果的顺序。
另一方面,从逻辑语义上说,SELECT 语句的各个部分可以看作是按以下顺序“执行”的:
窗口函数执行
注意到窗口函数的求值仅仅位于 ORDER BY 之前,而位于 SQL 的绝大部分之后。这也和窗口函数只附加、不修改的语义是呼应的,结果集在此时已经确定好了,再依次计算窗口函数。