1 over()窗口函数
1.1 语法结构
分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
1.2 over中的三个函数具体含义
- order by:排序的意思,跟sql一样
- partition by:分区的概念,后面接字段表示跟什么分区,比如日期 partition by day
- rows between 开始位置 and 结束位置:窗口范围
PRECEDING:往前FOLLOWING:往后CURRENTROW:当前行UNBOUNDED:起点(一般结合PRECEDING,FOLLOWING使用)UNBOUNDEDPRECEDING 表示该窗口最前面的行(起点)UNBOUNDEDFOLLOWING:表示该窗口最后面的行(终点)比如说:ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW(表示从起点到当前行)ROWSBETWEEN2PRECEDINGAND1FOLLOWING(表示往前2行到往后1行)ROWSBETWEEN2PRECEDINGAND1CURRENTROW(表示往前2行到当前行)ROWSBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING(表示当前行到终点)
2 与over窗口函数一般配套使用的分析函数
2.1 聚合类
- 内容
- avg()
- sum()
- max()
- min()
- 使用:略,和sql一样
2.2 排名类
- 内容
- row_number()
- rank()
- dense_rank()
- 使用
- row_number()按照值排序时产生一个自增编号,不会重复(如:1、2、3、4、5、6)
- rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位(如:1、2、3、3、3、6)
- dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位(如:1、2、3、3、3、4)
2.3 其他类
- lag()
- lag(col,n,DEFAULT) 用于统计窗口内往上第n行值
- 参数含义:第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
- lead()
- lead(col,n,DEFAULT) 用于统计窗口内往下第n行值
- 参数含义:第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
- ntile()
- ntile(n)用于将分组数据按照顺序切分成n片,返回当前切片值
- 参数含义:参数n表示将数据分为几块,并返回n所在的那一块
- first_value()
- first_value(字段) 取分组内排序后,截止到当前行,第一个值
- 参数含义:first_value(字段),参数是字段,和分组排序搭配使用后,表示该字段的第一个值作为结果返回
- 来张图理解一下
- last_value()
- last_value(字段)取分组内排序后,截止到当前行,最后一个值
- 参数含义:last_value(字段),参数是字段,和分组排序搭配使用后,表示该字段的第最后一个值作为结果返回
3 开窗函数在拉链表上的使用案例
3.1 场景
- 场景:表a是截止前一天的全量最新拉链表,表b是今天ods层产生的新增和变化的数据
- 需求:需要产出新的拉链表维表数据,并且需要剔除前一天拉链表中过期的数据,并且放到前一天的分区的
3.2 分析
- 难点就是如何在这两张表中需要剔除的数据,即a表的6,7
- 对两张表进行union all
- 使用开窗函数rank,并降序排序,rank=1的就是需要的最新拉链表数据,rank=2就是过期的数据
3.3 实操
- 将两表union all
select
id,
name,
start_date,
end_date
from a
where dt ='9999-12-31'unionallselectselect id,
name,
start_date,
end_date
from b
where dt ='2020-06-15'
- 开窗处理
select
id,
name,
start_date,if(rk=2,date_add('2020-06-15',-1),end_date) end_date
from(select
id,
name,
start_date,
end_date
rank()over(partitionby id orderby start_date desc) rk
from(select
id,
name,
start_date,
end_date
from a
where dt ='9999-12-31'unionallselectselect id,
name,
start_date,
end_date
from b
where dt ='2020-06-15') t1
) t2
- 设置动态分区
insert overwrite table a partition(dt)select
id,
name,
start_date,if(rk=2,date_add('2020-06-15',-1),end_date) end_date
if(rk=1,'9999-12-31',date_add('2020-06-15',-1))from(select
id,
name,
start_date,
end_date
rank()over(partitionby id orderby start_date desc) rk
from(select
id,
name,
start_date,
end_date
from a
unionallselectselect id,
name,
start_date,
end_date
from b
) t1
) t2
4 总结
我感觉是,hive中方便和分析函数一起查看原表数据的一种工具,特别是在聚合分析的时候
本文转载自: https://blog.csdn.net/m0_46507516/article/details/127269371
版权归原作者 :Concerto 所有, 如有侵权,请联系我们删除。
版权归原作者 :Concerto 所有, 如有侵权,请联系我们删除。