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秒一段，上述代碼可以用秒機關進行替換