1 概述
先定义了窗口的大小(按行来算),然后对窗口内的行的数据进行计算,再将计算结果返回给改行。
窗口函数包括窗口和函数两部分,窗口用于定义计算范围,函数用于定义计算逻辑,窗口函数只会在原来的表上增加一列结果列,不改变原来的数据。
1.1 窗口函数使用语法
--窗口函数使用语法
select
...,
函数(col_name) over (窗口范围) result_col_name -表示在窗口范围之上应用函数逻辑
from table_name;
函数:
绝大多数聚合函数都可以配合窗口使用,如max(),min(),sum(),count(),avg()等。
窗口:
分为两种,一种是基于行的,一种是基于值的。
基于行的:如每行数据的窗口为上一行到当前行:对于第1行来说,处于窗口内的行为第0行和第1行;对于第2行来说,处于窗口内的行为第1、2行。
基于值的:如要求每行数据的窗口值位于当前值-1到当前值:对于值为1的数据来说,窗口范围的值为[1-1,1]即[0,1],此时只有1行数据;对于值为2的数据来说,窗口范围的值为[2-1,2]即[1,2],有第1、2、3行数据。
1.2 窗口语法(基于行row)
基于真正运算时的行,并不是基于看到的表数据,所以需要声明一个排序字段 order by ,在真正进行窗口计算时使用哪个窗口进行排序。
--基于行
函数(col_name) over (窗口范围) result_col_name
--窗口范围(写在over后面的括号里):
order by [col_name] rows between ***1 and ***2
***1(窗口的起点):unbounded preceding / [num] preceding / current row / [num] following
***2(窗口的终点):
对于 unbounded preceding / [num] preceding 来说,终点可以写为 [num] preceding / current row / [num] following / unbounded following
对于 current row 来说,终点可以写为 current row / [num] following / unbounded following
对于 [num] following 来说,终点可以写为 [num] following / unbounded following
(1)窗口起点(可以不包含当前行):
(1)unbounded preceding:无边界的,可以理解为负无穷,表示表起点的第一行。
(2)[num] preceding:num为数字,表示当前行的前 num 行作为起点。
(3)current row:起点为当前行。
(4)[num] following:表示当前行的后 num 行作为起点。
(2)窗口终点(取决于起点方式):
(1)[num] preceding:当前行的前 num 行作为终点;当起点和终点都用 [num] preceding 时,起点的num值要比终点的大,保证终点在起点后面。
(2)current row:当前行作为终点。
(3)[num] following:当前行的后 num 行作为终点;当起点和终点都用 [num] following 时,起点的num值要小于终点的num值,保证终点在起点后面。
(4)unbounded following:相当于正无穷,表示表里的最后一行作为终点。
例如:
上述例子可以理解为统计从历史到当前的每一个订单的销售额。
1.3 窗口语法(基于值range)
基于值的 order by 表示指定哪个字段的值进行划分窗口,当使用 [num] preceding 和 [num] following 时使用的字段必须是整数类型(否则窗口划分失效) 。
--基于值
函数(col_name) over (窗口范围) result_col_name
--窗口范围(写在over后面的括号里):
order by [col_name] range between ***1 and ***2
***1(窗口的起点):unbounded preceding / [num] preceding / current row / [num] following
***2(窗口的终点):
对于 unbounded preceding / [num] preceding 来说,终点可以写为 [num] preceding / current row / [num] following / unbounded following
对于 current row 来说,终点可以写为 current row / [num] following / unbounded following
对于 [num] following 来说,终点可以写为 [num] following / unbounded following
(1)窗口起点(可以不包含当前行):
(1)unbounded preceding:无边界的,可以理解为负无穷。
(2)[num] preceding:num为数字,表示当前值 减num 的值作为起点。
(3)current row:起点为当前值。
(4)[num] following:表示当前值 加num 的值作为起点。
(2)窗口终点(取决于起点方式):
(1)[num] preceding:当前值 减num 的值作为终点;当起点和终点都用 [num] preceding 时,起点的num值要比终点的大,保证终点在起点后面。
(2)current row:当前值作为终点。
(3)[num] following:当前值 加num 的值作为终点;当起点和终点都用 [num] following 时,起点的num值要小于终点的num值,保证终点在起点后面。
(4)unbounded following:相当于正无穷。
例如:
1.4 窗口语法(分区)
在定义窗口范围时,指定分区字段,可以对每个分区进行单独划分窗口。使用 partition by 进行分区。
--分区,在分区之后再使用基于行或基于值的窗口范围定义
函数(col_name) over (窗口范围) result_col_name
--窗口范围(写在over后面的括号里):
partition by col_name order by [col_name] range/rows between ***1 and ***2
***1(窗口的起点):unbounded preceding / [num] preceding / current row / [num] following
***2(窗口的终点):
对于 unbounded preceding / [num] preceding 来说,终点可以写为 [num] preceding / current row / [num] following / unbounded following
对于 current row 来说,终点可以写为 current row / [num] following / unbounded following
对于 [num] following 来说,终点可以写为 [num] following / unbounded following
例如:
上述例子可以理解为每个用户截至到每次下单时间的历史下单总额。
1.5 窗口语法(缺省)
over() 中的三部分内容 partition by、order by、rows/range between … and … 都可以省略不写。
(1)partition by 省略表示不分区。
(2)order by 省略表示不排序;基本上使用 rows 和 range 都需要写 order by ;使用 rows 时不写 order by 即表示不声明排序,使用随机的顺序;使用 range 时不写 order by 则表示窗口划分为负无穷到正无穷。
(3)rows/range between … and … 省略则使用默认值:
(i)如果 over() 中包含 order by 则默认:range between unbounded preceding and current row (即第一行到当前行)。
(ii)如果 over() 中不包含 order by 则默认:rows between unbounded preceding and unbounded following (即第一行到最后一行)。
2 常用窗口函数
分为聚合函数、跨行取值函数、排名函数。
2.1 聚合函数
max()、min()、sum()、avg()、count()。
2.2 跨行取值函数
(1)lead和lag
功能:获取当前行的**上面(lag)或下面(lead)**的某行、某个字段的值。
lead和lag函数不支持自定义窗口,即不能用rows或range。
语法:
select
...,
lag(col_name,偏移量(数字,指上面哪行),默认值(用于取不到时)) over (partition by col_name order by col_name) result_last_col_name,
lead(col_name,偏移量(数字,指下面哪行),默认值(用于取不到时)) over (partition by col_name order by col_name) result_next_col_name
from table_name;
(2)first_value和last_value
功能:获取窗口内某一列的第一个或最后一个值。允许自定义窗口。
语法:
select
...,
first_value(col_name,true/false(是否要跳过null值)) over (partition by col_name order by col_name) result_first_col_name,
last_value(col_name,true/false(是否要跳过null值)) over (partition by col_name order by col_name) result_last_col_name
from table_name;
2.3 排名函数
常用排名函数(排名函数不支持自定义窗口):
(1)rank():考虑并列,稀疏排名,如1 1 3
(2)dense_rank():考虑并列,密集排名,如1 1 2
(3)row_number():不考虑比列,如1 2 3
语法:
select
...,
rank() over (partition by col_name order by col_name asc/desc) result_rk_col_name,
dense_rank() over (partition by col_name order by col_name asc/desc) result_dense_rk_col_name,
row_number() over (partition by col_name order by col_name asc/desc) result_rn_col_name
from table_name;
3 案例
数据准备:
表结构:文章来源:https://www.toymoban.com/news/detail-511328.html
--建表
create table order_info
(
order_id string, --订单id
user_id string, -- 用户id
user_name string, -- 用户姓名
order_date string, -- 下单日期
order_amount int -- 订单金额
)
row format delimited fields terminated by '\t';
--插入数据
insert overwrite table order_info
values ('1', '1001', '小元','2022-01-01',10),
('2', '1002', '小海', '2022-01-02',15),
('3', '1001', '小元', '2022-02-03',23),
('4', '1002', '小海', '2022-01-04',29),
('5', '1001', '小元', '2022-01-05',46),
('6', '1001', '小元', '2022-04-06',42),
('7', '1002', '小海', '2022-01-07',50),
('8', '1001', '小元', '2022-01-08',50),
('9', '1003', '小辉', '2022-04-08',62),
('10', '1003', '小辉', '2022-04-09',62),
('11', '1004', '小猛', '2022-05-10',12),
('12', '1003', '小辉', '2022-04-11',75),
('13', '1004', '小猛', '2022-06-12',80),
('14', '1003', '小辉', '2022-04-13',94);
需求及实现:文章来源地址https://www.toymoban.com/news/detail-511328.html
--统计每个用户截至每次下单的累积下单总额:order_id user_id user_name order_date order_amount sum_so_far
select
order_id,
user_id,
user_name,
order_date,
order_amount,
sum(order_amount) over (partition by user_id
order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;
--统计每个用户截至每次下单的当月累积下单总额:order_id user_id user_name order_date order_amount sum_so_far
select
order_id,
user_id,
user_name,
order_date,
order_amount,
sum(order_amount) over (partition by user_id,substring(order_date,1,7)
order by order_date rows between unbounded preceding and current row) sum_so_far
from order_info;
--统计每个用户每次下单距离上次下单相隔的天数(首次下单按0天算):order_id user_id user_name order_date order_amount diff
select
order_id,
user_id,
user_name,
order_date,
order_amount,
datediff(order_date,lag(order_date,1,order_date)) over (partition by user_id order by order_date) diff
from order_info;
select
order_id,
user_id,
user_name,
order_date,
order_amount,
datediff(order_date,last_order_date) diff
from
(
select
order_id,
user_id,
user_name,
order_date,
order_amount,
lag(order_date,1,order_date) over(partition by user_id order by order_date)last_order_date
from order_info
)t1;
--查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期:order_id user_id user_name order_date order_amount first_date last_date
select
order_id,
user_id,
user_name,
order_date,
order_amount,
first_value(order_date,false) over (partition by user_id,substring(order_date,1,7) order by order_date) first_date,
last_value(order_date,false) over (partition by user_id,substring(order_date,1,7) order by order_date
rows between unbounded preceding and unbounded following) last_date
from order_info;
--为每个用户的所有下单记录按照订单金额进行排名:order_id user_id user_name order_date order_amount rk drk rn
select
order_id,
user_id,
user_name,
order_date,
order_amount,
rank() over (partition by user_id order by order_amount desc) rk,
dense_rank() over (partition by user_id order by order_amount desc) drk,
row_number() over (partition by user_id order by order_amount desc) rn
from order_info;
--rank()、dense_rank()、row_number()可以解决分组topN问题,配合where使用
到了这里,关于【大数据之Hive】十六、Hive-HQL函数之窗口函数(开窗函数)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!