设备REPAIR状态平均周转时长计算（间隔与连续区间问题）

针对你的需求，我们可以通过SQL窗口函数来分组连续的REPAIR状态记录，进而计算每个维修周期的时长，最后得到平均周转时长。以下是具体的解决方案：

思路拆解 #

1. 分组连续REPAIR记录：利用窗口函数生成两组行号，通过行号的差值将同一设备连续处于REPAIR状态的记录归为同一组。
2. 计算单个维修周期时长：对每个分组取最早和最晚的快照日期，通过日期差计算该周期的维修天数（注意要加1，因为包含起始和结束当天）。
3. 统计设备总维修时长：按设备汇总所有维修周期的时长。
4. 计算平均周转时长：对所有设备的总维修时长取平均值。

完整SQL代码 #

WITH repair_groups AS (
    -- 为每个设备的记录和每个设备的REPAIR状态记录分别生成行号
    SELECT 
        equipmentNumber,
        snapshotDate,
        status,
        ROW_NUMBER() OVER (PARTITION BY equipmentNumber ORDER BY snapshotDate) AS overall_rn,
        ROW_NUMBER() OVER (PARTITION BY equipmentNumber, status ORDER BY snapshotDate) AS status_rn
    FROM your_snapshot_table -- 替换成你的实际表名
    WHERE status = 'REPAIR'
),
repair_periods AS (
    -- 按设备和分组标识，计算每个维修周期的开始、结束日期及时长
    SELECT 
        equipmentNumber,
        MIN(snapshotDate) AS repair_start_date,
        MAX(snapshotDate) AS repair_end_date,
        -- 计算维修天数：结束日期 - 开始日期 + 1（包含首尾两天）
        DATEDIFF(day, MIN(snapshotDate), MAX(snapshotDate)) + 1 AS repair_duration_days
    FROM repair_groups
    GROUP BY equipmentNumber, overall_rn - status_rn
),
equipment_repair_summary AS (
    -- 按设备汇总总维修时长（如果一个设备有多次维修，会累加）
    SELECT 
        equipmentNumber,
        SUM(repair_duration_days) AS total_repair_days
    FROM repair_periods
    GROUP BY equipmentNumber
)
-- 计算所有设备的平均维修周转时长
SELECT 
    AVG(total_repair_days * 1.0) AS average_repair_turnaround_days
FROM equipment_repair_summary;

代码验证（针对你的示例数据） #

把示例数据代入后：

• 设备123456的REPAIR分组包含2018-05-02和2018-05-03，时长为2天。
• 设备654321的REPAIR分组包含2018-04-30、2018-05-01、2018-05-02，时长为3天。
• 最终平均时长为(2 + 3) / 2 = 2.5天，和你的预期完全一致。

补充说明 #

• 如果设备当前仍处于REPAIR状态（没有后续状态变化的快照），该方案依然有效，因为MAX(snapshotDate)会取最新的快照日期，计算的时长截止到最后一次快照。
• 如果需要统计单次维修周期的平均时长（而非设备总维修时长的平均），可以直接对repair_periods表中的repair_duration_days字段取平均即可。

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