在odps中使用sql创建日期维表
create table if not exists dim_date (
date_id string comment '日期(yyyymmdd)'
,datestr string comment '日期(yyyy-mm-dd)'
,date_name string comment '日期名称中文'
,weekid int comment '周(0-6,周一~周日)'
,week_cn_name string comment '周_名称_中文'
,yearmonthid string comment '月份id(yyyymm)'
,yearmonthstr string comment '月份(yyyy-mm)'
,monthid int comment '月份id（1-12）'
,monthstr string comment '月份'
,month_cn_name string comment '月份名称_中文'
,quarterid int comment '季度id（1-4）'
,quarterstr string comment '季度名称'
,quarter_cn_name string comment '季度名称_中文'
,quarter_cn_nm string comment '季度名称_简写中文'
,yearid int comment '年份id'
,year_cn_name string comment '年份名称_中文'
,month_start_date string comment '当月1号(yyyy-mm-dd)'
,month_end_date string comment '当月最后日期(yyyy-mm-dd)'
,month_timespan int comment '月跨天数'
,week_of_year int comment '当年第几周'
,workday_flag string comment '是否工作日(周一至周五y，否则:n)'
,weekend_flag string comment '是否周末(周六和周日y,否则:n)'
)comment '日期维度表';
insert overwrite table dim_date
select date_id
,datestr
,concat(
yearid
,'年'
,monthid
,'月'
,substr(datestr,9,2)
,'日'
) as date_name
,weekid
,case when weekid = 0 then '星期一'
when weekid = 1 then '星期二'
when weekid = 2 then '星期三'
when weekid = 3 then '星期四'
when weekid = 4 then '星期五'
when weekid = 5 then '星期六'
when weekid = 6 then '星期日'
end as week_cn_name
,yearmonthid
,yearmonthstr
,monthid
,concat(yearid,'年',monthid,'月') as monthstr
,concat(monthid,'月') as month_cn_name
,quarterid
,concat(yearid,quarterid) as quarterstr
,concat(yearid,'年第',quarterid,'季度') as quarter_cn_name
,case quarterid when 1 then '第一季度'
when 2 then '第二季度'
when 3 then '第三季度'
when 4 then '第四季度'
end as quarter_cn_nm
,yearid
,concat(yearid,'年') as year_cn_name
,month_start_date
,month_end_date
,datediff(
to_date(month_end_date,'yyyy-mm-dd')
,to_date(month_start_date,'yyyy-mm-dd')
,'dd'
) + 1 as month_timespan
,week_of_year
,case when weekid in (0,1,2,3,4) then 'y'
else 'n'
end as workday_flag
,case when weekid in (5,6) then 'n'
else 'n'
end as weekend_flag
from (
select to_char(datestr,'yyyymmdd') as date_id
,substr(datestr,1,10) as datestr
,weekday(datestr) as weekid
,to_char(datestr,'yyyymm') 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
,concat(substr(datestr,1,7),'-01') as month_start_date --当月第一天
,substr(lastday(datestr),1,10) as month_end_date --当月最后一天
,weekofyear(datestr) as week_of_year
from (
select dateadd(to_date('20140101','yyyymmdd'),t0.pos,'dd') as datestr
from (
select posexplode(
split(
repeat(
'o '
,datediff(
to_date('20991231','yyyymmdd')
,to_date('20140101','yyyymmdd')
,'dd'
)
)
,'o'
)
)
) t0
) t1
) t2
;
- 参考文档
- https://www.cnblogs.com/Jims2016/p/15070624.html
- https://www.cnblogs.com/DavonC/p/13868389.html
