数据仓库可以帮助我们追溯数据的历史变化，其中最主要的一个方法就是缓慢变化维。
此篇主要介绍最常用的缓慢变化维：Type1, Type2, Type3。
此笔记使用的数据库为SQLServer，相应的示例脚本都围绕于此，其它数据库的相应实现会略有不同。
在数据仓库中，维度表存储的是业务的描述性信息（如客户、产品、地区等）。这些属性会随着时间缓慢发生变化，如何处理这些变化，就是缓慢变化维（SCD）问题。
核心问题：当维度属性发生变化时，是覆盖旧值、保留历史，还是部分保留？
原理：新值直接覆盖旧值，不保留任何历史记录。
最简单，粗暴的方法就是TRUNCATE + INSERT。
┌─────────────────────────────────────────────────────────────┐
│ Type 1 更新流程 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 【更新前】 │
│ ┌──────────────────────────────────┐ │
│ │ customer_id = 1001 │ │
│ │ city = "北京" │ │
│ └──────────────────────────────────┘ │
│ ↓ │
│ 收到更新请求 │
│ city = "北京" → "上海" │
│ ↓ │
│ 【更新后】 │
│ ┌──────────────────────────────────┐ │
│ │ customer_id = 1001 │ │
│ │ city = "上海" ← 直接覆盖 │ │
│ └──────────────────────────────────┘ │
│ │
│ ❌ 旧值"北京"永久丢失 │
│ │
└─────────────────────────────────────────────────────────────┘
特点：
适用场景：
原理：为每次变化创建新记录，通过生效时间标识历史版本。
┌─────────────────────────────────────────────────────────────┐
│ Type 2 更新流程 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 【更新前】只有1条记录 │
│ ┌────────────────────────────────────────────┐ │
│ │ customer_id = 1001 │ │
│ │ city = "北京" │ │
│ │ effective_start = 2025-01-01 │ │
│ │ effective_end = 9999-12-31 ← "永远有效" │ │
│ │ is_current = 1 ← "当前版本" │ │
│ └────────────────────────────────────────────┘ │
│ ↓ │
│ 收到更新请求 │
│ city = "北京" → "上海" │
│ ↓ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Step 1: 关闭旧记录 │ │
│ │ ┌────────────────────────────────────────────┐ │ │
│ │ │ customer_id = 1001 │ │ │
│ │ │ city = "北京" │ │ │
│ │ │ effective_start = 2025-01-01 │ │ │
│ │ │ effective_end = 2026-04-26 ← 设置为当前日期 │ │ │
│ │ │ is_current = 0 ← 标记为历史版本 │ │ │
│ │ └────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────┘ │
│ ↓ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Step 2: 插入新记录 │ │
│ │ ┌────────────────────────────────────────────┐ │ │
│ │ │ customer_id = 1001 │ │ │
│ │ │ city = "上海" ← 新值 │ │ │
│ │ │ effective_start = 2026-04-26 ← 当前日期 │ │ │
│ │ │ effective_end = 9999-12-31 ← "永远有效" │ │ │
│ │ │ is_current = 1 ← "当前版本" │ │ │
│ │ └────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 【更新后】共有2条记录 │
│ ✅ 完整保留了历史轨迹 │
│ │
└─────────────────────────────────────────────────────────────┘
核心字段：
surrogate_key：代理键（自增主键）effective_start_date / effective_end_date：生效时间区间，还有一种拼写方法是valid_from和valid_to。is_current：是否当前版本标志特点：
适用场景：
原理：增加"旧值"字段，只保留最近一次变化的前值。
┌─────────────────────────────────────────────────────────────┐
│ Type 3 更新流程 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 【更新前】 │
│ ┌────────────────────────────────────────────┐ │
│ │ customer_id = 1001 │ │
│ │ current_city = "北京" ← 当前城市 │ │
│ │ previous_city = NULL ← 上一个城市 │ │
│ │ city_change_date = NULL ← 变更日期 │ │
│ └────────────────────────────────────────────┘ │
│ ↓ │
│ 收到更新请求 │
│ current_city = "北京" → "上海" │
│ ↓ │
│ 【更新中】字段值"移动" │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ previous_city ← current_city (北京移入"上一值") │ │
│ │ current_city ← "上海" (新值成为"当前值") │ │
│ │ city_change_date ← GETDATE() (记录变更时间) │ │
│ └─────────────────────────────────────────────────────┘ │
│ ↓ │
│ 【更新后】 │
│ ┌────────────────────────────────────────────┐ │
│ │ customer_id = 1001 │ │
│ │ current_city = "上海" ← 当前城市 │ │
│ │ previous_city = "北京" ← 上一个城市 │ │
│ │ city_change_date = 2026-04-26 ← 变更日期 │ │
│ └────────────────────────────────────────────┘ │
│ │
│ ✅ 保留了上一个版本 │
│ ❌ 更早的"广州"历史已丢失（如果之前还有变更） │
│ │
└─────────────────────────────────────────────────────────────┘
特点：
适用场景：
| 对比维度 | Type 1 | Type 2 | Type 3 |
|---|---|---|---|
| 历史保留 | 不保留 | 完整保留 | 保留上一个版本 |
| 数据量 | 稳定 | 持续增长 | 稳定 |
| 实现复杂度 | 简单 | 复杂 | 中等 |
| 查询复杂度 | 简单 | 需时间过滤 | 简单 |
| 审计追踪 | 无 | 完整 | 部分 |
| 适用场景 | 修正错误、非关键字段 | 合规审计、历史分析 | 对比分析 |
┌─────────────────────────────────────────────────────────────┐
│ SCD 类型选择决策树 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 维度字段发生变化 │
│ │ │
│ ▼ │
│ ┌─────────────────────────┐ │
│ │ 是否需要历史追踪？ │ │
│ └─────────────────────────┘ │
│ ╱ ╲ │
│ 否 是 │
│ │ │ │
│ ▼ ▼ │
│ ┌────────────┐ ┌─────────────────────┐ │
│ │ Type 1 │ │ 需要完整历史还是 │ │
│ │ 直接覆盖 │ │ 仅上一版本？ │ │
│ └────────────┘ └─────────────────────┘ │
│ ╱ ╲ │
│ 完整历史 仅上一版本 │
│ │ │ │
│ ▼ ▼ │
│ ┌────────────┐ ┌────────────┐ │
│ │ Type 2 │ │ Type 3 │ │
│ │ 新增记录 │ │ 新增字段 │ │
│ └────────────┘ └────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ Type 2 设计核心要点 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌───────────────────────────────────────────────────┐ │
│ │ 1. 主键设计 │ │
│ │ ✅ 使用代理键（自增ID）作为主键 │ │
│ │ ✅ 业务键（CustomerID）用于关联 │ │
│ │ ❌ 不要用业务键做主键（会导致历史记录冲突） │ │
│ └───────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────┐ │
│ │ 2. 时间区间设计 │ │
│ │ ✅ 使用闭开区间 [start, end) │ │
│ │ ✅ 当前记录 end_date = '9999-12-31' │ │
│ │ ✅ 查询条件: start <= 目标日期 AND end > 目标日期 │ │
│ └───────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────┐ │
│ │ 3. 索引设计 │ │
│ │ ✅ (business_key, is_current) │ │
│ │ ✅ (business_key, start_date, end_date) │ │
│ │ ✅ 过滤索引 WHERE is_current = 1 │ │
│ └───────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。