mysql中產生時間維度表，mysql產生維度資料表

mysql中產生時間維度表，mysql產生維度資料表

mysql中產生時間維度表

       利用mysql常用日期函數產生時間維度表，效率最高，最簡單，無需其他的一些工具支援。產生結果樣本如：

# time spanSET @d0 = "2012-01-01";SET @d1 = "2012-12-31"; SET @date = date_sub(@d0, interval 1 day); # set up the time dimension tableDROP TABLE IF EXISTS time_dimension;CREATE TABLE `time_dimension` (  `date` date DEFAULT NULL,  `id` int NOT NULL,  `y` smallint DEFAULT NULL,  `m` smallint DEFAULT NULL,  `d` smallint DEFAULT NULL,  `yw` smallint DEFAULT NULL,  `w` smallint DEFAULT NULL,  `q` smallint DEFAULT NULL,  `wd` smallint DEFAULT NULL,  `m_name`  char(10) DEFAULT NULL,  `wd_name` char(10) DEFAULT NULL,  PRIMARY KEY (`id`)); # populate the table with datesINSERT INTO time_dimensionSELECT @date := date_add(@date, interval 1 day) as date,    # integer ID that allowsimmediate understanding    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_nameFROM TWHERE date_add(@date, interval 1 day) <= @d1ORDER BY date;

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