跳到主要内容

缓慢变化维

问题​

什么是缓慢变化维（SCD）？拉链表是什么？

答案​

什么是缓慢变化维​

维度表中的数据会随时间变化（如用户换了城市、商品调了价格），但变化频率不高，称为缓慢变化维（Slowly Changing Dimension, SCD）。关键问题是：变化后，历史数据要不要保留？

SCD 类型对比​

类型策略历史保留适用场景
Type 1直接覆盖❌ 不保留错误修正
Type 2新增一行✅ 完整保留需要追溯历史
Type 3加列存旧值⚠️ 只保留上一次只关心前值

Type 1：直接覆盖​

Type 1：用户城市变了，直接 UPDATE
UPDATE dim_user
SET city = '深圳'
WHERE user_id = 'U001';

-- 结果：只能看到当前值，历史值丢失
-- | user_id | name | city  |
-- | U001    | 张三 | 深圳  |  ← 之前是"广州"，已被覆盖

Type 2：新增行（拉链表）​

Type 2：新增一行，标记有效期
-- 原始记录失效
UPDATE dim_user
SET expire_date = '2024-06-30', is_current = 0
WHERE user_id = 'U001' AND is_current = 1;

-- 插入新记录
INSERT INTO dim_user (user_sk, user_id, name, city, effective_date, expire_date, is_current)
VALUES (10002, 'U001', '张三', '深圳', '2024-07-01', '9999-12-31', 1);

-- 结果：历史完整保留
-- | user_sk | user_id | city | effective_date | expire_date | is_current |
-- | 10001   | U001    | 广州 | 2024-01-01     | 2024-06-30  | 0          |
-- | 10002   | U001    | 深圳 | 2024-07-01     | 9999-12-31  | 1          |
拉链表查询技巧
-- 查当前数据
SELECT * FROM dim_user WHERE is_current = 1;

-- 查某天的历史快照
SELECT * FROM dim_user 
WHERE '2024-03-15' BETWEEN effective_date AND expire_date;

Type 3：加列​

Type 3：加一列存旧值
ALTER TABLE dim_user ADD COLUMN prev_city VARCHAR(50);

UPDATE dim_user
SET prev_city = city, city = '深圳'
WHERE user_id = 'U001';

-- 结果：只保留"上一次"的值
-- | user_id | city | prev_city |
-- | U001    | 深圳 | 广州      |

拉链表完整构建流程​

拉链表日更新脚本
-- 1. 找出发生变化的用户
WITH changed AS (
    SELECT
        s.user_id, s.name, s.city, s.phone
    FROM ods_user_snapshot s
    JOIN dim_user d ON s.user_id = d.user_id AND d.is_current = 1
    WHERE s.name != d.name
       OR s.city != d.city
       OR s.phone != d.phone
)
-- 2. 关闭旧记录
UPDATE dim_user d
JOIN changed c ON d.user_id = c.user_id AND d.is_current = 1
SET d.expire_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),
    d.is_current = 0;

-- 3. 插入新记录
INSERT INTO dim_user (user_id, name, city, phone, effective_date, expire_date, is_current)
SELECT user_id, name, city, phone, CURRENT_DATE, '9999-12-31', 1
FROM changed;

常见面试问题​

Q1: 你们维度表用的什么 SCD 类型？为什么？​

答案：

  • 用户维度：Type 2（需要追溯历史城市、等级变化）
  • 商品维度：Type 2（价格变化需要在历史订单中保持当时价格）
  • 错误修正：Type 1（数据录错了直接改）
  • 日期维度：不变，一次性生成

Q2: 拉链表的优缺点？​

答案：

优点缺点
完整保留历史查询需加条件过滤
存储效率高（只存变化）维护逻辑复杂
支持任意时间点快照更新需要两步（关闭+插入）

Q3: 事实表引用的是维度表的代理键还是业务键？​

答案：

  • 引用代理键（Surrogate Key）
  • 因为 Type 2 下同一个业务键对应多行，代理键唯一指向某一时刻的维度快照
  • 这样事实表中的订单能关联到下单那天的用户城市，而非当前城市

相关链接​