Hive中常用的窗口函数

窗口函数简介与总结

在构建数据仓库或者进行数据分析时,难免会使用Hive中的窗口函数完成一些较复杂的ETL工作,现对Hive中常用的窗口函数进行总结与记录。

在Hive中,一般会使用窗口函数生成新的一列,使用样式为:

select 字段1, 字段2,..., window_function() over(partition by 字段1 order by 字段2 )  as 新字段1 from 表1 where ......

常用的窗口函数有:

1.  窗口排序函数:ROW_NUMBER()、RANK()、DENSE_RANK();

基于上文中的使用样式,窗口排序函数是先根据字段1进行分组,组内根据字段2进行升序排序或降序排序,然后新生成的一列为排序的序号,三个排序函数之间有一些区别如下。

  • ROW_NUMBER()函数,生成的排序序号从1开始,按照顺序,生成分组内记录的序号,不存在相同序号。
  • RANK()函数,生成的排序序号从1开始,字段2数值相同的行,序号相同,且会在排序序号中留下空位。
  • DENSE_RANK()函数,生成的排序序号从1开始,对于字段2数值相同的行,序号相同,但在排序序号中不会留下空位。

2. 窗口聚合函数:SUM()、MIN()、MAX()、AVG();

窗口聚合函数可以搭配窗口子句使用, 如:

select 字段1,字段2, ..., sum(字段3) over( partition by 字段1 order by 字段2 rows between unbounded preceding and current row) as 新字段1 
// 新字段1含义为计算当前分组中,从第一行到当前行字段3的和。

其中ROWS BETWEEN叫窗口子句, 其中CURRENT ROW表示当前行,UNBOUNDED PRECEDING表示前面的起点,UNBOUNDED FOLLOWING表示后面的终点,当没有写窗口子句时,语义为从第一行到当前行。

MIN()、MAX()、AVG()的用法与SUM()一样。 

3. LAG()

LAG(col,n,DEFAULT) 用于取窗口内列col往前第N行的值,其中第一个参数为列名,第二个参数表示往前取n行(可选,默认为1),第三个参数为默认值(当列col往前第n行为NULL时,取该默认值,如不指定,则为NULL)。

4. LEAD()

LEAD(col, n, DEFAULT) 与函数LAG类似,不过取数方向相反,用于去窗口内列col往后第n行的值,其中第一个参数为列名,第二个参数表示往后取n行(可选,默认为1),第三个参数为默认值(即当列col往后取n行为NULL时,取该默认值,若不指定,则为NULL)。

5. FIRST_VALUE()

用法:FIRST_VALUE(col), 表示分组排序后,截止到当前行排名第一的那一行对应的列col的值。

6. LAST_VALUE()

用法:LAST_VALUE(col),表示分组排序后,截止到当前行排名最后一名的那一行对应的列col的值。

7. NTILE()

NTILE函数的作用是,对数据分组排序后,将有序的数据平均分配到指定数量的桶中,即给每一行一个桶的编号。用法:NTILE(n) OVER(PARTITION BY 字段1 ORDER BY 字段2)。基于此,可以在查询结果的外面再嵌套一层查询语句,这样就可以过滤出排序之后的前N分之几或者后N分之几的数据,达到数据抽样的效果。

2. 使用窗口函数的CASE

 待补充。

posted @   任风扬  阅读(3380)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· TypeScript + Deepseek 打造卜卦网站:技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配,妙~啊~
点击右上角即可分享
微信分享提示