mysql生成时间维度表

在MySQL中生成时间维度表通常用于存储时间相关的信息，以便于在数据分析和报告中使用。时间维度表通常包含日期、月份、季度、年份等字段，并可能还包括星期几、节假日、工作日等信息。以下是一个简单的时间维度表创建示例：

CREATE TABLE `time_dimension` (
  `date_key` INT NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `day_of_week` INT NOT NULL,
  `day_of_month` INT NOT NULL,
  `day_of_year` INT NOT NULL,
  `month` INT NOT NULL,
  `month_name` VARCHAR(10) NOT NULL,
  `quarter` INT NOT NULL,
  `year` INT NOT NULL,
  PRIMARY KEY (`date_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO `time_dimension` (`date`, `day_of_week`, `day_of_month`, `day_of_year`, `month`, `month_name`, `quarter`, `year`)
VALUES ('2023-01-01', 1, 1, 1, 1, 'January', 1, 2023);
-- 其他数据插入语句...

在上述表结构中，我们创建了一个包含日期相关字段的时间维度表。`date_key`是自增的主键，`date`是日期字段，`day_of_week`表示星期几（1代表星期一，7代表星期日），`day_of_month`表示月份中的第几天，`day_of_year`表示一年中的第几天，`month`表示月份（1至12），`month_name`是月份的名称，`quarter`表示季度（1至4），`year`表示年份。

在生成这样的表时，可以使用MySQL的内置函数来填充日期相关的字段。例如：

SET @first_date = '2023-01-01';
SET @last_date = '2023-12-31';

SELECT ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY) AS `date`,
       DAYOFWEEK(ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY)) AS `day_of_week`,
       DAYOFMONTH(ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY)) AS `day_of_month`,
       DAYOFYEAR(ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY)) AS `day_of_year`,
       MONTH(ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY)) AS `month`,
       DATE_FORMAT(ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY), '%M') AS `month_name`,
       QUARTER(ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY)) AS `quarter`,
       YEAR(ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY)) AS `year`
FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n,
     (SELECT 0 nn UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) nn,
     (SELECT 0 nnn UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) nnn
WHERE ADDDATE(@first_date, INTERVAL n.n + 999 * n.nn + 99999 * n.nnn DAY) <= @last_date
ORDER BY n.n, nn.n, nnn.n;

该查询通过生成足够多的数字组合来构造出指定日期范围内的所有日期，并计算出日期相关的各个字段值。