按MTP/FSV/DOT统计车辆逾期作业平均耗时的SQL实现问询

需求回顾 #

需要统计MTP、FSV、DOT三类作业中，车辆被标记为作业到期（work_is_due='D'）后完成对应工作的平均耗时天数，且需处理同一车辆同一作业多次执行的场景（比如同一辆车多次开展MTP作业，每次都要单独计算周期耗时）。

核心问题处理 #

同一车辆的同一作业会存在多次到期-完成周期，关键是要把这些独立周期区分开，再分别计算每个周期的耗时，最后按作业类型求平均值。

解决方案1：基于连续到期记录的周期分组 #

如果作业到期后，系统会每日更新work_is_due='D'直到作业完成（完成后该字段不再为'D'），可以通过日期连续性来划分独立作业周期：

WITH cycle_groups AS (
    SELECT 
        vehicle,
        work_code,
        record_update_date,
        -- 生成每个独立作业周期的唯一ID：日期不连续则视为新周期
        SUM(CASE 
            WHEN DATEDIFF(day, LAG(record_update_date) OVER (PARTITION BY vehicle, work_code ORDER BY record_update_date), record_update_date) != 1 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY vehicle, work_code ORDER BY record_update_date) AS cycle_id
    FROM your_table
    WHERE work_code IN ('MTP', 'FSV', 'DOT')
      AND work_is_due = 'D'
),
cycle_durations AS (
    SELECT 
        work_code,
        vehicle,
        cycle_id,
        -- 计算单个周期的耗时：从首次标记到期到最后一次标记到期的天数
        DATEDIFF(day, MIN(record_update_date), MAX(record_update_date)) AS days_taken
    FROM cycle_groups
    GROUP BY work_code, vehicle, cycle_id
)
SELECT 
    work_code,
    AVG(days_taken) AS average_days_to_complete
FROM cycle_durations
GROUP BY work_code;

代码逻辑说明

1. cycle_groups CTE：通过LAG()窗口函数获取同一车辆同一作业的上一条记录日期，判断当前记录是否属于新周期（日期差不为1天则是新周期），用SUM()累加生成唯一的cycle_id，每个cycle_id对应一次独立的到期-完成周期。
2. cycle_durations CTE：按作业类型、车辆、周期ID分组，计算每个周期的起始（最早到期日期）和结束（最晚到期日期）的天数差，得到单次作业的耗时。
3. 外层查询按作业类型分组，计算所有周期的平均耗时。

解决方案2：基于状态变更的准确周期计算 #

如果作业完成后work_is_due会切换为非'D'的值，推荐用状态变更来定位每个周期的起始和结束日期，结果更准确：

WITH all_records AS (
    SELECT 
        vehicle,
        work_code,
        record_update_date,
        work_is_due,
        -- 获取下一条记录的日期和状态
        LEAD(record_update_date) OVER (PARTITION BY vehicle, work_code ORDER BY record_update_date) AS next_record_date,
        LEAD(work_is_due) OVER (PARTITION BY vehicle, work_code ORDER BY record_update_date) AS next_work_is_due
    FROM your_table
    WHERE work_code IN ('MTP', 'FSV', 'DOT')
),
cycle_durations AS (
    SELECT 
        work_code,
        vehicle,
        -- 计算从到期标记到作业完成的天数（下一条非'D'记录的日期为完成日期）
        DATEDIFF(day, record_update_date, next_record_date) AS days_taken
    FROM all_records
    WHERE work_is_due = 'D'
      -- 只取每个到期周期的第一条记录（前一条状态不是'D'）
      AND COALESCE(LAG(work_is_due) OVER (PARTITION BY vehicle, work_code ORDER BY record_update_date), '') != 'D'
      -- 确保下一条记录是作业完成的状态
      AND next_work_is_due != 'D'
)
SELECT 
    work_code,
    AVG(days_taken) AS average_days_to_complete
FROM cycle_durations
GROUP BY work_code;

代码逻辑说明

1. all_records CTE：用LEAD()窗口函数获取同一车辆同一作业的下一条记录的日期和状态，方便定位作业完成的时间点。
2. cycle_durations CTE：筛选出每个到期周期的起始记录（首次标记'D'且前一条状态非'D'），并计算该日期到下一条非'D'记录日期的天数差，得到准确的作业耗时。
3. 外层查询按作业类型计算平均耗时。

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