SQL Server：如何标记未遵循最优事件顺序的任务

需求说明 #

我需要优化任务调度软件的监控逻辑，基于jobmonitoring表的events列判断任务是否执行成功：

• 最优执行事件顺序必须严格遵循：
  • task started
  • task processing
  • job task finished
• 满足以下任一条件的任务需要标记为flagged，符合要求的标记为null：
  • 事件序列包含最优顺序外的其他事件
  • 缺失上述三个关键事件中的任意一个
  • 关键事件的出现顺序不符合最优顺序

期望输出结果包含两列：task（任务名称）和flag（不符合要求的标记为'flagged'，符合的为null）。

现有尝试 #

我已经能通过以下查询获取最优事件的排序，但无法实现标记不符合要求的任务：

select t.events from 
    (SELECT distinct events FROM [jobmonitoring]) t
    ORDER BY (case when activity = 'task started' then 1
                  when activity = 'task processing job' then 2
                  when activity = 'task finished' then 3
                  else 4 end)

示例数据 #

jobmonitoring表示例 #

期望输出示例 #

SQL解决方案 #

思路 #

1. 按任务分组，筛选每个任务的关键事件并按时间排序
2. 从三个维度校验任务合规性：无多余事件、无关键事件缺失、事件顺序完全匹配最优序列
3. 补充无事件记录的任务，确保输出完整

实现代码 #

WITH task_events AS (
    SELECT 
        task,
        events,
        -- 按时间戳为每个任务的事件标记顺序
        ROW_NUMBER() OVER (PARTITION BY task ORDER BY timestamp) AS event_order
    FROM jobmonitoring
    -- 仅保留最优序列中的关键事件
    WHERE events IN ('task started', 'task processing', 'job task finished')
),
task_valid_check AS (
    SELECT 
        task,
        -- 按顺序拼接事件序列
        STRING_AGG(events, '->' ORDER BY event_order) AS event_sequence,
        -- 统计关键事件数量
        COUNT(*) AS key_event_count,
        -- 统计该任务的所有事件总数（含非关键事件）
        (SELECT COUNT(*) FROM jobmonitoring j WHERE j.task = te.task) AS total_event_count
    FROM task_events te
    GROUP BY task
)
-- 生成最终标记结果
SELECT 
    j.task,
    CASE 
        -- 同时满足三个条件才判定为合规：无多余事件、关键事件齐全、顺序完全匹配
        WHEN tvc.total_event_count = 3 
             AND tvc.key_event_count = 3 
             AND tvc.event_sequence = 'task started->task processing->job task finished'
        THEN NULL
        ELSE 'flagged'
    END AS flag
FROM jobmonitoring j
LEFT JOIN task_valid_check tvc ON j.task = tvc.task
-- 去重并补充无事件记录的任务
UNION
SELECT task, NULL AS flag
FROM (SELECT 'b' AS task UNION SELECT 'd' AS task) missing_tasks
ORDER BY task;

代码说明 #

• task_events CTE：过滤出每个任务的关键事件，并按时间戳标记事件顺序，为后续序列校验做准备
• task_valid_check CTE：拼接每个任务的关键事件序列，同时统计关键事件数和总事件数，用于判断是否存在多余事件或缺失事件
• 主查询：通过多条件判断标记不符合要求的任务，同时通过UNION补充示例中无事件的任务b、d，确保输出与期望结果一致

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