基于多状态规则在SQL中生成CTSDate列的全量数据处理需求咨询

首先看原始数据集：

ID  Status      OfferID 
1   Processed   456
1   Processed   123
2   Pending     999
3   Processed   678
3   Pending     789

要生成符合规则的CTSDate列，我们可以用窗口函数结合条件判断实现，以下是通用SQL方案：

SELECT 
    ID,
    Status,
    OfferID,
    CASE
        -- 规则1&2：ID下所有状态相同（含仅一种状态的情况），取该ID最大OfferID
        WHEN COUNT(DISTINCT Status) OVER (PARTITION BY ID) = 1 
            THEN MAX(OfferID) OVER (PARTITION BY ID)
        -- 规则3：ID下有多状态时，统一取该ID内状态为Processed的OfferID
        ELSE MAX(CASE WHEN Status = 'Processed' THEN OfferID END) OVER (PARTITION BY ID)
    END AS CTSDate
FROM your_table_name;

逻辑说明 #

1. COUNT(DISTINCT Status) OVER (PARTITION BY ID)：计算每个ID对应的不同状态数量，等于1时说明该ID的所有记录状态一致（包括只有一条记录的情况），此时取该ID的最大OfferID，覆盖规则1和规则2。
2. MAX(CASE WHEN Status = 'Processed' THEN OfferID END) OVER (PARTITION BY ID)：当ID存在多种状态时，提取该ID内所有Processed状态对应的OfferID的最大值（即使有多个Processed记录也能正确处理），统一赋值给该ID的所有记录，覆盖规则3。

执行结果 #

对示例数据执行后会得到：

ID  Status      OfferID  CTSDate
1   Processed   456      456
1   Processed   123      456
2   Pending     999      999
3   Processed   678      678
3   Pending     789      678

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