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

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

• 建表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)'
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

• 生成维表脚本

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

;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248