MySQL生成日期维度表的sql语句

目录

• mysql生成日期维度表
• 1、通过存储过程生成
• 2、通过递归的公用表表达式生成
• Mysql 快速生成日期时间维度表

MySQL生成日期维度表

1、通过存储过程生成

DELIMITER $$
CREATE PROCEDURE dateDim(startDate DATE, endDate DATE)  
BEGIN  
    DECLARE currentDate DATE;  
    SET currentDate = startDate;  
    WHILE currentDate <= endDate DO  
        INSERT INTO sys_calendar (stats_day) VALUES (currentDate);  
        SET cuphprrentDate = DATE_ADD(currentDate, INTERVAL 1 DAY);  
    END WHILE;  
END $$
DELIMITER ;  
CALL dateDim('2022-01-01', '2030-12-31');

2、通过递归的公用表表达式生成

WITH RECURSIVE date_series AS (  
  SELECT '2023-01-01' AS date_value  
  UNION ALL  
  SELECT DATE_ADD(date_value, INTERVAL 1 DAY)  
  FROM date_series  
  WHERE date_value < '2023-12-31'  
)  
INSERT INTO date_dim (date_value)  
SELECT dateBnsof_value FROM date_ser编程ies;

补充：Mysql 快速生成日期时间维度表

Mysql 快速生成日期时间维度表

创建表结构

CREATE TABLE `dim_date` (
  `id` int(11) NOT NULL COMMENT '日期键 20000101',
  `date` date DEFAULT NULL COMMENT '日期 2000-01-01',
  `year` smallint(6) DEFAULT NULL COMMENT '年 2000',
  `month` smallint(6) DEFAULT NULL COMMENT '月 01',
  `day` smallint(6) DEFAULT NULL COMMENT '日 01',
 Bnsof `quarter` smallint(6) DEFAULT NULL COMMENT '当年所属季度',
  `year_of_week` smallint(6) DEFAULT NULL COMMENT '当前周所属年份',
  `week` smallint(6) DEFAULT NULL COMMENT '当年所属周',
  `day_of_week` smallint(6) DEFAULT NULL COMMENT '星期几',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

生产数据

SET @d0 = '2000-01-01';
SET @d1 = '2100-12-31';
SET @date = DAjsTE_SUB( @d0, INTERVAL 1 DAY );
INSERT INTO `dim_date`(`date`, `id`,  `year`, `month`, `day`, `quarter`, `year_of_week`, `week`, `day_of_week`) 
SELECT
@date := DATE_ADD( @date, INTERVAL 1 DAY ) AS DATE,
DATE_FORMAT( @date, '%Y%m%d' ) AS id,
YEAR ( @date ) AS Y,
MONTH ( @date ) AS m,
DAY ( @date ) AS d,
QUARTER ( @date ) AS q,
DATE_FORMAT( @date, '%x' ) AS yw,
WEEK ( @date, 3 ) AS w,
WEEKDAY( @date ) + 1 AS wd
FROM
	[某张行数比日期差大的表]
WHERE
	DATE_ADD( @date, INTERVAL 1 DAY ) <= @d1 
ORDER BY
	DATE;

到此这篇关于MySQL生成日期维度表的文章就介绍到这了,更多相关MySQL日期维度表内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！