写在前面的话第一次使用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