时间维度表是什么
时间维度表把每个日期展开成多列属性：年、季、月、周、是否周末、是否节假日、是否工作日等，让报表能按任意时间层级切片而不必在每条 SQL 里写日期函数。它一次构建可长期复用，是数仓里最典型的一致性维度。关键维护点是节假日与调休：这部分无法由日期函数推导，必须按年人工维护。
基本信息
| 主键 | date_sk，形如 20261011 的整数，或 ISO 日期字符串 |
|---|---|
| 常用属性 | 年、季、月、周、日、星期、是否周末、是否节假日、是否工作日 |
| 覆盖范围 | 历史加未来若干年，一次性生成 |
| 维护重点 | 节假日与调休需按年更新，无法由日期函数推导 |
| 配套字段 | 财年与财周口径若与自然年不同，需单独建列 |
| 使用方式 | 与事实表按 date_sk 关联，或直接用于时间过滤 |
典型使用场景
- 多层级报表：同一指标按日、周、月、季、年灵活切换
- 工作日口径：按工作日而非自然日计算日均，避免节假日干扰
- 同比环比：按周序号或月序号做同周同月对齐
- 节假日分析：大促与节假日效应单独统计
- 口径统一：时间归属与层级定义集中在一张表，避免各报表各写一套
时间维度建表与生成
时间维度建表与生成
CREATE TABLE dim_date (
date_sk INT, date_str STRING,
year INT, quarter INT, month INT, week INT,
is_weekend INT, is_holiday INT, is_workday INT
);
-- Spark SQL 生成 2020-01-01 起连续日期的骨架，节假日列另行维护
INSERT INTO dim_date
SELECT CAST(DATE_FORMAT(d, 'yyyyMMdd') AS INT) AS date_sk,
DATE_FORMAT(d, 'yyyy-MM-dd') AS date_str,
YEAR(d) AS year, QUARTER(d) AS quarter, MONTH(d) AS month,
WEEKOFYEAR(d) AS week,
IF(DAYOFWEEK(d) IN (1, 7), 1, 0) AS is_weekend,
0 AS is_holiday, 1 AS is_workday
FROM (SELECT EXPLODE(SEQUENCE(DATE '2020-01-01', DATE '2030-12-31', INTERVAL 1 DAY)) AS d) t;按工作日与月度汇总
按工作日与月度汇总
-- 工作日日均：用时间维度过滤，避免自然日口径被节假日拉低
SELECT d.month,
SUM(f.pay_amt) / SUM(d.is_workday) AS amt_per_workday
FROM fact_order f
JOIN dim_date d ON f.date_sk = d.date_sk
WHERE d.year = 2026
GROUP BY d.month;
-- 缺失日期检查：某年内应有 365 或 366 行
SELECT COUNT(1) AS day_cnt FROM dim_date WHERE year = 2026;常用属性与用途
| 属性 | 用途 | 维护要点 |
|---|---|---|
| 年季月周 | 任意层级聚合与切片 | 周口径要与业务约定统一（自然周或自定义周） |
| is_weekend | 周末效应分析 | 由星期推导，无需人工维护 |
| is_holiday | 节假日效应分析 | 必须按年人工维护，含法定假日与调休 |
| is_workday | 工作日日均、时效考核 | 调休上班日要标记为工作日 |
| 财年与财周 | 按财年口径的报表 | 与自然年不一致时单独建列 |
节假日与调休建议单独维护一张年度表并按年导入，不要硬编码在生成 SQL 里：硬编码后每年都要改 SQL，且容易在换人接管时遗漏。
覆盖范围要给足未来年份：时间维度表没生成到明年时，跨年报表会因关联不到日期而丢行，指标凭空少一截，而 SQL 本身不会报错。