窗口函数 · 不合并行的分组统计 · PostgreSQL 特性详解

窗口函数为每一行计算「它在所属分组里的排名 / 相对值 / 累计」，却不像 GROUP BY 那样把行合并掉。排名、环比同比、累计销售、每组 Top N、滑动平均——这些「以为得写代码循环」的活，一条 SQL 就干完了。

窗口函数 · 不合并行的分组统计

GROUP BY 有个天生的限制：它把每一组压成一行。所以你要么拿明细、要么拿汇总，没法在一行里既看到明细，又看到它在组里的位置。

窗口函数(Window Function)就是来补这个缺口的：它对每一行计算「在一个窗口（分组）里，这一行的排名 / 相对值 / 累计」，但保留所有明细行。排名、环比、累计、每组 Top N、滑动平均——这些经典需求，它一招通吃。

OVER：窗口函数的核心语法

窗口函数 = 一个函数 + 一个 OVER (...) 子句。OVER 里两件事最关键：PARTITION BY（按什么分组）和 ORDER BY（组内按什么排序）。

-- 每个 region 内,按金额从高到低编号
SELECT region, month, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales;

East  m3 140 rn=1     North m2 150 rn=1     South m3 220 rn=1
East  m2 120 rn=2     North m3 130 rn=2     South m1 200 rn=2
East  m1  80 rn=3     North m1 100 rn=3     South m2 180 rn=3

注意：每个 region 各自从 1 开始编号，而所有明细行都还在。这就是窗口函数和 GROUP BY 的根本区别。

四类常用窗口函数

① 排名类 —— ROW_NUMBER（严格 1,2,3）、RANK（并列跳号 1,1,3）、DENSE_RANK（并列不跳 1,1,2）、NTILE(n)（分成 n 桶）。

② 偏移类 —— LAG / LEAD 取前 / 后 N 行的值，做环比同比的利器：

SELECT region, month, amount,
       LAG(amount)  OVER w AS prev,
       LEAD(amount) OVER w AS next
FROM sales WINDOW w AS (PARTITION BY region ORDER BY month);

North m1 amount=100 prev=∅   next=150
North m2 amount=150 prev=100 next=130
North m3 amount=130 prev=150 next=∅

③ 聚合当窗口 —— 把 SUM / AVG / MAX 加上 OVER，就成了「累计 / 分组内聚合但不合并行」：

-- 累计销售额(从组首累加到当前行)
SUM(amount) OVER (PARTITION BY region ORDER BY month
                  ROWS UNBOUNDED PRECEDING)
-- North: 100 → 250 → 380

④ 滑动窗口 —— 用框架(frame) 限定「参与计算的行范围」，算移动平均：

-- 3 个月滑动平均(前 1 行 + 当前 + 后 1 行)
AVG(amount) OVER (PARTITION BY region ORDER BY month
                  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
-- North: 125.0 → 126.7 → 140.0

框架有 ROWS 和 RANGE 两种，别混：ROWS 按物理行数算（「前 1 行」就是紧邻的上一行）；RANGE 按 ORDER BY 列的值算（「前 1」指值差在范围内的所有行，并列值会被算作一体）。做「精确的 N 行滑窗」用 ROWS；做「按金额 / 时间的值区间累计」用 RANGE。不写框架时，有 ORDER BY 的默认框架是 RANGE UNBOUNDED PRECEDING——这常常不是你以为的那个，踩过坑的人都记得。

经典套路：每组 Top N

「每个 region 卖得最好的那一单」，用窗口函数 + 子查询是标准解法——先编号，再过滤：

SELECT region, month, amount FROM (
  SELECT region, month, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
) t WHERE rn = 1;
-- East: m3=140 | North: m2=150 | South: m3=220

把 rn = 1 改成 rn <= 3 就是「每组 Top 3」。这个「窗口编号 + 外层过滤」的套路，是日常查询里出现频率极高的一招，值得背下来。（数据量大时，PG 还有更快的 DISTINCT ON 或 LATERAL 写法，见 LATERAL 一章。）

横向对比

能力	PostgreSQL	MySQL	Oracle	SQL Server
窗口函数	PG 8.4 起完整支持	8.0 才有（5.x 没有）	很早就有	2005 起
框架 `ROWS` / `RANGE`	齐全	8.0+	齐全	齐全
`GROUPS` 框架模式	PG 11+	8.0+	有	部分

窗口函数是 SQL 标准，如今主流库都支持。值得记住的是：MySQL 直到 8.0(2018)才有窗口函数——在那之前，MySQL 用户算个「组内排名」都得靠用户变量玩 hack，而 PostgreSQL 从 8.4(2009)就完整支持了。这十年差距，是很多老 MySQL 项目里那些绕来绕去的排名 SQL 的由来。

应用场景

排行榜 / 分组排名：每个品类销量前十、每个班级成绩排名。
环比同比：LAG / LEAD 取上期数据，当场算增长率。
累计与占比：累计销售额、amount / SUM(amount) OVER () 算每行占总量的比例。
去重保留每组最新：ROW_NUMBER() ... ORDER BY updated_at DESC 编号后取 rn = 1。
滑动平均 / 趋势平滑：监控指标、股价、传感器读数的移动平均。

小结与下一章

窗口函数的精髓是「算出每行在组里的位置，但不丢明细」。掌握 PARTITION BY + ORDER BY + 框架这三件套，排名、环比、累计、Top N、滑窗就都是一条 SQL 的事，不用把数据捞到应用层再循环。

下一章看两个「进一步撑大查询表达力」的特性：LATERAL 让子查询能引用左表的列（每组 Top N 的另一种更快写法），GROUPING SETS 让一条 SQL 同时输出多种粒度的小计与总计。👉 LATERAL 与 GROUPING SETS：把「每组取 N」和「透视表」写进一条 SQL。

参考文献

[1] PostgreSQL 官方文档.「3.5. Window Functions(Tutorial).」https://www.postgresql.org/docs/current/tutorial-window.html —— 概念入门。
[2] PostgreSQL 官方文档.「4.2.8. Window Function Calls.」https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS —— OVER、PARTITION、框架语法。
[3] PostgreSQL 官方文档.「9.22. Window Functions.」https://www.postgresql.org/docs/current/functions-window.html —— row_number / rank / lag / lead / ntile 等内置函数。

窗口函数 · 不合并行的分组统计

本页目录