基于hivesql的日期维度表

CREATE TABLE dim_bsc_date_df (
  `date_id` STRING COMMENT '日期(yyyymmdd) ',
  `datestr` STRING COMMENT '日期(yyyy-mm-dd) ',
  `date_name` STRING COMMENT '日期名称中文 ',
  `weekid` STRING COMMENT '周(0-6,周日~周六) ',
  `week_cn_name` STRING COMMENT '周_名称_中文 ',
  `week_en_name` STRING COMMENT '周_名称_英文 ',
  `week_en_nm` STRING COMMENT '周_名称_英文缩写 ',
  `yearmonthid` STRING COMMENT '月份id(yyyymm) ',
  `yearmonthstr` STRING COMMENT '月份(yyyy-mm) ',
  `monthid` STRING COMMENT '月份id（1-12） ',
  `monthstr` STRING COMMENT '月份 ',
  `month_cn_name` STRING COMMENT '月份名称_中文 ',
  `month_en_name` STRING COMMENT '月份名称_英文 ',
  `month_en_nm` STRING COMMENT '月份名称_简写_英文 ',
  `quarterid` STRING COMMENT '季度id（1-4） ',
  `quarterstr` STRING COMMENT '季度名称 ',
  `quarter_cn_name` STRING COMMENT '季度名称_中文 ',
  `quarter_en_name` STRING COMMENT '季度名称_英文 ',
  `quarter_cn_nm` STRING COMMENT '季度名称_简写中文 ',
  `quarter_en_nm` STRING COMMENT '季度名称_简写英文 ',
  `yearid` STRING COMMENT '年份id ',
  `year_cn_name` STRING COMMENT '年份名称_中文 ',
  `year_en_name` STRING COMMENT '年份名称_英文 ',
  `month_start_date` STRING COMMENT '当月1号(yyyy-mm-dd) ',
  `month_end_date` STRING COMMENT '当月最后日期(yyyy-mm-dd) ',
  `month_timespan` STRING COMMENT '月跨天数 ',
  `week_of_year` STRING COMMENT '当年第几周 ',
  `workday_flag` STRING COMMENT '是否工作日(周一至周五Y，否则:N) ',
  `weekend_flag` STRING COMMENT '是否周末(周六和周日Y,否则:N) ',
  `week_start_date` STRING COMMENT '当周起始日期 ',
  `week_end_date` STRING COMMENT '当周结束日期 ',
  `year_timespan` STRING COMMENT '年跨天数 '
) COMMENT '日期维表'
PARTITIONED BY (`ds` STRING COMMENT 'string') STORED AS parquet 

INSERT OVERWRITE TABLE dim_bsc_date_df PARTITION(ds = '${bdp.system.bizdate}')
select  date_id 
,datestr 
,concat(yearid,'年',monthid,'月',substr(datestr,9,2),'日')       as date_name
,weekid 
,case weekid when 0 then '星期日'
when 1 then '星期一'
when 2 then '星期二'
when 3 then '星期三'
when 4 then '星期四'
when 5 then '星期五'
when 6 then '星期六'
end                                                             as week_cn_name
,case weekid when 0 then 'Sunday'
when 1 then 'Monday'
when 2 then 'Tuesday'
when 3 then 'Wednesday'
when 4 then 'Thurday'
when 5 then 'Friday'
when 6 then 'Saturday'
end                                                             as week_en_name
,case weekid when 0 then 'Sun'
when 1 then 'Mon'
when 2 then 'Tues'
when 3 then 'Wed'
when 4 then 'Thur'
when 5 then 'Fri'
when 6 then 'Sat'
end                                                             as week_en_nm
,substr(date_id,1,6)                                            as yearmonthid
,substr(datestr,1,7)                                            as yearmonthstr
,monthid
,concat(yearid,'年',monthid,'月')                                as monthstr
,concat(monthid,'月')                                            as month_cn_name
,case monthid when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end                                                             as month_en_name
,case monthid when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sept'
when 10 then 'Oct'
when 11 then 'Nov'
when 12 then 'Dec'
end                                                             as month_en_nm
,quarterid
,concat(yearid,quarterid)                                       as quarterstr
,concat(yearid,'年第',quarterid,'季度')                           as quarter_cn_name
,concat(yearid,'Q',quarterid) as quarter_en_name
,case quarterid when 1 then '第一季度'
when 2 then '第二季度'
when 3 then '第三季度'
when 4 then '第四季度'
end                                                             as quarter_cn_nm
,concat('Q',quarterid)                                          as quarter_en_nm
,yearid 
,concat(yearid,'年')                                            as year_cn_name
,yearid                                                         as year_en_name
,month_start_date
,month_end_date
,datediff(month_end_date,month_start_date) + 1                  as month_timespan
,week_of_year 
,case when weekid in (1,2,3,4,5) then 'Y' else 'N' end          as workday_flag
,case when weekid in (0,6) then 'Y' else 'N' end                as weekend_flag
--,date_sub(datestr,weekid-1) as week_start_date  --周起始日期
,case when weekid = 0 then date_sub(datestr,6) 
else date_sub(datestr,weekid-1)
end                                                             as week_start_date   --周起始日期
--,date_add(datestr,6-weekid+1) as week_end_date  --周结束日期
,case when weekid = 0 then datestr
else date_add(datestr,6-weekid+1)
end                                                             as week_end_date  --周结束日期
,datediff(concat(yearid, '-12-31'), concat(yearid, '-01-01')) + 1 as year_timespan
from (
  select  from_unixtime(unix_timestamp(datestr,'yyyy-MM-dd'),'yyyyMMdd')  as date_id
  ,datestr as datestr
  ,pmod(datediff(datestr, '2012-01-01'), 7)                       as weekid
  ,concat(substr(datestr,1,4),substr(datestr,6,2))                as yearmonthid
  ,substr(datestr,1,7) as yearmonthstr
  ,cast(substr(datestr,6,2) as int)                               as monthid
  ,case when cast(substr(datestr,6,2) as int) <= 3 then 1 
  when cast(substr(datestr,6,2) as int) <= 6 then 2
  when cast(substr(datestr,6,2) as int) <= 9 then 3
  when cast(substr(datestr,6,2) as int) <= 12 then 4
  end                                                             as quarterid
  ,substr(datestr,1,4)                                            as yearid 
  ,date_sub(datestr,dayofmonth(datestr)-1)                        as month_start_date  --当月第一天
  ,last_day(date_sub(datestr,dayofmonth(datestr)-1))              as month_end_date --当月最后一天
  ,weekofyear(datestr)                                            as week_of_year
  from (
          select 
          date_add('1980-01-01',t0.pos)   as datestr
          from (
                  select posexplode(
                          split(
                                  repeat('o', datediff(from_unixtime(unix_timestamp('20301231','yyyymmdd'),'yyyy-mm-dd'), '1980-01-01')), 'o'
                                )
                        ) 
                ) t0
        ) t 
) t 

-- 判断当月的日期是属于当月的第几周
,if(weekid = 1 ,datestr,DATE_ADD(cast(datestr AS date) ,-cast((weekid - 1)as BIGINT ) ))                         AS week_start_date
	       ,if(weekid = 1,DATE_ADD(cast(datestr AS date) ,6 ),DATE_ADD(cast(datestr AS date) ,cast((7-weekid)as BIGINT ) )) AS week_end_date
	       
dense_rank() OVER (PARTITION BY substr(week_start_date,1,7) ORDER BY  week_start_date ) AS mon_week      
;