HIve窗口函数和分析函数

最新推荐文章于 2023-01-27 15:57:53 发布

Hugh_Guan

最新推荐文章于 2023-01-27 15:57:53 发布

阅读量679

点赞数

分类专栏：技术工具知识文章标签： hive 大数据

本文链接：https://blog.csdn.net/Hugh_Guan/article/details/108302180

版权

本文详细介绍了Hive的窗口函数和分析函数，包括应用场景、FIRST_VALUE、LAST_VALUE、LEAD、LAG等，以及OVER从句的使用。同时，讲解了ROW_NUMBER、RANK、DENSE_RANK等排名函数，NTILE、CUME_DIST和PERCENT_RANK等分析函数，并通过应用测试展示了它们的实际操作和结果。

摘要由CSDN通过智能技术生成

一、窗口函数与分析函数

1.1 应用场景

应用场景：

用于分区排序
动态Group By
排名计算 Top N
累计计算
层次查询

1.2 窗口函数

FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
LAST_VALUE：取分组内排序后，截止到当前行，最后一个值
LEAD(col,n,DEFAULT) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG(col,n,DEFAULT) ：与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

1.3 OVER从句

使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
使用PARTITION BY语句，使用一个或者多个原始数据类型的列
使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者排序列
使用窗口规范，窗口规范支持以下格式：

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

注意：

当ORDER BY后面缺少窗口从句条件，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
当ORDER BY和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
OVER从句支持以下函数，但是并不支持和窗口一起使用它们。
（1）Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank.
（2）Lead 和 Lag 函数.

1.4 分析函数

ROW_NUMBER() ：从1开始，按照顺序，生成分组内记录的序列,比如，按照pv降序排列，生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。
RANK()：生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK() ：生成数据项在分组中的排名，排名相等会在名次中不会留下空位
CUME_DIST ：小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例
PERCENT_RANK ：分组内当前行的RANK值-1/分组内总行数-1
NTILE(n) ：用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。
注意：1. NTILE不支持ROWS BETWEEN，比如

NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW);

Hive2.1.0及以后支持Distinct
在聚合函数（SUM, COUNT and AVG）中，支持distinct，但是在ORDER BY 或者窗口限制不支持。

COUNT(DISTINCT a) OVER (PARTITION BY c)

Hive 2.2.0中在使用ORDER BY和窗口限制时支持distinct

COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

Hive2.1.0及以后支持在OVER从句中支持聚合函数

SELECT rank() OVER (ORDER BY sum(b))
FROM T
GROUP BY a;

二、应用测试

2.1 测试数据

测试数据集：

2.2 COUNT、SUM、MIN、MAX、AVG

代码演示：

select 
    user_id,
    user_type,
    sales,
    --默认为从起点到当前行
    sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc) AS sales_1,
    --从起点到当前行，结果与sales_1不同。
    sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sales_2,
    --当前行+往前3行
    sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS sales_3,
    --当前行+往前3行+往后1行
    sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS sales_4,
    --当前行+往后所有行  
    sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sales_5,
    --分组内所有行
    SUM(sales) OVER(PARTITION BY user_type) AS sales_6                          
from 
    order_detail
order by 
    user_type,
    sales,
    user_id

结果展示：

+----------+------------+--------+----------+----------+----------+----------+----------+----------+--+
| user_id  | user_type  | sales  | sales_1  | sales_2  | sales_3  | sales_4  | sales_5  | sales_6  |
+-----