数据仓库笔记 第三篇：常用缓慢变化维处理方式介绍
数据仓库笔记 第三篇：常用缓慢变化维处理方式介绍
摘要
数据仓库可以帮助我们追溯数据的历史变化，其中最主要的一个方法就是缓慢变化维。
此篇主要介绍最常用的缓慢变化维：Type1, Type2, Type3。
此笔记使用的数据库为SQLServer，相应的示例脚本都围绕于此，其它数据库的相应实现会略有不同。
什么是缓慢变化维？
在数据仓库中，维度表存储的是业务的描述性信息（如客户、产品、地区等）。这些属性会随着时间缓慢发生变化，如何处理这些变化，就是缓慢变化维（SCD）问题。
核心问题：当维度属性发生变化时，是覆盖旧值、保留历史，还是部分保留？
SCD 类型详解
📌 Type 1 - 直接覆盖（不保留历史）
原理：新值直接覆盖旧值，不保留任何历史记录。
最简单，粗暴的方法就是TRUNCATE + INSERT。
┌─────────────────────────────────────────────────────────────┐
│ Type 1 更新流程 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 【更新前】 │
│ ┌──────────────────────────────────┐ │
│ │ customer_id = 1001 │ │
│ │ city = "北京" │ │
│ └──────────────────────────────────┘ │
│ ↓ │
│ 收到更新请求 │
│ city = "北京" → "上海" │
│ ↓ │
│ 【更新后】 │
│ ┌──────────────────────────────────┐ │
│ │ customer_id = 1001 │ │
│ │ city = "上海" ← 直接覆盖 │ │
│ └──────────────────────────────────┘ │
│ │
│ ❌ 旧值"北京"永久丢失 │
│ │
└─────────────────────────────────────────────────────────────┘
特点：
- ✅ 实现简单，数据量小
- ✅ 查询性能好（无历史版本）
- ❌ 无法追溯历史状态
- ❌ 历史报表可能不一致
适用场景：
- 修正错误数据（拼写错误、格式问题）
- 用户频繁更新的非关键字段（如昵称、头像）
- 不需要历史追溯的属性（如备注信息）
📌 Type 2 - 新增历史记录（完整保留历史）
原理：为每次变化创建新记录，通过生效时间标识历史版本。
┌─────────────────────────────────────────────────────────────┐
│ 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：是否当前版本标志
特点：
- ✅ 完整保留历史轨迹
- ✅ 支持时间点查询（"某客户在某日期的地址是什么"）
- ✅ 历史报表准确可重现
- ❌ 数据量增长快，但只是变化的数据才有增长，没变化的数据不会增长。所以增长基本都能控制在可接受的范围内。
- ❌ ETL 逻辑略复杂
- ❌ 查询需要额外过滤条件
适用场景：
- 需要完整审计追踪（合规要求）
- 历史数据分析（客户迁移路径、产品价格变化）
- 财务、监管类场景
📌 Type 3 - 新增字段保留上一个版本
原理：增加"旧值"字段，只保留最近一次变化的前值。
┌─────────────────────────────────────────────────────────────┐
│ 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 类型的决策树
┌─────────────────────────────────────────────────────────────┐
│ SCD 类型选择决策树 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 维度字段发生变化 │
│ │ │
│ ▼ │
│ ┌─────────────────────────┐ │
│ │ 是否需要历史追踪？ │ │
│ └─────────────────────────┘ │
│ ╱ ╲ │
│ 否 是 │
│ │ │ │
│ ▼ ▼ │
│ ┌────────────┐ ┌─────────────────────┐ │
│ │ Type 1 │ │ 需要完整历史还是 │ │
│ │ 直接覆盖 │ │ 仅上一版本？ │ │
│ └────────────┘ └─────────────────────┘ │
│ ╱ ╲ │
│ 完整历史 仅上一版本 │
│ │ │ │
│ ▼ ▼ │
│ ┌────────────┐ ┌────────────┐ │
│ │ Type 2 │ │ Type 3 │ │
│ │ 新增记录 │ │ 新增字段 │ │
│ └────────────┘ └────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Type 2 设计要点
┌─────────────────────────────────────────────────────────────┐
│ 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 │ │
│ └───────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
参考资料
- Ralph Kimball - The Data Warehouse Toolkit
- SQL Server 官方文档 - Temporal Tables
- Microsoft Learn - Slowly Changing Dimensions
---------------------------------------------------------------
来自博客园的aspnetx宋卫东