mysql建立维度表_mysql生成日期维度表sql

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('2018-01-01',365);

方法二：

DROP PROCEDURE IF EXISTS getAllDate;

CREATE PROCEDURE getAllDate()

BEGIN

DECLARE count int default 0;

DECLARE startDay DATE DEFAULT date(now());

DECLARE endDay DATE DEFAULT DATE(NOW());

-- 定义异常处理方式 http://www.cnblogs.com/cookiehu/p/4994278.html

DECLARE out_status VARCHAR(200) DEFAULT 'OK';

DECLARE CONTINUE HANDLER

FOR 1062

SET out_status='Duplicate Entry';

-- 异常处理方式完毕

WHILE count<3650 DO

-- 插入后一百年时间

INSERT INTO `dim_date`(`id`, `key`, `year`, `quarter`, `month`, `week`, `weekofyear`, `day`, `dayofyear`) VALUES (cast(DATE_FORMAT(startDay,'%Y%m%d') as UNSIGNED), startDay, YEAR(startDay), QUARTER(startDay), MONTH(startDay), WEEKDAY(startDay)+1, week(startDay,1), DAY(startDay), DAYOFYEAR(startDay));

set count = count +1;

set startDay = DATE_ADD(DATE(now()),INTERVAL count DAY);

SET endDay = DATE_SUB(DATE(NOW()),INTERVAL count DAY);

-- 插入前一百年时间

INSERT INTO `dim_date`(`id`, `key`, `year`, `quarter`, `month`, `week`, `weekofyear`, `day`, `dayofyear`) VALUES (cast(DATE_FORMAT(endDay,'%Y%m%d') as UNSIGNED), endDay, YEAR(endDay), QUARTER(endDay), MONTH(endDay), WEEKDAY(endDay)+1, week(endDay,1), DAY(endDay), DAYOFYEAR(endDay));

END WHILE;

END;

call getAllDate();