hive窗口函数在拉链表上的运用案例

:Concerto

已于 2022-12-06 15:55:23 修改

阅读量825

点赞数 1

文章标签： hive hadoop 数据仓库开窗函数

于 2022-10-11 18:50:53 首次发布

本文链接：https://blog.csdn.net/m0_46507516/article/details/127269371

版权

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：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）
比如说：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

三者结合的案例

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'

    union all 

    select 
        select 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(partition by id order by start_date desc) rk
    from

    (
        select 
            id,
            name,
            start_date,
            end_date
        from a
	where dt = '9999-12-31'

        union all 

        select 
            select 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(partition by id order by start_date desc) rk
    from

    (
        select 
            id,
            name,
            start_date,
            end_date
        from a
        where dt = '9999-12-31'

        union all 

        select 
            select id,
            name,
            start_date,
            end_date
        from b
        where dt = '2020-06-15'

     ) t1
) t2