HIVE时间范围生成时间点

1、建立时间维度表

将时间范围与时间维度表生成笛卡尔积

SELECT /*+MAPJOIN(b)*/
        a.start_date
        ,a.end_date
        ,b.time
FROM    (
            SELECT  '2020-01-01 10:00:00' AS start_date
                    ,'2020-01-02 11:12:00' AS end_date
        ) a
JOIN    (
            SELECT  time
            FROM    dim_time
        ) b
WHERE   b.time BETWEEN a.start_date
a.end_date

           

结果如下

问题

①维度表需要生成的比较精细

需要生成到秒级别，才可将时间范围准确展开，假设只有一张时间维度表且开始时间由2020-01-01 00:00:00开始，戒指戴2020-12-31 23:59:59记录条数共31536000条记录，假设建立日期、小时、分钟、秒维度4张单独的维表，每张表中最多60条记录，但是4张表关联起来也是31536000条记录，还增加了使用成本

②关联膨胀问题

上述sql采用的是笛卡尔积的方式，假设a表共有1万条记录，b表也有1条记录，笛卡尔积之后则变成1亿条记录，数据膨胀过渡，会使任务运行变慢

2、行转列union时间维度

将时间范围行转列，并加上同组标记，与时间维度表union，最后使用

row_number() over(order by create_time asc)

问题

这样可以避免数据的过渡关联导致的膨胀问题，但是往往业务中不仅仅只有时间字段还存在其他业务字段，union过程维度表需要冗余很多业务字段,且找到时间范围对应的边界也比较复杂

3、LATERAL VIEW posexplode函数

列转行函数LATERAL VIEW explode比较常见，

LATERAL VIEW posexplode函数是将列转行并加上行号

目前已经有了一组时间范围，假设要转成时间范围行以1小时递增形式，目前已经知道两者的时间差值，现在要做的是把时间差值转变成可拆分的列值，并将起始时间与行号相加即可得到按一小时增加的时间范围

SELECT  start_date
        ,end_date
        ,DATEADD(start_date,index,'hour') time 
        ,index
FROM    (
            SELECT  start_date
                    ,end_date
                    ,REGEXP_REPLACE(
                        space(DATEDIFF(end_date,start_date,'hour')
                        )
                        ,' '
                        ,',1'
                    ) flag
            FROM    (
                        SELECT  '2020-01-01 10:00:00' AS start_date
                                ,'2020-01-01 13:00:00' AS end_date
                    ) a
        ) m
LATERAL VIEW posexplode( split(flag, ',' ) ) t AS index, flags
;
           

结果

问题

当起始时间与终止时间相差不到1小时，生成列转行的字段则没有值

SELECT  start_date
        ,end_date
        ,DATEADD(start_date,index,'hour') time 
        ,index
FROM    (
            SELECT  start_date
                    ,end_date
                    ,REGEXP_REPLACE(
                        space(DATEDIFF(end_date,start_date,'hour')
                        )
                        ,' '
                        ,',1'
                    ) flag
            FROM    (
                        SELECT  '2020-01-01 10:00:00' AS start_date
                                ,'2020-01-01 10:30:00' AS end_date
                    ) a
        ) m
LATERAL VIEW posexplode( split(flag, ',' ) ) t AS index, flags
;
           

结果

因此将代码改进如下

SELECT  start_date
        ,end_date
        ,DATEADD(start_date,if(DATEDIFF(end_date,start_date,'hour') < 1,0,index),'hour') time 
        ,index 
FROM    (
            SELECT  start_date
                    ,end_date
                    ,REGEXP_REPLACE(
                        space(
                            IF(
                                DATEDIFF(end_date,start_date,'hour') < 1
                                ,1
                                ,DATEDIFF(end_date,start_date,'hour')
                            )
                        )
                        ,' '
                        ,',1'
                    ) flag
            FROM    (
                        SELECT  '2020-01-01 10:00:00' AS start_date
                                ,'2020-01-01 10:30:00' AS end_date
                    ) a
        ) m
LATERAL VIEW posexplode( split(flag, ',' ) ) t AS index, flags
;
           

结果

再将输出结果去重即可

本文中示例为了方便以小时为递增，要实现1秒一段，上述代码可以用秒单位进行替换