这里写自定义目录标题
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)