MySQL创建日期维度表

DROP TABLE if EXISTS dim_day;

CREATE TABLE if not EXISTS `dim_day` (
  `DAY_ID` varchar(10) NOT NULL,
	`DAY_TS` BIGINT DEFAULT NULL,
  `DAY_SHORT_DESC` varchar(14) DEFAULT NULL,
	`DAY_SHORT_DESC_Oth` varchar(14) DEFAULT NULL,
  `DAY_LONG_DESC` varchar(100) DEFAULT NULL,
  `WEEK_ID` varchar(100) DEFAULT NULL,
  `WEEK_LONG_DESC` varchar(100) DEFAULT NULL,
  `MONTH_ID` varchar(100) DEFAULT NULL,
	`MONTH_SHORT` varchar(100) DEFAULT NULL,
  `MONTH_SHORT_DESC` varchar(100) DEFAULT NULL,
	`MONTH_SHORT_DESC_OTH` varchar(100) DEFAULT NULL,
  `MONTH_LONG_DESC` varchar(100) DEFAULT NULL,
  `QUARTER_ID` varchar(100) DEFAULT NULL,
  `QUARTER_LONG_DESC` varchar(100) DEFAULT NULL,
  `YEAR_ID` varchar(100) DEFAULT NULL,
  `YEAR_LONG_DESC` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`DAY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
drop procedure if exists f_dim_day;  
create procedure f_dim_day(in start_date VARCHAR(20),in date_count int)
begin  
declare i int;  
set i=0;  
DELETE from dim_day;  
while i < date_count do  
INSERT into dim_day  
SELECT  
REPLACE(start_date,'-','') DAY_ID,  
UNIX_TIMESTAMP(start_date) DAY_TS,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') DAY_SHORT_DESC,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y/%m/%d') DAY_SHORT_DESC_OTH,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年%m月%d日') DAY_LONG_DESC,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%u') WEEK_ID,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%u周') WEEK_LONG_DESC,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m') MONTH_ID,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%m') MONTH_SHOR,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m') MONTH_SHORT_DESC,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y/%m') MONTH_SHORT_DESC_OTH,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%m月') MONTH_LONG_DESC,  
CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'Q',quarter(STR_TO_DATE( start_date,'%Y-%m-%d %H:%i:%s'))) QUARTER_ID,  
CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'年第',quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')),'季度') QUARTER_LONG_DESC,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y') YEAR_ID,  
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年') YEAR_LONG_DESC  
from dual;  
set i=i+1;  
set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');  
end while;  
end;  
  
call f_dim_day('2018-01-01',36500);