PostgreSQL / 系统课时
窗口函数与窗口帧
窗口函数与窗口帧
窗口函数让聚合结果与明细行同时存在:既能在分组内排名和比较,也能做累计值、移动平均等序列计算。本课重点区分“分区、排序、窗口帧”三个层次,并用一条可直接执行的 SQL 把它们组合起来。
学习目标
- 能区分
PARTITION BY、窗口内ORDER BY与 frame 的职责 - 能实现分组排名、累计、移动统计和组内均值比较
- 能避免默认
RANGE窗口帧在并列排序值下带来的意外结果
先分清窗口的三个层次
窗口函数不会像普通 GROUP BY 那样把多行压成一行。PARTITION BY 决定“和哪些行一起算”,窗口内 ORDER BY 决定“这些行按什么顺序算”,frame 则进一步限定“当前行这一次计算实际看到哪一段行”。

排名函数主要依赖分区和排序;SUM()、AVG() 这类聚合函数作为窗口函数使用时,是否累计或移动,则直接受 frame 影响。
一条 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;预期输出
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 | 0DENSE_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 的含义更稳定,也更容易在代码评审中发现边界错误。