Hive开窗函数总结

最新推荐文章于 2024-08-04 14:17:30 发布

IT_心如止水

最新推荐文章于 2024-08-04 14:17:30 发布

阅读量787

点赞数 6

分类专栏：数据仓库文章标签： hive sql 大数据

本文链接：https://blog.csdn.net/qq_36893938/article/details/119387330

版权

数据仓库专栏收录该内容

20 篇文章 2 订阅

订阅专栏

随着技术的发展，各行各业产生的数据量级都越来越大，而hive sql作为大数据最好用的工具，使用的用户越来越多。但是，在日常工作中，好多同学对于hive sql的使用还仅仅停留在MySQL阶段。这可就大材小用了，要知道hive的数据处理能力要比mysql强很多，不仅仅是数量大，功能上也更强大。今天就给介绍一下hive的数据分析利器——开窗函数。

应用场景

在日常工作中，我们经常会遇到按照某个指标排序并增加排名；按照顺序累加某个指标；计算前一名与后一名的差距等问题。遇到这种问题，如果用MySQL解决，就需要使用自定义用户变量并进行自关联或者做笛卡尔积（例如：https://blog.csdn.net/qq_36893938/article/details/119250330），这种东西想想就让人头大。但是，如果使用hive的话，开窗函数就刚好解决了这个问题，第一次用hive开窗函数的我，大呼流弊！

定义

开窗函数用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

开窗函数主要分为三类：聚合计算类、排序类和取值类（详见后面部分）

参数详解

格式：函数名 over(partition by col1 order by col2)

函数名 over()
-- 直接进行函数计算

函数名 over(partiton by col1)
-- 按照col1进行分组后，进行函数计算

函数名 )
-- 按照col1分组并按照col2进行排序，截止到当前行的数据进行函数计算

函数名 over(partition by col1 order by col2 ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4,

-- 按照col1分组并按照col2进行排序，取当前行往前3行到往后1行作为窗口进行函数计算

具体参数释义：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点
–其他AVG，MIN，MAX，和SUM用法一样

聚合计算类

sum/count/avg，配合partition by 和 order by可以实现累计求和，计算，求平均

排序类

排序类的函数主要分为以下三类：

rank：并列后一名名次=并列名次+并列人数

dense_rank：并列后一名名次=并列名次+1

row_number：序号没有相同的

SELECT subject, score,
RANK() OVER(PARTITION BY subject ORDER BY score desc) AS rank1,
DENSE_RANK() OVER(PARTITION BY subject ORDER BY score desc) AS d_rank2,
ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS rank3 
FROM test1