SQL按ID结合RegStatus、DocStatus规则过滤表数据需求实现咨询

需求说明 #

需要实现符合以下规则的SQL数据过滤逻辑：

1. 基础规则：针对表内同一ID下，RegStatus取值为Hardcopies或SoftCopies的条目，若存在DocStatus为InvalidData的行，仅保留该类行，同ID同RegStatus范围内的其余行全部忽略；若同ID对应范围内无InvalidData状态行，则保留全部相关行。
2. 补充规则：

• 若RegStatus不属于Hardcopies/SoftCopies，取值为ABC Pending，则每个唯一ID仅保留1条条目；
• 若RegStatus不属于Hardcopies/SoftCopies，也不属于ABC Pending，则保留所有原有行；
• 若RegStatus取值为Hardcopies/SoftCopies，则该ID下所有DocStatus为InvalidData的多条条目全部保留。

实现代码 #

采用窗口函数做分组判断实现，兼容MySQL 8.0+、PostgreSQL、Spark SQL、Hive等绝大多数支持标准SQL的引擎：

with source as (
       select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'ID of  signatory' as Name
         union all
          select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'Taxable Status ' as Name
         union all
          select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'Bank Letter' as Name
         union all
            select 'ABC' as ID , 'SoftCopies' as RegStatus , 'InValidData' as DocStatus, 'Articles of Association' as Name
         union all
            select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'ID of  signatory' as Name
         union all
          select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'Questionnaire document' as Name
         union all
          select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
            select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Questionnaire document' as Name
         union all
            select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Taxable Status Certificate for GB' as Name
         union all
          select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Bank Letter' as Name
         union all
          select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
            select 'MON' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'HardCopies' as RegStatus , 'InValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'Onboarding' as RegStatus , 'InValidData' as DocStatus, 'Trade Register Extract' as Name
     union all
      select 'XYZ' as ID , 'AcceptanceReview' as RegStatus , 'ValidData' as DocStatus , 'Trade Register Extract' as Name
     union all
      select 'xyz' as ID , 'AcceptanceReview' as RegStatus , 'InValidData' as DocStatus , 'Trade Register Extract' as Name
     union all
         select 'XYZ' as ID , 'PacketSubmitted' as RegStatus , 'ValidData' as DocStatus , 'Trade Register Extract' as Name
       ),
ranked_data AS (
    SELECT 
        *,
        -- 标记同ID同RegStatus（Hardcopies/SoftCopies）分组内是否存在无效数据
        MAX(CASE WHEN LOWER(DocStatus) = 'invaliddata' THEN 1 ELSE 0 END) OVER (PARTITION BY ID, RegStatus) AS has_invalid,
        -- 给ABC Pending分组的行做编号，仅取第一条
        ROW_NUMBER() OVER (PARTITION BY ID, RegStatus ORDER BY Name) AS rn
    FROM source
)
SELECT ID, RegStatus, DocStatus, Name
FROM ranked_data
WHERE 
    -- Hardcopies/SoftCopies过滤规则
    (RegStatus IN ('HardCopies', 'SoftCopies') 
     AND (has_invalid = 0 OR LOWER(DocStatus) = 'invaliddata'))
    -- ABC Pending过滤规则
    OR (RegStatus = 'ABC Pending' AND rn = 1)
    -- 其他状态全部保留
    OR RegStatus NOT IN ('HardCopies', 'SoftCopies', 'ABC Pending')

执行结果说明 #

针对测试用例的过滤结果如下：

• ID=ABC：SoftCopies分组存在无效数据，仅保留1条DocStatus为InValidData的行
• ID=EDG：RegStatus为ABC Pending，仅保留1条行
• ID=JFG：RegStatus为Onboarding属于其他类型，4条行全部保留
• ID=MON：HardCopies分组存在无效数据保留1条无效行，Onboarding的2条行全部保留，共返回3条行
• ID=XYZ：RegStatus为AcceptanceReview、PacketSubmitted属于其他类型，3条行全部保留

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