生成mysql日期维表

骑猪看日落 2022-12-16 09:15 167阅读 0赞

CREATE TABLE \`dim\_day\` (  
  \`DAY\_ID\` varchar(10) NOT NULL,  
  \`DAY\_SHORT\_DESC\` 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\_DESC\` 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    
(DAY\_ID,DAY\_SHORT\_DESC,DAY\_LONG\_DESC,WEEK\_ID,WEEK\_LONG\_DESC,MONTH\_ID,MONTH\_SHORT\_DESC,MONTH\_LONG\_DESC,QUARTER\_ID,QUARTER\_LONG\_DESC,YEAR\_ID,YEAR\_LONG\_DESC)    
SELECT    
REPLACE(start\_date,'-','') DAY\_ID,    
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\_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'),'%Y-%m') MONTH\_SHORT\_DESC,    
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'),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('2020-01-01',365);