PostgreSQL窗口函数：不符合窗口条件的最新行问题排查与修复

• PostgreSQL窗口函数中的n PRECEDING是取排序后当前行之前的最多n行，而非强制要求必须存在n行。如果当前行是分区内倒序排序后的前12行（即最新的12条数据），前置行数量不足12时，窗口仍会用现有前置行（甚至0行）进行计算。
• 由于你按_1h DESC排序，最新行的9 FOLLOWING会包含之后的9条更早的数据。如果当前行的high大于这9条数据的最大high，或者low小于这9条数据的最小low，就会触发pivot_low或pivot_high为true，最终被筛选出来，不符合你“窗口必须有12行前置”的预期。

需要在查询中额外添加行号和总行数的判断，确保当前行的窗口范围能完整包含12个前置行和9个后置行。

修复后的示例查询 #

SELECT ticker, _1h, high, low, pivot_low, pivot_high
FROM (
    SELECT 
        ticker,
        _1h,
        high,
        low,
        -- 原窗口函数判断逻辑
        high > MAX(high) OVER (
            PARTITION BY ticker 
            ORDER BY _1h DESC 
            ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING
            EXCLUDE CURRENT ROW
        ) AS pivot_low,
        low < MIN(low) OVER (
            PARTITION BY ticker 
            ORDER BY _1h DESC 
            ROWS BETWEEN 12 PRECEDING AND 9 FOLLOWING
            EXCLUDE CURRENT ROW
        ) AS pivot_high,
        -- 获取当前行在分区内的倒序行号
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY _1h DESC) AS rn,
        -- 获取分区内的总行数
        COUNT(*) OVER (PARTITION BY ticker) AS total_rows
    FROM your_table  -- 替换为你的表名
) sub
WHERE (pivot_low OR pivot_high)
  -- 确保前面至少有12行
  AND rn > 12
  -- 确保后面至少有9行
  AND rn <= total_rows - 9;

修复逻辑说明 #

• rn > 12：保证当前行在分区倒序排序后的位置大于12，即前面有至少12行数据，满足12 PRECEDING的行数要求。
• rn <= total_rows -9：保证当前行后面至少有9行数据，满足9 FOLLOWING的行数要求。
• 只有同时满足这两个条件的行，才会进入最终筛选，确保窗口范围完整符合你的预期。

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