重复ID多状态数据筛选需求及SQL实现问询

筛选规则概述 #

• 排除所有状态为Obsolete的行
• 若ID仅出现一次：直接保留该行（即使状态为NULL）
• 若ID重复出现：
  • 若存在状态为Release的行且另一行状态为NULL：仅保留Release状态的行
  • 若重复行无Release状态：保留非Obsolete的有效行（如示例中11558254仅保留Review状态行）

示例数据 #

53689875  state = Obsolete  SID # 1
78569852  state = NULL SID # 2
72215538  state = NULL SID # 3
72215538  state = Release SID # 4
78542155  state = Approved SID # 5
75369521  state = Draft SID # 6
32586846  state = Release SID # 7
32586846  state = NULL SID # 8
44855221  state = Obsolete SID # 9
65475331  state = Development SID # 10
56665875  state = NULL SID # 11
21233698  state = Release SID # 12
11558254  state = Review SID # 13
11558254  state = Obsolete SID # 14
89756852  state = Obsolete SID # 15

预期输出 #

78569852  state = NULL SID # 2
72215538  state = Release SID # 4
78542155  state = Approved SID # 5
75369521  state = Draft SID # 6
32586846  state = Release SID # 7
65475331  state = Development SID # 10
56665875  state = NULL SID # 11
21233698  state = Release SID # 12
11558254  state = Review SID # 13

实现方案 #

使用窗口函数分组统计ID的出现次数与状态特征，精准筛选符合规则的行，SQL代码如下：

WITH id_summary AS (
    SELECT 
        id,
        state,
        sid,
        -- 统计每个ID的出现次数
        COUNT(*) OVER (PARTITION BY id) AS id_count,
        -- 标记该ID是否存在Release状态
        MAX(CASE WHEN state = 'Release' THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS has_release
    FROM your_table_name
    -- 先排除所有Obsolete状态的行
    WHERE state != 'Obsolete' OR state IS NULL
)
SELECT id, state, sid
FROM id_summary
WHERE 
    -- 情况1：ID仅出现一次，直接保留
    id_count = 1
    OR
    -- 情况2：ID重复且存在Release状态，只保留Release行
    (id_count > 1 AND has_release = 1 AND state = 'Release')
    OR
    -- 情况3：ID重复但无Release状态，保留非NULL的有效行
    (id_count > 1 AND has_release = 0 AND state IS NOT NULL);

逻辑说明 #

1. CTE预处理：先排除Obsolete行，同时计算每个ID的出现次数、标记是否存在Release状态，为后续筛选提供依据；
2. 多条件筛选：覆盖三种规则场景，确保所有符合要求的行都被保留，不符合的被剔除。

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