mysql中生成时间维度的存储过程(存储过程示例)
本文主要记录在BI和数据分析过程中碰到的生成时间维度的问题,另外也是一个mysql的存储过程基础示例
包含:存储过程基本语法、变量定义、while循环、异常处理
以下存储过程生成了以当前日期为基准前后3650天的日期记录
sql如下:
创建表:
CREATE TABLE `dim_date` ( `id` int(8) NOT NULL DEFAULT '0', `key` date NOT NULL DEFAULT '0000-00-00', `year` int(4) NOT NULL, `quarter` int(1) NOT NULL, `month` int(2) NOT NULL, `week` int(1) NOT NULL COMMENT '星期', `weekofyear` int(2) NOT NULL COMMENT '一年中的第几周', `day` int(2) NOT NULL COMMENT '日', `dayofyear` int(3) NOT NULL COMMENT '一年总的第几天', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
存储过程:
delimiter // 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// delimiter ;
调用存储过程
-- TRUNCATE table dim_date; call getAllDate();
相关推荐
Omega 2020-08-16
zjyzz 2020-08-16
minggehenhao 2020-07-28
zhaojp0 2020-06-27
zjuwangleicn 2020-06-25
wenjieyatou 2020-06-17
liaomingwu 2020-06-16
hungzz 2020-06-16
muzirigel 2020-06-14
gsmfan 2020-06-14
ncomoon 2020-06-14
xiaobaif 2020-06-13
Rain 2020-06-12
hanshangzhi 2020-06-12
talkingDB 2020-06-12
IBMRational 2020-06-09
snowguy 2020-06-01