[hive] posexplode生成从去年一月一号，到本月的月时间表

最新推荐文章于 2024-07-09 23:16:04 发布

胖胖学编程

最新推荐文章于 2024-07-09 23:16:04 发布

阅读量226

点赞数 3

文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/qq_35896718/article/details/139963023

版权

生成从去年一月一号，到本月的月时间表

posexplode用法：

lateral view 表别名 as 序号列名,数组中的元素的名

1、生成序列


        SELECT 
        time_stamp_fist_day_of_last_year,--去年第一天的时间戳
        num
        from
        (
            SELECT
            split(repeat_o,',') o_array,
            time_stamp_fist_day_of_last_year
            from
            (
                SELECT
                concat(repeat('o,',sub_momth_num),'o') repeat_o,--因为posexplode序号从0开始所以多拼一个
                time_stamp_fist_day_of_last_year
                from
                (
                    SELECT 
                    datediff(time_stamp_yestoday,time_stamp_fist_day_of_last_year,'MM')sub_momth_num,
                    time_stamp_fist_day_of_last_year
                    from
                    (
                        select
                        to_date(${bizdate},'yyyyMMdd') time_stamp_yestoday,--昨天的时间戳
                        to_date(concat(cast(SUBSTR(${bizdate},1,4) as int )-1,'0101'),'yyyyMMdd') time_stamp_fist_day_of_last_year --去年第一天的时间戳
                    )t1
                )t2
            )t3
        )t4 LATERAL view posexplode(o_array) t5 as num,val
        ;

2、结果


SELECT
date_format(new_date,'yyyy-MM') year_month
from
(
    SELECT 
    dateadd(time_stamp_fist_day_of_last_year,num,'MM') new_date
    from
    (
        SELECT 
        time_stamp_fist_day_of_last_year,--去年第一天的时间戳
        num
        from
        (
            SELECT
            split(repeat_o,',') o_array,
            time_stamp_fist_day_of_last_year
            from
            (
                SELECT
                concat(repeat('o,',sub_momth_num),'o') repeat_o,--因为posexplode序号从0开始所以多拼一个
                time_stamp_fist_day_of_last_year
                from
                (
                    SELECT 
                    datediff(time_stamp_yestoday,time_stamp_fist_day_of_last_year,'MM')sub_momth_num,
                    time_stamp_fist_day_of_last_year
                    from
                    (
                        select
                        to_date(${bizdate},'yyyyMMdd') time_stamp_yestoday,--昨天的时间戳
                        to_date(concat(cast(SUBSTR(${bizdate},1,4) as int )-1,'0101'),'yyyyMMdd') time_stamp_fist_day_of_last_year --去年第一天的时间戳
                    )t1
                )t2
            )t3
        )t4 LATERAL view posexplode(o_array) t5 as num,val
    )t6
)t7
;