DORIS数据库生成日期维度表

建表语句

DROP TABLE IF EXISTS XXX.DIM_DATE;
CREATE TABLE XXX.DIM_DATE (
`id` INT NOT NULL COMMENT "ID",
`month_value` INT NOT NULL COMMENT "所属月份",
`date_key` VARCHAR(10) COMMENT "日期主键",
`date_value` VARCHAR(8) COMMENT "日期值",
`day_of_month` INT COMMENT "本月第几天",
`month_cn` VARCHAR(10) COMMENT "月份(中文)",
`year_month` VARCHAR(6) COMMENT "所属年月",
`month_first` DATETIME COMMENT "本月第一天",
`month_end` DATETIME COMMENT "本月最后一天",
`day_of_week` INT COMMENT "本周第几天",
`day_of_week_cn` VARCHAR(10) COMMENT "本周第几天(中文)",
`week_cn` VARCHAR(10) COMMENT "星期几(中文)",
`week_en` VARCHAR(10) COMMENT "星期几(英文)",
`week_num` INT COMMENT "本年第几周",
`week_num_cn` VARCHAR(10) COMMENT "本年第几周(中文)",
`year_week` VARCHAR(6) COMMENT "年周",
`year_week_cn` VARCHAR(25) COMMENT "年周(中文)",
`season` INT COMMENT "季度",
`season_cn` VARCHAR(10) COMMENT "季度(中文)",
`season_of_year` VARCHAR(15) COMMENT "年季",
`season_of_year_cn` VARCHAR(25) COMMENT "年季(中文)",
`half_year` VARCHAR(20) COMMENT "本年半年",
`half_year_cn` VARCHAR(20) COMMENT "本年半年(中文)",
`year_half_year_cn` VARCHAR(20) COMMENT "年度半年(中文)",
`year_first` DATETIME COMMENT "本年第一天",
`year_end` DATETIME COMMENT "本年最后一天",
`year_num` INT COMMENT "年份",
`year_num_cn` VARCHAR(10) COMMENT "年份(中文)"
)
ENGINE=OLAP
UNIQUE KEY(`id`,`month_value`)
DISTRIBUTED BY HASH(`month_value`) BUCKETS 12
PROPERTIES(
"replication_allocation" = "tag.location.default: 3"
);

数据生成语句

insert into XXX.DIM_DATE (id, date_key, date_value, day_of_month, month_value, month_cn, year_month, month_first, month_end, day_of_week, day_of_week_cn, week_cn, week_en, week_num, week_num_cn, year_week, year_week_cn, season, season_cn, season_of_year, season_of_year_cn, half_year, half_year_cn, year_half_year_cn, year_first, year_end, year_num, year_num_cn)

select row_number() over ( order by dim.date_value) id,

date_format(dim.date_value,'%Y-%m-%d') date_key,

date_format(dim.date_value,'%Y%m%d') date_value,

day(dim.date_value) day_of_month,

month(dim.date_value) month_value,
CASE
			DATE_FORMAT( STR_TO_DATE( dim.date_value, '%Y-%m-%d %H:%i:%s' ), '%c' ) 
			WHEN 1 THEN
			'一月' 
			WHEN 2 THEN
			'二月' 
			WHEN 3 THEN
			'三月' 
			WHEN 4 THEN
			'四月' 
			WHEN 5 THEN
			'五月' 
			WHEN 6 THEN
			'六月' 
			WHEN 7 THEN
			'七月' 
			WHEN 8 THEN
			'八月' 
			WHEN 9 THEN
			'九月' 
			WHEN 10 THEN
			'十月' 
			WHEN 11 THEN
			'十一月' 
			WHEN 12 THEN
			'十二月' 
		END AS `month_cn`,

date_format(dim.date_value,'%Y%m') year_month,

date_trunc(dim.date_value,'month') month_first,

last_day(dim.date_value) month_end,

weekday(dim.date_value) + 1 day_of_week,

CONCAT('第', weekday(dim.date_value) + 1, '天') day_of_week_cn,

case

when weekday(dim.date_value) = 0 then '星期一'

when weekday(dim.date_value) = 1 then '星期二'

when weekday(dim.date_value) = 2 then '星期三'

when weekday(dim.date_value) = 3 then '星期四'

when weekday(dim.date_value) = 4 then '星期五'

when weekday(dim.date_value) = 5 then '星期六'

when weekday(dim.date_value) = 6 then '星期日'

end week_cn,

case

when weekday(dim.date_value) = 0 then 'Mon'

when weekday(dim.date_value) = 1 then 'Tues'

when weekday(dim.date_value) = 2 then 'Wed'

when weekday(dim.date_value) = 3 then 'Thur'

when weekday(dim.date_value) = 4 then 'Fri'

when weekday(dim.date_value) = 5 then 'Sat'

when weekday(dim.date_value) = 6 then 'Sun'

end week_en,

substr(yearweek(dim.date_value,1),-2) week_num,


CONCAT('第', week(dim.date_value,1), '周') week_num_cn,

yearweek(dim.date_value,1) year_week,

CONCAT(year(dim.date_value),'年第', week(dim.date_value,1), '周') year_week_cn,

quarter(dim.date_value) season,
CASE QUARTER ( STR_TO_DATE( dim.date_value, '%Y-%m-%d %H:%i:%s' ) ) 
			WHEN 1 THEN
			'一季度' 
			WHEN 2 THEN
			'二季度' 
			WHEN 3 THEN
			'三季度' 
			WHEN 4 THEN
			'四季度' 
		END AS season_cn,

year(dim.date_value)*10 + quarter(dim.date_value) season_of_year,

CASE QUARTER ( STR_TO_DATE( dim.date_value, '%Y-%m-%d %H:%i:%s' ) ) 
			WHEN 1 THEN
			CONCAT(year(dim.date_value),'年一季度' ) 
			WHEN 2 THEN
			CONCAT(year(dim.date_value),'年二季度' ) 
			WHEN 3 THEN
			CONCAT(year(dim.date_value),'年三季度' )
			WHEN 4 THEN
			CONCAT(year(dim.date_value),'年四季度' )
		END AS season_of_year_cn,
		
if(month(dim.date_value)>6,2,1) half_year,

CASE if(month(dim.date_value)>6,2,1) 
			WHEN 1 THEN
			'上半年' 
			WHEN 2 THEN
			'下半年' 
		END AS half_year_cn,

CASE if(month(dim.date_value)>6,2,1) 
			WHEN 1 THEN
			CONCAT(year(dim.date_value),'年上半年' ) 
			WHEN 2 THEN
			CONCAT(year(dim.date_value),'年下半年' )  
		END AS year_half_year_cn,

date_trunc(dim.date_value,'year') year_first,

days_sub(date_trunc(years_sub(dim.date_value,-1),'year'),1) year_end,

year(dim.date_value) year_num,

CONCAT(year(dim.date_value),'年' )   year_num_cn

from (select adddate('1900-01-01', number) date_value from numbers("number" = '73048')) dim -- 73048