如何编写SQL实现布尔值曲线积分计算 统计泵运行总秒数

统计规则 #

• 采样值为0/1布尔类型，1代表泵处于运行状态，最终统计结果为泵当日累计运行时长，单位为秒
• 固定统计区间为当日00:00:00.000至23:59:59.000
• 计算逻辑：按采样时间升序遍历所有记录，对比相邻记录时间差，仅累加运行状态的时段时长：
  • 当前记录值为1时，累加当前记录时间到下一条相邻记录时间的间隔秒数
  • 若当前为当日最后一条采样记录且值为1（后续无0值采样），累加当前记录时间到当日23:59:59.000的间隔秒数
  • 值为0的时段不纳入统计

示例计算说明 #

对应示例数据的计算过程：

• 午夜至2点区间值为0，不统计时长
• 2点至4点泵开启，时长为04:00:06 - 02:00:07 = 7199s
• 15点至17点泵开启，时长为17:00:04 - 15:00:04 = 7200s
• 22:00:03至当日23:59:59无后续0值记录，该段时长全部纳入统计

SQL实现方案（适配8.0及以上版本MySQL，支持万级采样量高性能查询） #

采用窗口函数替代传统自关联写法，针对不规则时间间隔的采样数据天然适配，单日10000条以上采样场景下查询性能优异：

WITH sample_series AS (
    SELECT
        sample_time,
        pump_run_status,
        -- 取出当前记录的下一条采样时间，无下一条时默认取当日结束时间作为边界
        LEAD(sample_time, 1, CONCAT(DATE(sample_time), ' 23:59:59.000'))
            OVER (ORDER BY sample_time ASC) AS next_record_time
    FROM pump_sampling_log
    -- 过滤当日采样数据，可根据实际统计日期调整过滤条件
    WHERE sample_time >= CONCAT(CURDATE(), ' 00:00:00.000')
      AND sample_time <= CONCAT(CURDATE(), ' 23:59:59.000')
)
SELECT
    SUM(
        CASE WHEN pump_run_status = 1
        THEN TIMESTAMPDIFF(SECOND, sample_time, next_record_time)
        ELSE 0 END
    ) AS total_pump_running_seconds
FROM sample_series;

说明：如果使用的SQL方言不支持LEAD窗口函数，可以通过用户变量按顺序遍历记录实现相同逻辑，窗口函数版本代码更简洁、执行效率更高，是优先推荐的实现方式。

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