阿里云环境下纯sql构建日期维度表

 

     一个完整的数据仓库，日期维度表是必不可少的，对于数据的多维度处理是一个很好的东西。现在的工作环境是阿里云，在这个环境下用纯sql构建了一个日期维度表，主要字段包含以下结果，后续农历日期将通过udf实现，再补充进来

数据生产方案先借用调度系统的补数据方案补指定日期内的数据：

主要字段包含以下数据：

create table dim_date_data(
	`dt` 			bigint comment '数字日期yyyymmdd类型',
	`dt_before_1` 	string comment '1天前-yyyymmdd类型',
	`dt_before_7` 	string comment '7天前-yyyymmdd类型',
	`date_name` 	string comment '公历日期（yyyy-mm-dd）',
	`date_name_cn` 	string comment 'yyyy年-mm月-dd日',
	`business_date` datetime comment '日期时间（精确到秒时间）',
	`current_day` 	bigint comment '当月的日期（dd）',
	`month_id` 		string comment '月id',
	`month_name` 	string comment '当前年月（yyyy-mm）',
	`month_name_cn` 		string comment '当前年月全称（X年X月）',
	`month_name_short` 		string comment '当前月份（mm）',
	`month_name_short_cn` 	string comment '当前月份全称（X月）',
	`days_in_month` 		bigint comment '当前月有多少天',
	`first_of_month` 		string comment '当前月第一天（yyyymmdd）',
	`last_month_id` 		string comment '上个月id（yyyymm）',
	`month_end` 			string comment '当前月最后一天（yyyymmdd）',
	`quarter_id` 			string comment '当前季度id',
	`quarter_name` 			string comment '带年的季度（YYYY-Q）',
	`quarter_name_cn` 		string comment '带年的季度中式表示法（X年X季度）',
	`quarter_name_short` 	bigint comment '不带年的季度（Q：1~4）',
	`quarter_name_short_cn` string comment '不带年的季度中式表示法（X季度）',
	`year_name` 			bigint comment '当前年份（YYYY）',
	`year_name_cn` 			string comment '当前年份中式表示法（X年）',
	`month_week_begin` 		datetime comment '当前日期是当前月的第几周的第一天',
	`month_week_end` 		datetime comment '当前日期是当前月的第几周的最后一天',
	`half_year` 			string comment '半年周期',
	`week_of_year` 			bigint comment '当日是本年度的第几周',
	`day_period` 			string comment '天周期(旬)',
	`leap_year` 			string comment '平年还是闰年'
	`weekend_flag1`			string comment '星期标识',
	`weekend_flag2`			string comment '周末标识'
) comment '日期维度表';

 数据写入如下：

set odps.sql.type.system.odps2 = true;
select 	dt																										--日
		,to_char(dateadd(to_date(${bdp.system.bizdate},'yyyymmdd'), -1, 'dd'),'yyyymmdd') as dt_before_1		--1日前
		,to_char(dateadd(to_date(${bdp.system.bizdate},'yyyymmdd'), -7, 'dd'),'yyyymmdd') as dt_before_7		--7日前
		,to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd') as date_name							--公历日期（yyyy-mm-dd）
		,to_char(to_date(dt,'yyyymmdd'),'yyyy年mm月dd日') as date_name_cn					--日期全称（X年X月X日）
		,to_date(dt,'yyyymmdd') as business_date											--日期时间（精确到秒时间）
		,day(to_date(dt,'yyyymmdd')) as current_day											--当月的日期（dd）
		,substr(dt,1,6) as month_id															--月id
		,to_char(to_date(dt,'yyyymmdd'),'yyyy-mm') as month_name							--当前年月（yyyy-mm）
		,to_char(to_date(dt,'yyyymmdd'),'yyyy年mm月') as month_name_cn						--当前年月全称（X年X月）
		,to_char(to_date(dt,'yyyymmdd'),'mm') as month_name_short							--当前月份（mm）
		,to_char(to_date(dt,'yyyymmdd'),'mm月') as month_name_short_cn						--当前月份全称（X月）
		,day(last_day(to_date(dt,'yyyymmdd'))) as days_in_month								--当前月有多少天
		,to_char(to_date(dt,'yyyymmdd'),'yyyymm01') as first_of_month						--当前月第一天（yyyymmdd）
		,add_months(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd'),-1) as last_month_id		--上个月id（yyyymm）
		,to_char(cast(concat(last_day(to_date(dt,'yyyymmdd')),' 00:00:00') as datetime),'yyyymmdd') as month_end											--当前月最后一天（yyyymmdd）
		,concat(year(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')),quarter(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd'))) as quarter_id						--当前季度id
		,concat(year(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')),'-',quarter(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd'))) as quarter_name				--带年的季度（YYYY-Q）
		,concat(year(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')),'年',quarter(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')),'季度') as quarter_name_cn	--带年的季度中式表示法（X年X季度）
		,quarter(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')) as quarter_name_short																		--不带年的季度（Q：1~4）
		,concat(quarter(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')),'季度') as quarter_name_short_cn														--不带年的季度中式表示法（X季度）
		,year(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')) as year_name																					--当前年份（YYYY）
		,concat(year(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')),'年') as year_name_cn																	--当前年份中式表示法（X年）
		,dateadd(to_date(dt,'yyyymmdd'),weekday(to_date(dt,'yyyymmdd')) * -1,'dd') as month_week_begin			--当前日期是当前月的第几周的第一天
		,dateadd(to_date(dt,'yyyymmdd'),6 - weekday(to_date(dt,'yyyymmdd')),'dd') as month_week_end				--当前日期是当前月的第几周的最后一天
		,concat(year(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')),if(substr(dt,4,2) between 1 and 6,'上半年','下半年')) as half_year						--半年周期
		,weekofyear(to_date(dt,'yyyymmdd')) as week_of_year
		,case when floor(day(to_date(dt,'yyyymmdd')) / 10) between 1 and 10 then '上旬' 
			  when floor(day(to_date(dt,'yyyymmdd')) / 10) between 11 and 20 then '中旬' 
		 else '下旬' end as day_period																														--上中下旬
		,if(year(to_char(to_date(dt,'yyyymmdd'),'yyyy-mm-dd')) % 4 = 0,'闰年','') as leap_year																--平年还是闰年
from	(
			select	${bdp.system.bizdate} as dt
		) t
;
	





	
select	case when datediff(to_date(dt,'yyyymmdd'),'1920-01-04 00:00:00','dd') % 7 = 1 then '星期一'
			  when datediff(to_date(dt,'yyyymmdd'),'1920-01-04 00:00:00','dd') % 7 = 2 then '星期二'
			  when datediff(to_date(dt,'yyyymmdd'),'1920-01-04 00:00:00','dd') % 7 = 3 then '星期三'
			  when datediff(to_date(dt,'yyyymmdd'),'1920-01-04 00:00:00','dd') % 7 = 4 then '星期四'
			  when datediff(to_date(dt,'yyyymmdd'),'1920-01-04 00:00:00','dd') % 7 = 5 then '星期五'
			  when datediff(to_date(dt,'yyyymmdd'),'1920-01-04 00:00:00','dd') % 7 = 6 then '星期六'
			  else '星期日' end as weekend_flag1																			--星期标识
		,case when datediff(to_date(dt,'yyyymmdd'),'1920-01-04 00:00:00','dd') % 7 in (1,2,3,4,5) then '工作日'
			  else '休息日' end as weekend_flag2																			--周末标识
from	(
			select	${bdp.system.bizdate} as dt
		) t