如何在PostgreSQL中高效聚合连续ongoing_sequence的时间数据？

核心思路 #

针对同一name下连续的ongoing_sequence=1记录聚合需求，采用**间隙与岛屿（Gap & Island）**经典算法，结合窗口函数+聚合操作实现，全程基于SQL原生优化，无迭代逻辑，保障高性能。

实现SQL #

WITH processed_records AS (
    -- 预处理每条记录的difference：duration>1分钟则设为0
    SELECT
        name,
        start,
        end,
        CASE
            WHEN EXTRACT(EPOCH FROM (end - start)) > 60 THEN 0
            ELSE difference
        END AS adjusted_difference,
        ongoing_sequence
    FROM your_table_name
),
grouped_islands AS (
    -- 为每个连续的ongoing_sequence=1组分配唯一ID
    SELECT
        *,
        COUNT(CASE WHEN ongoing_sequence != 1 THEN 1 END) OVER (
            PARTITION BY name
            ORDER BY start
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS island_group_id
    FROM processed_records
    WHERE ongoing_sequence = 1 -- 仅处理目标记录，减少计算量
)
-- 按组聚合得到最终结果
SELECT
    name,
    MIN(start) AS min_start,
    MAX(end) AS max_end,
    SUM(adjusted_difference) AS total_difference
FROM grouped_islands
GROUP BY name, island_group_id
ORDER BY name, min_start;

关键部分说明 #

1. 预处理阶段：
   • 用EXTRACT(EPOCH FROM (end - start))计算时间差秒数，判断是否超过60秒，将符合条件的difference置为0，严格匹配业务规则。
2. 岛屿分组阶段：
   • 窗口函数COUNT(...) OVER (...)在name分区内按start排序，每遇到一条ongoing_sequence !=1的记录，就为后续的1记录分配递增的组ID，自动将连续的1记录归为同一组。
   • 提前过滤ongoing_sequence=1的记录，减少后续聚合计算的数据量。
3. 聚合阶段：
   • 按name和island_group_id分组，直接取最小start、最大end，汇总调整后的difference，得到目标输出。

示例验证 #

针对你给出的测试场景：

• test1的3条连续1记录、后续2条连续1记录会被分成两个独立组，分别输出聚合结果
• test2的2条连续1记录会被合并为一条聚合记录

该方案利用PostgreSQL对窗口函数和聚合的原生优化，适合大表场景，无需自定义函数或迭代逻辑，性能优异。

内容的提问来源于stack exchange，提问作者robert.oh.