This paper mainly records the problem of generating time dimension in the process of BI and data analysis. It is also a basic example of mysql stored procedure
Including: basic syntax of stored procedure, variable definition, while loop, exception handling
The following stored procedure generates a date record of 3650 days before and after the current date
sql is as follows:
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 'week', `weekofyear` int(2) NOT NULL COMMENT 'Week of the year', `day` int(2) NOT NULL COMMENT 'day', `dayofyear` int(3) NOT NULL COMMENT 'Day of the year', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Stored procedure:
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()); -- Define exception handling 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'; -- Exception handling completed 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 ;Call stored procedure
-- TRUNCATE table dim_date; call getAllDate();