数据仓库 - hive - 日期维表，日，周，月份，年份，节假日，工作日

最新推荐文章于 2024-08-05 21:31:41 发布

Doki067

最新推荐文章于 2024-08-05 21:31:41 发布

阅读量841

点赞数 2

文章标签：数据仓库 hive hadoop

本文链接：https://blog.csdn.net/DolphinF/article/details/131548129

版权

数仓中常用的日期维表，每年生成一次，供大家参考。

建表DDL

CREATE TABLE IF NOT EXISTS dim_date
(
    calendar_date                     DATETIME COMMENT '日期',
    calendar_date_str                 STRING   COMMENT '日期',
    calendar_year                     STRING   COMMENT '自然年',
    calendar_year_cn                  STRING   COMMENT '自然年,中文名',
    fiscal_year                       STRING   COMMENT '财务年',
    fiscal_year_cn                    STRING   COMMENT '财务年,中文名',
    calendar_quarter                  STRING   COMMENT '自然季度',
    calendar_quarter_cn               STRING   COMMENT '自然季度，中文名',
    fiscal_quarter                    STRING   COMMENT '财务季度',
    fiscal_quarter_cn                 STRING   COMMENT '财务季度,中文名',
    calendar_month                    STRING   COMMENT '月份',
    calendar_month_en                 STRING   COMMENT '月份',
    calendar_month_cn                 STRING   COMMENT '月份',
    calendar_week                     STRING   COMMENT '周数',
    calendar_weekday_en               STRING   COMMENT '工作日历',
    calendar_weekday_cn               STRING   COMMENT '工作日历',
    is_last_day_of_month              BIGINT   COMMENT '是否是每月最后一天',
    is_leap_year                      BIGINT   COMMENT '是否是闰年',
    is_cn_holiday                     BIGINT   COMMENT '是中国节假日 ',
    is_weekend                        BIGINT   COMMENT '是否周末 ',
    is_cn_workday                     BIGINT   COMMENT '是否是工作日',
    year_week                         STRING   COMMENT '自然年-周',
    is_cn_first_workday_follow_holidy BIGINT   COMMENT '是否是节假日后的第一个工作日，0-不是，1-是'
) 
COMMENT 'dim层 通用日期维度表,粒度-天'
PARTITIONED BY
(
    p_year                            STRING   COMMENT '年分区(yyyy)'
);

生成维表脚本

WITH 
v_gen_calendar AS 
(
    SELECT 
         t1_date.calendar_date       
        ,TO_CHAR(t1_date.calendar_date,'yyyymmdd') AS calendar_date_str   
        ,calendar_year       
        ,CONCAT(t1_date.calendar_year,'年') AS calendar_year_cn    
        ,fiscal_year         
        ,CONCAT(t1_date.fiscal_year,'财年') AS fiscal_year_cn      
        ,t1_date.calendar_quarter    
        ,CASE 
            WHEN t1_date.calendar_quarter = 'Q1' THEN '第一季度' 
            WHEN t1_date.calendar_quarter = 'Q2' THEN '第二季度' 
            WHEN t1_date.calendar_quarter = 'Q3' THEN '第三季度' 
            WHEN t1_date.calendar_quarter = 'Q4' THEN '第四季度' 
         END AS calendar_quarter_cn 
        ,t1_date.fiscal_quarter     
        ,CASE 
            WHEN t1_date.fiscal_quarter = 'Q1' THEN '第一财季' 
            WHEN t1_date.fiscal_quarter = 'Q2' THEN '第二财季' 
            WHEN t1_date.fiscal_quarter = 'Q3' THEN '第三财季' 
            WHEN t1_date.fiscal_quarter = 'Q4' THEN '第四财季' 
         END AS fiscal_quarter_cn  
        ,t1_date.calendar_month      
        ,t1_date.calendar_month_en   
        ,t1_date.calendar_month_cn   
        ,t1_date.calendar_week       
        ,t1_date.calendar_weekday_en 
        ,t1_date.calendar_weekday_cn 
        ,t1_date.is_last_day_of_month
        ,t1_date.is_leap_year 
        ,0 AS is_cn_holiday
        ,t1_date.is_weekend
        ,0 AS is_cn_workday
        ,t1_date.year_week
        ,0 AS is_cn_first_workday_follow_holidy
        ,TO_CHAR(t1_date.calendar_date,'yyyy') AS p_year 

    FROM 
    (
        SELECT 
             t2_date.calendar_date      
            ,YEAR(t2_date.calendar_date) AS calendar_year 
            ,CASE 
                WHEN t2_date.calendar_date < DATEADD(TO_DATE('${current_year}','yyyy'),3,'mm') THEN TO_CHAR(calendar_date,'yyyy') 
                WHEN t2_date.calendar_date >= DATEADD(TO_DATE('${current_year}','yyyy'),3,'mm') THEN TO_CHAR(DATEADD(calendar_date,1,'yyyy'),'yyyy') 
             END AS fiscal_year            
            ,CONCAT('Q',quarter(t2_date.calendar_date)) AS calendar_quarter   
            ,CASE 
                WHEN t2_date.calendar_date < DATEADD(TO_DATE('${current_year}','yyyy'),3,'mm') THEN 'Q4'
                WHEN t2_date.calendar_date >= DATEADD(TO_DATE('${current_year}','yyyy'),3,'mm') 
                    AND t2_date.calendar_date < DATEADD(TO_DATE('${current_year}','yyyy'),6,'mm')  THEN 'Q1'
                WHEN t2_date.calendar_date >= DATEADD(TO_DATE('${current_year}','yyyy'),6,'mm') 
                    AND t2_date.calendar_date < DATEADD(TO_DATE('${current_year}','yyyy'),9,'mm')  THEN 'Q2'
                WHEN t2_date.calendar_date >= DATEADD(TO_DATE('${current_year}','yyyy'),9,'mm') 
                    AND t2_date.calendar_date < DATEADD(TO_DATE('${current_year}','yyyy'),12,'mm')  THEN 'Q3' 
             END AS fiscal_quarter
            ,MONTH(t2_date.calendar_date) AS  calendar_month   
            ,CASE 
                WHEN MONTH(t2_date.calendar_date) = 1 THEN 'January'
                WHEN MONTH(t2_date.calendar_date) = 2 THEN 'February'
                WHEN MONTH(t2_date.calendar_date) = 3 THEN 'March'
                WHEN MONTH(t2_date.calendar_date) = 4 THEN 'April'
                WHEN MONTH(t2_date.calendar_date) = 5 THEN 'May'
                WHEN MONTH(t2_date.calendar_date) = 6 THEN 'June'
                WHEN MONTH(t2_date.calendar_date) = 7 THEN 'July'
                WHEN MONTH(t2_date.calendar_date) = 8 THEN 'August'
                WHEN MONTH(t2_date.calendar_date) = 9 THEN 'September'
                WHEN MONTH(t2_date.calendar_date) = 10 THEN 'October'
                WHEN MONTH(t2_date.calendar_date) = 11 THEN 'November'
                WHEN MONTH(t2_date.calendar_date) = 12 THEN 'December'
             END AS calendar_month_en  
            ,CASE 
                WHEN MONTH(t2_date.calendar_date) = 1 THEN '一月'
                WHEN MONTH(t2_date.calendar_date) = 2 THEN '二月'
                WHEN MONTH(t2_date.calendar_date) = 3 THEN '三月'
                WHEN MONTH(t2_date.calendar_date) = 4 THEN '四月'
                WHEN MONTH(t2_date.calendar_date) = 5 THEN '五月'
                WHEN MONTH(t2_date.calendar_date) = 6 THEN '六月'
                WHEN MONTH(t2_date.calendar_date) = 7 THEN '七月'
                WHEN MONTH(t2_date.calendar_date) = 8 THEN '八月'
                WHEN MONTH(t2_date.calendar_date) = 9 THEN '九月'
                WHEN MONTH(t2_date.calendar_date) = 10 THEN '十月'
                WHEN MONTH(t2_date.calendar_date) = 11 THEN '十一月'
                WHEN MONTH(t2_date.calendar_date) = 12 THEN '十二月'
             END AS calendar_month_cn  
            ,WEEKOFYEAR(t2_date.calendar_date) AS calendar_week      
            ,CASE 
                WHEN WEEKDAY(t2_date.calendar_date) = 1 THEN 'Tuesday'
                WHEN WEEKDAY(t2_date.calendar_date) = 2 THEN 'Wednesday'
                WHEN WEEKDAY(t2_date.calendar_date) = 3 THEN 'Thursday'
                WHEN WEEKDAY(t2_date.calendar_date) = 4 THEN 'Friday'
                WHEN WEEKDAY(t2_date.calendar_date) = 5 THEN 'Saturday'
                WHEN WEEKDAY(t2_date.calendar_date) = 6 THEN 'Sunday'
                WHEN WEEKDAY(t2_date.calendar_date) = 0 THEN 'Monday'
             END AS calendar_weekday_en
            ,CASE 
                WHEN WEEKDAY(t2_date.calendar_date) = 1 THEN '星期二'
                WHEN WEEKDAY(t2_date.calendar_date) = 2 THEN '星期三'
                WHEN WEEKDAY(t2_date.calendar_date) = 3 THEN '星期四'
                WHEN WEEKDAY(t2_date.calendar_date) = 4 THEN '星期五'
                WHEN WEEKDAY(t2_date.calendar_date) = 5 THEN '星期六'
                WHEN WEEKDAY(t2_date.calendar_date) = 6 THEN '星期日'
                WHEN WEEKDAY(t2_date.calendar_date) = 0 THEN '星期一'
             END AS calendar_weekday_cn
            ,CASE 
                WHEN  LASTDAY(t2_date.calendar_date) = calendar_date  THEN 1
                ELSE 0 
             END AS is_last_day_of_month
            ,CASE 
                WHEN YEAR(t2_date.calendar_date) % 4 = 0 AND YEAR(t2_date.calendar_date) % 100 !=0   THEN 1 
                WHEN YEAR(t2_date.calendar_date) % 400 = 0 THEN 1 
                ELSE 0
             END AS is_leap_year
            ,CASE 
                WHEN WEEKDAY(t2_date.calendar_date) = 5 THEN 1 
                WHEN WEEKDAY(t2_date.calendar_date) = 6 THEN 1 
                ELSE 0
             END AS is_weekend
            ,CASE 
                WHEN TO_CHAR(t2_date.calendar_date,'mmdd') >= '0101' AND TO_CHAR(t2_date.calendar_date,'mmdd') <= '0106' 
                    THEN CONCAT(YEAR(DATEADD(t2_date.calendar_date, -1 * WEEKDAY(t2_date.calendar_date),'dd')) ,'年',WEEKOFYEAR(t2_date.calendar_date), '周' )
                ELSE CONCAT(YEAR(t2_date.calendar_date),'年',WEEKOFYEAR(t2_date.calendar_date),'周')
             END AS  year_week
        FROM 
        (
            SELECT 
                DATEADD(TO_DATE('${current_year}','yyyy'),tf.pos,'dd') AS calendar_date
            FROM (SELECT 0)  t3_tmp
            LATERAL VIEW posexplode(split(repeat('0,',DATEDIFF(TO_DATE('${next_1year}','yyyy'),TO_DATE('${current_year}','yyyy'))),',')) tf AS pos,val 
        ) t2_date
    ) t1_date
) 
,
v_special_day_cn AS 
(
    SELECT  
         t1_specal_day.special_day
        ,t1_specal_day.is_day_off
    FROM  atta_dw_marketing${atta_env}.dwd_parse_mkt_special_day  t1_specal_day
    WHERE  t1_specal_day.area_code = 'CN'
        AND YEAR(t1_specal_day.special_day) = '${current_year}' 
    GROUP BY  t1_specal_day.special_day
        ,t1_specal_day.is_day_off   
) 


-- 补充中国节假日
INSERT OVERWRITE TABLE atta_tools${atta_env}.dim_date PARTITION(p_year)
SELECT 
     t1_date.calendar_date
    ,t1_date.calendar_date_str
    ,t1_date.calendar_year
    ,t1_date.calendar_year_cn
    ,t1_date.fiscal_year
    ,t1_date.fiscal_year_cn
    ,t1_date.calendar_quarter
    ,t1_date.calendar_quarter_cn
    ,t1_date.fiscal_quarter
    ,t1_date.fiscal_quarter_cn
    ,t1_date.calendar_month
    ,t1_date.calendar_month_en
    ,t1_date.calendar_month_cn
    ,t1_date.calendar_week
    ,t1_date.calendar_weekday_en
    ,t1_date.calendar_weekday_cn
    ,t1_date.is_last_day_of_month
    ,t1_date.is_leap_year
    ,t1_date.is_cn_holiday
    ,t1_date.is_weekend
    ,t1_date.is_cn_workday
    ,t1_date.year_week
    ,CASE 
        WHEN t1_date.is_cn_workday = 1 AND t1_date.prior_is_cn_workday = 0 THEN 1
        ELSE 0 
     END AS  is_cn_first_workday_follow_holidy 
    ,t1_date.p_year
FROM 
(
    SELECT 
         t2_date.calendar_date
        ,t2_date.calendar_date_str
        ,t2_date.calendar_year
        ,t2_date.calendar_year_cn
        ,t2_date.fiscal_year
        ,t2_date.fiscal_year_cn
        ,t2_date.calendar_quarter
        ,t2_date.calendar_quarter_cn
        ,t2_date.fiscal_quarter
        ,t2_date.fiscal_quarter_cn
        ,t2_date.calendar_month
        ,t2_date.calendar_month_en
        ,t2_date.calendar_month_cn
        ,t2_date.calendar_week
        ,t2_date.calendar_weekday_en
        ,t2_date.calendar_weekday_cn
        ,t2_date.is_last_day_of_month
        ,t2_date.is_leap_year
        ,t2_date.is_cn_holiday
        ,t2_date.is_weekend
        ,t2_date.is_cn_workday
        ,t2_date.year_week
        ,LAG(t2_date.is_cn_workday,1,t2_date.is_cn_workday) OVER(PARTITION BY calendar_year ORDER BY t2_date.calendar_date ASC) AS prior_is_cn_workday 
        ,t2_date.p_year
    FROM 
    (
        SELECT 
             t3_date.calendar_date       
            ,t3_date.calendar_date_str   
            ,t3_date.calendar_year       
            ,t3_date.calendar_year_cn    
            ,t3_date.fiscal_year         
            ,t3_date.fiscal_year_cn      
            ,t3_date.calendar_quarter    
            ,t3_date.calendar_quarter_cn 
            ,t3_date.fiscal_quarter      
            ,t3_date.fiscal_quarter_cn   
            ,t3_date.calendar_month      
            ,t3_date.calendar_month_en   
            ,t3_date.calendar_month_cn   
            ,t3_date.calendar_week       
            ,t3_date.calendar_weekday_en 
            ,t3_date.calendar_weekday_cn 
            ,t3_date.is_last_day_of_month
            ,t3_date.is_leap_year
            ,CASE 
                WHEN t3_specal_day.is_day_off = 1 THEN 1 
                ELSE 0
             END AS is_cn_holiday 
            ,t3_date.is_weekend
            -- 1.如果是法定假日，肯定不是工作日
            -- 2.如果不是法定假日和法定调休日，且是周末，也肯定不是工作日
            ,CASE 
                WHEN t3_specal_day.is_day_off = 1  THEN 0  
                WHEN t3_specal_day.is_day_off IS NULL AND t3_date.is_weekend = 1  THEN 0  
                ELSE 1
             END AS is_cn_workday   
            ,t3_date.year_week  
            ,p_year
        FROM  v_gen_calendar t3_date
        LEFT JOIN v_special_day_cn  t3_specal_day
        ON t3_date.calendar_date = t3_specal_day.special_day
        WHERE t3_date.p_year = '${current_year}'
    ) t2_date
) t1_date

;

Doki067

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
数据仓库 - hive - 日期维表，日，周，月份，年份，节假日，工作日

数仓中常用的日期维表，每年生成一次，供大家参考。
复制链接

扫一扫