如何筛选无后续成功恢复的异常/失败作业数据？

需求说明 #

需要从作业执行记录表中筛选出满足以下条件的记录：

• 作业状态为异常（e）或失败（f）
• 同一location和job_name组合下，不存在状态为**完成（c）**的执行记录（系统会自动重试问题作业，存在完成记录意味着已成功恢复）

示例数据集 #

期望输出 #

解决方案 #

方法1：使用NOT EXISTS子查询

直接检查当前记录的location+job_name组合下是否存在完成状态的记录，不存在则保留异常/失败记录：

SELECT t.*
FROM your_table t
WHERE t.status_code IN ('e', 'f')
  AND NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.location = t.location
      AND t2.job_name = t.job_name
      AND t2.status_code = 'c'
  );

方法2：GROUP BY筛选有效组合后关联

先找出所有无完成记录的location+job_name组合，再关联原表获取目标记录：

WITH valid_job_groups AS (
  SELECT location, job_name
  FROM your_table
  GROUP BY location, job_name
  HAVING SUM(CASE WHEN status_code = 'c' THEN 1 ELSE 0 END) = 0
)
SELECT t.*
FROM your_table t
JOIN valid_job_groups v ON t.location = v.location AND t.job_name = v.job_name
WHERE t.status_code IN ('e', 'f');

方法3：MAX()判断组合最终状态

给状态赋予优先级（c最高，代表已恢复），通过MAX()获取每个组合的最高状态，排除已恢复的组合：

WITH job_group_status AS (
  SELECT 
    location, 
    job_name,
    MAX(CASE status_code 
        WHEN 'c' THEN 3 
        WHEN 'e' THEN 2 
        WHEN 'f' THEN 1 
        ELSE 0 
        END) AS highest_status
  FROM your_table
  GROUP BY location, job_name
)
SELECT t.*
FROM your_table t
JOIN job_group_status j ON t.location = j.location AND t.job_name = j.job_name
WHERE t.status_code IN ('e', 'f')
  AND j.highest_status != 3;

方法说明 #

• 方法1逻辑直白，适合中小数据集，建议给location、job_name、status_code建联合索引提升效率。
• 方法2先过滤有效组合，适合需要复用该组合的场景。
• 方法3通过状态数值化判断恢复状态，适合需要基于状态优先级扩展的场景。

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