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');

数据结果示例:
month dimention

posted on 2021-03-12 09:46  AI应用技术  阅读(1074)  评论(0编辑  收藏  举报

导航