[大数据]Hive开窗函数总结

随着技术的发展，各行各业产生的数据量级都越来越大，而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

取值类

• lag / lead函数

格式：LAG/LEAD(col,n,DEFAULT) 用于统计窗口内往上第n行值。

第一个参数为列名，

第二个参数为往上第n行（可选，默认为1），

第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

• FIRST_VALUE / LAST_VALUE函数

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

LAST_VALUE 函数则相反：LAST_VALUE 取分组内排序后，截止到当前行，最后一个值。

结语

梳理不易，欢迎一键三连！！！

转载请注明出处——IT_心如止水