基于jobs表计算活跃作业数及可多次执行的已完成作业时长

1. 计算进行中作业（已启动未结束）的数量 #

由于inProgress状态可能重复出现，直接计数相减的方式不适用。我们需要先按作业的执行批次分组，再判断每个批次是否完成：

思路 #

用窗口函数给每个作业的执行记录按end状态的出现次数划分批次，同一执行周期内的记录属于同一个批次；之后统计没有end状态的批次数量，即为进行中的作业实例数。如果需要统计唯一作业ID的数量（而非执行实例数），只需修改最后一步的计数方式。

SQL代码 #

WITH job_batches AS (
    SELECT 
        jobID,
        state,
        timestamp,
        -- 按job分组，统计当前记录之前出现的end数量，作为批次ID
        COALESCE(SUM(CASE WHEN state = 'end' THEN 1 ELSE 0 END) OVER (
            PARTITION BY jobID 
            ORDER BY timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0) AS batch_id
    FROM jobs
),
batch_status AS (
    SELECT 
        jobID,
        batch_id,
        -- 判断该批次是否完成（存在end状态）
        MAX(CASE WHEN state = 'end' THEN 1 ELSE 0 END) AS is_completed
    FROM job_batches
    GROUP BY jobID, batch_id
)
-- 统计未完成的批次数量（进行中的作业实例数）
SELECT COUNT(*) AS in_progress_jobs_count
FROM batch_status
WHERE is_completed = 0;

-- 如果需要统计唯一作业ID的数量，替换上面的SELECT为：
-- SELECT COUNT(DISTINCT jobID) AS in_progress_job_ids_count
-- FROM batch_status
-- WHERE is_completed = 0;

2. 获取每个已完成作业的执行时长 #

需要为每个作业的每一次完成执行匹配对应的启动时间（该批次最早的inProgress时间）和结束时间（该批次的end时间），再计算时间差。

思路 #

基于上面的批次分组，提取每个已完成批次的最早inProgress时间和end时间，最后用时间函数计算时长（可根据需求选择时间单位）。

SQL代码 #

WITH job_batches AS (
    SELECT 
        jobID,
        state,
        timestamp,
        COALESCE(SUM(CASE WHEN state = 'end' THEN 1 ELSE 0 END) OVER (
            PARTITION BY jobID 
            ORDER BY timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0) AS batch_id
    FROM jobs
),
batch_timestamps AS (
    SELECT 
        jobID,
        batch_id AS execution_times,
        -- 提取该批次最早的inProgress时间作为启动时间
        MIN(CASE WHEN state = 'inProgress' THEN timestamp END) AS start_time,
        -- 提取该批次的end时间作为结束时间
        MAX(CASE WHEN state = 'end' THEN timestamp END) AS end_time
    FROM job_batches
    GROUP BY jobID, batch_id
    -- 仅保留已完成的批次
    HAVING MAX(CASE WHEN state = 'end' THEN timestamp END) IS NOT NULL
)
SELECT 
    jobID,
    execution_times,
    -- 以分钟为单位计算时长，可替换为SECOND/HOUR等
    TIMESTAMPDIFF(MINUTE, start_time, end_time) AS duration_minutes,
    -- 格式化显示为小时:分钟:秒
    CONCAT(
        TIMESTAMPDIFF(HOUR, start_time, end_time), '小时 ',
        TIMESTAMPDIFF(MINUTE, start_time, end_time) % 60, '分钟 ',
        TIMESTAMPDIFF(SECOND, start_time, end_time) % 60, '秒'
    ) AS duration_hhmmss
FROM batch_timestamps
ORDER BY jobID, execution_times;

执行结果示例 #

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