如何在CASE语句中处理数据表中的多组重叠状态值

需求背景 #

数据表存在两类状态组合，需按规则标记：

• 组合1：包含New、Change request、Cancellation → 标记为Y
• 组合2：包含Renewal、Change request、Cancellation → 标记为N
  注意：同一Id和时间戳（date）下，可能同时存在上述两组状态

原CASE语句的问题 #

你写的CASE语句存在3个核心问题：

1. 冗余条件：date= min(date) over (partition by id, date)完全无效——按id和date分区后，每个分区内的date值完全一致，min(date)必然等于当前行的date，这个条件相当于没加。
2. 逻辑方向错误：需求是按同一id+date的状态集合判断标记，但原语句是逐行判断单条记录的状态，会导致同一组内不同状态的行被标记不同值，完全不符合需求。
3. 未处理冲突场景：没有考虑同一组同时存在New和Renewal的情况，逻辑会出现冲突。

正确SQL写法 #

通用SQL版本（适配多数数据库） #

先通过窗口函数聚合同一id+date下的所有状态，再判断组合类型：

WITH grouped_data AS (
    SELECT 
        id,
        date,
        status,
        -- 拼接当前组内的所有不重复状态
        STRING_AGG(DISTINCT status, ',') OVER (PARTITION BY id, date) AS status_set
    FROM table_name abc
)
SELECT 
    id,
    date,
    status,
    CASE
        -- 优先判断组合1（若同一组同时满足两组，按Y优先，可根据需求调整顺序）
        WHEN status_set LIKE '%New%' 
             AND status_set LIKE '%Change request%' 
             AND status_set LIKE '%Cancellation%' THEN 'Y'
        -- 再判断组合2
        WHEN status_set LIKE '%Renewal%' 
             AND status_set LIKE '%Change request%' 
             AND status_set LIKE '%Cancellation%' THEN 'N'
        ELSE 'unknown'
    END AS status_indicator
FROM grouped_data;

PostgreSQL专属优化版本（用数组更严谨） #

WITH grouped_data AS (
    SELECT 
        id,
        date,
        status,
        ARRAY_AGG(DISTINCT status) OVER (PARTITION BY id, date) AS status_array
    FROM table_name abc
)
SELECT 
    id,
    date,
    status,
    CASE
        WHEN '{New,Change request,Cancellation}' <@ status_array THEN 'Y'
        WHEN '{Renewal,Change request,Cancellation}' <@ status_array THEN 'N'
        ELSE 'unknown'
    END AS status_indicator
FROM grouped_data;

说明 #

• 若同一id+date下同时存在两组状态（既包含New又包含Renewal），上述写法会优先标记为Y，如果需要优先N，调换CASE分支的顺序即可。
• 不同数据库的字符串/数组聚合函数语法可能有差异，比如MySQL可用JSON_ARRAYAGG替代STRING_AGG，需根据实际数据库调整。

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