SQL Server实现IoT连续相邻行合并为单行的可行性与实现方法咨询

• 完全具备可行性：该场景属于SQL领域经典的*间隙与孤岛（Gaps and Islands）*连续序列识别问题，SQL Server 2008及以上版本都支持所需的窗口函数能力。
• 性能提升非常显著：
  • 省去了全量数据从数据库到应用侧的跨网络传输开销，TB级数据下该部分开销占比可超过70%
  • SQL Server内置的窗口函数经过底层优化，运算效率远高于应用层逐行迭代逻辑
  • 可通过创建覆盖索引进一步压缩查询耗时，无需修改表结构即可完成优化

前提假设 #

先约定表结构（可根据你的实际字段调整）：
表名：IotDeviceReport
核心字段：

• DeviceID varchar(64) ：设备唯一标识
• ReportTime datetime2(0) ：数据上报时间
• CollectValue int ：采集的计数器数值，可替换为你实际的采集字段

实现逻辑 #

核心思路是给每个连续序列的行分配相同的分组ID：按设备分组后，每行的上报时间减去「该行在设备内的上报顺序数*1分钟」，如果两行属于同一个连续序列（相邻差1分钟），得到的计算值会完全相同，以此作为分组依据聚合即可。

完整SQL代码 #

WITH RankedData AS (
    -- 第一步：给每个设备的上报记录按时间排序，生成序号
    SELECT 
        DeviceID,
        ReportTime,
        CollectValue,
        ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY ReportTime) AS RowNum
    FROM IotDeviceReport
    -- 可在这里加筛选条件，比如只统计某个时间段的数：WHERE ReportTime BETWEEN '2024-01-01' AND '2024-02-01'
),
GroupedData AS (
    -- 第二步：计算分组ID，连续的行分组ID相同
    SELECT 
        *,
        DATEADD(MINUTE, -RowNum, ReportTime) AS GroupID
    FROM RankedData
)
-- 第三步：按分组聚合，得到每个连续时间段的统计结果
SELECT 
    DeviceID,
    MIN(ReportTime) AS PeriodStartTime, -- 连续时间段开始时间
    MAX(ReportTime) AS PeriodEndTime, -- 连续时间段结束时间
    DATEDIFF(MINUTE, MIN(ReportTime), MAX(ReportTime)) + 1 AS DurationMinutes, -- 连续时长（分钟）
    COUNT(*) AS ReportCount, -- 该时间段内上报次数
    MAX(CollectValue) - MIN(CollectValue) AS CounterIncrement -- 计数器增量，按需修改聚合逻辑
FROM GroupedData
GROUP BY DeviceID, GroupID
ORDER BY DeviceID, PeriodStartTime;

性能优化建议 #

创建覆盖索引可让查询直接走索引扫描，无需回表，性能提升10倍以上：

CREATE NONCLUSTERED INDEX IX_IotDeviceReport_DeviceTime ON IotDeviceReport (DeviceID, ReportTime)
INCLUDE (CollectValue); -- 把你需要聚合的其他采集字段都加到INCLUDE里

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