基于规则的带溯源数据取值及高效SQL Merge语句编写咨询

首先附上样本数据集：

实现思路 #

默认字段取值优先级为 source3 > source2 > source1，和示例输出匹配：

1. 先聚合三个数据源的所有ID作为主键全集，左关联三个源表获取各字段值
2. 每个字段用COALESCE按优先级取第一个非空值，同时用CASE判断该字段的数据源标签
3. 按「NAME来源-CATEGORY来源-HEIGHT来源」的规则拼接LINEAGE血缘字段
4. 把处理好的数据集作为MERGE的USING数据源，按主键ID匹配目标表T1做增改操作

完整SQL代码 #

MERGE INTO T1 T
USING (
    -- 先获取三个数据源的所有不重复ID，避免丢失数据
    WITH all_ids AS (
        SELECT ID FROM source1
        UNION
        SELECT ID FROM source2
        UNION
        SELECT ID FROM source3
    )
    SELECT
        i.ID,
        -- 按优先级取各字段非空值
        COALESCE(s3.NAME, s2.NAME, s1.NAME) AS NAME,
        COALESCE(s3.CATEGORY, s2.CATEGORY, s1.CATEGORY) AS CATEGORY,
        COALESCE(s3.HEIGHT, s2.HEIGHT, s1.HEIGHT) AS HEIGHT,
        -- 拼接血缘字段
        CONCAT_WS(
            '-',
            CASE WHEN s3.NAME IS NOT NULL THEN 'S3' WHEN s2.NAME IS NOT NULL THEN 'S2' ELSE 'S1' END,
            CASE WHEN s3.CATEGORY IS NOT NULL THEN 'S3' WHEN s2.CATEGORY IS NOT NULL THEN 'S2' ELSE 'S1' END,
            CASE WHEN s3.HEIGHT IS NOT NULL THEN 'S3' WHEN s2.HEIGHT IS NOT NULL THEN 'S2' ELSE 'S1' END
        ) AS LINEAGE
    FROM all_ids i
    LEFT JOIN source1 s1 ON i.ID = s1.ID
    LEFT JOIN source2 s2 ON i.ID = s2.ID
    LEFT JOIN source3 s3 ON i.ID = s3.ID
) S
-- 按主键匹配目标表
ON (T.ID = S.ID)
-- 匹配到则更新所有非主键字段
WHEN MATCHED THEN
    UPDATE SET
        T.NAME = S.NAME,
        T.CATEGORY = S.CATEGORY,
        T.HEIGHT = S.HEIGHT,
        T.LINEAGE = S.LINEAGE
-- 未匹配到则插入新数据
WHEN NOT MATCHED THEN
    INSERT (ID, NAME, CATEGORY, HEIGHT, LINEAGE)
    VALUES (S.ID, S.NAME, S.CATEGORY, S.HEIGHT, S.LINEAGE);

注：如果使用的数据库不支持CONCAT_WS函数，可以替换为对应数据库的字符串拼接语法，比如Oracle用||、SQL Server用+，注意做好空值处理即可。

性能优化建议 #

• 三个源表的ID字段都提前建立索引，关联时可以大幅提升查询速度
• 如果源表数据量很大，可以在all_ids、各源表关联前加过滤条件，提前剔除不需要合并的记录
• 数据量超千万级时可以分批按ID范围执行MERGE，避免长事务锁表

内容的提问来源于stack exchange，提问作者Dinakar Ullas