-- 建表
create table if not exists crisps_dim.dim_com_date_info
(
cur_date date comment '自然日期',
yes_date string comment '昨日',
tom_date string comment '明日',
cur_year string comment '年',
cur_year_month string comment '年月',
cur_quarter string comment '季',
year_quarter string comment '年季',
day_of_year int comment '年天数',
day_of_month int comment '月天数',
day_of_week int comment '周天数',
cur_week int comment '当前周',
week_day string comment '星期几',
cur_mon string comment '本周一',
cur_sum string comment '本周末',
cur_last string comment '本周上期',
last_mon string comment '上周一',
last_sum string comment '上周末',
week_start_day string comment '业务周开始时间',
week_end_day string comment '业务周结束时间',
year_first_day string comment '年第一天',
quarter_first_day string comment '季第一天',
cur_mon_fstday string comment '本月第一天',
cur_mon_lstday string comment '本月最后一天',
last_mon_curday string comment '本月上期',
last_mon_fstday string comment '上月第一天',
last_mon_lstday string comment '上月最后一天',
start_date string comment '业务月开始时间',
end_date string comment '业务月结束时间',
is_weekday int comment '是否周末## 1周末， 2非周末',
is_workday int comment '是否工作日## 1工作日，2非工作日',
is_holiday int comment '是否节假日## 1工作日，2节假日',
remark string comment '备注',
load_time string comment '加载时间'
) comment '时间维度表'
row format delimited fields terminated by '\001'
stored as parquet;
1 --时间维表
2 insert overwrite table crisps_dim.dim_com_date_info
3 select tmp.cur_date,
4 tmp.yes_date,
5 tmp.tom_date,
6 tmp.cur_year,
7 tmp.cur_year_month,
8 tmp.cur_quarter,
9 tmp.year_quarter,
10 tmp.day_of_year,
11 tmp.day_of_month,
12 tmp.day_of_week,
13 tmp.cur_week,
14 tmp.week_day,
15 tmp.cur_mon,
16 tmp.cur_sum,
17 tmp.cur_last,
18 tmp.last_mon,
19 tmp.last_sum,
20 if(day_of_week >= 5, date_add(cur_mon, 4), date_sub(cur_mon, 3)) as week_start_day,
21 if(day_of_week >= 5, date_add(cur_date, 7 - (day_of_week - 4)), date_sub(cur_sum, 3)) as week_end_day,
22 tmp.year_first_day,
23 tmp.quarter_first_day,
24 tmp.cur_mon_fstday,
25 tmp.cur_mon_lstday,
26 tmp.last_mon_curday,
27 tmp.last_mon_fstday,
28 tmp.last_mon_lstday,
29 tmp.start_date,
30 tmp.end_date,
31 tmp.is_weekday,
32 tmp.is_workday,
33 tmp.is_holiday,
34 tmp.remark,
35 from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as load_time
36 from
37 (
38 select
39 k1.date_time as cur_date,
40 date_sub(k1.date_time, 1) as yes_date,
41 date_add(k1.date_time, 1) as tom_date,
42 k1.year as cur_year,
43 substr(k1.date_time, 1, 7) as cur_year_month,
44 k1.quarter as cur_quarter,
45 concat_ws('-', cast(k1.year as string),cast(k1.quarter as string)) as year_quarter,
46 datediff(k1.date_time, k1.year_first_day) + 1 as day_of_year,
47 datediff(k1.date_time, k1.cur_mon_fstday) + 1 as day_of_month,
48 datediff(k1.date_time, date_sub(k1.date_time, cast(date_format(k1.date_time, 'u') as int) - 1)) + 1 as day_of_week,
49 k1.cur_week,
50 if(k1.Sun = 0, '星期日',if(k1.Sun = 1, '星期一',if(k1.Sun = 2, '星期二',if(k1.Sun = 3, '星期三',if(k1.Sun = 4, '星期四',
51 if(k1.Sun = 5, '星期五',if(k1.Sun = 6, '星期六', ''))))))) as week_day,
52 date_sub(k1.date_time, cast(date_format(k1.date_time, 'u') as int) -1) as cur_mon,
53 date_sub(k1.date_time, cast(date_format(k1.date_time, 'u') as int) -7) as cur_sum,
54 date_add(date_sub(k1.date_time, cast(date_format(k1.date_time, 'u') as int) + 6),
55 cast(datediff(k1.date_time, date_sub(k1.date_time, cast(date_format(k1.date_time, 'u') as int) -1)) as int)) as cur_last,
56 date_sub(k1.date_time, cast(date_format(k1.date_time, 'u') as int) + 6) as last_mon,
57 date_sub(k1.date_time, cast(date_format(k1.date_time, 'u') as int) - 0) as last_sum,
58 date_sub(date_add(k1.date_time, 1 - dayofweek(k1.date_time)), 2) as week_start_day,
59 date_sub(date_add(k1.date_time, 7 - dayofweek(k1.date_time)), 2) as week_end_day,
60 k1.year_first_day,
61 k1.quarter_first_day,
62 k1.cur_mon_fstday,
63 k1.cur_mon_lstday,
64 date_add(k1.last_mon_fstday, cast(datediff(k1.date_time, k1.cur_mon_fstday) as int)) as last_mon_curday,
65 k1.last_mon_fstday,
66 k1.last_mon_lstday,
67 if(datediff(k1.date_time, k1.cur_mon_fstday) + 1 <= 14, concat(substr(last_mon_fstday, 1, 7), '-15'),
68 concat(substr(cur_mon_fstday, 1, 7), '-15')) as start_date,
69 if(datediff(k1.date_time, k1.cur_mon_fstday) + 1 <= 14,
70 date_sub(add_months(concat(substr(last_mon_fstday, 1, 7), '-15'), 1), 1),
71 date_sub(add_months(concat(substr(cur_mon_fstday, 1, 7), '-15'), 1),
72 1)) as end_date,
73 if(k1.Sun in ('0', '6'), '1', '2') as is_weekday,
74 if(k1.date_time = k2.cur_date, k2.work_type, null) as is_workday,
75 if(k1.date_time = k2.cur_date, k2.work_type, null) as is_holiday,
76 if(k1.date_time = k2.cur_date, k2.remark, null) as remark
77 from
78 (
79 select
80 k.obj_date as date_time,
81 year(k.obj_date) as year,
82 quarter(k.obj_date) as quarter,
83 month(k.obj_date) as month,
84 day(k.obj_date) as day,
85 weekofyear(k.obj_date) as cur_week,
86 pmod(datediff(k.obj_date, '2012-01-01'), 7) as Sun,
87 trunc(k.obj_date, 'YEAR') as year_first_day,
88 trunc(k.obj_date, 'Q') as quarter_first_day,
89 trunc(k.obj_date, 'MM') as cur_mon_fstday,
90 last_day(k.obj_date) as cur_mon_lstday,
91 add_months(trunc(k.obj_date, 'MM'), -1) as last_mon_fstday,
92 date_add(trunc(k.obj_date, 'MM'), -1) as last_mon_lstday
93 from
94 (
95 select
96 date_add('2020-01-01', a.rk - 1) as obj_date
97 from
98 (
99 select
100 row_number() over (order by id) as rk
101 from crisps_dim.dim_date_info
102 ) a
103 ) k
104 ) k1
105 left join
106 (
107 select
108 cur_date,
109 work_type,
110 remark
111 from
112 crisps_ods.ods_crisps_work_day t
113 group by
114 cur_date, work_type, remark
115 ) k2 on (k1.date_time = k2.cur_date)
116 where k1.date_time <= '${daydate}'
117 ) as tmp;
浙公网安备 33010602011771号