如何使用SQL MERGE按状态优先级更新交易表并满足指定条件

表结构定义 #

现有历史状态表TBL_B建表语句如下：

DROP TABLE TBL_B;
CREATE TABLE TBL_B (
PERSON_IDENTIFICATION VARCHAR(100) NOT NULL,
TRANSACTION_IDENTIFICATION VARCHAR(100) NOT NULL,
LAST_STATUS VARCHAR(100) NOT NULL,
BOUGHT_DATETIME DATETIME NOT NULL,
CLAIMED_DATETIME DATETIME NOT NULL,
RETURNED_DATETIME DATETIME NOT NULL,
EXPIRATION_DATETIME DATETIME NOT NULL
);

注：原建表语句中PERSON_IDENFITICATION为拼写笔误，已统一修正为标准拼写PERSON_IDENTIFICATION，实际使用请和业务字段名保持一致

业务规则 #

• 交易状态固定流转顺序为：BOUGHT → CLAIMED → RETURNED，流转不可逆，状态等级随流转顺序递增：RETURNED > CLAIMED > BOUGHT
• 仅当从增量表TBL_1聚合得到的单交易最新状态等级高于TBL_B中存储的历史最新状态时，才更新TBL_B对应记录
• 增量中首次出现的交易直接插入TBL_B

完整实现SQL #

核心思路：给不同状态映射权重值，匹配后先做权重判断，仅当新状态权重更高时执行更新操作。

MERGE INTO TBL_B B
USING (
SELECT    
PERSON_IDENTIFICATION,
TRANSACTION_IDENTIFICATION
,(array_agg(STATUS) within group(order by STATUS_DATETIME desc)[0])::varchar as LAST_STATUS
,coalesce(max(case when STATUS = 'BOUGHT' THEN STATUS_DATETIME END), '1900-01-01'::datetime) as BOUGHT_DATETIME
,coalesce(max(case when STATUS = 'CLAIMED' THEN STATUS_DATETIME END),'1900-01-01'::datetime) as CLAIMED_DATETIME
,coalesce(max(case when STATUS = 'RETURNED' THEN STATUS_DATETIME END),'1900-01-01'::datetime) as RETURNED_DATETIME
,EXPIRATION_DATETIME
FROM TBL_1
GROUP BY 1,2,7
 ) A ON (A.PERSON_IDENTIFICATION = B.PERSON_IDENTIFICATION
   and A.TRANSACTION_IDENTIFICATION = B.TRANSACTION_IDENTIFICATION)
-- 匹配到历史记录时，仅当新状态等级更高才更新
WHEN MATCHED AND 
    CASE A.LAST_STATUS 
        WHEN 'BOUGHT' THEN 1 
        WHEN 'CLAIMED' THEN 2 
        WHEN 'RETURNED' THEN 3 
    END > 
    CASE B.LAST_STATUS 
        WHEN 'BOUGHT' THEN 1 
        WHEN 'CLAIMED' THEN 2 
        WHEN 'RETURNED' THEN 3 
    END
THEN UPDATE SET 
    LAST_STATUS = A.LAST_STATUS,
    BOUGHT_DATETIME = COALESCE(A.BOUGHT_DATETIME, B.BOUGHT_DATETIME),
    CLAIMED_DATETIME = COALESCE(A.CLAIMED_DATETIME, B.CLAIMED_DATETIME),
    RETURNED_DATETIME = COALESCE(A.RETURNED_DATETIME, B.RETURNED_DATETIME),
    EXPIRATION_DATETIME = A.EXPIRATION_DATETIME
-- 未匹配到的新交易直接插入
WHEN NOT MATCHED THEN INSERT (
    PERSON_IDENTIFICATION,
    TRANSACTION_IDENTIFICATION,
    LAST_STATUS,
    BOUGHT_DATETIME,
    CLAIMED_DATETIME,
    RETURNED_DATETIME,
    EXPIRATION_DATETIME
) VALUES (
    A.PERSON_IDENTIFICATION,
    A.TRANSACTION_IDENTIFICATION,
    A.LAST_STATUS,
    A.BOUGHT_DATETIME,
    A.CLAIMED_DATETIME,
    A.RETURNED_DATETIME,
    A.EXPIRATION_DATETIME
);

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