基于BusinessKey全记录的Merge语句实现账户客户关系变更同步

需求说明 #

当某账户的任一客户关系发生变更时：

• 将该账户所有历史数据的IsCurrent设为0，EndDate设为变更日期的前一日；
• 插入新的关系数据，设置IsCurrent=1，StartDate为变更日期；
• 若账户关系无变更，MERGE语句不执行任何操作。

数据示例 #

目标表（AccountCustomerRelation）

源表（AccountCustomerRelation_Updates）

期望结果

解决方案（SQL Server 示例） #

WITH ChangedAccounts AS (
    SELECT 
        s.AccountId,
        MAX(s.Date) AS ChangeDate
    FROM AccountCustomerRelation_Updates s
    GROUP BY s.AccountId
    HAVING NOT EXISTS (
        -- 对比当前有效关系集合，判断是否存在变更
        SELECT s_inner.CustomerId, s_inner.RelationId
        FROM AccountCustomerRelation_Updates s_inner
        WHERE s_inner.AccountId = s.AccountId
        EXCEPT
        SELECT t.CustomerId, t.RelationId
        FROM AccountCustomerRelation t
        WHERE t.AccountId = s.AccountId AND t.IsCurrent = 1
    )
)
MERGE INTO AccountCustomerRelation t
USING (
    -- 生成需要更新的历史记录和插入的新记录
    SELECT 
        c.AccountId,
        t.CustomerId,
        t.RelationId,
        t.StartDate,
        DATEADD(DAY, -1, c.ChangeDate) AS EndDate,
        0 AS IsCurrent,
        'UPDATE' AS ActionType
    FROM ChangedAccounts c
    JOIN AccountCustomerRelation t 
        ON c.AccountId = t.AccountId AND t.IsCurrent = 1
    UNION ALL
    SELECT 
        s.AccountId,
        s.CustomerId,
        s.RelationId,
        s.Date AS StartDate,
        NULL AS EndDate,
        1 AS IsCurrent,
        'INSERT' AS ActionType
    FROM AccountCustomerRelation_Updates s
    JOIN ChangedAccounts c 
        ON s.AccountId = c.AccountId
) src 
    ON t.AccountId = src.AccountId 
    AND t.CustomerId = src.CustomerId 
    AND t.RelationId = src.RelationId 
    AND t.IsCurrent = src.IsCurrent
WHEN MATCHED AND src.ActionType = 'UPDATE' THEN
    UPDATE SET 
        t.EndDate = src.EndDate,
        t.IsCurrent = src.IsCurrent
WHEN NOT MATCHED AND src.ActionType = 'INSERT' THEN
    INSERT (AccountId, CustomerId, RelationId, StartDate, EndDate, IsCurrent)
    VALUES (src.AccountId, src.CustomerId, src.RelationId, src.StartDate, src.EndDate, src.IsCurrent);

逻辑解释 #

1. ChangedAccounts CTE：通过EXCEPT对比源表和目标表的当前关系集合，找出存在变更的账户，并确定统一的变更日期（取该账户在源表中的最大日期）。
2. MERGE源数据集：
   • 第一部分：关联变更账户和目标表的当前有效记录，生成历史记录的更新参数；
   • 第二部分：从源表提取新关系数据，生成插入参数。
3. MERGE操作：
   • 匹配到的历史记录执行更新，修改EndDate和IsCurrent；
   • 未匹配到的新关系执行插入，添加新的当前记录。

注意事项 #

• 确保源表中同一账户的所有变更记录日期一致，若存在多日期，需调整ChangeDate的计算逻辑；
• 源表若有重复的账户-客户关系，需先去重，避免插入重复记录；
• 不同数据库的MERGE语法略有差异，需根据实际数据库调整（比如Oracle的日期函数为TRUNC(s.Date) - 1）。

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