如何按事务状态流转规则实现SQL表的条件更新

现有表TLA_1，首轮录入数据如下：

第二轮数据发生变更：交易222新增RETURNED状态，交易111移除RETURNED状态，变更后数据如下：

交易状态固定流转顺序为：BOUGHT → CLAIMED → RETURNED，每笔交易必定存在BOUGHT状态，后续可能的状态组合为：

• 仅BOUGHT
• BOUGHT & CLAIMED
• BOUGHT & CLAIMED & RETURNED
• BOUGHT & RETURNED

更新规则要求：首轮数据中某笔交易的最新状态优先级高于第二轮的变更状态时，不执行更新，仅满足以下条件时才执行更新：

1. 表中该交易仅存BOUGHT状态，后续新增CLAIMED或CLAIMED+RETURNED状态时，执行更新
2. 表中该交易存BOUGHT、CLAIMED状态，后续新增RETURNED状态时，执行更新
3. 表中该交易已存全部3种状态，后续移除RETURNED或同时移除RETURNED、CLAIMED时，不执行更新
4. 表中该交易存BOUGHT、CLAIMED状态（无RETURNED），后续仅移除CLAIMED时，不执行更新

现有代码 #

TBL_1建表语句 #

DROP TABLE TBL_1;
CREATE TABLE TBL_1 (
PERSON_IDENFITICATION VARCHAR(100) NOT NULL,
TRANSACTION_IDENTIFICATION VARCHAR(100) NOT NULL,
STATUS VARCHAR(100) NOT NULL,
STATUS_DATETIME datetime NOT NULL,
EXPIRATION_DATETIME datetime NOT NULL
);

TBL_EXPANDED_COLUMNS建表及未完成MERGE语句 #

DROP TABLE TBL_EXPANDED_COLUMNS;
CREATE TABLE TBL_EXPANDED_COLUMNS(
PERSON_IDENFITICATION 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
);

MERGE INTO TBL_EXPANDED_COLUMNS B
USING (
SELECT    
PERSON_IDENFITICATION,
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), max(case when STATUS = 'CLAIMED' THEN STATUS_DATETIME END), max(case when STATUS = 'RETURNED' 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 THEN

我们先给状态定义优先级分值：BOUGHT=1、CLAIMED=2、RETURNED=3，优先级越高分值越大，仅当新状态的优先级分值≥旧状态优先级分值时才允许更新，完全匹配你的规则要求。
另外修正原代码中PERSON_IDENFITICATION的拼写不一致问题，完整改写后的MERGE语句如下：

MERGE INTO TBL_EXPANDED_COLUMNS B
USING (
SELECT    
PERSON_IDENFITICATION,
TRANSACTION_IDENTIFICATION,
(array_agg(STATUS) within group(order by STATUS_DATETIME desc)[0])::varchar as LAST_STATUS,
-- 修正原BOUGHT_DATETIME逻辑，无需取其他状态的时间
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,
-- 计算新状态优先级分值
CASE 
    WHEN array_agg(STATUS) within group(order by STATUS_DATETIME desc)[0] = 'BOUGHT' THEN 1
    WHEN array_agg(STATUS) within group(order by STATUS_DATETIME desc)[0] = 'CLAIMED' THEN 2
    WHEN array_agg(STATUS) within group(order by STATUS_DATETIME desc)[0] = 'RETURNED' THEN 3
END AS NEW_STATUS_SCORE
FROM TBL_1
GROUP BY PERSON_IDENFITICATION, TRANSACTION_IDENTIFICATION, EXPIRATION_DATETIME
 ) A ON (A.PERSON_IDENFITICATION = B.PERSON_IDENFITICATION
   and A.TRANSACTION_IDENTIFICATION = B.TRANSACTION_IDENTIFICATION)
WHEN MATCHED THEN UPDATE SET
    LAST_STATUS = A.LAST_STATUS,
    BOUGHT_DATETIME = A.BOUGHT_DATETIME,
    -- 已有时间不回退
    CLAIMED_DATETIME = CASE WHEN A.CLAIMED_DATETIME > B.CLAIMED_DATETIME THEN A.CLAIMED_DATETIME ELSE B.CLAIMED_DATETIME END,
    RETURNED_DATETIME = CASE WHEN A.RETURNED_DATETIME > B.RETURNED_DATETIME THEN A.RETURNED_DATETIME ELSE B.RETURNED_DATETIME END,
    EXPIRATION_DATETIME = A.EXPIRATION_DATETIME
-- 仅新状态优先级不低于旧状态时才更新
WHERE CASE 
    WHEN B.LAST_STATUS = 'BOUGHT' THEN 1
    WHEN B.LAST_STATUS = 'CLAIMED' THEN 2
    WHEN B.LAST_STATUS = 'RETURNED' THEN 3
END <= A.NEW_STATUS_SCORE
-- 新增首次出现的交易
WHEN NOT MATCHED THEN INSERT (
    PERSON_IDENFITICATION,
    TRANSACTION_IDENTIFICATION,
    LAST_STATUS,
    BOUGHT_DATETIME,
    CLAIMED_DATETIME,
    RETURNED_DATETIME,
    EXPIRATION_DATETIME
) VALUES (
    A.PERSON_IDENFITICATION,
    A.TRANSACTION_IDENTIFICATION,
    A.LAST_STATUS,
    A.BOUGHT_DATETIME,
    A.CLAIMED_DATETIME,
    A.RETURNED_DATETIME,
    A.EXPIRATION_DATETIME
);

逻辑验证 #

• 交易111原有RETURNED状态（分值3），第二轮变更后状态优先级低于3，不更新，符合规则3
• 交易222原有CLAIMED状态（分值2），第二轮新增RETURNED状态（分值3≥2），执行更新，符合规则2
• 仅存BOUGHT的交易后续升级为CLAIMED/RETURNED时，优先级1≤2/3，执行更新，符合规则1
• 存BOUGHT+CLAIMED的交易后续回退为仅BOUGHT时，优先级2>1，不更新，符合规则4

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