如何通过SQL计算指定时段内加热器的运行时长？

问题分析 #

你的场景中，加热器仅在状态变化时生成记录，需要统计昨日6:00至今日6:00时段内的运行时长，需处理三种情况：

• 时段内包含完整的开关周期
• 时段起始时加热器已开启（无起始记录但第一条状态为0）
• 时段结束时加热器仍开启（最后一条状态为1，需计算至时段结束时间）

解决方案：单条SQL查询实现 #

使用CTE（公共表表达式）结合窗口函数LEAD来处理边界情况并计算总时长，具体查询如下：

-- 定义时段参数，可替换为具体日期或动态计算
DECLARE @start_time DATETIME = '2022-12-18 06:00:00';
DECLARE @end_time DATETIME = '2022-12-19 06:00:00';

WITH heater_events AS (
    -- 1. 获取时段内的加热器状态记录
    SELECT timestamp, pointvalue
    FROM [home_network].[dbo].[DataPoints]
    WHERE pointname = 'Heaters'
      AND timestamp BETWEEN @start_time AND @end_time
    
    UNION ALL
    
    -- 2. 处理边界：若时段开始前加热器已开启，添加起始时间的"开"状态记录
    SELECT @start_time AS timestamp, 1 AS pointvalue
    FROM (
        SELECT TOP 1 pointvalue
        FROM [home_network].[dbo].[DataPoints]
        WHERE pointname = 'Heaters'
          AND timestamp < @start_time
        ORDER BY timestamp DESC
    ) AS prev_state
    WHERE prev_state.pointvalue = 1
    
    UNION ALL
    
    -- 3. 处理边界：若时段结束时加热器仍开启，添加结束时间的"关"状态记录
    SELECT @end_time AS timestamp, 0 AS pointvalue
    FROM (
        SELECT TOP 1 pointvalue
        FROM [home_network].[dbo].[DataPoints]
        WHERE pointname = 'Heaters'
          AND timestamp BETWEEN @start_time AND @end_time
        ORDER BY timestamp DESC
    ) AS last_state
    WHERE last_state.pointvalue = 1
),
event_pairs AS (
    -- 用LEAD函数获取每条记录的下一个状态变更时间
    SELECT 
        timestamp AS start_time,
        LEAD(timestamp) OVER (ORDER BY timestamp) AS end_time,
        pointvalue
    FROM heater_events
)
-- 汇总所有"开"状态的持续时长（转换为分钟）
SELECT 
    SUM(DATEDIFF(MINUTE, start_time, end_time)) AS total_heater_run_minutes
FROM event_pairs
WHERE pointvalue = 1;

逻辑说明 #

1. heater_events CTE：

   • 基础数据：时段内的加热器状态记录
   • 补充边界1：如果时段开始前最后一次状态是"开"（1），添加起始时间的虚拟"开"记录，确保计算从时段开始到第一次关闭的时长
   • 补充边界2：如果时段内最后一次状态是"开"（1），添加结束时间的虚拟"关"记录，确保计算从最后一次开启到时段结束的时长

2. event_pairs CTE：

   • 使用LEAD函数为每条记录匹配下一次状态变更的时间，形成"开-关"或"关-开"的时间对

3. 最终统计：

   • 筛选所有"开"状态的时间对，用DATEDIFF计算每段时长，求和得到总运行分钟数

测试验证 #

针对你提供的示例数据，查询会自动计算：

• 2022-12-19 02:00:20 至 2022-12-19 02:22:22 → 22分钟
• 2022-12-19 03:43:28 至 2022-12-19 04:25:31 → 42分钟
• 总和为64分钟，与预期结果一致

动态时段优化 #

如果需要每日自动统计夜间时段，可将参数替换为动态计算：

DECLARE @start_time DATETIME = DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) + '06:00:00';
DECLARE @end_time DATETIME = CAST(GETDATE() AS DATE) + '06:00:00';

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