Hive【Hive(六)窗口函数】

窗口函数(window functions)

概述

定义

        窗口函数能够为每行数据划分 一个窗口,然后对窗口范围内的数据进行计算,最后将计算结果返回给该行数据。

语法

        窗口函数的语法主要包括 窗口函数 两个部分。其中窗口用于定义计算范围,函数用于定义计算逻辑。

select order_id,
       order_date,
       amount,
       函数(amount) over (窗口范围) 别名
from order_info;

函数

        绝大多数的聚合函数都可以配合窗口函数使用,例如 max、min、sum、count、avg、以及前面学到的 collect_list、collect_set 等。

窗口

        窗口范围的定义分为两种,一种是基于的,一种是基于的。

基于行

sum(amount) over(order by 排序字段 rows between 起点 and 终点) 别名

如果起点是下面两种:

  1. unbounded preceding       第一行
  2. [num] preceding               当前行的前 num 行

则终点可以是:

  1. [num] preceding              当前行的前 num 行
  2. current row                      当前行
  3. [num] following                当前行的后 num 行
  4. unbounded following       最后一行

如果起点是:

  • current row

那么终点可以是:

  1. current row                      当前行
  2. [num] following                当前行的后 num 行
  3. unbounded following       最后一行

如果起点是:

  • [num] following

则终点可以是:

  1. [num] following                当前行的后 num 行
  2. unbounded following       最后一行

注意:

        真正进行窗口函数计算的时候,必须选定一个排序的字段(order by),因为每个窗口函数的作用范围会由于 MapReduce 切片、Shuffle 这些因素而不确定(上一行和下一行可能在不同的切片中)。

案例

 实际意义:截止当前订单的销售总额。

基于值

sum(amount) over (order by 划分窗口范围的字段 range between 起点 and 终点) 别名

除了 over 关键字换成了 range ,别的没有变化。

        注意:这里的 order by 并不是指的排序字段,基于值的窗口函数并不会排序,这里的 order  by 指的是基于哪个字段在值进行窗口范围的划分。 order by 的字段可以是数值型(比如计算指定窗口范围值 num 的就必须是数值型)也可以是别的类型(这时 order by 的字段不可以通过 num 指定窗口范围 )。

        同样,基于列的窗口函数中,[num] preceding 和 [num] following 中的 num 指的是当前值 -num 和 +num。

案例

实际意义:截止当前日期的销售总额。

分区

定义窗口范围时,可以指定分区字段,每个分区单独划分一个窗口。

sum(amount) over (partition by 划分窗口范围的字段 rows between 起点 and 终点) 别名
案例

实际意义:每个用户截止到最后下单的累计下单金额。

缺省

        over() 中的三部分内容 partition by、order by 、(rows |range) between ... and ... 均可省略不写。

partition by 省略不写,代表不分区。

order by 省略不写:

  1. 如果是基于 row 的,则 order by 必须写,除非窗口范围是第一行到最后一行(rows between unbounded preceding and unbounded following)。
  2. 如果是基于 range ,order by 同样必须写。因为如果不写,就相当于没有声明根据哪个字段的值来声明窗口的范围,同样无效,此时,窗口范围是 (负无穷,正无穷)。

(rows |range) between ... and ...省略不写:

  1. 如果over()中包含 order by:则默认值为 range between unbounded preceding and current row (也就是从 小于等于当前值的第一个值 到 当前值。)
  2. 如果over()中不包含 order by:则默认值为 rows between unbounded preceding and unbounded following (也就是从 当前值 到 大于等于当前值的最后一个值。)

常用窗口函数

按照功能,常用窗口可划分为如下几类:聚合函数、跨行取值函数、排名函数。

1)聚合函数

  • max:最大值。
  • min:最小值。
  • sum:求和。
  • avg:平均值。
  • count:计数。

2)跨行取值函数

(1)lead和lag

功能:获取当前行的上/下边某行、某个字段的值。

  • lead:用来获取下边某行的值
  • lag:用来获取上边某行的值
select 
    order_id,
    user_id,
    order_date,
    amount,
    lag(order_date,1,'1970-01-01') over (partition by user_id order by order_date) last_date,
    lead(order_date,1,'9999-12-31') over (partition by user_id order by order_date) next_date
from order_info;

        注意:lead 和 lag 不能够自定义窗口范围(也就是不能添加 range between ... / rows between ...),因为 lead 和 lag 有它特定的逻辑,如果真的支持自定义窗口范围的话,上面的代码中应该默认是基于列的: range between unbounded preceding and current row ,这样的话根本无法实现取上一行或者下一行。

        其中,lag 和 lead 的3个参数分别代表:(字段名偏移量默认值)。

运行结果:

实际意义:获取用户两次下单时间,我们可以计算出两次下单时间的差值。

(2)first_value 和 last_value

功能:获取窗口内某一列的第一个值/最后一个值

select 
    order_id,
    user_id,
    order_date,
    amount,
    first_value(order_date,false) over (partition by user_id order by order_date) first_date,
    last_value(order_date,false) over (partition by user_id order by order_date) last_date
from order_info;

first_value 和 last_value 的2个参数分别代表:(字段名是否跳过 null)。

注意:这里的 first_value 和 last_value 是可以自定义窗口范围的,上面的代码中我们有 order by 字段,所以默认是基于列的  range between unbounded preceding and current row (<= 当前值,当前值)。

运行结果:

(3)排名函数

常用的排名函数: rank、dense_rank、row_number。

遇到相同名次时,rank 的结果是:1 1 3 ;而 dense_rank 是密集排名,结果是:1 1 2 ;row_number 是 1 2 3;

注意:排名函数也不支持自定义窗口范围!

select 
    st_id,
    course
    score,
    rank() over (partition by course order by score) rk,
    dense_rank() over(partition by course order by score desc) dense_rk,
    row_number() over(partition by course order by score desc) rn
from score_info;

运行结果: