基于时间差将时序数据合并为带起止区间的数据点

解法原理：间隙与孤岛（Gaps and Islands） #

你遇到的问题属于典型的时序数据孤岛聚合场景——需要把同一设备、相同错误码的连续时间区间合并。之前用LAG()/LEAD()没成功，是因为没给每个连续组生成唯一标识。核心步骤是：

1. 标记每个连续组的起始行：按设备、错误码分区，按时间排序，对比当前行与上一行的错误码，不同则标记为新组起点。
2. 生成组ID：累加起始标记，为每个连续组分配唯一ID。
3. 分组聚合：按设备、错误码、组ID分组，取每组的最早和最晚时间，得到合并后的起止区间。

具体实现SQL #

假设你的测试表结构如下：

CREATE TABLE device_errors (
    device_id TEXT,
    code INT,
    time TIMESTAMPTZ NOT NULL
);
-- 创建TimescaleDB超表优化时序数据查询
SELECT create_hypertable('device_errors', 'time');

完整聚合查询语句：

WITH grouped_data AS (
    SELECT
        device_id,
        code,
        time,
        -- 标记新组起始：当前行code与上一行不同或为第一行时，标记为1
        CASE
            WHEN LAG(code) OVER (PARTITION BY device_id ORDER BY time) != code
                OR LAG(code) OVER (PARTITION BY device_id ORDER BY time) IS NULL
            THEN 1
            ELSE 0
        END AS is_new_group,
        -- 累加起始标记生成唯一组ID
        SUM(
            CASE
                WHEN LAG(code) OVER (PARTITION BY device_id ORDER BY time) != code
                    OR LAG(code) OVER (PARTITION BY device_id ORDER BY time) IS NULL
                THEN 1
                ELSE 0
            END
        ) OVER (PARTITION BY device_id ORDER BY time) AS group_id
    FROM device_errors
)
SELECT
    device_id,
    code,
    MIN(time) AS start_time,
    MAX(time) AS end_time,
    -- 可选：计算连续区间的总持续时长（按每300秒一条数据计算）
    COUNT(*) * 300 AS total_duration_seconds
FROM grouped_data
GROUP BY device_id, code, group_id
ORDER BY device_id, start_time;

关键细节说明 #

• 分区逻辑：仅按device_id分区，再通过LAG(code)判断错误码连续性，比同时按device_id和code分区更准确——后者会把同一设备、相同错误码的非连续区间（中间插了其他错误码）归为同一分区，无法区分间隙。
• TimescaleDB适配：由于使用超表，按time排序和分组的性能会自动得到优化，无需额外调整。
• 边界处理：通过LAG(...) IS NULL处理每个设备的第一行数据，确保初始行被正确标记为新组起点。

是否需要使用pgSQL函数？ #

不需要。这种场景下纯SQL的间隙孤岛解法已经足够高效，且更易于维护、适配TimescaleDB的时序优化特性。自定义pgSQL函数反而会增加复杂度，且无法利用TimescaleDB针对批量聚合的内置优化，性能不如纯SQL方案。

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