Hive开窗函数

一 概述

1 定义

窗口函数，能为每行数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果返回给改行数据。
语法：函数() over(窗口范围)
函数：绝大部分的聚合函数都可以配合窗口使用，例如:max(),imin(),sum(),avg(),count()等。
窗口：窗口范围的定义分为2种，一种基于行的，一种基于值的。
基于行的语法：rows between …and …
例如：

• sum(amount) over(rows between unbounded preceding and unbounded following ),窗口范围为从负无穷到正无穷行
• sum(amount) over(rows between 1 preceding and 2following),窗口范围为从前1行到后2行

基于值的语法：rang between …and …
例如：

• sum(amount) over(range between unbounded preceding and current row ),窗口范围为从负无穷到当前值
• sum(amount) over(range between 1 preceding and 2 following),窗口范围为比当前值小1到比当前值大2之间

2 窗口缺省

over()中的三部分内容partition by, order by, (rows|range) bewteen … and …都可以省略不写。

• partition by 省略不写，表示不分区
• order by 省略不写，表示不排序
• (rows|range) bewteen … and …省略不写，则使用其默认值，默认值如下：
  • 若over()包含order by，则默认值为 range between unbounded preceding and current row
  • 若over()不包含order by，则默认值为 rows between unbounded preceding and unbounded following

二 常用窗口函数

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

1 聚合函数

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

2 跨行取值函数

1） lead和lag函数

功能：获取当前行的上下边某行、某个字段的值
语法：

注：lead和lag函数不支持自定义窗口。

2）first_value和last_value

功能：获取窗口内某一列的第一个值和最后一个值
语法：

3 排名函数

功能：计算排名
语法：

二 实操练习

表结构

1 统计每个用户截至每次下单的累积下单总额

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;

2 统计每个用户截至每次下单的当月累积下单总额

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;

3 统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    nvl(datediff(order_date,last_order_date),0) diff
from
(
    select
        order_id,
        user_id,
        user_name,
        order_date,
        order_amount,
        lag(order_date,1,null) over(partition by user_id order by order_date) last_order_date
    from order_info
)t1

4 查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    first_value(order_date) over(partition by user_id,substring(order_date,1,7) order by order_date) first_date,
    last_value(order_date) 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;

5 为每个用户的所有下单记录按照订单金额进行排名

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;