【大数据之Hive】十六、Hive-HQL函数之窗口函数（开窗函数）

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 案例

数据准备：
表结构：

--建表
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);

需求及实现：

--统计每个用户截至每次下单的累积下单总额：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使用