日期维度表左右
在数据仓库（Data Warehouse）中，日期维度表（Date Dimension Table）是非常重要的，因为大多数业务数据都涉及时间维度。日期维度表提供了丰富的日期信息，帮助用户进行时间相关的数据分析和报表生成。具体作用包括：
- 提供标准化的日期信息
日期维度表为每个日期提供了标准化的、多种格式的日期表示，如短日期、中日期、长日期、完整日期等。这些格式可以满足不同的业务需求，使得在数据展示时更加灵活。
- 支持多种时间粒度的分析
日期维度表提供了丰富的时间粒度信息，例如：
年、月、季度、周等字段，可以方便地进行按年、按月、按季度或按周的分析。
一年中的第几天（day_in_year）、一月中的第几天（day_in_month）、周的第一天或最后一天等字段，支持更细致的时间计算和汇总分析。
- 支持日期的国际化
通过存储英文和中文等不同语言的日期格式和星期几、月份的名称，日期维度表可以适应国际化需求，支持多语言环境下的数据展示和分析。
- 支持复杂的时间维度计算
日期维度表预先计算了一些复杂的时间逻辑，如：
周的第一天和最后一天（is_first_day_in_week 和 is_last_day_in_week），方便做周度数据分析。
月份和季度的起止日期（is_first_day_in_month、is_last_day_in_month），帮助识别月度或季度的起止时间。
季度编号和年度季度（quarter_number、year_quarter），可以简化季度分析时的逻辑。
- 增强查询效率
通过使用日期维度表，数据仓库中的事实表可以使用日期维度表的主键（日期键 date_key）进行关联，从而减少在查询中对时间的复杂计算。例如，避免在每次查询时都使用 DATE_FORMAT 函数转换日期，而是直接关联维度表中的现成字段，提升查询效率。
- 方便时间序列分析
日期维度表可以帮助快速进行时间序列分析，如趋势分析、环比分析、同比分析等。它为每个日期提供了连续的时间线，方便与事实表关联进行时间序列计算。
- 支持特殊日的标记
日期维度表还可以扩展，加入一些自定义的特殊日标记，如节假日（元旦、春节等）或其他业务相关的重要日期，这样在分析时可以轻松区分平日和特殊日的业务表现。
- 作为业务时间轴的基础
日期维度表通常作为其他维度表的基础，比如根据日期维度表中的季度信息生成季度维度表，根据月份生成月份维度表等。它是建立时间相关的业务逻辑的核心。
- 统一时间标准
通过使用统一的日期维度表，数据仓库中的所有时间相关分析都可以基于同一日期集合，确保一致性。例如，所有按周、月或季度的分析，都可以从同一维度表中获取相关信息，避免由于日期计算的不同而导致的不一致性。
- 易于维护和扩展
日期维度表通常是自动生成的，维护简单。如果需要扩展新的时间字段（如农历日期或其他时区的日期信息），也可以在日期维度表中直接扩展字段，保持数据仓库的灵活性。
日期维度表生成
创建日期维度表的表结构：
CREATE TABLE `dim_date` ( `date` date NOT NULL COMMENT '完整日期 (作为主键)', `date_short` varchar(10) DEFAULT NULL COMMENT '短日期格式 (YY/MM/DD)', `date_medium` varchar(12) DEFAULT NULL COMMENT '中日期格式 (MMM DD, YYYY)', `date_long` varchar(20) DEFAULT NULL COMMENT ' 长日期格式 (MMMM DD, YYYY)', `date_full` varchar(30) DEFAULT NULL COMMENT ' 完整日期格式 (Weekday, Month DD, YYYY)', `day_in_year` int DEFAULT NULL COMMENT ' 一年中的第几天', `day_in_month` int DEFAULT NULL COMMENT ' 一月中的第几天', `day_name` varchar(10) DEFAULT NULL COMMENT ' 完整的星期几名称 (e.g., Monday)', `day_abbreviation` varchar(3) DEFAULT NULL COMMENT ' 星期几缩写 (e.g., Mon)', `day_abbreviation_zh` varchar(6) DEFAULT NULL COMMENT ' 星期几缩写 (e.g., 星期一，星期二)', `week_in_year` int DEFAULT NULL COMMENT ' 一年中的第几周', `week_in_month` int DEFAULT NULL COMMENT ' 一月中的第几周', `month_number` int DEFAULT NULL COMMENT ' 月份编号 (1-12)', `month_abbreviation` varchar(3) DEFAULT NULL COMMENT ' 月份缩写 (e.g., Jan)', `month_abbreviation_zh` varchar(3) DEFAULT NULL COMMENT ' 月份缩写 (e.g., 一月，二月)', `year2` varchar(2) DEFAULT NULL COMMENT ' 2位年份 (e.g., 23)', `year4` varchar(4) DEFAULT NULL COMMENT ' 4位年份 (e.g., 2023)', `quarter_name` varchar(6) DEFAULT NULL COMMENT ' 季度名称 (e.g., Q1)', `quarter_name_zh` varchar(6) DEFAULT NULL COMMENT ' 季度名称 (e.g., 第一季度，第二季度)', `quarter_number` int DEFAULT NULL COMMENT ' 季度编号 (1-4)', `is_first_day_in_week` tinyint(1) DEFAULT NULL COMMENT ' 是否为一周的第一天', `is_last_day_in_week` tinyint(1) DEFAULT NULL COMMENT ' 是否为一周的最后一天', `is_first_day_in_month` tinyint(1) DEFAULT NULL COMMENT ' 是否为一个月的第一天', `is_last_day_in_month` tinyint(1) DEFAULT NULL COMMENT ' 是否为一个月的最后一天', `year_quarter` varchar(7) DEFAULT NULL COMMENT ' 年度季度 (e.g., 2023-Q1)', `year_month_number` varchar(7) DEFAULT NULL COMMENT ' 年度月份编号 (e.g., 2023-01)', `year_month_abbreviation` varchar(8) DEFAULT NULL COMMENT ' 年度月份缩写 (e.g., 2023-Jan)', `date_key` int DEFAULT NULL COMMENT ' 日期键 (YYYYMMDD)', PRIMARY KEY (`date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
创建日期维度表存储过程：
CREATE PROCEDURE `fill_dim_date`(IN start_date DATE, IN end_date DATE) begin DECLARE current_date1 DATE; DECLARE day_name_zh VARCHAR(6); DECLARE month_abbreviation_zh VARCHAR(3); DECLARE quarter_name_zh VARCHAR(6); SET current_date1 = start_date; WHILE current_date1 <= end_date DO -- 计算中文星期几 CASE DAYOFWEEK(current_date1) WHEN 1 THEN SET day_name_zh = '星期日'; WHEN 2 THEN SET day_name_zh = '星期一'; WHEN 3 THEN SET day_name_zh = '星期二'; WHEN 4 THEN SET day_name_zh = '星期三'; WHEN 5 THEN SET day_name_zh = '星期四'; WHEN 6 THEN SET day_name_zh = '星期五'; WHEN 7 THEN SET day_name_zh = '星期六'; END CASE; -- 计算中文月份缩写 CASE MONTH(current_date1) WHEN 1 THEN SET month_abbreviation_zh = '一月'; WHEN 2 THEN SET month_abbreviation_zh = '二月'; WHEN 3 THEN SET month_abbreviation_zh = '三月'; WHEN 4 THEN SET month_abbreviation_zh = '四月'; WHEN 5 THEN SET month_abbreviation_zh = '五月'; WHEN 6 THEN SET month_abbreviation_zh = '六月'; WHEN 7 THEN SET month_abbreviation_zh = '七月'; WHEN 8 THEN SET month_abbreviation_zh = '八月'; WHEN 9 THEN SET month_abbreviation_zh = '九月'; WHEN 10 THEN SET month_abbreviation_zh = '十月'; WHEN 11 THEN SET month_abbreviation_zh = '十一月'; WHEN 12 THEN SET month_abbreviation_zh = '十二月'; END CASE; -- 计算中文季度名称 CASE QUARTER(current_date1) WHEN 1 THEN SET quarter_name_zh = '第一季度'; WHEN 2 THEN SET quarter_name_zh = '第二季度'; WHEN 3 THEN SET quarter_name_zh = '第三季度'; WHEN 4 THEN SET quarter_name_zh = '第四季度'; END CASE; -- 插入日期数据 INSERT INTO dim_date ( `date`, `date_short`, `date_medium`, `date_long`, `date_full`, `day_in_year`, `day_in_month`, `day_name`, `day_abbreviation`, `day_abbreviation_zh`, `week_in_year`, `week_in_month`, `month_number`, `month_abbreviation`, `month_abbreviation_zh`, `year2`, `year4`, `quarter_name`, `quarter_name_zh`, `quarter_number`, `is_first_day_in_week`, `is_last_day_in_week`, `is_first_day_in_month`, `is_last_day_in_month`, `year_quarter`, `year_month_number`, `year_month_abbreviation`, `date_key` ) VALUES ( current_date1, DATE_FORMAT(current_date1, '%y/%m/%d'), -- 短日期格式 DATE_FORMAT(current_date1, '%b %d, %Y'), -- 中日期格式 DATE_FORMAT(current_date1, '%M %d, %Y'), -- 长日期格式 DATE_FORMAT(current_date1, '%W, %M %d, %Y'), -- 完整日期格式 DAYOFYEAR(current_date1), -- 一年中的第几天 DAY(current_date1), -- 一月中的第几天 DAYNAME(current_date1), -- 完整星期几名称 LEFT(DAYNAME(current_date1), 3), -- 星期几缩写 day_name_zh, -- 中文星期几缩写 WEEKOFYEAR(current_date1), -- 一年中的第几周 WEEK(current_date1, 5) - WEEK(DATE_SUB(current_date1, INTERVAL DAYOFMONTH(current_date1) - 1 DAY), 5) + 1, -- 一月中的第几周 MONTH(current_date1), -- 月份编号 DATE_FORMAT(current_date1, '%b'), -- 月份缩写 month_abbreviation_zh, -- 中文月份缩写 DATE_FORMAT(current_date1, '%y'), -- 2位年份 DATE_FORMAT(current_date1, '%Y'), -- 4位年份 CONCAT('Q', QUARTER(current_date1)), -- 季度名称 quarter_name_zh, -- 中文季度名称 QUARTER(current_date1), -- 季度编号 IF(DAYOFWEEK(current_date1) = 1, 1, 0), -- 是否为一周的第一天 IF(DAYOFWEEK(current_date1) = 7, 1, 0), -- 是否为一周的最后一天 IF(DAY(current_date1) = 1, 1, 0), -- 是否为一个月的第一天 IF(LAST_DAY(current_date1) = current_date1, 1, 0), -- 是否为一个月的最后一天 CONCAT(YEAR(current_date1), '-Q', QUARTER(current_date1)), -- 年度季度 DATE_FORMAT(current_date1, '%Y-%m'), -- 年度月份编号 DATE_FORMAT(current_date1, '%Y-%b'), -- 年度月份缩写 DATE_FORMAT(current_date1, '%Y%m%d') -- 日期键 ); -- 将日期加1天 SET current_date1 = DATE_ADD(current_date1, INTERVAL 1 DAY); END WHILE; END
执行 call fill_dim_date('2024-01-01','2024-09-15')
技术点
设置全局变量 default_week_format
你可以通过设置 MySQL 的全局或会话变量 default_week_format 来更改默认的周计算方式，使得周一为一周的第一天。
SET @@global.default_week_format = 1; -- 设置全局默认周格式，周一为一周的第一天 SET @@session.default_week_format = 1; -- 设置当前会话的默认周格式
此变量值 1 表示周一为一周的第一天。如果你需要在整个 MySQL 会话中使用周一作为一周的第一天，可以在会话开始时设置。
