Hive的窗口函数详解

Hive的窗口函数详解

引用

CSDN

1.

https://blog.csdn.net/qq_39041459/article/details/145946552

Hive的窗口函数是一种强大的数据处理工具，可以对数据进行复杂的分析和计算。本文将详细介绍Hive窗口函数的使用方法和应用场景。

基本语法

窗口函数的基本语法如下：

分析函数/专用窗口函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

partition by 字段(窗口分区)

partition by用于对指定的字段进行分组，后续的统计分析操作将以每个分组为单位进行。每个分组单独作为一个窗口进行统计分析。

如果没有order by子句，整个分区将作为单个窗口帧进行处理。

rows between 开始位置 and 结束位置(窗口帧)

窗口帧用于从分区中选择指定的多条记录，供窗口函数处理。Hive提供了两种定义窗口帧的形式：ROWS和RANGE。两种类型都需要配置上界和下界。

常用的参数包括：

• n preceding：往前
• n following：往后
• current row：当前行
• unbounded：起点（一般结合preceding，following使用）

例如：

• rows between unbounded preceding and current row：表示选择分区起始记录到当前记录的所有行。
• rows between current row and unbounded following：表示当前行到终点的数据进行。
• rows between unbounded preceding and unbounded following：表示起点到终点的数据。

窗口函数

窗口函数会基于当前窗口帧的记录计算结果。Hive提供了以下窗口函数：

• NTILE(n)：用于将分组数据按照顺序切分成n片，返回当前切片值。
• ROW_NUMBER()：为帧内的每一行返回一个序数，从1开始。
• RANK()：为帧内的每一行返回一个序数，存在字段值相等的记录时，会返回相同的序数。
• DENSE_RANK()：为帧内的每一行返回一个序数，存在字段值相等的记录时，不会留下空位。
• LEAD(col, n)：返回当前记录的下n条记录的字段值。
• LAG(col, n)：返回当前记录的上n条记录的字段值。
• FIRST_VALUE(col)：返回窗口帧中第一条记录的指定字段值。
• LAST_VALUE(col)：返回窗口帧中最后一条记录的指定字段值。
• COUNT()、SUM(col)、MIN(col)：与一般的聚合操作相同。

排序窗口函数

ROW_NUMBER、RANK和DENSE_RANK函数操作

• ROW_NUMBER()：从1开始，按照顺序，生成分组内记录的序列。
• RANK()：生成数据项在分组中的排名，排名相等会在名次中留下空位。
• DENSE_RANK()：生成数据项在分组中的排名，排名相等不会留下空位。

示例SQL：

SELECT 
    order_id, 
    user_id, 
    user_name,
    order_date, 
    order_amount,
    RANK() OVER(PARTITION BY user_id ORDER BY order_amount desc) AS rnk1,
    DENSE_RANK() OVER(PARTITION BY user_id ORDER BY order_amount desc) AS rnk2,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_amount desc) AS rnk3
FROM ds_hive.ch8_t_order;

聚合窗口函数

聚合窗口函数如count、sum、avg、max、min等，默认计算第一行到当前行的数据。

示例SQL：

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 ds_hive.ch8_t_order;

执行结果：

位移窗口函数

LAG函数操作

LAG(col,n,DEFAULT)用于统计窗口内往上第n行值。参数说明：

• 第一个参数为列名
• 第二个参数为往上第n行（可选，默认为1）
• 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

LEAD函数操作

LEAD(col,n,DEFAULT)用于统计窗口内往下第n行值。参数说明：

• 第一个参数为列名
• 第二个参数为往下第n行（可选，默认为1）
• 第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

示例SQL：

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,
    lead(order_date,1,null) over(partition by user_id order by order_date) last_order_date
from ds_hive.ch8_t_order

极值窗口函数

FIRST_VALUE函数操作

取分组内排序后，截止到当前行，第一个值。

LAST_VALUE函数操作

取分组内排序后，截止到当前行，最后一个值。

示例SQL：

select
    order_id,
    user_id,
    user_name,
    order_date,
    order_amount,
    first_value(order_date) over(partition by user_id,substr(order_date,1,7) order by order_date) first_date,
    last_value(order_date) over(partition by user_id,substr(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date
from ds_hive.ch8_t_order;

分箱窗口函数

NTILE函数操作

NTILE(n)用于将分组数据按照顺序切分成n片，返回当前切片值。不支持ROWS BETWEEN，如果切片不均匀，默认增加第一个切片的分布。常用来求百分比的操作，比如求前20%，就可以分5个箱。

示例SQL：

SELECT 
    order_id, 
    user_id, 
    user_name,
    order_date, 
    order_amount,
    NTILE(2) OVER(PARTITION BY user_id ORDER BY order_amount DESC) AS rnk 
FROM ds_stu1.ch_t_order_temp2

执行结果：会先排序，然后按分桶数等分，rnk=1则取前50%数据，rnk=2则取后50%数据。