📚 课程目录第4章 SCD类型2

4. SCD 类型 2：增加新行——用时间戳留住历史

好，咱们进入 SCD 类型 2。这是所有缓慢变化维策略里，用得最多、也最容易被搞砸的一种。说白了，就是「保留全部历史」。

你想想看，业务上经常有这样的需求：客户去年搬家了，他的地址变了。如果你直接覆盖，那去年他住在哪，你就永远查不到了。财务要审计、运营要做地域分析，全都抓瞎。类型 2 就是来解决这个问题的。

4.1 核心思路：不更新，只新增

类型 2 的做法很简单——当某条维度的属性发生变化时，我不去修改原来的那条记录，而是直接插入一条新记录。原来的老记录继续保留，新记录代表当前最新的状态。

但问题来了：一张表里同时存在多条「同一个客户」的记录，我怎么知道哪条是当前有效的？哪条是历史的？

嗯，这里就需要引入两个关键字段：生效日期和失效日期。再加上一个标志位，三管齐下，就能把时间线理得清清楚楚。

类型 2 的核心三要素：

生效日期（Effective Date）：这条记录从什么时候开始生效
失效日期（Expiration Date）：这条记录到什么时候失效（当前有效记录通常设为 9999-12-31 或 NULL）
当前标志（Current Flag）：标记这条记录是否为当前有效版本（Y/N 或 1/0）

我个人习惯用「生效日期 + 失效日期」的组合，因为这样能精确到天甚至到秒，做时间区间查询特别方便。标志位虽然直观，但如果你哪天忘了维护，数据就乱了。所以我一般把标志位当作辅助，日期字段才是硬约束。

4.2 适用场景：客户地址历史

类型 2 最适合的场景，就是那些需要完整追溯历史的维度属性。我举几个我在项目中实际遇到的例子：

客户地址变更：客户搬家了，但历史地址对物流、风控、营销都有价值
产品分类调整：产品从 A 类调到 B 类，但历史订单需要按当时的分类统计
员工部门调动：员工换部门了，但历史薪资、绩效需要关联当时的组织架构
供应商评级变化：供应商从 A 级降到 C 级，但历史采购单需要按当时的评级来算

说白了，只要业务上存在「这条记录变了，但历史数据不能丢」的需求，类型 2 就是你的首选。

我的经验： 我曾经在一个电商项目里，客户地址变更频繁，一开始用了类型 1 直接覆盖。结果双十一复盘时，运营发现某个区域的订单量暴增，但怎么都查不到原因。后来一查，原来是那个区域最近搬进来一个大客户，地址覆盖后，历史数据全丢了。从那以后，凡是涉及地址、分类、等级这类属性，我全部强制用类型 2。

4.3 优缺点分析

任何技术方案都有两面性。类型 2 虽然强大，但也不是银弹。咱们客观地捋一捋。

维度	说明
✅ 优点	完整保留历史：任何时候都能回溯到某个时间点的准确状态支持时间点查询：比如「2023 年 6 月 1 日，这个客户住在哪？」——直接查生效日期 <= 目标时间 AND 失效日期 > 目标时间即可数据可审计：每次变更都有记录，谁改的、什么时候改的、改之前是什么，一清二楚实现简单：逻辑清晰，INSERT 语句就能搞定，不需要复杂的 UPDATE 逻辑
❌ 缺点	数据量膨胀：每次变更都新增一条记录，如果某个维度频繁变化，表会变得很大查询复杂度增加：查询当前有效记录时，必须带上失效日期 = '9999-12-31' 的条件，否则会查出多条外键关联变复杂：事实表需要关联到具体的某一条维度记录，而不是关联到维度主键（因为同一个逻辑主键对应多条物理记录）需要额外的 ETL 维护：每次变更都要判断是新增还是更新，ETL 逻辑比类型 1 复杂

维度

说明

✅ 优点

完整保留历史：任何时候都能回溯到某个时间点的准确状态
支持时间点查询：比如「2023 年 6 月 1 日，这个客户住在哪？」——直接查生效日期 <= 目标时间 AND 失效日期 > 目标时间即可
数据可审计：每次变更都有记录，谁改的、什么时候改的、改之前是什么，一清二楚
实现简单：逻辑清晰，INSERT 语句就能搞定，不需要复杂的 UPDATE 逻辑

❌ 缺点

数据量膨胀：每次变更都新增一条记录，如果某个维度频繁变化，表会变得很大
查询复杂度增加：查询当前有效记录时，必须带上失效日期 = '9999-12-31' 的条件，否则会查出多条
外键关联变复杂：事实表需要关联到具体的某一条维度记录，而不是关联到维度主键（因为同一个逻辑主键对应多条物理记录）
需要额外的 ETL 维护：每次变更都要判断是新增还是更新，ETL 逻辑比类型 1 复杂

⚠️ 注意： 数据量膨胀不是无限膨胀。大多数维度的变化频率其实很低，比如客户地址，一年变一两次就算频繁了。真正需要担心的是那些「每天变一次」的属性，比如股票价格、实时状态——那种情况，你应该考虑拉链表或者事实表，而不是维度表。

4.4 SQL 示例：INSERT + 日期维护

光说不练假把式。咱们直接上 SQL。假设有一张客户维度表 dim_customer，结构如下：

CREATE TABLE dim_customer (
    customer_sk INT PRIMARY KEY,        -- 代理键，自增
    customer_id INT,                     -- 业务主键，客户的唯一标识
    customer_name VARCHAR(100),
    address VARCHAR(200),
    effective_date DATE,                 -- 生效日期
    expiration_date DATE,                -- 失效日期，当前记录为 '9999-12-31'
    current_flag CHAR(1)                 -- 'Y' 表示当前有效，'N' 表示历史
);

现在，客户 ID 为 1001 的张三，地址从「北京市朝阳区」变更为「上海市浦东新区」。我们需要做两件事：

把老记录的失效日期更新为变更前一天，并把当前标志设为 'N'
插入一条新记录，生效日期为变更当天，失效日期为 '9999-12-31'，当前标志为 'Y'

SQL 如下：

-- 步骤 1：关闭旧记录
UPDATE dim_customer
SET expiration_date = '2024-03-14',  -- 假设变更发生在 2024-03-15
    current_flag = 'N'
WHERE customer_id = 1001
  AND current_flag = 'Y';

-- 步骤 2：插入新记录
INSERT INTO dim_customer (
    customer_sk,
    customer_id,
    customer_name,
    address,
    effective_date,
    expiration_date,
    current_flag
)
VALUES (
    (SELECT COALESCE(MAX(customer_sk), 0) + 1 FROM dim_customer),  -- 生成新的代理键
    1001,
    '张三',
    '上海市浦东新区',
    '2024-03-15',          -- 生效日期
    '9999-12-31',          -- 失效日期，表示当前有效
    'Y'
);

你看，逻辑其实不复杂。但这里有个坑——事务一致性。这两条 SQL 必须放在同一个事务里执行，否则如果步骤 1 成功了、步骤 2 失败了，就会出现「没有当前有效记录」的情况。

我曾经踩过的坑： 早期我写 ETL 时，把 UPDATE 和 INSERT 分成了两个独立的步骤，中间没有加事务。结果有一次数据库在 UPDATE 之后、INSERT 之前宕机了。恢复之后，这个客户在维度表里就「消失」了——所有记录都是历史状态，没有当前有效记录。查询直接报错。从那以后，我所有类型 2 的变更逻辑，都强制包在 BEGIN TRAN ... COMMIT 里。

4.5 查询当前有效记录

日常查询中，我们最常用的就是「查当前数据」。写法很简单：

SELECT * FROM dim_customer
WHERE current_flag = 'Y';

或者用日期条件：

SELECT * FROM dim_customer
WHERE expiration_date = '9999-12-31';

两种写法都可以。我个人更推荐用日期条件，因为如果哪天你忘了维护 current_flag，日期条件依然能正确过滤。而 current_flag 一旦出错，查询结果就全歪了。

4.6 查询历史某个时间点的快照

这才是类型 2 的真正威力。比如我想知道「2023 年 12 月 1 日，张三住在哪？」：

SELECT * FROM dim_customer
WHERE customer_id = 1001
  AND effective_date <= '2023-12-01'
  AND expiration_date > '2023-12-01';

这条 SQL 会返回一条记录——就是 2023 年 12 月 1 日那天，张三的地址。不管他后来搬了多少次家，这条记录永远不变。这就是类型 2 的价值所在。

4.7 类型 2 的完整流程

为了让你更直观地理解整个过程，我画了一张流程图。这张图展示了从「收到变更数据」到「完成历史维护」的完整链路。

嗯，这张图把流程拆得很清楚了。你注意看，整个流程的核心就两个动作：关旧 + 开新。只要保证这两个动作是原子的，数据就不会乱。

4.8 关于代理键的补充说明

你可能注意到了，我在 INSERT 语句里用了 customer_sk 作为代理键，并且每次新增时都取最大值加 1。这在实际生产环境里不太推荐——高并发下会有冲突。

我一般会用数据库的自增序列（如 PostgreSQL 的 SERIAL、MySQL 的 AUTO_INCREMENT），或者用雪花算法生成全局唯一的 ID。代理键的唯一要求就是：每条物理记录一个唯一值，不重复、不重用。至于怎么生成，看你的数据库平台。

记住一个原则： 类型 2 的维度表，永远不要用业务主键（如 customer_id）作为主键。因为同一个业务主键对应多条记录，用业务主键做主键会直接报唯一性冲突。代理键才是维度表的真正主键。

4.9 什么时候不该用类型 2

最后说一句，类型 2 虽好，但别滥用。我见过有人把「客户手机号」也用类型 2 来维护——结果客户换了几次手机号，维度表膨胀了十几倍，查询性能直线下降。其实手机号这种属性，用类型 1 覆盖就够了，历史价值不大。

判断标准很简单：这个属性的历史变化，对业务分析有没有实际意义？ 有，就用类型 2；没有，就别给自己找麻烦。