machinery_table中OOP停机时长统计SQL查询开发需求

需求说明 #

从machinery_table中统计指定站点（site）、机械部件（machine_part）在给定日期范围（from_date到to_date，格式DD/MM/YYYY）内的总停机时长（out of operation hours）。表结构及示例数据如下：

核心逻辑是计算所有**IO（运行）→OOP（停机）到OOP（停机）→IO（运行）**的状态切换间隔，累加这些间隔的总时长（转换为小时）。

实现思路 #

无需自定义函数，利用窗口函数LEAD()即可关联相邻的状态切换记录：

1. 筛选目标站点、部件的有效状态切换记录（仅保留IO↔OOP的切换），并确保记录在指定日期范围内。
2. 按Journal_date升序排序，用LEAD()获取每条记录的下一条切换日期和状态，实现停机开始与结束记录的配对。
3. 仅保留停机开始（IO→OOP）的记录，匹配对应的停机结束（OOP→IO）记录，计算时间差后累加总和。

示例SQL代码 #

MySQL版本 #

SELECT 
    Site,
    Machine_part,
    SUM(TIMESTAMPDIFF(HOUR, start_time, end_time)) AS total_oop_hours
FROM (
    SELECT 
        Site,
        Machine_part,
        STR_TO_DATE(Journal_date, '%d/%m/%Y') AS start_time,
        LEAD(STR_TO_DATE(Journal_date, '%d/%m/%Y')) OVER (PARTITION BY Site, Machine_part ORDER BY STR_TO_DATE(Journal_date, '%d/%m/%Y')) AS end_time,
        New_status AS current_status,
        LEAD(New_status) OVER (PARTITION BY Site, Machine_part ORDER BY STR_TO_DATE(Journal_date, '%d/%m/%Y')) AS next_status
    FROM machinery_table
    WHERE 
        Site = 'London' -- 替换为输入参数site
        AND Machine_part = 'Ship1' -- 替换为输入参数machine_part
        AND STR_TO_DATE(Journal_date, '%d/%m/%Y') BETWEEN STR_TO_DATE('01/03/2023', '%d/%m/%Y') -- 替换为输入参数from_date
            AND STR_TO_DATE('30/06/2023', '%d/%m/%Y') -- 替换为输入参数to_date
        AND (Old_status = 'IO' AND New_status = 'OOP' OR Old_status = 'OOP' AND New_status = 'IO')
) AS status_changes
WHERE current_status = 'OOP' -- 仅保留停机开始的记录
      AND next_status = 'IO' -- 确保下一条是恢复运行的切换
      AND end_time IS NOT NULL -- 排除无对应结束记录的情况
GROUP BY Site, Machine_part;

PostgreSQL版本 #

SELECT 
    Site,
    Machine_part,
    SUM(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) AS total_oop_hours
FROM (
    SELECT 
        Site,
        Machine_part,
        TO_DATE(Journal_date, 'DD/MM/YYYY') AS start_time,
        LEAD(TO_DATE(Journal_date, 'DD/MM/YYYY')) OVER (PARTITION BY Site, Machine_part ORDER BY TO_DATE(Journal_date, 'DD/MM/YYYY')) AS end_time,
        New_status AS current_status,
        LEAD(New_status) OVER (PARTITION BY Site, Machine_part ORDER BY TO_DATE(Journal_date, 'DD/MM/YYYY')) AS next_status
    FROM machinery_table
    WHERE 
        Site = 'London' -- 替换为输入参数site
        AND Machine_part = 'Ship1' -- 替换为输入参数machine_part
        AND TO_DATE(Journal_date, 'DD/MM/YYYY') BETWEEN TO_DATE('01/03/2023', 'DD/MM/YYYY') -- 替换为输入参数from_date
            AND TO_DATE('30/06/2023', 'DD/MM/YYYY') -- 替换为输入参数to_date
        AND (Old_status = 'IO' AND New_status = 'OOP' OR Old_status = 'OOP' AND New_status = 'IO')
) AS status_changes
WHERE current_status = 'OOP'
      AND next_status = 'IO'
      AND end_time IS NOT NULL
GROUP BY Site, Machine_part;

边界情况处理 #

• 若日期范围起始时部件已处于OOP状态：需添加虚拟开始记录（from_date当天的IO→OOP），或调整逻辑计算from_date到第一条OOP→IO的时长。
• 若日期范围结束时部件仍处于OOP状态：需添加虚拟结束记录（to_date当天的OOP→IO），或计算到to_date的时长。

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