MySQL基础知识:生成给定时间段内的年、月、日数据
后续会用SQL
对书店的订单数据按天、月、年进行统计分析。 主要思路是通过定时任务来生成每天、月和年的统计数据,并存储到对应的summary表中。
在具体统计之前,先构建:天、月、年的维度(dimention)表,以便执行统计任务。
虚构书店数据库的dump脚本:Github
操作系统环境为MacOS Catalina
, MySQL版本为: 8.0.13 MySQL Community Server - GPL
。
Dimention表
USE mysql_practice;
DROP TABLE IF EXISTS `report_month_dimention`;
CREATE TABLE `report_month_dimention` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`current_month` datetime DEFAULT NULL, -- 存储每月的第一天,方便后续日期区间比较;如:2020-01-01
`pre_month` datetime DEFAULT NULL,
`next_month` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
DROP TABLE IF EXISTS `report_day_dimention`;
CREATE TABLE `report_day_dimention` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`current_day` datetime DEFAULT NULL,
`pre_day` datetime DEFAULT NULL,
`next_day` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
DROP TABLE IF EXISTS `report_year_dimention`;
CREATE TABLE `report_year_dimention` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`current_year` datetime DEFAULT NULL, -- 只存每年的第一天,如:2019-01-01, 2020-01-01
`pre_year` datetime DEFAULT NULL,
`next_year` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
);
生成年、月、日dimention的Store Procedure
DROP PROCEDURE IF EXISTS sp_generate_dimention;
DELIMITER $$
CREATE PROCEDURE sp_generate_dimention(
IN startDay datetime,
IN endDay datetime
)
BEGIN
declare startIndex datetime default startDay;
declare endIndex datetime default endDay;
declare preIndex datetime;
declare nextIndex datetime;
-- Generate months
--
truncate table report_month_dimention;
WHILE startIndex <= endIndex DO
set preIndex = date_add(startIndex, interval -1 month);
set nextIndex = date_add(startIndex, interval 1 month);
INSERT INTO report_month_dimention(current_month, pre_month, next_month)
VALUES(startIndex, preIndex, nextIndex);
set startIndex = nextIndex;
END WHILE;
-- Generate years
-- reset variables
set startIndex = startDay;
set endIndex = endDay;
truncate table report_day_dimention;
WHILE startIndex <= endIndex DO
set preIndex = date_add(startIndex, interval -1 day);
set nextIndex = date_add(startIndex, interval 1 day);
INSERT INTO report_day_dimention(current_day, pre_day, next_day)
VALUES(startIndex, preIndex, nextIndex);
set startIndex = nextIndex;
END WHILE;
-- Generate days
-- reset variables
set startIndex = startDay;
set endIndex = endDay;
truncate table report_year_dimention;
WHILE startIndex <= endIndex DO
set preIndex = date_add(startIndex, interval -1 year);
set nextIndex = date_add(startIndex, interval 1 year);
INSERT INTO report_year_dimention(current_year, pre_year, next_year)
VALUES(startIndex, preIndex, nextIndex);
set startIndex = nextIndex;
END WHILE;
END $$
DELIMITER ;
sp调用:
call sp_generate_dimention('2018-01-01', '2030-01-01');
数据结果示例: