带优先级条件的SQL过滤查询构建及结果异常求助

问题背景 #

需要按以下规则过滤Principal表的记录：

• 优先返回**同一组（advertiser_id、credito_id、contract_id）**中status匹配%SIGNED%的记录
• 若组内无符合上述条件的记录，再返回status为ENDORSEMENT_SUCCESS的记录

现有CTE查询会同时返回同一组内的两条符合条件的记录（一条匹配SIGNED，一条是ENDORSEMENT_SUCCESS），不符合“每组只保留优先级最高的一条”的预期。

现有代码 #

CTE_aux AS (
  SELECT *,
    CASE 
      WHEN status LIKE '%SIGNED%' THEN 1 
      ELSE 0 
    END AS rn
  FROM Principal
),
CTE_aux2 AS (
  SELECT *,
    CASE 
      WHEN rn = 0 and status = 'ENDORSEMENT_SUCCESS' THEN 1
      ELSE 0
    END AS rn2
 FROM CTE_aux
),
Consulta2 AS (
  SELECT * 
  FROM CTE_aux2  
  WHERE rn = 1 OR (rn = 0 AND rn2 = 1)
)

错误结果示例 #

advertiser_id   credito_id   contract_id    status         rn rn2
dfb03613         5225           1409   ENDORSEMENT_SUCCESS  0  1
dfb03613           5225        1409     CONTRACTS_SIGNED   1  0

优化方案 #

问题核心是现有逻辑仅筛选符合条件的记录，但未对每组进行优先级排序后取第一条的操作。使用ROW_NUMBER()窗口函数可以完美解决这个问题：

优化后的SQL #

WITH ranked_records AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY advertiser_id, credito_id, contract_id
      ORDER BY 
        -- 第一优先级：匹配SIGNED的记录排最前
        CASE WHEN status LIKE '%SIGNED%' THEN 1 ELSE 2 END,
        -- 第二优先级：非SIGNED记录中，ENDORSEMENT_SUCCESS排前面
        CASE WHEN status = 'ENDORSEMENT_SUCCESS' THEN 1 ELSE 2 END
    ) AS row_num
  FROM Principal
)
-- 只保留每组中优先级最高的第一条记录
SELECT advertiser_id, credito_id, contract_id, status
FROM ranked_records
WHERE row_num = 1;

逻辑说明 #

1. PARTITION BY：将数据按advertiser_id、credito_id、contract_id分组，确保每组内单独排序
2. ORDER BY：定义优先级顺序，匹配%SIGNED%的记录优先级最高，其次是ENDORSEMENT_SUCCESS
3. ROW_NUMBER()：给每组内的记录按优先级编号，编号为1的就是该组需要保留的记录

简化写法（可选） #

如果表中仅需保留这两种目标状态，可以先过滤再排序，提升查询效率：

WITH ranked_records AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY advertiser_id, credito_id, contract_id
      ORDER BY 
        CASE 
          WHEN status LIKE '%SIGNED%' THEN 1
          WHEN status = 'ENDORSEMENT_SUCCESS' THEN 2
          ELSE 3 
        END
    ) AS row_num
  FROM Principal
  -- 先过滤掉不需要的状态
  WHERE status LIKE '%SIGNED%' OR status = 'ENDORSEMENT_SUCCESS'
)
SELECT advertiser_id, credito_id, contract_id, status
FROM ranked_records
WHERE row_num = 1;

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