基于MySQL按功率值计算电机每日停机时长

需求说明 #

需要通过MySQL查询计算电机每日停机时长（功率Pot=0时），数据集仅包含：

• Datum：时间戳字段（采样率10秒）
• Pot：功率数值字段
  表结构为(Datum Timestamp, Pot int)。

现有问题：

• 自行编写的查询仅用每日最大/最小时间戳，忽略了当日内的分段停机时段
• 未处理跨日期的停机情况
• 云分析服务结果不准确
• 参考的多数方案基于状态字段，不匹配当前仅存功率值的数据集

期望输出格式：

解决方案 #

核心思路是先识别连续的停机时间段，再将跨天的时段拆分为分属不同日期的部分，最后按日期汇总计算总停机时长及占比。

完整SQL查询 #

WITH 
-- 标记每条数据的停机状态，同时获取下一条数据的时间戳
status_changes AS (
    SELECT
        Datum,
        Pot,
        LEAD(Datum) OVER (ORDER BY Datum) AS next_datum,
        CASE WHEN Pot = 0 THEN 1 ELSE 0 END AS is_down
    FROM your_table_name
),
-- 提取连续的停机时段，计算每个时段的开始、结束时间及时长
downtime_intervals AS (
    SELECT
        Datum AS down_start,
        COALESCE(next_datum, Datum) AS down_end,
        TIMESTAMPDIFF(SECOND, Datum, COALESCE(next_datum, Datum)) AS down_seconds
    FROM status_changes
    WHERE is_down = 1
),
-- 将跨天的停机时段拆分为每日的分段
daily_downtime_segments AS (
    SELECT
        DATE(down_start) AS segment_date,
        down_start AS segment_start,
        CASE 
            WHEN DATE(down_start) = DATE(down_end) THEN down_end
            ELSE DATE_ADD(DATE(down_start), INTERVAL 1 DAY)
        END AS segment_end,
        TIMESTAMPDIFF(SECOND, down_start, 
            CASE 
                WHEN DATE(down_start) = DATE(down_end) THEN down_end
                ELSE DATE_ADD(DATE(down_start), INTERVAL 1 DAY)
            END) AS segment_seconds
    FROM downtime_intervals
    UNION ALL
    SELECT
        DATE(down_end) AS segment_date,
        DATE(down_end) AS segment_start,
        down_end AS segment_end,
        TIMESTAMPDIFF(SECOND, DATE(down_end), down_end) AS segment_seconds
    FROM downtime_intervals
    WHERE DATE(down_start) != DATE(down_end)
),
-- 按日期汇总总停机秒数
daily_total AS (
    SELECT
        segment_date AS `Date`,
        SUM(segment_seconds) AS total_down_seconds
    FROM daily_downtime_segments
    GROUP BY segment_date
)
-- 转换为时分秒格式，并计算占比
SELECT
    `Date`,
    SEC_TO_TIME(total_down_seconds) AS `Downtime`,
    ROUND((total_down_seconds / 86400) * 100, 1) AS `Daily downtime [%]`
FROM daily_total
ORDER BY `Date`;

逻辑说明 #

1. status_changes：标记每条数据是否为停机状态，同时用LEAD()函数获取下一条数据的时间戳，为计算时段时长做准备。
2. downtime_intervals：筛选出所有停机状态的记录，计算每个连续停机时段的开始、结束时间及时长（秒）。
3. daily_downtime_segments：将跨天的停机时段拆分为两个部分（前一天的结束到当日0点，当日0点到时段结束），确保每个时段都归属到对应的日期。
4. daily_total：按日期汇总所有停机时段的总秒数。
5. 最后将总秒数转换为HH:MM:SS格式，同时计算停机时长占当日总时长（86400秒）的百分比。

注意事项 #

• 替换查询中的your_table_name为实际表名
• 若数据存在缺失采样的情况，需根据实际业务逻辑调整时段计算（比如假设缺失采样期间状态保持不变）
• 百分比计算中使用ROUND()函数保留1位小数，可根据需求调整精度

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