数据仓库缓慢变化维：SCD Type1/2/3/4/6 的适用场景对比

数据仓库缓慢变化维：SCD Type1/2/3/4/6 的适用场景对比

一、维度的"变化"是个麻烦事

数据仓库的同学都知道"事实表"和"维度表"的区别。事实表记录"发生了什么"，维度表描述"它是什么"。

举个简单的例子：订单表（事实）里有一条记录——用户张三在2026年7月17日买了一个手机。那问题来了：如果7月20日张三从"普通会员"变成了"VIP会员"，我回头看7月17日的订单时，张三应该显示为"普通会员"还是"VIP会员"？

这就是**缓慢变化维（Slowly Changing Dimension，简称SCD）**要解决的问题。

二、核心场景：会员等级变更

我们先建一张初始的维度表和对应的SCD实现，再看各种Type的区别。

— 初始数据：张三在2026-07-17时的会员等级是"普通会员"
— 7月20日，张三升级为"VIP会员"
— 我们来看看不同SCD策略如何表现

Type 1：覆盖式更新（不保留历史）

策略：当维度属性变化时，直接UPDATE覆盖旧值。

— 建表
CREATE TABLE dim_user_type1 (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
member_level VARCHAR(20), — 会员等级
city VARCHAR(20),
updated_at DATETIME — 最后更新时间
);

— 初始数据
INSERT INTO dim_user_type1 VALUES
(1001, '张三', '普通会员', '北京', '2026-07-17 10:00:00');

— 7月20日：张三升级为VIP，直接覆盖
UPDATE dim_user_type1
SET member_level = 'VIP会员',
updated_at = '2026-07-20 14:00:00'
WHERE user_id = 1001;

— 查询结果
SELECT * FROM dim_user_type1 WHERE user_id = 1001;
— user_id | user_name | member_level | city | updated_at
— 1001 | 张三 | VIP会员 | 北京 | 2026-07-20 14:00:00
—
— ⚠️ 问题：7月17日的订单关联到这个维度时，
— 会看到张三当时是"VIP会员"，但实际是"普通会员"！
— 历史数据被污染了。

适用场景：

• 修正性变更（拼写错误修正：'北竟' → '北京'）
• 变更记录无分析价值（如用户的头像URL）
• 数据量极大且存储成本远高于分析价值

Type 2：新增版本行（最常用）

策略：当属性变化时，不修改旧行，而是插入一行新数据，并通过有效期字段标记每行的生效时间。

— 建表（多了三个关键字段）
CREATE TABLE dim_user_type2 (
user_key INT AUTO_INCREMENT PRIMARY KEY, — 代理键（Surrogate Key）
— 为什么用代理键？业务主键user_id不唯一了
user_id INT NOT NULL, — 业务主键（可重复）
user_name VARCHAR(50),
member_level VARCHAR(20),
city VARCHAR(20),
— ===== TYPE2核心字段 =====
effective_date DATE NOT NULL, — 生效日期
expire_date DATE DEFAULT '9999-12-31', — 失效日期（9999-12-31=当前有效）
is_current TINYINT DEFAULT 1, — 当前有效标记（0/1）
— 为什么冗余is_current？
— expire_date='9999-12-31'也能判断当前有效
— 但is_current有索引更高效
INDEX idx_user_current (user_id, is_current)
);

— 初始数据（7月17日）
INSERT INTO dim_user_type2 VALUES
(1, 1001, '张三', '普通会员', '北京',
'2026-07-01', '9999-12-31', 1);

— 7月20日：张三升级为VIP会员
— 三步操作，必须在一个事务中完成！
START TRANSACTION;

— 步骤1：旧行的expire_date改为升级日期，is_current=0
UPDATE dim_user_type2
SET expire_date = '2026-07-20',
is_current = 0
WHERE user_id = 1001 AND is_current = 1;

— 步骤2：插入新行，生效日期=升级日期
INSERT INTO dim_user_type2 VALUES
(2, 1001, '张三', 'VIP会员', '北京',
'2026-07-20', '9999-12-31', 1);

COMMIT;

— 查询所有历史
SELECT user_key, user_id, user_name, member_level,
effective_date, expire_date, is_current
FROM dim_user_type2
WHERE user_id = 1001
ORDER BY effective_date;

— user_key | user_id | user_name | member_level | effective_date | expire_date | is_current
— 1 | 1001 | 张三 | 普通会员 | 2026-07-01 | 2026-07-20 | 0
— 2 | 1001 | 张三 | VIP会员 | 2026-07-20 | 9999-12-31 | 1

— 关联订单时：用订单日期 < BETWEEN effective_date AND expire_date
— 7月17日的订单关联到 user_key=1（普通会员）✅
— 7月21日的订单关联到 user_key=2（VIP会员）✅
SELECT o.order_id, o.order_date, u.member_level
FROM fact_orders o
JOIN dim_user_type2 u
ON o.user_id = u.user_id
AND o.order_date BETWEEN u.effective_date AND u.expire_date;

适用场景：

• 需要完整历史追溯的维度（会员等级、用户状态、组织架构）
• 财务、合规等对历史准确性要求高的场景
• 事实表需要关联"当时"的维度快照

Type 2 虽然最常用，但要注意代理键的管理和维护成本。如果维度变更频率太高（比如每天更新），表膨胀会很快，这时可以考虑转用 Type 4 的分离策略。

Type 3：新增属性列（保留上一个值）

策略：在维度表中新增一列来保存"上一个值"，同时保留"当前值"。

— 建表
CREATE TABLE dim_user_type3 (
user_id INT PRIMARY KEY, — 仍然用业务主键（行不会增加）
user_name VARCHAR(50),
current_member_level VARCHAR(20), — 当前会员等级
previous_member_level VARCHAR(20), — 上一个会员等级
membership_changed_date DATE, — 会员等级变更日期
city VARCHAR(20)
);

— 初始数据
INSERT INTO dim_user_type3 VALUES
(1001, '张三', '普通会员', NULL, NULL, '北京');

— 7月20日：张三升级为VIP会员
UPDATE dim_user_type3
SET previous_member_level = current_member_level, — 先保存当前值到previous
current_member_level = 'VIP会员', — 再更新当前值
membership_changed_date = '2026-07-20' — 记录变更日期
WHERE user_id = 1001;

— 查询结果
SELECT * FROM dim_user_type3 WHERE user_id = 1001;
— user_id | user_name | current_level | previous_level | changed_date | city
— 1001 | 张三 | VIP会员 | 普通会员 | 2026-07-20 | 北京

— ⚠️ 问题：只能保留"上一个"值，如果多次变更（普通→银卡→金卡→VIP），
— 只能看到"VIP会员(当前)"和"金卡(上一个)"，看不到"普通"和"银卡"

适用场景：

• 只需要比较"现在 vs 上一个"（如去年 vs 今年的会员等级对比）
• 确定变更只发生有限次（如婚姻状态最多变一次）
• 对存储敏感但又不想完全丢失历史

Type 4：拆表策略（当前表 + 历史表）

策略：把维度表拆成两张——一张存当前有效的数据，一张存所有历史的数据。

— 当前维度表：只有当前有效的记录
CREATE TABLE dim_user_current (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
member_level VARCHAR(20),
city VARCHAR(20),
updated_at DATETIME
);

— 历史快照表：记录所有变更
CREATE TABLE dim_user_history (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
user_name VARCHAR(50),
member_level VARCHAR(20),
city VARCHAR(20),
— 每次变更的快照时间
snapshot_date DATE NOT NULL,
change_type VARCHAR(20), — INSERT/UPDATE/DELETE
changed_fields VARCHAR(200), — 变更的字段列表
INDEX idx_user_snapshot (user_id, snapshot_date)
);

— 初始数据
INSERT INTO dim_user_current VALUES
(1001, '张三', '普通会员', '北京', '2026-07-17 10:00:00');

INSERT INTO dim_user_history VALUES
(1, 1001, '张三', '普通会员', '北京', '2026-07-17', 'INSERT', 'ALL');

— 7月20日：张三升级为VIP（事务保证一致性）
START TRANSACTION;

— 更新当前表
UPDATE dim_user_current
SET member_level = 'VIP会员', updated_at = '2026-07-20 14:00:00'
WHERE user_id = 1001;

— 插入历史记录
INSERT INTO dim_user_history VALUES
(2, 1001, '张三', 'VIP会员', '北京', '2026-07-20', 'UPDATE', 'member_level');

COMMIT;

适用场景：

• 当前数据查询频率远高于历史查询（当前维度表小，查询快）
• 历史数据需要单独存储策略（冷热分离）
• 维度属性变更频繁且历史数据量巨大

Type 6：混合策略（1+2+3组合拳）

策略：在Type 2的版本行基础上，再加入Type 1的覆盖字段和Type 3的上一个值记录。

— Type 6: 最完整的实现
CREATE TABLE dim_user_type6 (
user_key INT AUTO_INCREMENT PRIMARY KEY, — Type 2: 代理键
user_id INT NOT NULL, — Type 2: 业务主键可重复
user_name VARCHAR(50),

— Type 2: 版本控制
effective_date DATE NOT NULL,
expire_date DATE DEFAULT '9999-12-31',
is_current TINYINT DEFAULT 1,

— Type 1: 直接覆盖的字段（修正类变更，无需追溯）
— 用户昵称、头像URL等，每次变更直接覆盖所有版本
user_nickname VARCHAR(50), — Type 1: 覆盖式
avatar_url VARCHAR(200), — Type 1: 覆盖式

— Type 3: 上一个值（方便"现在的 vs 之前的"对比）
current_member_level VARCHAR(20), — Type 3: 当前
previous_member_level VARCHAR(20), — Type 3: 上一个

— 实际值（在Type 2各版本中保持一致）
city VARCHAR(20),

change_reason VARCHAR(200), — 变更原因（运营记录）

INDEX idx_user_current (user_id, is_current),
INDEX idx_user_effective (user_id, effective_date, expire_date)
);

— 7月17日：初始记录
INSERT INTO dim_user_type6 VALUES
(1, 1001, '张三',
'2026-07-01', '9999-12-31', 1,
'小张', '2026-09-10b15xxdb5ra5.jpg', — Type 1字段
'普通会员', NULL, — Type 3字段
'北京', '初始创建');

— 7月20日：升级VIP + 改昵称（两种变更同时发生）
START TRANSACTION;

— 旧行过期
UPDATE dim_user_type6
SET expire_date = '2026-07-20', is_current = 0
WHERE user_id = 1001 AND is_current = 1;

— 新行（Type 2）
INSERT INTO dim_user_type6 VALUES
(2, 1001, '张三',
'2026-07-20', '9999-12-31', 1,
'大Zhang', '2026-09-10b15xxdb5ra5.jpg', — Type 1: 昵称变了但无需追溯到旧昵称
'VIP会员', '普通会员', — Type 3: 当前VIP，上一个普通
'北京', '消费满10000元自动升级');

— 同时修正旧行的昵称（Type 1的特性：覆盖所有版本）
— 如果用纯Type 2，旧行的昵称仍然是"小张"
— 但昵称属于修正性变更，没有历史分析价值
UPDATE dim_user_type6
SET user_nickname = '大Zhang' — 同步修正旧行昵称
WHERE user_id = 1001 AND user_key = 1;

COMMIT;

三、五种策略的对比决策

速查表

四、实战建议

4.1 事实表关联 Type 2 维度的标准写法

— 正确 ✅：用BETWEEN关联有效期
SELECT
o.order_id,
o.order_date,
o.order_amount,
u.member_level,
u.city
FROM fact_orders o
INNER JOIN dim_user_type2 u
ON o.user_id = u.user_id
AND o.order_date BETWEEN u.effective_date AND u.expire_date
WHERE o.order_date >= '2026-07-01';
— 这个查询能保证每个订单关联到"当时的维度快照"

4.2 Type 2 维度的定期清理

— 长期运行的Type 2维度表会越来越大
— 可以定期归档过期很久的数据

— 示例：归档2年前过期的版本行
— 注意：归档前必须确认没有事实表还在引用这些版本！
SELECT
MIN(order_date) AS oldest_active_order
FROM fact_orders;
— 如果最老的订单是2026-01-01，那2024年之前的维度版本可以归档

— 归档SQL
INSERT INTO dim_user_type2_archive
SELECT * FROM dim_user_type2
WHERE expire_date < '2024-01-01' AND is_current = 0;

DELETE FROM dim_user_type2
WHERE expire_date < '2024-01-01' AND is_current = 0;

五、总结

SCD是数据仓库建模中绕不开的话题。五种策略的区别总结如下：

选择原则很简单：不是什么都需要追溯，但该追溯的一定不能丢。从分析需求出发，够用就好，不要过度设计。