PostgreSQL滑动窗口求列最值：最优实现方式咨询

核心结论 #

针对百万级时序数据的滑动窗口分析，窗口函数+CTE是最优方案，远优于循环/游标。SQL是声明式语言，集合操作能借助PostgreSQL优化器高效处理数据，彻底避免逐行循环带来的性能损耗。

需求转化与实现思路 #

你的逻辑本质是：对每个设备（column1），检查每行是否满足以下任一条件：

• 该行column2是其前12行+自身+后9行窗口内的最大值；
• 该行column3是该窗口内的最小值。

由于数据时间间隔固定为5分钟，按时间排序后行的位置直接对应时间顺序，用窗口函数可精准定义窗口范围，无需手动循环调整时间区间。

完整脚本示例 #

1. 计算窗口最值并筛选符合条件的行 #

WITH ranked_data AS (
    SELECT
        column1,
        date_time,
        column2,
        column3,
        -- 按设备分组、时间升序，定义窗口：前12行到后9行
        MAX(column2) OVER (
            PARTITION BY column1
            ORDER BY date_time ASC
            ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING
        ) AS window_max_column2,
        MIN(column3) OVER (
            PARTITION BY column1
            ORDER BY date_time ASC
            ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING
        ) AS window_min_column3
    FROM table1
    WHERE column1 IN ('cpu1') -- 可扩展为多个设备，或删除此条件处理全量设备
),
filtered_rows AS (
    SELECT
        column1,
        date_time,
        column2,
        column3
    FROM ranked_data
    -- 判断当前行是否满足最值条件
    WHERE (column2 = window_max_column2) OR (column3 = window_min_column3)
)
-- 将符合条件的行插入新表
INSERT INTO new_table (column1, date_time, column2, column3)
SELECT * FROM filtered_rows;

2. 关键细节说明 #

• 窗口范围精准匹配：ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING完全对应你描述的“上方12行、下方9行”的窗口范围，包含当前行共22条数据。
• 性能优化建议：给table1创建复合索引(column1, date_time)，分组和排序时可快速定位数据，避免全表扫描。
• 时间窗口替代方案：若需基于时间范围而非行数（比如前60分钟到后45分钟），可替换为时间窗口：

  MAX(column2) OVER (
      PARTITION BY column1
      ORDER BY date_time ASC
      RANGE BETWEEN INTERVAL '60 minutes' PRECEDING AND INTERVAL '45 minutes' FOLLOWING
  ) AS window_max_column2
  

为什么不推荐循环/游标？ #

• 性能极差：百万级数据逐行循环会产生大量IO和上下文切换，执行时间是窗口函数的几十甚至上百倍。
• 代码冗余易出错：循环/游标需要编写复杂的控制逻辑，维护成本高，且容易出现边界错误。
• 违背SQL设计思想：SQL擅长集合操作，循环是过程式思维，无法发挥数据库的优化能力。

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