商品维度历史追踪：用 MERGE INTO 实现缓慢变化维度（SCD Type 2）

业务背景

电商平台的商品信息并不是一成不变的——价格会调整、品类会重新归属、品牌会更名。这带来一个数据分析难题：当你回溯三个月前的订单时，应该用当时的商品价格还是现在的价格？用当时的品类还是现在的品类？

如果维度表只保留最新值，历史分析就会失真：

  • 促销期间降价卖出的订单，事后看起来像是按现价卖的
  • 品类调整后，历史 GMV 报表的品类分布会突然变化，无法还原当时的真实情况
  • 财务对账时，订单金额与商品价格对不上

缓慢变化维度（SCD Type 2） 的解法是：不覆盖旧记录，而是为每次变更新增一个版本，用 

valid_from
valid_from
、
valid_to
valid_to
、
is_current
is_current
三个字段标记每个版本的有效期。历史订单通过时间范围 JOIN 精确关联到下单时的商品属性。

适用场景

场景说明
商品价格调整促销、涨价后，历史订单仍关联原价，财务对账准确
品类重新归属品类架构调整后，历史 GMV 按原品类统计，不影响趋势分析
供应商/品牌变更同一商品换了供应商，历史采购记录保留原供应商信息
员工职级/薪资变更HR 系统中，历史薪资记录关联当时的职级，不受后续调整影响

涉及的 SQL 命令

命令用途
MERGE INTO
MERGE INTO
关闭有变化的旧版本（UPDATE）+ 插入全新商品（INSERT）
INSERT INTO ... SELECT ... JOIN
INSERT INTO ... SELECT ... JOIN
为已关闭的旧版本插入新版本行
JOIN ... AND order_time >= valid_from AND (valid_to IS NULL OR order_time < valid_to)
JOIN ... AND order_time >= valid_from AND (valid_to IS NULL OR order_time < valid_to)
时间范围 JOIN，关联下单时的商品属性

数据架构

外部数据源（商品管理系统 / ERP / ...） │ 变更推送 ▼ doc_dim_products（商品维度表，SCD Type 2） ├── P001 v1 valid_from=2026-01-01 valid_to=2026-05-28 is_current=false └── P001 v2 valid_from=2026-05-28 valid_to=NULL is_current=true │ │ 时间范围 JOIN ▼ doc_orders_fact（订单事实表） └── O001 order_time=2026-03-10 → 关联 P001 v1（下单时的价格）

维度数据如何实时写入

本文用 

INSERT INTO
INSERT INTO
和 
MERGE INTO
MERGE INTO
模拟商品信息的变更，方便你在测试环境快速复现。生产环境中，商品数据通常来自商品管理系统或 ERP，Lakehouse 提供多种方式持续同步变更：

数据来源推荐方式说明参考文档
MySQL / PostgreSQL 等关系型数据库Studio 实时同步任务（CDC）捕获源库 binlog，毫秒级延迟同步变更到 Lakehouse实时同步任务
多张业务表（商品、品类、供应商）Studio 多表实时同步一次配置同步整库，自动处理表结构变更多表实时同步任务
批量变更文件（CSV / Parquet）Pipe 持续导入适合商品系统定期导出变更文件的场景使用 Pipe 持续导入对象存储数据

前置准备

建商品维度表

CREATE TABLE IF NOT EXISTS doc_dim_products ( product_id STRING, product_name STRING, category STRING, brand STRING, price DECIMAL(10,2), valid_from DATE, valid_to DATE, is_current BOOLEAN );

SCD Type 2 的三个关键字段：

字段含义
valid_from
valid_from
该版本生效日期（含）
valid_to
valid_to
该版本失效日期（不含）；
NULL
NULL
表示当前有效版本
is_current
is_current
true
true
表示当前有效版本，方便快速过滤，无需每次判断 
valid_to IS NULL
valid_to IS NULL

建订单事实表

CREATE TABLE IF NOT EXISTS doc_orders_fact ( order_id STRING, user_id STRING, product_id STRING, quantity INT, amount DECIMAL(10,2), order_time TIMESTAMP );

初始加载商品数据

INSERT INTO doc_dim_products VALUES ('P001', '无线蓝牙耳机', '数码配件', 'SoundMax', 299.00, CAST('2026-01-01' AS DATE), NULL, true), ('P002', '机械键盘', '电脑外设', 'KeyPro', 459.00, CAST('2026-01-01' AS DATE), NULL, true), ('P003', '运动跑鞋', '运动户外', 'SpeedRun', 389.00, CAST('2026-01-01' AS DATE), NULL, true), ('P004', '瑜伽垫', '运动户外', 'FlexFit', 128.00, CAST('2026-01-01' AS DATE), NULL, true), ('P005', '保温杯', '家居生活', 'ThermoKeep', 89.00, CAST('2026-01-01' AS DATE), NULL, true);

插入历史订单

INSERT INTO doc_orders_fact VALUES ('O001', 'U101', 'P001', 2, 598.00, CAST('2026-03-10 10:00:00' AS TIMESTAMP)), ('O002', 'U102', 'P002', 1, 459.00, CAST('2026-03-15 14:00:00' AS TIMESTAMP)), ('O003', 'U103', 'P003', 1, 389.00, CAST('2026-04-01 09:00:00' AS TIMESTAMP)), ('O004', 'U104', 'P001', 1, 299.00, CAST('2026-04-20 16:00:00' AS TIMESTAMP));

场景一：商品价格调整 + 品类迁移 + 新商品上架

2026-05-28，运营团队发起三项变更：

  • P001 无线蓝牙耳机：价格从 299 元调整为 349 元
  • P003 运动跑鞋：品类从"运动户外"迁移到"健康运动"
  • P006 智能手环：全新商品上架，定价 199 元

SCD Type 2 的更新分两步执行。

步骤一：关闭旧版本 + 插入全新商品

MERGE INTO
MERGE INTO
一条语句同时处理两件事：对有变化的商品关闭旧版本（
is_current = false
is_current = false
），对全新商品直接插入。

MERGE INTO doc_dim_products AS t USING ( SELECT 'P001' AS product_id, '无线蓝牙耳机' AS product_name, '数码配件' AS category, 'SoundMax' AS brand, 349.00 AS price, CAST('2026-05-28' AS DATE) AS valid_from UNION ALL SELECT 'P003', '运动跑鞋', '健康运动', 'SpeedRun', 389.00, CAST('2026-05-28' AS DATE) UNION ALL SELECT 'P006', '智能手环', '数码配件', 'SmartBand', 199.00, CAST('2026-05-28' AS DATE) ) AS s ON t.product_id = s.product_id AND t.is_current = true WHEN MATCHED AND (t.price <> s.price OR t.category <> s.category) THEN UPDATE SET is_current = false, valid_to = CAST('2026-05-28' AS DATE) WHEN NOT MATCHED THEN INSERT (product_id, product_name, category, brand, price, valid_from, valid_to, is_current) VALUES (s.product_id, s.product_name, s.category, s.brand, s.price, s.valid_from, NULL, true);

执行后，P001 和 P003 的旧版本被关闭，P006 作为新商品直接插入：

+----------+-------------+----------+-------+------------+------------+----------+ |product_id|product_name |category |price |valid_from |valid_to |is_current| +----------+-------------+----------+-------+------------+------------+----------+ |P001 |无线蓝牙耳机 |数码配件 |299.00 |2026-01-01 |2026-05-28 |false | |P002 |机械键盘 |电脑外设 |459.00 |2026-01-01 |NULL |true | |P003 |运动跑鞋 |运动户外 |389.00 |2026-01-01 |2026-05-28 |false | |P004 |瑜伽垫 |运动户外 |128.00 |2026-01-01 |NULL |true | |P005 |保温杯 |家居生活 |89.00 |2026-01-01 |NULL |true | |P006 |智能手环 |数码配件 |199.00 |2026-05-28 |NULL |true | +----------+-------------+----------+-------+------------+------------+----------+

步骤二：插入新版本

为刚关闭的旧版本（

valid_to = 今天
valid_to = 今天
）插入对应的新版本行。通过 JOIN 
valid_to = 变更日期
valid_to = 变更日期
精确定位刚关闭的行，复用 
product_name
product_name
、
brand
brand
等未变更的字段。

INSERT INTO doc_dim_products SELECT s.product_id, t.product_name, s.category, t.brand, s.price, CAST('2026-05-28' AS DATE) AS valid_from, NULL AS valid_to, true AS is_current FROM ( SELECT 'P001' AS product_id, '数码配件' AS category, 349.00 AS price UNION ALL SELECT 'P003', '健康运动', 389.00 ) AS s JOIN doc_dim_products AS t ON t.product_id = s.product_id AND t.is_current = false AND t.valid_to = CAST('2026-05-28' AS DATE);

执行后，维度表完整状态：

+----------+-------------+----------+-------+------------+------------+----------+ |product_id|product_name |category |price |valid_from |valid_to |is_current| +----------+-------------+----------+-------+------------+------------+----------+ |P001 |无线蓝牙耳机 |数码配件 |299.00 |2026-01-01 |2026-05-28 |false | |P001 |无线蓝牙耳机 |数码配件 |349.00 |2026-05-28 |NULL |true | |P002 |机械键盘 |电脑外设 |459.00 |2026-01-01 |NULL |true | |P003 |运动跑鞋 |运动户外 |389.00 |2026-01-01 |2026-05-28 |false | |P003 |运动跑鞋 |健康运动 |389.00 |2026-05-28 |NULL |true | |P004 |瑜伽垫 |运动户外 |128.00 |2026-01-01 |NULL |true | |P005 |保温杯 |家居生活 |89.00 |2026-01-01 |NULL |true | |P006 |智能手环 |数码配件 |199.00 |2026-05-28 |NULL |true | +----------+-------------+----------+-------+------------+------------+----------+

P001 和 P003 各有两个版本，旧版本有 

valid_to
valid_to
，新版本 
is_current = true
is_current = true
。

场景二：历史订单关联下单时的商品属性

历史订单发生在 2026-03 至 2026-04，此时 P001 价格还是 299 元，P003 品类还是"运动户外"。通过时间范围 JOIN 精确关联：

SELECT o.order_id, o.order_time, o.product_id, p.product_name, p.category, p.price AS price_at_order_time, o.quantity, o.amount FROM doc_orders_fact AS o JOIN doc_dim_products AS p ON o.product_id = p.product_id AND o.order_time >= CAST(p.valid_from AS TIMESTAMP) AND (p.valid_to IS NULL OR o.order_time < CAST(p.valid_to AS TIMESTAMP)) ORDER BY o.order_time;

+--------+---------------------+----------+-------------+----------+--------------------+--------+--------+ |order_id|order_time |product_id|product_name |category |price_at_order_time |quantity|amount | +--------+---------------------+----------+-------------+----------+--------------------+--------+--------+ |O001 |2026-03-10T10:00:00 |P001 |无线蓝牙耳机 |数码配件 |299.00 |2 |598.00 | |O002 |2026-03-15T14:00:00 |P002 |机械键盘 |电脑外设 |459.00 |1 |459.00 | |O003 |2026-04-01T09:00:00 |P003 |运动跑鞋 |运动户外 |389.00 |1 |389.00 | |O004 |2026-04-20T16:00:00 |P001 |无线蓝牙耳机 |数码配件 |299.00 |1 |299.00 | +--------+---------------------+----------+-------------+----------+--------------------+--------+--------+

结果解读：

  • O001、O004 均关联到 P001 的旧版本（299 元），而不是当前的 349 元——财务对账时金额与实际成交价一致
  • O003 关联到 P003 的旧品类"运动户外"，而不是当前的"健康运动"——历史 GMV 按原品类统计，趋势分析不受品类调整影响

JOIN 条件说明：

valid_to IS NULL OR order_time < valid_to
valid_to IS NULL OR order_time < valid_to
同时覆盖当前版本（
valid_to
valid_to
为 NULL）和历史版本（
order_time
order_time
在有效期内）。

场景三：查询当前商品目录

只需过滤 

is_current = true
is_current = true
，无需每次写时间范围条件：

SELECT product_id, product_name, category, brand, price FROM doc_dim_products WHERE is_current = true ORDER BY category, product_id;

+----------+-------------+----------+----------+-------+ |product_id|product_name |category |brand |price | +----------+-------------+----------+----------+-------+ |P003 |运动跑鞋 |健康运动 |SpeedRun |389.00 | |P005 |保温杯 |家居生活 |ThermoKeep|89.00 | |P001 |无线蓝牙耳机 |数码配件 |SoundMax |349.00 | |P006 |智能手环 |数码配件 |SmartBand |199.00 | |P002 |机械键盘 |电脑外设 |KeyPro |459.00 | |P004 |瑜伽垫 |运动户外 |FlexFit |128.00 | +----------+-------------+----------+----------+-------+

场景四：查询某商品的完整变更历史

SELECT product_id, product_name, category, price, valid_from, valid_to, is_current FROM doc_dim_products WHERE product_id = 'P001' ORDER BY valid_from;

+----------+-------------+--------+-------+------------+------------+----------+ |product_id|product_name |category|price |valid_from |valid_to |is_current| +----------+-------------+--------+-------+------------+------------+----------+ |P001 |无线蓝牙耳机 |数码配件|299.00 |2026-01-01 |2026-05-28 |false | |P001 |无线蓝牙耳机 |数码配件|349.00 |2026-05-28 |NULL |true | +----------+-------------+--------+-------+------------+------------+----------+

两行记录完整还原了 P001 的价格变更历史：2026-01-01 上架定价 299 元，2026-05-28 调价至 349 元。

清理资源

DROP TABLE IF EXISTS doc_orders_fact; DROP TABLE IF EXISTS doc_dim_products;

关键点总结

  • 两步法是必要的：Lakehouse 的 
    MERGE INTO
    MERGE INTO
    不支持 
    WHEN NOT MATCHED BY SOURCE
    WHEN NOT MATCHED BY SOURCE
    ，无法在一条语句里同时关闭旧版本和插入新版本。步骤一用 MERGE 关闭旧版本，步骤二用 INSERT + JOIN 插入新版本
  • 全新商品只需步骤一：
    WHEN NOT MATCHED
    WHEN NOT MATCHED
    直接处理目标表中完全不存在的商品，不需要步骤二
  • 时间范围 JOIN 是核心：
    order_time >= valid_from AND (valid_to IS NULL OR order_time < valid_to)
    order_time >= valid_from AND (valid_to IS NULL OR order_time < valid_to)
    精确定位下单时的有效版本，确保历史分析不受后续变更影响
  • is_current
    is_current
    是查询加速器    ：当前目录查询只需 
    WHERE is_current = true
    WHERE is_current = true
    ，避免每次写时间范围条件；时间范围 JOIN 时仍需用 
    valid_from
    valid_from
    /
    valid_to
    valid_to
    ，不能只用 
    is_current
    is_current

相关文档

销售实时看板：用 Dynamic Table 构建多层增量数仓
用户行为漏斗分析：从曝光到下单的转化追踪
Yunqi © 2024 Yunqi, Inc. All Rights Reserved.
联系我们
预约咨询
微信咨询
电话咨询