日期维度码表

 创建日期维度表

CREATE TABLE `date_dimension` (
  `date_id` int(8) NOT NULL COMMENT '日期ID',
  `date_format` varchar(20) DEFAULT NULL COMMENT '日期格式化',
  `curr_day` int(8) DEFAULT NULL COMMENT '当前星期几',
  `curr_day_desc` varchar(8) DEFAULT NULL COMMENT '当前星期几',
  `week_id` int(8) DEFAULT NULL COMMENT '周ID',
  `curr_week` int(8) DEFAULT NULL COMMENT '当前第几周',
  `curr_week_desc` varchar(8) DEFAULT NULL COMMENT '当前第几周',
  `month_id` int(8) DEFAULT NULL COMMENT '月ID',
  `month_format` varchar(20) DEFAULT NULL COMMENT '月份格式化',
  `curr_month` int(8) DEFAULT NULL COMMENT '当前月份',
  `curr_month_desc` varchar(8) DEFAULT NULL COMMENT '当前月份',
  `quarter_id` int(8) DEFAULT NULL COMMENT '季度ID',
  `curr_quarter` int(8) DEFAULT NULL COMMENT '当前季度',
  `year_id` int(8) DEFAULT NULL COMMENT '年ID',
  PRIMARY KEY (`date_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='时间维表';

创建函数 

CREATE PROCEDURE f_date_dimension (
	IN start_date VARCHAR (20),
	IN date_count INT
)
BEGIN
	DECLARE
		i INT;
	DECLARE
		day_num INT;
	DECLARE
		day_str VARCHAR(4);

SET i = 0;
SET day_num = 1;
SET day_str = '';

DELETE
FROM
	date_dimension;


while i < date_count DO

select DAYOFWEEK(DATE_SUB(start_date,INTERVAL 1 day)) INTO day_num from dual;

IF day_num = 1 THEN
   SET day_str = '一';
ELSEIF day_num = 2 THEN
	  SET day_str = '二';
ELSEIF day_num = 3 THEN
	  SET day_str = '三';
ELSEIF day_num = 4 THEN
	  SET day_str = '四';
ELSEIF day_num = 5 THEN
	  SET day_str = '五';
ELSEIF day_num = 6 THEN
	  SET day_str = '六';
ELSEIF day_num = 7 THEN
	  SET day_str = '日';
END IF;

INSERT INTO date_dimension (
	date_id,
	date_format,
	curr_day,
	curr_day_desc,
	week_id,
	curr_week,
	curr_week_desc,
	month_id,
	month_format,
	curr_month,
	curr_month_desc,
	quarter_id,
	curr_quarter,
	year_id
)  SELECT
REPLACE(start_date,'-','') date_id,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') date_format,
day_num curr_day,
day_str curr_day_desc,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%x%v') week_id,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%v') curr_week,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%xW%v')	curr_week_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_format,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%m') curr_month,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%YM%m') curr_month_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,
quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')) curr_quarter,
DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y') year_id
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;

运行函数， 第一个参数为日期，第二个参数为天数。