DuckDB 中 SCD Type 2 的 Merge 语句

搜索快捷键 cmd + k | ctrl + k

文档 / 指南 / SQL 功能

用于 SCD 类型 2 的 Merge 语句

这是一份实用的分步指南，介绍如何使用 DuckDB 的 MERGE 语句（在 v1.4.0 中引入）来执行 upsert 操作并构建缓慢变化维度表（SCD Type 2）。SCD Type 2 允许您保留记录的完整历史版本，同时清晰地标识当前版本，非常适合审计跟踪、数据仓库和分析工作负载。当您需要了解主键数据的先前值、何时发生变更以及在特定状态下持续了多久时，SCD Type 2 非常实用。

为什么要使用 DuckDB 的 MERGE？

通过单一 SQL 语句完成 INSERT、UPDATE 和软 DELETE（upsert 和过期处理）。
比同等的 Python/Pandas 逻辑更简洁、更快速。
实现完整的历史记录跟踪，无需进行硬删除。
得益于 DuckDB 的连接性，可直接在 Parquet、CSV 和数据库上运行！

先决条件

DuckDB ≥ 1.4.0
具备基础 SQL 知识

关键术语

术语	含义
目标表 (Target table)	您正在更新的主表/基础表（例如 `master_ducks`）
源表 (Source table)	传入/新数据（例如 `incoming_ducks`）
MERGE INTO	指定目标表
USING	指定源表/查询
ON	连接条件（通常是主键/业务键 + 当前标志）
WHEN MATCHED	行在双方均存在 → 通常执行 UPDATE（或 DELETE）
WHEN NOT MATCHED BY TARGET	新行（执行插入）
WHEN NOT MATCHED BY SOURCE	行已消失 → 执行软删除/使旧版本过期
RETURNING merge_action	可选：显示每行发生了什么（INSERT/UPDATE/DELETE）

构建一个 SCD Type 2 维度表

我们将跟踪鸭子的信息，并在它们的名称、品种或位置发生变化时保留历史记录。

DuckDB 拥有一个前端笔记本 UI，非常适合管理多条 SQL 语句并对代码进行分段。该 UI 随 DuckDB CLI 一起提供，因此如果您安装了 CLI，就可以使用该前端。要启动笔记本前端，只需运行：duckdb -ui，然后您可以访问 https://:4213/ 开始在笔记本中编写 SQL 代码。只需复制并粘贴以下代码块即可跟随本指南操作。

第一步：创建传入（源）表

该表代表今天的事务数据。

CREATE TABLE IF NOT EXISTS incoming_ducks (
    duck_id     INTEGER,
    duck_name   VARCHAR,
    breed       VARCHAR,
    location    VARCHAR,
    begin_date  DATE,
    end_date    DATE,
    is_current  BOOLEAN
);

INSERT INTO incoming_ducks VALUES
    (101, 'Quackers',   'Mallard',       'Pond B',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (102, 'Waddles',    'Pekin',         'Pond A',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (104, 'Splash',     'Muscovy',       'Pond C',      CURRENT_DATE - INTERVAL '1 day', NULL, true),
    (105, 'Puddles',    'Indian Runner', 'Relocated',   CURRENT_DATE - INTERVAL '1 day', NULL, true);

第二步：创建主（目标）表

该表代表 SCD Type 2 数据（即包含历史记录的事务数据）。

CREATE TABLE IF NOT EXISTS master_ducks (
    record_id   INTEGER PRIMARY KEY,
    duck_id     INTEGER NOT NULL,
    duck_name   VARCHAR,
    breed       VARCHAR,
    location    VARCHAR,
    begin_date  DATE NOT NULL,
    end_date    DATE,
    is_current  BOOLEAN NOT NULL DEFAULT true
);

CREATE SEQUENCE IF NOT EXISTS duck_record_seq START 1;

INSERT INTO master_ducks VALUES
    (nextval('duck_record_seq'), 101, 'Quackers', 'Mallard',       'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 102, 'Waddles',  'Pekin',         'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 103, 'Feathers', 'Rouen',         'Pond B', CURRENT_DATE - INTERVAL '2 days', NULL, true),
    (nextval('duck_record_seq'), 105, 'Puddles',  'Indian Runner', 'Pond A', CURRENT_DATE - INTERVAL '2 days', NULL, true);

第三步：执行 Merge 语句

该语句将执行合并操作，检查目标表和源表数据之间的差异，并遵循指定的 WHEN MATCHED 或 WHEN NOT MATCHED 逻辑。

MERGE INTO master_ducks AS target
USING incoming_ducks AS source
ON target.duck_id = source.duck_id AND target.is_current = true

WHEN MATCHED AND (
       target.duck_name <> source.duck_name OR
       target.breed     <> source.breed     OR
       target.location  <> source.location
) THEN UPDATE SET
    end_date    = CURRENT_DATE - INTERVAL '1 day',
    is_current  = false

WHEN NOT MATCHED BY SOURCE AND target.is_current = true THEN UPDATE SET
    end_date    = CURRENT_DATE - INTERVAL '1 day',
    is_current  = false

WHEN NOT MATCHED BY TARGET THEN INSERT (
    record_id, duck_id, duck_name, breed, location,
    begin_date, end_date, is_current
) VALUES (
    nextval('duck_record_seq'),
    source.duck_id, source.duck_name, source.breed, source.location,
    source.begin_date, source.end_date, source.is_current
)

RETURNING merge_action, *;

第四步：为已更改的记录插入新的当前版本

此语句将新的当前记录插入主表。虽然可以通过 MERGE 语句的 RETURNING 子句实现相同的结果，但这种两步法更加直观且易于理解。

INSERT INTO master_ducks (
    record_id, duck_id, duck_name, breed, location,
    begin_date, end_date, is_current
)
SELECT
    nextval('duck_record_seq'),
    source.duck_id,
    source.duck_name,
    source.breed,
    source.location,
    CURRENT_DATE AS begin_date,
    NULL AS end_date,
    true AS is_current
FROM incoming_ducks AS source
INNER JOIN master_ducks AS target
    ON source.duck_id = target.duck_id
WHERE target.is_current = false
  AND target.end_date = CURRENT_DATE - INTERVAL '1 day';

第五步：查询结果

以下查询可用于查看 MERGE 语句产生的数据。

-- All history
SELECT * FROM master_ducks ORDER BY duck_id, begin_date DESC;

-- Only current records
SELECT * FROM master_ducks WHERE is_current = true;

-- Only expired historical records
SELECT * FROM master_ducks WHERE is_current = false ORDER BY duck_id, begin_date DESC;

第六步：检查单个鸭子

为了更好地说明这一概念，让我们检查一只鸭子，以体现 SCD Type 2 的价值。在运行 merge 语句和更新后的插入语句后，如果我们从主表中进行选择，就可以看到 Quackers 的单条记录。

查看历史数据行

SELECT * FROM master_ducks where duck_name = 'Quackers' and is_current = false;

record_id	duck_id	duck_name	breed	location	begin_date	end_date	is_current
1	101	Quackers	Mallard	Pond A	2025-11-24	2025-11-25	false

注意:

end date 不为 NULL，它包含了该鸭子数据更新时的日期。
is_current 为 false，表明这是一条历史记录。
将要更改的字段是 location，它当前为 Pond A，并将更新为 Pond B。

查看当前数据行

SELECT * FROM master_ducks where duck_name = 'Quackers' and is_current = true;

record_id	duck_id	duck_name	breed	location	begin_date	end_date	is_current
10	101	Quackers	Mallard	Pond B	2025-11-26	NULL	true

注意:

end date 为 NULL，在此上下文中，NULL 表示这是该 duck_id 的最新记录。
is_current 为 true，同样表明这是一条当前记录。
location 现在为 Pond B。

查看 Quackers 的所有数据（包含当前行和非当前行）

SELECT * FROM master_ducks where duck_name = 'Quackers';

record_id	duck_id	duck_name	breed	location	begin_date	end_date	is_current
1	101	Quackers	Mallard	Pond A	2025-11-24	2025-11-25	false
10	101	Quackers	Mallard	Pond B	2025-11-26	NULL	true

常用模式和变体

用例	使用的子句
简单 upsert（无历史记录）	`WHEN MATCHED THEN UPDATE` 和 `WHEN NOT MATCHED BY TARGET THEN INSERT`
Upsert 并删除缺失的行	添加 `WHEN NOT MATCHED BY SOURCE THEN DELETE`
只插入新数据，从不更新	省略 `WHEN MATCHED`
返回受影响的行	添加 `RETURNING merge_action, *`

最佳实践

请记住，TARGET 是主表，SOURCE 是传入表或查询。
保持当前行的 end_date 为 NULL（可加快查询速度）。
在需要时将 MERGE 和 INSERT 语句包装在事务中。
使用主键或代理键来确保唯一性。
先使用 RETURNING 进行测试。

关于此页面

行为准则商标使用指南

关于此页面

本文中