SQL计算OEE可用性百分比：处理事件拆分与未完成事件

要解决事件拆分、未完成事件的时长统计问题，核心是对每条记录的时间区间做精准的范围裁切，以下是具体的SQL实现思路和代码：

1. 运行时间（设备状态STATUS_CODE=1）的计算逻辑 #

针对每条设备状态记录，先确定它在查询时间范围（@StartTime 到 @EndTime）内的有效时长：

• 取记录的START_TIMESTAMP和查询起始时间的较大值作为有效开始时间
• 取记录的END_TIMESTAMP（如果为NULL则用查询结束时间）和查询结束时间的较小值作为有效结束时间
• 只有当有效开始时间早于有效结束时间时，才计算这段时长的秒数

对应的SQL片段：

SELECT SUM(
    TIMESTAMPDIFF(
        SECOND,
        GREATEST(start_timestamp, @StartTime),
        LEAST(COALESCE(end_timestamp, @EndTime), @EndTime)
    )
) AS total_run_time
FROM equipment_status
WHERE status_code = 1
AND GREATEST(start_timestamp, @StartTime) < LEAST(COALESCE(end_timestamp, @EndTime), @EndTime);

2. 需求时间（调度记录AVAILABLE=1）的计算逻辑 #

和运行时间的处理逻辑完全一致，只是数据源换成调度表：

SELECT SUM(
    TIMESTAMPDIFF(
        SECOND,
        GREATEST(start_timestamp, @StartTime),
        LEAST(COALESCE(end_timestamp, @EndTime), @EndTime)
    )
) AS total_demand_time
FROM schedule_records
WHERE available = 1
AND GREATEST(start_timestamp, @StartTime) < LEAST(COALESCE(end_timestamp, @EndTime), @EndTime);

3. 整合计算可用性百分比 #

把两个统计结果结合，计算最终的可用性：

SET @StartTime = '2025-06-03 08:00:00';
SET @EndTime = '2025-06-03 09:45:00';

SELECT
    (total_run_time / total_demand_time) * 100 AS availability_percentage
FROM (
    SELECT SUM(
        TIMESTAMPDIFF(
            SECOND,
            GREATEST(start_timestamp, @StartTime),
            LEAST(COALESCE(end_timestamp, @EndTime), @EndTime)
        )
    ) AS total_run_time
    FROM equipment_status
    WHERE status_code = 1
    AND GREATEST(start_timestamp, @StartTime) < LEAST(COALESCE(end_timestamp, @EndTime), @EndTime)
) AS run_time_calc,
(
    SELECT SUM(
        TIMESTAMPDIFF(
            SECOND,
            GREATEST(start_timestamp, @StartTime),
            LEAST(COALESCE(end_timestamp, @EndTime), @EndTime)
        )
    ) AS total_demand_time
    FROM schedule_records
    WHERE available = 1
    AND GREATEST(start_timestamp, @StartTime) < LEAST(COALESCE(end_timestamp, @EndTime), @EndTime)
) AS demand_time_calc;

用你给出的示例时间（2025-06-03 08:00:00 到 09:45:00）测试时，这段SQL会正确计算出1200秒的运行时间和4500秒的需求时间，最终得到26.67%左右的可用性百分比。

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