MYSQL 生成日期维度表

MYSQL 生成日期维度表

在报表统计中经常要用到日期维度进行汇总，所以需要提前生成通用的日期维度表。

1、创建建维度表

DROP TABLE IF EXISTS tb_rpt_dim_day;
CREATE TABLE tb_rpt_dim_day (
day_id bigint(0) NOT NULL COMMENT ‘主健’,
day_short date NOT NULL COMMENT ‘日期’,
year int(0) NOT NULL COMMENT ‘所属年份’,
week int(0) NOT NULL COMMENT ‘星期几’,
week_cn varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘星期几(中文)’,
week_en varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘星期几(英文)’,
month int(0) NOT NULL COMMENT ‘所属月份’,
month_cn varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘所属月份(中文)’,
month_en varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘所属月份(英文)’,
quarter int(0) NOT NULL COMMENT ‘所属季度’,
quarter_cn varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘所属季度(中文)’,
week_of_year int(0) NOT NULL COMMENT ‘本年第几周’,
day_of_month int(0) NOT NULL COMMENT ‘本月第几天’,
day_of_year int(0) NOT NULL COMMENT ‘本年第几天’,
PRIMARY KEY (day_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2、生成维度数据函数

CREATE DEFINER=root@% PROCEDURE f_dim_day( IN start_date VARCHAR ( 20 ), IN date_count INT )
BEGIN
DECLARE
i INT;

SET i = 0;
DELETE 
FROM
	tb_rpt_dim_day;
WHILE
		i < date_count DO
		INSERT INTO tb_rpt_dim_day SELECT REPLACE
		( start_date, '-', '' ) AS day_id,-- 日期主健
		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%Y-%m-%d' ) AS day_short,
		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%Y' ) AS `year`,
		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%w' ) AS `week`,
	CASE
			DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%w' ) 
			WHEN 1 THEN
			'星期一' 
			WHEN 2 THEN
			'星期二' 
			WHEN 3 THEN
			'星期三' 
			WHEN 4 THEN
			'星期四' 
			WHEN 5 THEN
			'星期五' 
			WHEN 6 THEN
			'星期六' 
			WHEN 0 THEN
			'星期天' 
		END AS `week_cn`,
		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%W' ) AS `week_en`,
		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%c' ) AS `month`,
	CASE
			DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%c' ) 
			WHEN 1 THEN
			'一月' 
			WHEN 2 THEN
			'二月' 
			WHEN 3 THEN
			'三月' 
			WHEN 4 THEN
			'四月' 
			WHEN 5 THEN
			'五月' 
			WHEN 6 THEN
			'六月' 
			WHEN 7 THEN
			'七月' 
			WHEN 8 THEN
			'八月' 
			WHEN 9 THEN
			'九月' 
			WHEN 10 THEN
			'十月' 
			WHEN 11 THEN
			'十一月' 
			WHEN 12 THEN
			'十二月' 
		END AS `month_cn`,
		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%b' ) AS `month_en`,
		QUARTER ( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) AS `quarter`,
	CASE
			QUARTER ( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) 
			WHEN 1 THEN
			'一季度' 
			WHEN 2 THEN
			'二季度' 
			WHEN 3 THEN
			'三季度' 
			WHEN 4 THEN
			'四季度' 
		END AS quarter_cn,
		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%u' ) AS `week_of_year`,
		DATE_FORMAT( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), '%e' ) AS `day_of_month`,
		DAYOFYEAR( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ) ) AS `day_of_year` 
	FROM
		DUAL;
	
	SET i = i + 1;
	
	SET start_date = DATE_FORMAT( date_add( STR_TO_DATE( start_date, '%Y-%m-%d %H:%i:%s' ), INTERVAL 1 DAY ), '%Y-%m-%d' );
	
END WHILE;

END

调用函数如下：call f_dim_day(‘2021-01-01’,365)