Oracle SQL实现任一作业超时则全部设为KO的技术问询

我有4个作业（job1、job2、job3、job4），它们的状态（KO/OK）原本根据是否超出特定时间阈值判断，但现在需要实现联动逻辑：只要这4个作业里任意一个满足超时条件，所有4个作业的状态都要设为KO。

举个例子：

记录1：job1 (24/10/2023 23:45) (25/10/2023 00:54) (25/10/2023 01:14) (Ended OK) (KO)
记录2：job2 (24/10/2023 21:45) (25/10/2023 22:56) (25/10/2023 23:17) (Ended OK) (OK)
按照需求，这两个作业的状态都要设为KO。

我目前用CASE-WHEN处理，但代码冗余且无法实现联动逻辑，现有代码如下：

WHEN a.JOB_NAME IN ('job1','job2','job3','job4') AND
(
    (a.JOB_NAME = 'job1' AND 
    (
        (TO_DATE(a.ORDER_DATE, 'DD/MM/YYYY') = TO_DATE(SUBSTR(a.END_TIME, 1, 10), 'DD/MM/YYYY') AND 
        to_date(substr(a.END_TIME, -5), 'hh24:mi') > to_date('23:45', 'hh24:mi'))
        OR 
        (TO_DATE(a.ORDER_DATE, 'DD/MM/YYYY') < TO_DATE(SUBSTR(a.END_TIME, 1, 10), 'DD/MM/YYYY') AND 
        to_date(substr(a.END_TIME, -5), 'hh24:mi') > to_date('00:00', 'hh24:mi'))
    ))
    OR
    (a.JOB_NAME = 'job2' AND 
    (
        (TO_DATE(a.ORDER_DATE, 'DD/MM/YYYY') = TO_DATE(SUBSTR(a.END_TIME, 1, 10), 'DD/MM/YYYY') AND 
        to_date(substr(a.END_TIME, -5), 'hh24:mi') > to_date('23:45', 'hh24:mi'))
        OR 
        (TO_DATE(a.ORDER_DATE, 'DD/MM/YYYY') < TO_DATE(SUBSTR(a.END_TIME, 1, 10), 'DD/MM/YYYY') AND 
        to_date(substr(a.END_TIME, -5), 'hh24:mi') > to_date('00:00', 'hh24:mi'))
    ))
    OR
    (a.JOB_NAME = 'job3' AND 
    (
        (TO_DATE(a.ORDER_DATE, 'DD/MM/YYYY') = TO_DATE(SUBSTR(a.END_TIME, 1, 10), 'DD/MM/YYYY') AND 
        to_date(substr(a.END_TIME, -5), 'hh24:mi') > to_date('23:45', 'hh24:mi'))
        OR 
        (TO_DATE(a.ORDER_DATE, 'DD/MM/YYYY') < TO_DATE(SUBSTR(a.END_TIME, 1, 10), 'DD/MM/YYYY') AND 
        to_date(substr(a.END_TIME, -5), 'hh24:mi') > to_date('00:00', 'hh24:mi'))
    ))
    OR
    (a.JOB_NAME = 'job4' AND 
    (
        (TO_DATE(a.ORDER_DATE, 'DD/MM/YYYY') = TO_DATE(SUBSTR(a.END_TIME, 1, 10), 'DD/MM/YYYY') AND 
        to_date(substr(a.END_TIME, -5), 'hh24:mi') > to_date('23:45', 'hh24:mi'))
        OR 
        (TO_DATE(a.ORDER_DATE, 'DD/MM/YYYY') < TO_DATE(SUBSTR(a.END_TIME, 1, 10), 'DD/MM/YYYY') AND 
        to_date(substr(a.END_TIME, -5), 'hh24:mi') > to_date('00:00', 'hh24:mi'))
    ))
) AND a.COMPLETION_STATUS = 'Ended OK' THEN 'KO'

现在需要优化代码，实现上述联动设置所有作业为KO的逻辑。

步骤1：简化超时判断逻辑 #

当前的超时判断逻辑可以大幅简化——所有4个作业的超时规则一致，不需要按job_name重复写4遍。直接将END_TIME转换为完整日期时间，再和ORDER_DATE加上阈值时间对比即可：

TO_DATE(a.END_TIME, 'DD/MM/YYYY HH24:MI') > 
TO_DATE(a.ORDER_DATE || ' 23:45', 'DD/MM/YYYY HH24:MI')

这个表达式完全等价于你原来的判断逻辑：若结束日期与下单日期相同，结束时间晚于23:45；若结束日期是下单次日及以后，必然满足超时条件。

步骤2：用窗口函数实现联动逻辑 #

要实现「任意一个超时则全部设为KO」，需要用窗口函数统计目标作业组内是否存在超时记录。假设这些作业按ORDER_DATE（下单日期）分组，代码如下：

SELECT
    a.*,
    CASE
        -- 目标作业组内存在超时作业，且原状态为Ended OK时，统一设为KO
        WHEN a.JOB_NAME IN ('job1','job2','job3','job4') 
             AND MAX(CASE 
                        WHEN TO_DATE(a.END_TIME, 'DD/MM/YYYY HH24:MI') > TO_DATE(a.ORDER_DATE || ' 23:45', 'DD/MM/YYYY HH24:MI') 
                             AND a.COMPLETION_STATUS = 'Ended OK' 
                        THEN 1 
                        ELSE 0 
                     END) OVER (PARTITION BY a.ORDER_DATE) = 1
        THEN 'KO'
        -- 其他情况保留原状态
        ELSE a.ORIGINAL_STATUS -- 替换为你实际的状态字段名
    END AS FINAL_STATUS
FROM your_table a

关键说明 #

• PARTITION BY a.ORDER_DATE：按下单日期分组，确保同一批次的4个作业归为一组；如果你的分组依据不是ORDER_DATE，替换为实际的分组字段（如批次ID）即可。
• MAX(...) OVER (...)：统计分组内是否存在超时作业（返回1表示有，0表示无），只要有一个作业超时，整个分组的该值都会是1。

扩展：作业超时阈值不同的情况 #

如果后续4个作业的超时阈值不一样，只需在CASE中匹配不同作业的阈值即可：

MAX(CASE 
    WHEN a.JOB_NAME = 'job1' AND TO_DATE(a.END_TIME, 'DD/MM/YYYY HH24:MI') > TO_DATE(a.ORDER_DATE || ' 23:45', 'DD/MM/YYYY HH24:MI') AND a.COMPLETION_STATUS = 'Ended OK' THEN 1
    WHEN a.JOB_NAME = 'job2' AND TO_DATE(a.END_TIME, 'DD/MM/YYYY HH24:MI') > TO_DATE(a.ORDER_DATE || ' 22:30', 'DD/MM/YYYY HH24:MI') AND a.COMPLETION_STATUS = 'Ended OK' THEN 1
    WHEN a.JOB_NAME = 'job3' AND TO_DATE(a.END_TIME, 'DD/MM/YYYY HH24:MI') > TO_DATE(a.ORDER_DATE || ' 00:15', 'DD/MM/YYYY HH24:MI') AND a.COMPLETION_STATUS = 'Ended OK' THEN 1
    WHEN a.JOB_NAME = 'job4' AND TO_DATE(a.END_TIME, 'DD/MM/YYYY HH24:MI') > TO_DATE(a.ORDER_DATE || ' 23:00', 'DD/MM/YYYY HH24:MI') AND a.COMPLETION_STATUS = 'Ended OK' THEN 1
    ELSE 0
END) OVER (PARTITION BY a.ORDER_DATE) = 1

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