PostgreSQL提取连续满足值条件的日期起止与段内最大值

连续满足阈值的时序分段统计属于经典的间隙与岛屿（Gaps and Islands）场景，使用窗口函数实现是PostgreSQL下最高效可靠的方案，可避免多层子查询带来的逻辑混乱和性能问题。

核心实现逻辑 #

• 按采集时间升序遍历所有记录，为每条记录标记是否满足value > 170的阈值条件
• 通过窗口函数累计统计遍历到当前行时，不满足阈值条件的总记录数：所有连续满足阈值的记录，这个累计值完全一致，可作为同一连续分段的唯一分组标识
• 过滤掉不满足阈值的记录后，按分组标识聚合计算，即可直接得到每个分段的起始时间、结束时间和段内最大值

实现代码 #

以下代码可直接运行，生产环境替换为实际业务表名即可：

-- 测试数据初始化（业务环境使用时可跳过本段）
CREATE TEMP TABLE time_series_collect (
    date timestamp,
    value numeric
);
INSERT INTO time_series_collect VALUES
('2022-02-07 15:30:30', 169.6),
('2022-02-07 15:30:55', 171),
('2022-02-07 15:31:10', 170.9),
('2022-02-07 15:31:50', 171.1),
('2022-02-07 15:32:00', 172),
('2022-02-07 15:32:45', 168),
('2022-02-07 15:33:20', 168.7),
('2022-02-07 15:34:10', 173.7),
('2022-02-07 15:34:55', 171.5),
('2022-02-07 15:35:20', 171.7),
('2022-02-07 15:36:05', 163.5);

-- 核心查询逻辑
WITH marked_segment AS (
    SELECT
        date,
        value,
        SUM(CASE WHEN value <= 170 THEN 1 ELSE 0 END) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS segment_id
    FROM time_series_collect
)
SELECT
    MIN(date) AS date_start,
    MAX(date) AS date_end,
    MAX(value) AS value_max
FROM marked_segment
WHERE value > 170
GROUP BY segment_id
ORDER BY date_start;

执行结果 #

运行上述代码将完全返回预期结果：

性能说明 #

• 该实现仅需对表做一次窗口遍历+一次聚合计算，时间复杂度为O(n)，百万级以上时序数据也可稳定运行
• 为时间字段建立B树索引可进一步提升窗口排序阶段的执行效率

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