MySQL快速生成時間次元表

MySQL快速生成時間次元表：

MySQL裡面生成一張時間次元表，用于ETL工具使用。

原文位址：http://blog.csdn.net/neweastsun/article/details/43866599

SET @d0 = "2015-01-01";

SET @d1 = "2019-12-31";

SET @date = date_sub(@d0, interval 1 day);

use test;

CREATE TABLE `time_dimension` (

  `date` date DEFAULT NULL,

  `id` int(11) NOT NULL,

  `y` smallint(6) DEFAULT NULL,

  `m` smallint(6) DEFAULT NULL,

  `d` smallint(6) DEFAULT NULL,

  `yw` smallint(6) DEFAULT NULL,

  `w` smallint(6) DEFAULT NULL,

  `q` smallint(6) DEFAULT NULL,

  `wd` smallint(6) DEFAULT NULL,

  `m_name` char(10) DEFAULT NULL,

  `wd_name` char(10) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO time_dimension 

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,

  DATE_FORMAT(@date, "%x") AS yw,

  WEEK(@date, 3) AS w,

  QUARTER(@date) AS q,

  WEEKDAY(@date) + 1 AS wd,

  MONTHNAME(@date) AS m_name,

  DAYNAME(@date) AS wd_name 

FROM

  T 

WHERE DATE_ADD(@date, INTERVAL 1 DAY) <= @d1 

ORDER BY DATE ;

神秘的表T，僅僅需要有多于你需要生成日期的記錄數即可。思路是從T表選擇多行資料，同時生成對應的日期字段。

本文轉自 lirulei90 51CTO部落格，原文連結：http://blog.51cto.com/lee90/2053143，如需轉載請自行聯系原作者