在数仓以及数据可视化项目中,时间维度表都是一个重中之重,以下演示下如何通过存储过程动态的生成时间维度表,自由选择生成范围。演示环境MYSQL8.0。简单演示过程,如果不想看,可直接拉到底部,完整代码(包括表的创建也写到过程中了),也有一些简单且必要的注释。
方式一:
1、直接在存储过程中定义变量,且赋值。设置三个参数BEGINDATE,ENDDATE,TEMPDATE,三个日期类型的变量,并设置变量的值。
2、定义循环主体:当日期<结束日期时,循环插入数据,数据的样式以及格式注意和表的属性相符即可,字段的增减,需要哪些维度可以直接根据MYSQL提供的时间函数提取出来。并设置变量TEMPDATE每次循环后日期+1。
3、循环结束,BEGIN主体结束,自定义结束符号//。
执行存储过程:CALL DIM_TIME_PD()
方式二:
1、使用参数形式,传递参数到存储过程吗,每次调用时,输入自己所需要的时间范围即可。三个IN类型参数,注意位置,分别是:开始日期,结束日期,TEMP日期。
2、构建BEGIN------END主体
3、结束循环,结束主体,自定义结束符,调用过程并输入参数值(要生成的时间维度范围)。
结果展示:
总结:方式一比较死板,如果每次有修改的话,需要直接修改存储过程的代码;方式二比较灵活,调用的时候再输入参数值即可,注意参数代表的是什么值。
最后附上整体代码(包括两种方式以及建表):
DROP PROCEDURE IF EXISTS DIM_TIME_PD ; -- 检查过程是否存在,存在则删除
DELIMITER // -- 自定义结束符号
CREATE DEFINER=`root`@`%` PROCEDURE `DIM_TIME_PD`( IN BEGINDATE DATE,IN ENDDATE DATE,IN TEMPDATE DATE )
BEGIN
-- 定义参数
-- DECLARE BEGINDATE DATE;
-- DECLARE ENDDATE DATE;
-- DECLARE TEMPDATE DATE;
-- 设置参数值
-- SET BEGINDATE = '2000-01-01' ;
-- SET ENDDATE = '2030-12-31' ;
-- SET TEMPDATE = BEGINDATE ;
-- 建表分隔符 ----------------------------------------------------------------------------------------
DROP TABLE IF EXISTS `dim_time`;
CREATE TABLE `dim_time` (
`TIMEID` int(0) NOT NULL AUTO_INCREMENT,
`DATE` varchar(255) NULL DEFAULT NULL,
`YEARMONTH` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`YEAR` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`MONTH` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`DAY` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`MONTHDAY` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`WEEK` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`WEEKDAY` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`WEEKDAYDESC` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`SEASON` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`SEASONDESC` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`TIMEID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
-- 建表分割符 --------------------------------------------------------------------------------------------
-- 循环插入日期 ---------------------------------------------------------------------------------------------
WHILE TEMPDATE <= ENDDATE DO
INSERT INTO test_dim.dim_time ( DATE,YEARMONTH,YEAR,MONTH,DAY,MONTHDAY,WEEK,WEEKDAY,WEEKDAYDESC,SEASON,SEASONDESC )
VALUES
(
TEMPDATE, -- 日期
LEFT(TEMPDATE,7), -- 年月
YEAR ( TEMPDATE ), -- 年份
MONTH ( TEMPDATE ), -- 月份
DAYOFYEAR(TEMPDATE), -- 年的第几天
DAYOFMONTH(TEMPDATE), -- 月的第几天
WEEKOFYEAR(TEMPDATE), -- 周
WEEKDAY(TEMPDATE), -- 周的第几天
CASE WHEN WEEKDAY(TEMPDATE)=0 THEN '星期一'
WHEN WEEKDAY(TEMPDATE)=1 THEN '星期二'
WHEN WEEKDAY(TEMPDATE)=2 THEN '星期三'
WHEN WEEKDAY(TEMPDATE)=3 THEN '星期四'
WHEN WEEKDAY(TEMPDATE)=4 THEN '星期五'
WHEN WEEKDAY(TEMPDATE)=5 THEN '星期六'
WHEN WEEKDAY(TEMPDATE)=6 THEN '星期日'
ELSE NULL END, -- 周的第几天描述
QUARTER(TEMPDATE), -- 季节
CASE WHEN QUARTER(TEMPDATE)=1 THEN '春季'
WHEN QUARTER(TEMPDATE)=2 THEN '夏季'
WHEN QUARTER(TEMPDATE)=3 THEN '秋季'
WHEN QUARTER(TEMPDATE)=4 THEN '冬季'
ELSE NULL END -- 季节描述
);
SET TEMPDATE = DATE_ADD(TEMPDATE,INTERVAL 1 DAY) ;
END WHILE;
-- 循环结束-----------------------------------------------------------------------------------------------------
END -- 主体结束
//
DELIMITER ;
-- CALL DIM_TIME_PD(); --方法一,直接在存储过程声明区(BEGIN之前,CREAT之后)定义变量以及变量赋值
CALL DIM_TIME_PD('2021-01-01','2021-12-31','2021-01-01'); -- 以输入参数的形式,调用时输入值