【数据库】mysql调用过程函数生成日期维表

写在前面的话

第一次使用sql中的函数部分，本文会记录sql函数的一些基础语法，如果你已经很熟练，请忽视掉

创建日期维表

使用环境 Navicat 12

创建存储日期的表（日期，日期的不同的表达形式，这一年的第几周，一年的第几个月，一年的第几个季度，上半年或下半年）

CREATE TABLE `dim_date` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`day_id` VARCHAR ( 10 ) DEFAULT NULL,
`day_short_desc` VARCHAR ( 10 ) DEFAULT NULL,
`day_long_desc` VARCHAR ( 50 ) DEFAULT NULL,
`dayofweek_id` VARCHAR ( 10 ) DEFAULT NULL,
`dayofweek_desc` VARCHAR ( 20 ) DEFAULT NULL,
`weekofyear_id` VARCHAR ( 20 ) DEFAULT NULL,
`weekofyear_long_desc` VARCHAR ( 50 ) DEFAULT NULL,
`monthofyear_id` VARCHAR ( 20 ) DEFAULT NULL,
`monthofyear_long_desc` VARCHAR ( 50 ) DEFAULT NULL,
`quarter_id` VARCHAR ( 20 ) DEFAULT NULL,
`quarter_long_desc` VARCHAR ( 20 ) DEFAULT NULL,
`half_a_year_id` VARCHAR ( 20 ) DEFAULT NULL,
`half_a_year_long_desc` VARCHAR ( 50 ) DEFAULT NULL,
`year_id` VARCHAR ( 20 ) DEFAULT NULL,
`year_long_desc` VARCHAR ( 50 ) DEFAULT NULL,
PRIMARY KEY ( `ID` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

创建过程函数

 

这里我更新了代码，新代码需要创建两个in模式参数，并且规定了输入参数的格式

修改了每年交接处对第几个星期的算法，并将创建表写入函数，可直接调用函数完成建表和存储过程函数的操作，简化操作

代码如下 

CREATE DEFINER=`dbo`@`%` PROCEDURE `create_date_dimension`(IN `start_date_in` varchar(20),IN `end_date` varchar(20))
BEGIN

/*
创建日期维度表 ,调用create_date_dimension('xxxx-xx-xx','xxxx-xx-xx');
规定：每周的星期一为每周的第一天；每年的第一个星期一为第一周
*/

	declare i int;
	declare date_count int;
	declare mondayth int;
	declare yearth int;
	declare f int;
	declare start_date varchar(20);
	set f=0;
	set mondayth=52;
	set yearth=YEAR(start_date_in)-1;
	set i=0;
	set start_date=concat(yearth+1,'-01-01');
	set date_count=datediff(end_date,start_date);  
	CREATE TABLE IF NOT EXISTS `dim_date` (
	`day_id` char ( 10 ) DEFAULT NULL,
	`day_short_desc` date DEFAULT NULL,
	`day_long_desc` char ( 50 ) DEFAULT NULL,
	`dayofweek_id` tinyint  DEFAULT NULL,
	`dayofweek_desc` char ( 20 ) DEFAULT NULL,
	`weekofyear_id` char ( 20 ) DEFAULT NULL,
	`weekofyear_long_desc` char ( 50 ) DEFAULT NULL,
	`monthofyear_id` char ( 20 ) DEFAULT NULL,
	`monthofyear_long_desc` char ( 50 ) DEFAULT NULL,
	`quarter_id` char ( 20 ) DEFAULT NULL,
	`quarter_long_desc` char ( 20 ) DEFAULT NULL,
	`half_a_year_id` char ( 20 ) DEFAULT NULL,
	`half_a_year_long_desc` char ( 50 ) DEFAULT NULL,
	`year_id` char ( 20 ) DEFAULT NULL,
	`year_long_desc` char ( 50 ) DEFAULT NULL);
	while i<=date_count do
		DELETE from dim_date where day_short_desc=start_date;
		IF DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'))=2 
		THEN
			IF month(start_date)=1 and day(start_date)<7 THEN
				set yearth=yearth+1;
				set mondayth=1;
			else set mondayth=mondayth+1;
			END IF;
		END IF;
		INSERT into dim_date(day_id,day_short_desc,day_long_desc,dayofweek_id,dayofweek_desc,weekofyear_id,weekofyear_long_desc,monthofyear_id,monthofyear_long_desc,quarter_id,quarter_long_desc,half_a_year_id,half_a_year_long_desc,year_id,year_long_desc) 
			SELECT DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m%d') 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, 
						 case DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'))  when 1 then '7' when 2 then '1' when 3 then '2' when 4 then '3' when 5 then '4' when 6 then '5' when 7 then '6' end dayofweek_id,  
             case DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'))  when 1 then '星期日' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end dayofweek_desc,  
					
						  CONCAT(yearth,mondayth) weekofyear_id,
							CONCAT(yearth,'年第',mondayth,'周') weekofyear_desc,
             DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y%m') monthofyear_id,  
             DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%m月') monthofyear_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,
						 CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),case quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')) when 1 then 'H1' when 2 then 'H1' when 3 then 'H2' when 4 then 'H2' end ) half_a_year_id,
					  CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年'),case quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')) when 1 then '上半年' when 2 then '上半年' when 3 then '下半年' when 4 then '下半年' end ) half_a_year_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;
	delete from dim_date where day_short_desc < start_date_in or day_short_desc > end_date;
END

 直接保存运行函数即可

 SQL函数语法

declare 变量名 变量类型;

declare i int;

 set 变量赋值；

set i=0;

while 条件 do ... end while

while i < date_count DO  

        ...

        ...

end while;  

case 条件 when 1 then 1 end

case DAYOFWEEK(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'))  when 1 then '星期日' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end 

常用的日期函数

点这个