PostgreSQL / 系统课时

窗口函数与窗口帧

窗口函数与窗口帧

窗口函数让聚合结果与明细行同时存在:既能在分组内排名和比较,也能做累计值、移动平均等序列计算。本课重点区分“分区、排序、窗口帧”三个层次,并用一条可直接执行的 SQL 把它们组合起来。

学习目标

  • 能区分 PARTITION BY、窗口内 ORDER BY 与 frame 的职责
  • 能实现分组排名、累计、移动统计和组内均值比较
  • 能避免默认 RANGE 窗口帧在并列排序值下带来的意外结果

先分清窗口的三个层次

窗口函数不会像普通 GROUP BY 那样把多行压成一行。PARTITION BY 决定“和哪些行一起算”,窗口内 ORDER BY 决定“这些行按什么顺序算”,frame 则进一步限定“当前行这一次计算实际看到哪一段行”。

窗口分区、排序与窗口帧的作用范围

排名函数主要依赖分区和排序;SUM()、AVG() 这类聚合函数作为窗口函数使用时,是否累计或移动,则直接受 frame 影响。

一条 SQL 完成四类分析

下面的数据按销售员分组,每人有三天销售额。SQL 同时计算销售额排名、累计销售额、两行移动平均,以及当前销售额相对本组平均值的差值。

sql
WITH sales(rep, sale_day, amount) AS (
  VALUES
    ('A', DATE '2026-09-01', 100),
    ('A', DATE '2026-09-02', 150),
    ('A', DATE '2026-09-03', 50),
    ('B', DATE '2026-09-01', 80),
    ('B', DATE '2026-09-02', 120),
    ('B', DATE '2026-09-03', 100)
)
SELECT
  rep,
  sale_day,
  amount,
  DENSE_RANK() OVER (
    PARTITION BY rep
    ORDER BY amount DESC
  ) AS amount_rank,
  SUM(amount) OVER (
    PARTITION BY rep
    ORDER BY sale_day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROUND(AVG(amount) OVER (
    PARTITION BY rep
    ORDER BY sale_day
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ), 1) AS moving_avg_2,
  amount - ROUND(AVG(amount) OVER (
    PARTITION BY rep
  ), 0) AS diff_from_rep_avg
FROM sales
ORDER BY rep, sale_day;

预期输出

text
 rep |  sale_day  | amount | amount_rank | running_total | moving_avg_2 | diff_from_rep_avg
-----+------------+--------+-------------+---------------+--------------+-------------------
 A   | 2026-09-01 |    100 |           2 |           100 |        100.0 |                 0
 A   | 2026-09-02 |    150 |           1 |           250 |        125.0 |                50
 A   | 2026-09-03 |     50 |           3 |           300 |        100.0 |               -50
 B   | 2026-09-01 |     80 |           3 |            80 |         80.0 |               -20
 B   | 2026-09-02 |    120 |           1 |           200 |        100.0 |                20
 B   | 2026-09-03 |    100 |           2 |           300 |        110.0 |                 0

DENSE_RANK() 不减少明细行;累计和移动平均则通过不同的 ROWS frame 改变每一行可见的历史范围。最后一个 AVG() 没有窗口内排序,因此整个销售员分区都参与均值计算。

理解 ROWS、RANGE 与 GROUPS

ROWS 按物理行数扩展窗口,最适合“前 1 行、前 7 行”这类移动统计。RANGE 按排序值及其同值 peer 组理解边界;PostgreSQL 在存在窗口内 ORDER BY 且未显式写 frame 时,常见默认行为等价于从分区开头到当前排序值的最后一个 peer。GROUPS 则按 peer 组数量移动边界。

FIRST_VALUE()、LAST_VALUE()、NTH_VALUE() 也受 frame 影响,尤其 LAST_VALUE() 很容易因为默认 frame 只到当前 peer 组而得到“当前行附近的最后值”,而不是整个分区真正的最后值。

选择窗口定义的原则

先确定业务分组边界,再确定是否需要顺序,最后才决定 frame。只做组内均值比较时不需要窗口排序;做排名需要排序但通常不关心 frame;做累计和移动统计时则应把 frame 明确写出。这样 SQL 的含义更稳定,也更容易在代码评审中发现边界错误。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

窗口函数与窗口帧

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通