PHP小码农

Appearance

OVER 子句详解 ​

本节把 OVER 子句拆开讲透：PARTITION BY 怎么切分区、ORDER BY 怎么定顺序、WINDOW 命名子句怎么复用窗口定义、多个窗口函数如何共用一次排序，以及默认帧（Default Frame）带来的隐蔽语义差异。学完本节，你将能准确写出每个窗口函数的窗口范围，不再靠猜。

前置知识

阅读本节前，建议先了解：窗口函数概述

基础概念 ​

OVER 子句（OVER Clause） 紧跟在窗口函数名与参数之后，用来描述「这个函数在哪些行、以什么顺序、按什么范围计算」。它由三块可选内容组成：分区（PARTITION BY）、排序（ORDER BY）、帧（ROWS / RANGE / GROUPS）。

概念术语说明
OVER 子句OVER Clause窗口函数的作用域声明
分区PartitionPARTITION BY 切分出的互不相交的组
窗口排序Window OrderingORDER BY 定义的窗口内行序
帧Frame分区内参与计算的连续行区间
默认帧Default Frame不写帧子句时系统采用的范围
命名窗口Named Window用 WINDOW 子句命名的可复用窗口定义
窗口继承Window Inheritance命名窗口可以被引用并追加 ORDER BY
同行集Peer Group排序键取值相同的行

三块内容的可选性与语义：

组成是否必填不写时的行为写了以后的影响
PARTITION BY可选整个结果集视为一个分区计算在分区内独立封闭
ORDER BY可选行序未定义，帧为整个分区帧变为「分区起点到当前行」
帧子句可选有 ORDER BY 时为 RANGE UNBOUNDED PRECEDING AND CURRENT ROW；无 ORDER BY 时为整个分区精确控制参与计算的行区间
WINDOW 子句可选每个函数各自写完整定义复用定义、减少重复书写

默认帧的两种情况必须记牢，这是窗口函数最经典的坑：

窗口定义默认帧效果
OVER ()整个分区sum 返回全表总和，每行相同
OVER (PARTITION BY city)整个分区sum 返回该城市总和，组内每行相同
OVER (ORDER BY created_at)分区起点 → 当前行sum 返回累计和，逐行递增
OVER (PARTITION BY city ORDER BY created_at)分区起点 → 当前行sum 返回城市内累计和
sql
-- 同一张表，三种 OVER 写法，三个完全不同的结果
SELECT
  o.id,
  o.amount,
  sum(o.amount) OVER ()                                   AS 全表总额,
  round(sum(o.amount) OVER (PARTITION BY u.city), 2)      AS 城市总额,
  round(sum(o.amount) OVER (
    PARTITION BY u.city ORDER BY o.created_at, o.id
  ), 2)                                                   AS 城市内累计
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.city = '杭州' AND o.status <> 'cancelled'
ORDER BY o.created_at, o.id
LIMIT 5;
text
 id | amount |   全表总额    |  城市总额  | 城市内累计
----+--------+---------------+------------+------------
  5 | 512.30 | 247362941.08  | 21991099.2 |     512.30
 10 | 108.90 | 247362941.08  | 21991099.2 |     621.20
 15 | 776.40 | 247362941.08  | 21991099.2 |    1397.60
 20 | 233.10 | 247362941.08  | 21991099.2 |    1630.70
 25 | 640.50 | 247362941.08  | 21991099.2 |    2271.20
(5 rows)

语法与用法 ​

text
函数名(参数) OVER (
  [PARTITION BY 表达式 [, ...]]
  [ORDER BY 表达式 [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
  [帧子句]
)

-- 命名窗口
SELECT
  函数名(参数) OVER 窗口名,
  函数名(参数) OVER (窗口名 ORDER BY ...)
FROM ...
[WINDOW 窗口名 AS (PARTITION BY ... [ORDER BY ...] [帧子句]) [, ...];

准备本节贯穿使用的示例表：

sql
CREATE TABLE users (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username   TEXT NOT NULL,
  city       TEXT NOT NULL,
  status     TEXT NOT NULL,
  created_at DATE NOT NULL
);

CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT NOT NULL,
  user_id    INTEGER NOT NULL REFERENCES users (id),
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT NOT NULL,
  created_at DATE NOT NULL
);

CREATE INDEX ON users (city);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (status, amount DESC);
sql
INSERT INTO users (username, city, status, created_at)
SELECT
  'user_' || i,
  (ARRAY['北京', '上海', '广州', '深圳', '杭州'])[1 + (i % 5)],
  CASE WHEN i % 20 = 0 THEN 'inactive' ELSE 'active' END,
  DATE '2024-01-01' + (i % 365)
FROM generate_series(1, 20000) AS i;

INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT
  'SO' || lpad(i::text, 8, '0'),
  1 + (i % 20000),
  round((random() * 900 + 100)::numeric, 2),
  (ARRAY['pending', 'paid', 'shipped', 'cancelled'])[1 + (i % 4)],
  DATE '2024-06-01' + (i % 400)
FROM generate_series(1, 200000) AS i;

ANALYZE users;
ANALYZE orders;

详细说明 ​

PARTITION BY 的粒度与省略 ​

PARTITION BY 决定计算的封闭范围。粒度越细，组内行数越少，计算越快；粒度越粗，越接近全表聚合。

sql
-- 粒度 1：全表（最粗）
SELECT order_no, amount,
       rank() OVER (ORDER BY amount DESC) AS 全表排名
FROM orders
LIMIT 3;

-- 粒度 2：按状态
SELECT order_no, status, amount,
       rank() OVER (PARTITION BY status ORDER BY amount DESC) AS 状态内排名
FROM orders
LIMIT 3;

-- 粒度 3：按用户
SELECT order_no, user_id, amount,
       rank() OVER (PARTITION BY user_id ORDER BY amount DESC) AS 用户内排名
FROM orders
LIMIT 3;
分区粒度分区数量级单分区行数典型用途
不分区120 万全表排名、全表累计
按状态4约 5 万同状态内比较
按城市5约 4 万城市榜
按用户2 万约 10用户内 Top-N

省略 PARTITION BY 时要格外小心：它意味着「所有参与计算的行属于同一组」，很容易写出语义不符的 SQL：

sql
-- 错误！本意是「每笔订单占本城市总额的比例」，漏了分区就成了全表比例
SELECT u.city, o.order_no,
       round(100.0 * o.amount / sum(o.amount) OVER (), 2) AS 占比
FROM orders o JOIN users u ON u.id = o.user_id
LIMIT 3;

-- 正确
SELECT u.city, o.order_no,
       round(100.0 * o.amount / sum(o.amount) OVER (PARTITION BY u.city), 2) AS 占比
FROM orders o JOIN users u ON u.id = o.user_id
LIMIT 3;

分区列可以同时出现在 SELECT 中，这与 GROUP BY 不同——GROUP BY 后只能选分组键，而窗口函数不影响输出列。

ORDER BY 的作用与 NULL 位置 ​

ORDER BY 在 OVER 内部只决定行序，不影响输出顺序（输出顺序仍需在最外层写 ORDER BY）。行序对排名函数是必需的，对聚合窗口函数则会改变默认帧。

sql
-- 只排名，不改变输出顺序
SELECT order_no, amount,
       row_number() OVER (ORDER BY amount DESC) AS rn
FROM orders
LIMIT 5;
-- 输出顺序由这里的 LIMIT 前的执行计划决定，不保证按 rn 排列
sql
-- 正确：最外层补 ORDER BY
SELECT order_no, amount,
       row_number() OVER (ORDER BY amount DESC) AS rn
FROM orders
ORDER BY rn
LIMIT 5;
text
  order_no  | amount | rn
------------+--------+----
 SO00019xxx | 999.99 |  1
 SO00017xxx | 999.99 |  2
 SO00011xxx | 999.98 |  3
 SO00009xxx | 999.97 |  4
 SO00005xxx | 999.96 |  5
(5 rows)

NULL 在窗口排序中遵循 PostgreSQL 的默认规则：

排序方向默认 NULL 位置可用修饰
ASC最后（NULLS LAST）NULLS FIRST
DESC最前（NULLS FIRST）NULLS LAST
sql
-- 默认：DESC 时 NULL 排最前，占了 1~3 名
SELECT order_no, discount,
       row_number() OVER (ORDER BY discount DESC) AS rn
FROM orders
LIMIT 5;

-- 推荐：显式把 NULL 排在最后
SELECT order_no, discount,
       row_number() OVER (ORDER BY discount DESC NULLS LAST) AS rn
FROM orders
LIMIT 5;

排序键必须能唯一定序，否则结果不确定。row_number 尤其如此：

sql
-- 不推荐：金额相同的行，序号每次执行可能不同
row_number() OVER (ORDER BY amount DESC)

-- 推荐：补唯一键做决胜条件
row_number() OVER (ORDER BY amount DESC, id)

ORDER BY 是否加在窗口内，是 sum 语义的分水岭：

sql
SELECT
  id,
  amount,
  round(sum(amount) OVER (), 2)                     AS 全表总额,
  round(sum(amount) OVER (ORDER BY id), 2)          AS 累计金额,
  round(avg(amount) OVER (ORDER BY id), 2)          AS 累计均值
FROM orders
ORDER BY id
LIMIT 5;
text
 id | amount |   全表总额    | 累计金额  | 累计均值
----+--------+---------------+-----------+----------
  1 | 736.10 | 247362941.08  |    736.10 |   736.10
  2 | 180.20 | 247362941.08  |    916.30 |   458.15
  3 | 654.60 | 247362941.08  |   1570.90 |   523.63
  4 | 703.70 | 247362941.08  |   2274.60 |   568.65
  5 | 512.30 | 247362941.08  |   2786.90 |   557.38
(5 rows)

WINDOW 命名子句与窗口继承 ​

当一条查询里有多个窗口函数时，重复书写相同的 OVER 定义既冗长又容易写错。WINDOW 子句允许给窗口定义命名，然后多次引用。

sql
-- 不推荐：同样的窗口定义写了三遍
SELECT
  order_no,
  row_number() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS 序号,
  rank()       OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS 排名,
  dense_rank() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS 密集排名
FROM orders
LIMIT 5;

-- 推荐：命名一次，引用三次
SELECT
  order_no,
  row_number() OVER w AS 序号,
  rank()       OVER w AS 排名,
  dense_rank() OVER w AS 密集排名
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC, id)
LIMIT 5;

命名窗口可以被继承，在引用时追加或覆盖部分内容（只能追加 ORDER BY，不能覆盖 PARTITION BY）：

sql
SELECT
  order_no,
  sum(amount) OVER w                    AS 用户内累计,
  sum(amount) OVER (w ORDER BY id)      AS 用户内按 id 累计
FROM orders
WINDOW w AS (PARTITION BY user_id)
ORDER BY user_id, id
LIMIT 6;
text
  order_no  | 用户内累计 | 用户内按 id 累计
------------+------------+------------------
 SO00000001 |     736.10 |           736.10
 SO00020001 |     735.20 |           735.20
 SO00040001 |     464.40 |           464.40
 SO00060001 |     892.80 |           892.80
 SO00080001 |     149.20 |           149.20
 SO00100001 |     861.40 |           861.40
(6 rows)

多条窗口定义时，PostgreSQL 只在排序键完全相同时复用一次排序：

窗口定义组合需要的排序次数说明
两者 PARTITION BY 与 ORDER BY 完全相同1 次复用同一次排序
仅 PARTITION BY 相同，ORDER BY 不同2 次每个不同的排序键各排一次
PARTITION BY 不同2 次分区键不同，无法共用
sql
-- 两次排序：两个窗口的排序键不同
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  rank()       OVER (PARTITION BY user_id ORDER BY amount DESC)    AS 金额排名,
  rank()       OVER (PARTITION BY user_id ORDER BY created_at DESC) AS 时间排名
FROM orders
LIMIT 10;
text
Limit  (cost=... rows=10 width=16) (actual time=... rows=10 loops=1)
  ->  WindowAgg  (cost=... rows=200000 width=16)
        ->  WindowAgg  (cost=... rows=200000 width=16)
              ->  Sort  (cost=... rows=200000 width=32)
                    Sort Key: user_id, created_at DESC
                    ->  Sort  (cost=... rows=200000 width=32)
                          Sort Key: user_id, amount DESC
                          ->  Seq Scan on orders

WindowAgg 嵌套在另一个 WindowAgg 之上，底下是两层 Sort，代价相当可观。若两个窗口的排序键一致，计划里只会出现一个 Sort。

默认帧与显式帧子句 ​

帧（Frame）是分区内更细的一层范围限定。只有聚合类窗口函数受帧影响，排名函数（row_number、rank 等）和取值函数（lag、lead）不受帧影响。

帧写法含义典型用途
RANGE UNBOUNDED PRECEDING AND CURRENT ROW分区起点到当前行（含同行集）默认行为，累计值
ROWS UNBOUNDED PRECEDING AND CURRENT ROW分区起点到当前物理行逐行累计，不受同行集影响
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING前三行滑动窗口三点移动平均
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW最近 7 行七日移动平均
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING整个分区与不写 ORDER BY 等价
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING当前行到分区末尾剩余量、倒数累计

RANGE 与 ROWS 的关键差别：RANGE 按排序键的值划定范围，会把所有排序键与当前行相同的行（同行集）一起纳入；ROWS 严格按物理行位置计数。

sql
-- RANGE（默认）：金额相同的行全部算入累计
SELECT
  id, amount,
  round(sum(amount) OVER (
    ORDER BY amount RANGE UNBOUNDED PRECEDING AND CURRENT ROW
  ), 2) AS range累计
FROM orders
ORDER BY amount, id
LIMIT 6;
text
 id | amount | range累计
----+--------+-----------
  7 | 100.00 |    200.00   -- 两笔 100.00 的订单一起算入
 13 | 100.00 |    200.00
  9 | 100.01 |    300.01
 21 | 100.02 |    400.03
  3 | 100.03 |    500.06
 17 | 100.04 |    600.10
(6 rows)
sql
-- ROWS：严格逐行累加
SELECT
  id, amount,
  round(sum(amount) OVER (
    ORDER BY amount, id ROWS UNBOUNDED PRECEDING AND CURRENT ROW
  ), 2) AS rows累计
FROM orders
ORDER BY amount, id
LIMIT 6;
text
 id | amount | rows累计
----+--------+----------
  7 | 100.00 |   100.00   -- 严格逐行，第一行只算自己
 13 | 100.00 |   200.00
  9 | 100.01 |   300.01
 21 | 100.02 |   400.03
  3 | 100.03 |   500.06
 17 | 100.04 |   600.10
(6 rows)

两者的区别在第 1 行就显现了：RANGE 给出 200.00（把同值的第 13 行也算进来），ROWS 给出 100.00。

实战示例 ​

场景：为用户详情页设计「该用户订单流水」表格，需要同时给出：金额排名、按时间累计金额、最近 7 笔的滑动平均、以及全站对比用的金额四分位。要求用一个 WINDOW 子句组织，避免重复定义。

步骤 1：先看单个用户的原始订单 ​

sql
SELECT id, order_no, amount, created_at
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
ORDER BY created_at, id;
text
 id |  order_no  | amount | created_at
----+------------+--------+------------
 43 | SO00000043 | 736.10 | 2024-06-03
 45 | SO00000045 | 654.60 | 2024-07-01
 47 | SO00000047 | 180.90 | 2024-08-15
 49 | SO00000049 | 233.10 | 2024-09-20
  3 | SO00020003 | 703.70 | 2025-04-11
  5 | SO00040005 | 512.30 | 2025-06-02
(6 rows)

步骤 2：用一个命名窗口完成多个指标 ​

sql
SELECT
  o.order_no,
  o.amount,
  o.created_at,
  row_number() OVER w_order                       AS 流水序号,
  rank()       OVER w_amount                      AS 金额排名,
  round(sum(o.amount) OVER w_order, 2)            AS 时间累计,
  round(avg(o.amount) OVER (
    ORDER BY o.created_at, o.id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2)                                            AS 三笔滑动均值
FROM orders o
WHERE o.user_id = 42 AND o.status <> 'cancelled'
WINDOW
  w_order  AS (ORDER BY o.created_at, o.id),
  w_amount AS (ORDER BY o.amount DESC, o.id)
ORDER BY o.created_at, o.id;
text
  order_no  | amount | created_at | 流水序号 | 金额排名 | 时间累计 | 三笔滑动均值
------------+--------+------------+----------+----------+----------+--------------
 SO00000043 | 736.10 | 2024-06-03 |        1 |        1 |   736.10 |       736.10
 SO00000045 | 654.60 | 2024-07-01 |        2 |        2 |  1390.70 |       695.35
 SO00000047 | 180.90 | 2024-08-15 |        3 |        6 |  1571.60 |       523.87
 SO00000049 | 233.10 | 2024-09-20 |        4 |        5 |  1804.70 |       356.20
 SO00020003 | 703.70 | 2025-04-11 |        5 |        3 |  2508.40 |       372.57
 SO00040005 | 512.30 | 2025-06-02 |        6 |        4 |  3020.70 |       483.03
(6 rows)

步骤 3：验证帧子句确实按预期工作 ​

sql
SELECT
  order_no,
  amount,
  round(sum(amount) OVER (
    ORDER BY created_at, id ROWS UNBOUNDED PRECEDING
  ), 2) AS 物理累计,
  round(sum(amount) OVER (
    ORDER BY created_at, id RANGE UNBOUNDED PRECEDING
  ), 2) AS 键值累计
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
ORDER BY created_at, id;
text
  order_no  | amount | 物理累计 | 键值累计
------------+--------+----------+----------
 SO00000043 | 736.10 |   736.10 |   736.10
 SO00000045 | 654.60 |  1390.70 |  1390.70
 SO00000047 | 180.90 |  1571.60 |  1571.60
 SO00000049 | 233.10 |  1804.70 |  1804.70
 SO00020003 | 703.70 |  2508.40 |  2508.40
 SO00040005 | 512.30 |  3020.70 |  3020.70
(6 rows)

因为没有重复的时间戳，两者结果相同。换一个存在重复排序键的场景，差异立刻出现：

sql
-- 同一持仓日期有多行，RANGE 会把同日的行一起算入
SELECT
  created_at,
  amount,
  round(sum(amount) OVER (
    ORDER BY created_at ROWS UNBOUNDED PRECEDING
  ), 2) AS rows累计,
  round(sum(amount) OVER (
    ORDER BY created_at RANGE UNBOUNDED PRECEDING
  ), 2) AS range累计
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
ORDER BY created_at
LIMIT 3;

步骤 4：观察命名窗口对计划的影响 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  rank()       OVER w AS 金额排名,
  dense_rank() OVER w AS 密集排名,
  ntile(4)     OVER w AS 四分位
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC, id)
LIMIT 10;
text
Limit  (cost=... rows=10 width=24) (actual time=... rows=10 loops=1)
  Buffers: shared hit=... 
  ->  WindowAgg  (cost=... rows=200000 width=24) (actual time=... rows=10 loops=1)
        Window: w AS (PARTITION BY user_id ORDER BY amount DESC, id)
        Buffers: shared hit=...
        ->  Sort  (cost=... rows=200000 width=24) (actual time=... rows=200001 loops=1)
              Sort Key: user_id, amount DESC, id
              Sort Method: external merge  Disk: 12288kB
              Buffers: shared hit=...
              ->  Seq Scan on orders  (cost=... rows=200000 width=24)

三个函数共用一个 WindowAgg、一次 Sort。这就是命名窗口的收益。

步骤 5：给窗口排序建索引，消除排序节点 ​

sql
-- 排序键：user_id, amount DESC, id
CREATE INDEX ON orders (user_id, amount DESC, id);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  rank() OVER w AS 金额排名
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC, id)
LIMIT 10;
text
Limit  (cost=... rows=10 width=8) (actual time=... rows=10 loops=1)
  ->  WindowAgg  (cost=... rows=200000 width=8) (actual time=... rows=10 loops=1)
        Window: w AS (PARTITION BY user_id ORDER BY amount DESC, id)
        ->  Index Scan using orders_user_id_amount_id_idx on orders  (cost=... rows=200000 width=8)

Sort 节点消失了，取而代之的是按索引顺序返回的 Index Scan，索引既能提供分区顺序，也能提供排序顺序。

步骤 6：用 EXCEPT 验证命名窗口与展开写法的等价性 ​

sql
WITH a AS (
  SELECT id,
         rank() OVER w AS r1, dense_rank() OVER w AS r2
  FROM orders
  WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC, id)
),
b AS (
  SELECT id,
         rank()       OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS r1,
         dense_rank() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS r2
  FROM orders
)
SELECT 'only_in_a' AS 差异方向, count(*) FROM (
  SELECT id, r1, r2 FROM a EXCEPT ALL SELECT id, r1, r2 FROM b
) t
UNION ALL
SELECT 'only_in_b', count(*) FROM (
  SELECT id, r1, r2 FROM b EXCEPT ALL SELECT id, r1, r2 FROM a
) t;
text
 差异方向  | count
-----------+-------
 only_in_a |     0
 only_in_b |     0
(2 rows)

注意事项 ​

  1. 命名窗口只能追加 ORDER BY，不能覆盖 PARTITION BY：
sql
-- 语法错误！不能对已定义了 PARTITION BY 的窗口重新指定分区
SELECT sum(amount) OVER (w PARTITION BY city)
FROM orders
WINDOW w AS (PARTITION BY user_id);
-- 报错：cannot override PARTITION BY clause of window "w"

-- 合法：追加排序
SELECT sum(amount) OVER (w ORDER BY amount DESC)
FROM orders
WINDOW w AS (PARTITION BY user_id);
  1. WINDOW 子句必须写在 HAVING 之后、ORDER BY 之前：
sql
/* 子句顺序：
   SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ...
   WINDOW ...
   ORDER BY ... LIMIT ...
*/
  1. RANGE 帧只支持单列排序键后的偏移量，且不同引擎行为一致：
sql
-- 错误！RANGE 带偏移量时，ORDER BY 只能有一列
SELECT sum(amount) OVER (
  ORDER BY created_at, id RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) FROM orders;
-- 报错：RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
  1. ROWS 与 RANGE 在默认帧上不同，尤其当排序键有重复值。需要严格按行数计数时，必须显式写 ROWS：
sql
-- 不推荐：默认 RANGE，重复金额会把后续行一起算入
sum(amount) OVER (ORDER BY amount)

-- 推荐：需要逐行累加时明确写 ROWS
sum(amount) OVER (ORDER BY amount, id ROWS UNBOUNDED PRECEDING)
  1. GROUPS 帧按「同行集的个数」计数，是 PostgreSQL 11+ 提供的第三种帧模式：
sql
-- 当前同行集 + 前一行同集内的 2 个同行集
SELECT sum(amount) OVER (
  ORDER BY created_at GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW
) FROM orders;

  1. ORDER BY 加在 OVER 内部不等于输出有序，最外层仍要写 ORDER BY。这条规则在分页场景尤其重要。

  2. 不要在一个 OVER 里混用不同粒度的排序键来「省一次排序」。排序键不一致就必须重新排序，拆分反而更清晰：

sql
-- 不推荐：硬凑成同一个窗口
SELECT
  rank() OVER (ORDER BY amount DESC)          AS 金额排名,
  rank() OVER (ORDER BY created_at DESC, id)  AS 时间排名
FROM orders;
-- 计划中仍会出现两次 Sort，并不会因为写在同一层而共用

最佳实践 ​

  1. 窗口定义统一用 WINDOW 命名，即使只有两个函数，也便于后续扩展：
sql
SELECT
  row_number() OVER w AS 序号,
  rank()       OVER w AS 排名
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC, id);
  1. 排序键一律补唯一列做决胜，保证结果可复现：
sql
ORDER BY amount DESC, id
ORDER BY created_at, id
  1. 需要行序的窗口，优先看能不能用索引消除排序：
窗口排序键建议索引
(user_id, amount DESC, id)CREATE INDEX ON orders (user_id, amount DESC, id);
(city, created_at, id)CREATE INDEX ON users (city, created_at, id);
(status, amount DESC)CREATE INDEX ON orders (status, amount DESC, id);
  1. NULL 排序位置显式声明，不要依赖默认值：
sql
ORDER BY discount DESC NULLS LAST

  1. 需要「严格逐行」的累计，显式写 ROWS；需要「按值分组」的累计，显式写 RANGE。不写会有隐患。

  2. 把窗口函数放在最内层，过滤放在外层，这是唯一可行的组织方式（因为窗口函数不能出现在 WHERE 中）：

sql
SELECT * FROM (
  SELECT *, row_number() OVER w AS rn
  FROM orders
  WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC, id)
) t
WHERE rn <= 3;
  1. 调试时关注计划中 Sort 节点与 WindowAgg 的层级关系：
计划形态含义优化方向
WindowAgg → Index Scan排序由索引提供已最优
WindowAgg → Sort → ...显式排序考虑建匹配索引
WindowAgg → WindowAgg → Sort ×2多套排序键尽量统一排序键
Sort Method: external merge排序溢出到磁盘调大 work_mem 或减少排序行数

下一节

继续学习：排名函数

参考链接 ​