-- 建表
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;
create table if not exists crisps_dim.dim_date_info ( id bigint comment '主键id', load_time string comment '加载时间' ) comment '用于计算时间(勿删!勿删!)' row format delimited fields terminated by '\001' stored as parquet;
insert overwrite table crisps_dim.dim_date_info select row_number()over(order by id) as id, from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as load_time from crisps_dim.dim_date_info;
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;
标签:comment,tmp,cur,建立,k1,时间,维表,time,date From: https://www.cnblogs.com/ererciwan/p/16998538.html