非唯一数据的SQL Join优化：JOBS与JOBSTEPS表关联问题

给定两张表的示例数据：

表 JOBS #

表 JOBSTEPS #

现有SQL语句：

select
a.JOBNAME,
a.JOBCOUNT,
a.STEPTOTAL,
b.STEPCOUNT,
b.PROGNAME,
b.EXTCMD,
b.SDLDATE,
b.SDLTIME,
a.SDLUNAME,
a.STRTDATE,
a.STRTTIME
from JOBS a inner join JOBSTEPS b on a.JOBNAME = b.JOBNAME and a.JOBCOUNT = b.JOBCOUNT where b.SDLDATE = a.STRTDATE

需要优化关联逻辑，满足以下要求：

• 步骤运行时间过长时，b.SDLDATE可能大于a.STRTDATE
• a.STEPTOTAL为对应JOBSTEPS的最大b.STEPCOUNT
• 避免关联到后续时间的非唯一JOBNAME、JOBCOUNT数据

当前问题：扩展SDLDATE条件后，存在JOBNAME、JOBCOUNT次日复用，以及JOBSTEPS的SDLDATE跨多天的情况，导致要么丢失数据，要么错误关联到更早的JOBS记录。

核心思路是精准匹配每个JOBSTEPS记录所属的JOBS实例，既要允许步骤跨天，又要避免同JOBNAME+JOBCOUNT的不同JOBS实例混淆，以下两种方法可实现：

方法一：时间范围匹配法 #

通过窗口函数获取每个JOBS实例的有效时间范围（从自身启动时间到下一个同JOBNAME+JOBCOUNT实例的启动时间），将JOBSTEPS记录匹配到对应范围内：

WITH job_ranges AS (
    SELECT 
        JOBNAME,
        JOBCOUNT,
        STEPTOTAL,
        STRTDATE,
        STRTTIME,
        SDLUNAME,
        -- 获取下一个同JOBNAME+JOBCOUNT实例的启动时间，作为当前实例的截止时间
        LEAD(STRTDATE || STRTTIME) OVER (PARTITION BY JOBNAME, JOBCOUNT ORDER BY STRTDATE || STRTTIME) AS next_job_start
    FROM JOBS
)
SELECT
    a.JOBNAME,
    a.JOBCOUNT,
    a.STEPTOTAL,
    b.STEPCOUNT,
    b.PROGNAME,
    b.EXTCMD,
    b.SDLDATE,
    b.SDLTIME,
    a.SDLUNAME,
    a.STRTDATE,
    a.STRTTIME
FROM job_ranges a
JOIN JOBSTEPS b 
    ON a.JOBNAME = b.JOBNAME 
    AND a.JOBCOUNT = b.JOBCOUNT
    -- 步骤时间落在当前JOB实例的有效范围内
    AND b.SDLDATE || b.SDLTIME >= a.STRTDATE || a.STRTTIME
    AND (a.next_job_start IS NULL OR b.SDLDATE || b.SDLTIME < a.next_job_start)
-- 验证步骤数在当前JOB的步骤总数范围内
WHERE b.STEPCOUNT <= a.STEPTOTAL

方法二：批次分组匹配法 #

先对JOBSTEPS按JOBNAME+JOBCOUNT+最大步骤数分组确定批次，再关联到启动时间最接近的JOBS实例：

WITH step_batches AS (
    SELECT
        JOBNAME,
        JOBCOUNT,
        STEPCOUNT,
        SDLDATE,
        SDLTIME,
        PROGNAME,
        EXTCMD,
        -- 每个批次的最早启动时间，作为批次标识
        MIN(SDLDATE || SDLTIME) OVER (PARTITION BY JOBNAME, JOBCOUNT, MAX_STEP) AS batch_start,
        MAX_STEP
    FROM (
        SELECT
            *,
            -- 每个JOBNAME+JOBCOUNT分组内的最大步骤数，匹配JOBS的STEPTOTAL
            MAX(STEPCOUNT) OVER (PARTITION BY JOBNAME, JOBCOUNT) AS MAX_STEP
        FROM JOBSTEPS
    ) t
)
SELECT
    a.JOBNAME,
    a.JOBCOUNT,
    a.STEPTOTAL,
    b.STEPCOUNT,
    b.PROGNAME,
    b.EXTCMD,
    b.SDLDATE,
    b.SDLTIME,
    a.SDLUNAME,
    a.STRTDATE,
    a.STRTTIME
FROM JOBS a
JOIN step_batches b
    ON a.JOBNAME = b.JOBNAME
    AND a.JOBCOUNT = b.JOBCOUNT
    AND a.STEPTOTAL = b.MAX_STEP
    -- 匹配启动时间与批次最早时间最接近的JOBS实例
    AND ABS(TO_DATE(a.STRTDATE || a.STRTTIME, 'YYYYMMDDHH24MISS') - TO_DATE(b.batch_start, 'YYYYMMDDHH24MISS')) = (
        SELECT MIN(ABS(TO_DATE(aa.STRTDATE || aa.STRTTIME, 'YYYYMMDDHH24MISS') - TO_DATE(b.batch_start, 'YYYYMMDDHH24MISS')))
        FROM JOBS aa
        WHERE aa.JOBNAME = b.JOBNAME AND aa.JOBCOUNT = b.JOBCOUNT AND aa.STEPTOTAL = b.MAX_STEP
    )

关键优化说明 #

• 时间范围约束：通过LEAD函数明确每个JOBS实例的有效边界，彻底避免跨实例关联问题。
• 批次匹配逻辑：利用步骤的最大步骤数关联JOBS的STEPTOTAL，再通过时间差锁定对应启动实例，解决跨天步骤的归属问题。
• 步骤数验证：确保关联的步骤属于当前JOBS实例的步骤范围内，过滤无效数据。

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