mysql创建时间维度的sql脚本

本文主要是介绍mysql创建时间维度的sql脚本，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

mysql创建时间维度sql脚本
其中d0是开始时间,d1是结束时间
T是万能的表,只需满足T表的记录数量大于所插入的时间行数即可.

# time span  SET @d0 = "2014-01-01";  SET @d1 = "2016-12-31";  SET @date = date_sub(@d0, interval 1 day);  # set up the time dimension table  DROP 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 dates  INSERT INTO time_dimension  SELECT @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_name  FROM T  WHERE date_add(@date, interval 1 day) <= @d1  ORDER BY date  
;  

这篇关于mysql创建时间维度的sql脚本的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---