Gaps and Islands方案后续：传感器开机状态日志增量处理需求

这是此前《SQL Server中游标相关问题》的后续需求：每10秒采集传感器状态（0=关闭，1=开启），需在独立日志表记录传感器的开机时段，且每分钟批量处理一次数据（每次处理6条）。此前采用@Charlieface提供的无游标Gaps and Islands方案已能正常生成初始日志，但现需优化增量数据处理逻辑，兼容日志表中Endtime为NULL的未关闭开机条目，具体规则如下：

• 若后续新增数据全为1：无需对日志表做任何操作
• 若后续出现状态0：更新对应未关闭条目的Endtime为该0状态的最早时间戳
• 若传感器再次变为1：插入新的开机日志行（Endtime暂设为NULL）

原始全量处理方案（回顾） #

WITH cte1 AS (
    SELECT *,
      PrevValue =  LAG(t.Value) OVER (PARTITION BY t.SlaveID, t.Register ORDER BY t.Timestamp)
    FROM YourTable t
),
cte2 AS (
    SELECT *,
      NextTime = LEAD(t.Timestamp) OVER (PARTITION BY t.SlaveID, t.Register ORDER BY t.Timestamp)
    FROM cte1 t
    WHERE (t.Value <> t.PrevValue OR t.PrevValue IS NULL)
)
SELECT
  t.SlaveID,
  t.Register,
  StartTime = t.Timestamp,
  Endtime = t.NextTime
FROM cte2 t
WHERE t.Value = 1;

增量处理优化方案 #

假设原始传感器数据表为SensorData，日志表为SensorOnLog（结构：SlaveID, Register, StartTime, Endtime），以下是每分钟执行一次的增量处理逻辑：

1. 更新未关闭的开机日志

当新增数据中出现传感器的0状态时，找到对应未关闭的日志条目，将其Endtime更新为该0状态的最早时间戳：

UPDATE log
SET Endtime = (
    SELECT MIN(s.Timestamp)
    FROM SensorData s
    WHERE s.SlaveID = log.SlaveID
      AND s.Register = log.Register
      AND s.Value = 0
      -- 限定本次处理的一分钟数据范围
      AND s.Timestamp >= DATEADD(MINUTE, -1, GETDATE())
)
FROM SensorOnLog log
WHERE log.Endtime IS NULL
  AND EXISTS (
    SELECT 1
    FROM SensorData s
    WHERE s.SlaveID = log.SlaveID
      AND s.Register = log.Register
      AND s.Value = 0
      AND s.Timestamp >= DATEADD(MINUTE, -1, GETDATE())
);

2. 插入新的开机日志

识别新增数据中传感器从0切换为1的起始点（或第一条数据即为1且无未关闭日志），插入新的开机记录：

WITH NewData AS (
    SELECT 
        s.SlaveID,
        s.Register,
        s.Timestamp,
        s.Value,
        PrevValue = LAG(s.Value) OVER (PARTITION BY s.SlaveID, s.Register ORDER BY s.Timestamp)
    FROM SensorData s
    WHERE s.Timestamp >= DATEADD(MINUTE, -1, GETDATE())
),
StartPoints AS (
    SELECT 
        SlaveID,
        Register,
        Timestamp AS StartTime
    FROM NewData
    WHERE Value = 1
      AND (PrevValue IS NULL OR PrevValue = 0)
      -- 确保当前传感器无未关闭的开机日志
      AND NOT EXISTS (
          SELECT 1
          FROM SensorOnLog log
          WHERE log.SlaveID = NewData.SlaveID
            AND log.Register = NewData.Register
            AND log.Endtime IS NULL
      )
)
INSERT INTO SensorOnLog (SlaveID, Register, StartTime, Endtime)
SELECT SlaveID, Register, StartTime, NULL
FROM StartPoints;

注意事项 #

• 若需更精准的处理范围，建议维护一个LastProcessTime表记录上次处理的截止时间，替代DATEADD(MINUTE, -1, GETDATE())，避免重复处理或遗漏数据
• 可将上述两个步骤封装为存储过程，通过SQL Server代理作业每分钟执行一次

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