PostgreSQL 如何用 window frame clause 定义自定义范围求和

window frame clause用于定义窗口函数计算范围，默认RANGE按排序值重复包含多行，而ROWS按物理行数精确控制；实际应优先用ROWS避免重复键导致的统计偏差。

什么是 window frame clause，它和默认 frame 有什么区别

postgresql 的窗口函数默认使用 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是“从分区开头到当前行（按 ORDER BY 排序后）所有值求和”。但这个“所有值”可能包含重复排序键——比如多行 order_time = '2024-01-01 10:00'，它们会被一起纳入当前行的 frame，导致求和结果跳变。

ROWS frame 才真正按物理行偏移定义范围，比如 ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING 明确取前后固定行数，不受排序键重复影响。实际业务中，滚动平均、近 N 天累计、滑动窗口统计等，几乎都该用 ROWS，而不是依赖默认的 RANGE。

常见错误现象：SUM(val) OVER (ORDER BY ts) 在时间戳有重复时，sum 值突然增大一截，且无法预测跳变点。

如何用 ROWS 定义前 N 行滚动求和

这是最常用场景：对每个时间点，计算它及之前 2 行的 amount 总和。

SELECT   id,   ts,   amount,   SUM(amount) OVER (     ORDER BY ts, id     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW   ) AS sum_3_rows FROM sales;

关键点：

• ORDER BY 必须明确、确定（加 id 是为避免 ts 相同时排序不稳定）
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示“当前行 + 它前面 2 行”，共 3 行
• 开头几行会自动截断：第 1 行只有自身，第 2 行有前 1 行 + 自身，第 3 行才满 3 行

如果想强制补零或补 NULL，得额外用 CASE WHEN ROW_NUMBER() OVER (...) 判断，PostgreSQL 不支持 frame 内自动填充。

如何实现“过去 7 天”动态时间窗口求和（非固定行数）

ROWS 只认行号，不认时间；要按时间跨度算，必须回到 RANGE，但需规避重复键陷阱：

SELECT   dt,   sales,   SUM(sales) OVER (     ORDER BY dt     RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW   ) AS sum_7days FROM daily_sales;

注意事项：

• dt 必须是 date 或 timestamp 类型，否则 INTERVAL 会报错
• 如果 dt 有重复（如多条同日记录），RANGE 会把当天所有行全包进来，可能导致单日权重过大
• 更稳健做法：先用 GROUP BY dt 聚合每日总量，再在聚合后结果上开窗口

frame clause 中 CURRENT ROW、UNBOUNDED 的行为细节

CURRENT ROW 指当前正在计算的那行，不是“当前排序位置的首行”；UNBOUNDED PRECEDING 表示无上限起点，但它的“ preceding ”是相对于 ORDER BY 序列的，不是表物理顺序。

容易踩的坑：

• ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING 是合法的，表示当前行 + 下一行
• ROWS BETWEEN 1 FOLLOWING AND 1 PRECEDING 是空集（起始 > 结束），结果为 NULL
• UNBOUNDED FOLLOWING 可以用，但搭配 CURRENT ROW 时逻辑易混淆，例如 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 就是“当前行到分区末尾”

frame 定义一旦写错，PostgreSQL 不报语法错，但结果可能全错且难以调试——建议始终用小数据集 LIMIT 10 验证输出是否符合预期。

窗口 frame 看似简单，但 RANGE 和 ROWS 的语义差异、重复排序键、边界截断规则，三者叠加就很容易产出隐蔽的统计偏差。动手前先确认你到底要“按时间跨度”还是“按行数滚动”，选错 frame 类型比写错表达式更难排查。