全面梳理Hive窗口函数详解
本文已参与「新人创作礼」活动,一起开启掘金创作之路。
随着技术的发展,各行各业产生的数据量级都越来越大,而hive sql作为大数据最好用的工具,使用的用户越来越多。但是,在日常工作中,好多同学对于hive sql的使用还仅仅停留在MySQL阶段。这可就大材小用了,要知道hive的数据处理能力要比mysql强很多,不仅仅是数量大,功能上也更强大。今天就给介绍一下hive的数据分析利器——开窗函数。
目录
应用场景
定义
参数详解
聚合计算类
排序类
取值类
结语
应用场景
在日常工作中,我们经常会遇到按照某个指标排序并增加排名;按照顺序累加某个指标;计算前一名与后一名的差距等问题。遇到这种问题,如果用MySQL解决,就需要使用自定义用户变量并进行自关联或者做笛卡尔积(例如:MySQL如何添加排序后如何添加序号_IT_心如止水的博客-****博客_mysql排序加序号),这种东西想想就让人头大。但是,如果使用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
subject | score | rank1 | rank2 | rank3 |
数学 | 100 | 1 | 1 | 1 |
数学 | 90 | 2 | 2 | 2 |
数学 | 90 | 2 | 2 | 3 |
数学 | 83 | 4 | 3 | 4 |
取值类
- lag / lead函数
格式:LAG/LEAD(col,n,DEFAULT) 用于统计窗口内往上第n行值。
第一个参数为列名,
第二个参数为往上第n行(可选,默认为1),
第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
- FIRST_VALUE / LAST_VALUE函数
取分组内排序后,截止到当前行,第一个值;
LAST_VALUE 函数则相反:LAST_VALUE 取分组内排序后,截止到当前行,最后一个值。
结语
梳理不易,欢迎一键三连!!!
转载请注明出处——IT_心如止水
推荐阅读
-
C函数的全面解析和知识点梳理(七)
-
全面梳理scRNA-seq | 从零开始的单细胞详解系列(差异分析篇十三)
-
全面梳理:高中语法——虚拟语气在条件状语从句中的应用详解及实战习题集锦(最新完整版)
-
全面梳理:if、if-else与if-else-if循环语句详解,一次掌握所有技巧
-
全面掌握Pandas:从头到尾学会数据融合、拼接、插入、增补与函数实战教程(Python篇 - 数据连接详解)
-
在Hive中常使用的转换函数 - 条件处理函数详解
-
全面解读GitFlow工作流程详解 - 万字深度梳理,详细到极致
-
掌握Hive的强大工具:实用且高效的窗口函数详解
-
别再满世界找啦!Hive窗口函数详解与实战指南
-
全面掌握SQL技巧系列6:理解SQL窗口函数详解