首页 > 其他分享 >时间维表的建立

时间维表的建立

时间:2022-12-22 14:12:11浏览次数:39  
标签:comment tmp cur 建立 k1 时间 维表 time date

-- 建表
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

相关文章