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
;
基于hivesql的日期维度表
最新推荐文章于 2024-09-03 15:13:12 发布