PHP小码农

Appearance

窗口函数概述 ​

本节讲解窗口函数（Window Function）是什么、它和聚合函数（Aggregate Function）与 GROUP BY 的本质区别、哪些函数可以当窗口函数用、窗口函数在 SQL 执行顺序中的位置，以及它最适合解决的几类问题。学完本节，你将能判断一个问题该用聚合、GROUP BY 还是窗口函数。

前置知识

阅读本节前，建议先了解：CTE 优化实践

基础概念 ​

窗口函数（Window Function） 在保留每一行的同时，为该行计算一个基于「与之相关的一组行」的结果。这里的「一组行」称为 窗口（Window）。这与聚合函数形成鲜明对照：聚合函数把多行折叠成一行，窗口函数则是多行进、多行出，每行都附带了计算值。

概念术语说明
窗口函数Window Function保留行、附带跨行计算结果的函数
窗口Window当前行参与计算的那组行
OVER 子句OVER Clause声明窗口函数及其窗口范围
分区Partition用 PARTITION BY 把行分成互不相交的组，窗口不跨分区
排序Ordering用 ORDER BY 决定行在窗口内的顺序
帧Frame分区内再按行位置划定的子范围
当前行Current Row正在计算的那一行
同行集Peer GroupORDER BY 排序键值相同的行集合

三类「跨行计算」的对比：

对比项聚合函数（无 OVER）聚合函数 + GROUP BY窗口函数
输入行数 → 输出行数多行 → 1 行多行 → 每组 1 行多行 → 每行 1 行
能否保留原始列不能（除非该列在 GROUP BY 中）只能保留分组键可以，所有列都能保留
能否同时看明细与汇总不能不能能
能否按行排序取值不能不能能（row_number、lag、lead）
典型用途全表汇总分组报表组内排名、环比、移动平均、占比

一个最直观的对照。先看 GROUP BY 的写法：

sql
-- GROUP BY：明细行被折叠，只能看到汇总
SELECT city, count(*) AS 订单数, round(sum(amount), 2) AS 总额
FROM orders
GROUP BY city;
text
 city | 订单数 |      总额
------+--------+---------------
 上海 |  40021 | 22012668.30
 北京 |  40015 | 22004871.10
 广州 |  39991 | 21986218.88
 深圳 |  40012 | 22013874.02
 杭州 |  39961 | 21991099.20
(5 rows)

再看窗口函数的写法：

sql
-- 窗口函数：明细行保留，每行附带本组的汇总
SELECT
  order_no,
  city,
  amount,
  round(sum(amount) OVER (PARTITION BY city), 2) AS 该城市总额
FROM orders
ORDER BY city, order_no
LIMIT 8;
text
  order_no  | city | amount  | 该城市总额
------------+------+---------+---------------
 SO00000002 | 上海 |  736.10 |   22012668.30
 SO00000007 | 上海 |  180.20 |   22012668.30
 SO00000012 | 上海 |  654.60 |   22012668.30
 SO00000017 | 上海 |  703.70 |   22012668.30
 SO00000022 | 上海 |  180.90 |   22012668.30
 SO00000027 | 上海 |  896.30 |   22012668.30
 SO00000032 | 上海 |  474.00 |   22012668.30
 SO00000037 | 上海 |  537.30 |   22012668.30
(8 rows)

同样的汇总值，被贴到了每一行上，而明细一行没少。

可作窗口函数使用的函数类别：

类别代表函数是否要求 ORDER BY说明
排名函数row_number、rank、dense_rank、ntile必须有意义按顺序给出名次
取值函数lag、lead、first_value、last_value、nth_value通常需要取同分区内其他行的值
聚合函数sum、count、avg、min、max不要求加上 OVER 即成为窗口聚合
统计函数stddev、variance、percentile_cont、mode部分需要统计类聚合
布尔函数bool_and、bool_or、every不要求判断组内是否全真 / 有真
其他cume_dist、percent_rank必须有意义累积分布、百分比排名

SQL 执行顺序与窗口函数的位置：

阶段关键字窗口函数可用性
1FROM / JOIN—
2WHERE不可用
3GROUP BY / HAVING不可用
4窗口函数计算在此处计算
5SELECT 输出可使用
6DISTINCT已计算完成
7ORDER BY可使用
8LIMIT / OFFSET已计算完成

这张顺序表解释了两个最常见的报错：

sql
-- 错误！窗口函数不能出现在 WHERE 中（执行顺序早于窗口计算）
SELECT * FROM orders
WHERE row_number() OVER (ORDER BY amount DESC) <= 10;
-- 报错：window functions are not allowed in WHERE

-- 错误！窗口函数不能出现在 GROUP BY 中
SELECT city FROM orders GROUP BY row_number() OVER (ORDER BY city);
-- 报错：window functions are not allowed in GROUP BY

-- 正确：用子查询或 CTE 包一层，外层再过滤
SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
  FROM orders
) t
WHERE rn <= 10;

语法与用法 ​

窗口函数的基本骨架：

text
函数名(参数) OVER (
  [PARTITION BY 分区列, ...]
  [ORDER BY 排序列 [ASC | DESC] [, ...]]
  [帧子句]
)

三个组成部分都是可选的，但省略它们会带来完全不同的语义：

写法含义
OVER ()整个结果集是一个窗口，不分区不排序
OVER (PARTITION BY city)按城市分区，分区内所有行构成窗口
OVER (ORDER BY created_at)全表按时间排序，默认帧为「起点到当前行」
OVER (PARTITION BY city ORDER BY created_at)分区内排序，默认帧为「分区起点到当前行」
OVER (ORDER BY created_at ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)显式指定帧为最近两行

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

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 (created_at);
sql
-- 2 万名用户
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;

-- 20 万条订单
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） 是窗口函数的第一步切分：PARTITION BY 把结果集按指定列的值切成若干互不相交的组，窗口计算只在分区内部进行，绝不跨分区。

sql
-- 不分区：所有行同属一个窗口
SELECT order_no, amount, rank() OVER (ORDER BY amount DESC) AS 全局排名
FROM orders
ORDER BY 全局排名
LIMIT 5;
text
  order_no  | amount | 全局排名
------------+--------+----------
 SO0001xxxx | 999.99 |        1
 SO0001xxxx | 999.99 |        1
 SO0001xxxx | 999.98 |        3
 SO0001xxxx | 999.98 |        3
 SO0001xxxx | 999.97 |        5
(5 rows)
sql
-- 分区：每个城市各自从 1 开始排名
SELECT order_no, city, amount,
       rank() OVER (PARTITION BY city ORDER BY amount DESC) AS 城市内排名
FROM (
  SELECT o.order_no, u.city, o.amount
  FROM orders o JOIN users u ON u.id = o.user_id
) t
ORDER BY city, 城市内排名
LIMIT 6;
text
  order_no  | city | amount | 城市内排名
------------+------+--------+------------
 SO00019xxx | 上海 | 999.99 |          1
 SO00017xxx | 上海 | 999.99 |          1
 SO00011xxx | 上海 | 999.98 |          3
 SO00009xxx | 上海 | 999.97 |          4
 SO00005xxx | 上海 | 999.96 |          5
 SO00003xxx | 上海 | 999.95 |          6
(6 rows)

关键点：分区之间完全独立，一个分区里有多少行、怎么排序，都不会影响另一个分区的计算结果。

窗口函数与 GROUP BY 的取舍 ​

两者解决的是不同层次的问题，混用反而容易出错：

需求推荐手段原因
只要每个分组的汇总值GROUP BY输出行数少，代价低
需要明细行 + 本组汇总窗口函数GROUP BY 会丢明细
需要组内排名窗口函数GROUP BY 无法表达
需要与上一行比较窗口函数（lag）需要行序信息
需要累计值窗口函数（累计帧）GROUP BY 只能出离散分组值
需要按分组去重取一行窗口函数 + 外层过滤，或 DISTINCT ON见下文

一个常见的误用是试图用 GROUP BY 拿明细：

sql
-- 错误！既想按城市汇总，又想要 order_no，order_no 不在 GROUP BY 中
SELECT city, order_no, sum(amount)
FROM orders o JOIN users u ON u.id = o.user_id
GROUP BY city;
-- 报错：column "o.order_no" must appear in the GROUP BY clause
--       or be used in an aggregate function
sql
-- 正确：窗口函数保留明细
SELECT
  u.city,
  o.order_no,
  o.amount,
  round(sum(o.amount) OVER (PARTITION BY u.city), 2) AS 城市总额,
  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
ORDER BY u.city, o.amount DESC
LIMIT 6;
text
 city |  order_no  | amount | 城市总额  | 占比
------+------------+--------+-----------+-------
 上海 | SO00019xxx | 999.99 | 22012668  |  0.00
 上海 | SO00017xxx | 999.99 | 22012668  |  0.00
 上海 | SO00011xxx | 999.98 | 22012668  |  0.00
 上海 | SO00009xxx | 999.97 | 22012668  |  0.00
 上海 | SO00005xxx | 999.96 | 22012668  |  0.00
 上海 | SO00003xxx | 999.95 | 22012668  |  0.00
(6 rows)

窗口函数的典型应用场景 ​

场景用到的函数说明
组内排名 / Top-Nrow_number、rank、dense_rank每组取前 N 名
分组去重保留一行row_number按规则排序后取 rn = 1
环比 / 同比lag、lead取上一期、下一期的值
移动平均窗口聚合 + 帧最近 N 行的平均值
累计求和窗口聚合 + 默认帧从分区起点累加到当前行
占本组比例窗口聚合当前值 / 本组总和
首值 / 末值first_value、last_value分区内第一行、最后一行的值
分组编号ntile、cume_dist、percent_rank等分、累积分布、百分比排名
连续区间识别row_number + 差值用序号差判断是否连续

下面各举一例，后续各节会逐一展开。

sql
-- 场景 1：每个城市金额最高的 3 笔订单
SELECT city, order_no, amount, rn
FROM (
  SELECT u.city, o.order_no, o.amount,
         row_number() OVER (PARTITION BY u.city ORDER BY o.amount DESC) AS rn
  FROM orders o JOIN users u ON u.id = o.user_id
) t
WHERE rn <= 3
ORDER BY city, rn;
text
 city |  order_no  | amount | rn
------+------------+--------+----
 上海 | SO00019xxx | 999.99 |  1
 上海 | SO00017xxx | 999.99 |  2
 上海 | SO00011xxx | 999.98 |  3
 北京 | SO00018xxx | 999.99 |  1
 北京 | SO00016xxx | 999.99 |  2
 北京 | SO00010xxx | 999.97 |  3
 ...
sql
-- 场景 2：按城市看金额的月度环比
WITH monthly AS (
  SELECT
    u.city,
    date_trunc('month', o.created_at)::date AS 月份,
    round(sum(o.amount), 2) AS 月总额
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
  GROUP BY u.city, date_trunc('month', o.created_at)
)
SELECT
  城市, 月份, 月总额,
  lag(月总额) OVER (PARTITION BY 城市 ORDER BY 月份) AS 上月,
  round(月总额 - lag(月总额) OVER (PARTITION BY 城市 ORDER BY 月份), 2) AS 环比增减
FROM (SELECT city AS 城市, 月份, 月总额 FROM monthly) m
ORDER BY 城市, 月份
LIMIT 8;
text
 城市 |    月份    |  月总额   |   上月    | 环比增减
------+------------+-----------+-----------+----------
 上海 | 2024-06-01 | 742183.20 |           |
 上海 | 2024-07-01 | 749024.51 | 742183.20 |  6841.31
 上海 | 2024-08-01 | 748316.07 | 749024.51 |  -708.44
 上海 | 2024-09-01 | 743580.44 | 748316.07 | -4735.63
 上海 | 2024-10-01 | 745112.98 | 743580.44 |  1532.54
 上海 | 2024-11-01 | 741036.55 | 745112.98 | -4076.43
 ...
sql
-- 场景 3：累计销售额与占本组比例
SELECT
  u.city,
  o.created_at,
  o.amount,
  round(sum(o.amount) OVER (
    PARTITION BY u.city ORDER BY o.created_at, o.id
  ), 2) AS 累计金额,
  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
WHERE u.city = '杭州' AND o.status <> 'cancelled'
ORDER BY o.created_at, o.id
LIMIT 5;
text
 city | created_at | amount | 累计金额 | 占比
------+------------+--------+----------+------
 杭州 | 2024-06-01 | 512.30 |   512.30 | 0.00
 杭州 | 2024-06-01 | 108.90 |   621.20 | 0.00
 杭州 | 2024-06-01 | 776.40 |  1397.60 | 0.00
 杭州 | 2024-06-01 | 233.10 |  1630.70 | 0.00
 杭州 | 2024-06-01 | 640.50 |  2271.20 | 0.00
(5 rows)

实战示例 ​

场景：销售看板要展示一张「各城市订单明细 + 该城市排名 + 该城市占比 + 累计金额」的表格，每页 10 行。这是典型的「明细 + 多维计算」需求，GROUP BY 完全无法表达。

步骤 1：先看清只有明细的原始数据 ​

sql
SELECT
  u.city,
  o.order_no,
  o.amount,
  o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status <> 'cancelled'
ORDER BY u.city, o.amount DESC
LIMIT 10;
text
 city |  order_no  | amount | created_at
------+------------+--------+------------
 上海 | SO00019xxx | 999.99 | 2025-03-12
 上海 | SO00017xxx | 999.99 | 2024-11-05
 上海 | SO00011xxx | 999.98 | 2025-01-21
 上海 | SO00009xxx | 999.97 | 2024-08-30
 上海 | SO00005xxx | 999.96 | 2025-04-17
 上海 | SO00003xxx | 999.95 | 2024-07-03
 上海 | SO00001xxx | 999.94 | 2025-02-08
 上海 | SO00015xxx | 999.93 | 2024-12-29
 上海 | SO00013xxx | 999.92 | 2025-05-11
 上海 | SO00007xxx | 999.91 | 2025-06-25
(10 rows)

步骤 2：加上窗口列，一次查询得到全部指标 ​

sql
SELECT
  u.city,
  o.order_no,
  o.amount,
  row_number() OVER (PARTITION BY u.city ORDER BY o.amount DESC) AS 城市内序号,
  rank()       OVER (PARTITION BY u.city ORDER BY o.amount DESC) AS 城市内排名,
  round(100.0 * o.amount / sum(o.amount) OVER (PARTITION BY u.city), 4) AS 占比,
  round(sum(o.amount) OVER (
    PARTITION BY u.city ORDER BY o.amount DESC, o.id
  ), 2) AS 降序累计
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status <> 'cancelled' AND u.city = '杭州'
ORDER BY o.amount DESC, o.id
LIMIT 5;
text
 city |  order_no  | amount | 城市内序号 | 城市内排名 |  占比  | 降序累计
------+------------+--------+------------+------------+--------+----------
 杭州 | SO00019xxx | 999.99 |          1 |          1 | 0.0035 |   999.99
 杭州 | SO00017xxx | 999.99 |          2 |          1 | 0.0035 |  1999.98
 杭州 | SO00011xxx | 999.98 |          3 |          3 | 0.0035 |  2999.96
 杭州 | SO00009xxx | 999.97 |          4 |          4 | 0.0035 |  3999.93
 杭州 | SO00005xxx | 999.96 |          5 |          5 | 0.0035 |  4999.89
(5 rows)

注意第 1、2 行的 城市内序号 是 1 和 2，而 城市内排名 都是 1——这正是 row_number 与 rank 的关键差别，详见《排名函数》一节。

步骤 3：用窗口结果做分组去重 ​

窗口函数最常见的实战用途之一，是「每组只保留一行」：

sql
-- 每个城市取金额最高的一笔订单
SELECT city, order_no, amount
FROM (
  SELECT
    u.city,
    o.order_no,
    o.amount,
    row_number() OVER (PARTITION BY u.city ORDER BY o.amount DESC, o.id) AS rn
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
) t
WHERE rn = 1
ORDER BY city;
text
 city |  order_no  | amount
------+------------+--------
 上海 | SO00019xxx | 999.99
 北京 | SO00018xxx | 999.99
 广州 | SO00020xxx | 999.99
 深圳 | SO00021xxx | 999.99
 杭州 | SO00019xxx | 999.99
(5 rows)

步骤 4：查看窗口函数的执行计划 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.city,
  o.order_no,
  o.amount,
  row_number() OVER (PARTITION BY u.city ORDER BY o.amount DESC) AS rn
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status <> 'cancelled'
LIMIT 10;
text
Limit  (cost=... rows=10 width=48) (actual time=... rows=10 loops=1)
  Buffers: shared hit=... 
  ->  WindowAgg  (cost=... rows=150000 width=48) (actual time=... rows=10 loops=1)
        Window: w1 AS (PARTITION BY u.city ORDER BY o.amount DESC)
        Buffers: shared hit=...
        ->  Sort  (cost=... rows=150000 width=48) (actual time=... rows=150001 loops=1)
              Sort Key: u.city, o.amount DESC
              Sort Method: external merge  Disk: 9216kB
              ->  Hash Join  (cost=... rows=150000 width=48)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (...)
                          Filter: (status <> 'cancelled'::text)
                    ->  Hash  (...)
                          ->  Seq Scan on users u  (...)

关键读数：WindowAgg 是窗口函数的执行节点，它下面需要一次排序（Sort），并且排序键就是 PARTITION BY 列 + ORDER BY 列的组合。若排序量超过 work_mem，会出现 external merge 写临时文件。

步骤 5：对比「先聚合再连接」与窗口函数的代价 ​

sql
-- 方案 A：窗口函数，一次扫描 + 一次排序
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.city, o.order_no, o.amount,
       round(100.0 * o.amount / sum(o.amount) OVER (PARTITION BY u.city), 4) AS 占比
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status <> 'cancelled'
LIMIT 10;
text
Limit  (cost=... rows=10 width=52) (actual time=... rows=10 loops=1)
  ->  WindowAgg  (...)
        ->  Sort  (Sort Method: external merge  Disk: 9216kB)
              ->  Hash Join  (...)
                    ->  Seq Scan on orders o  (...)
sql
-- 方案 B：先聚合出城市总额，再连接回明细
EXPLAIN (ANALYZE, BUFFERS)
WITH city_total AS MATERIALIZED (
  SELECT u.city, sum(o.amount) AS total
  FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
  GROUP BY u.city
)
SELECT u.city, o.order_no, o.amount,
       round(100.0 * o.amount / ct.total, 4) AS 占比
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN city_total ct ON ct.city = u.city
WHERE o.status <> 'cancelled'
LIMIT 10;
text
Limit  (cost=... rows=10 width=60) (actual time=... rows=10 loops=1)
  CTE city_total
    ->  HashAggregate  (...)
          Group Key: u.city
          ->  Hash Join  (...)
  ->  Hash Join  (...)
        Hash Cond: (u.city = ct.city)
        ->  Hash Join  (...)

两者结果一致，但方案 B 不需要对 15 万行做全量排序，而是用一次小规模聚合 + 一次哈希连接替代。当计算结果只需要「每组的单一汇总值」时，GROUP BY + 连接往往比窗口函数更划算；只有当需要真正的行序信息（排名、lag、累计）时，窗口函数才不可替代。

步骤 6：用 EXCEPT 验证两种方案结果一致 ​

sql
WITH a AS (
  SELECT o.id, u.city,
         round(100.0 * o.amount / sum(o.amount) OVER (PARTITION BY u.city), 4) AS 占比
  FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
),
b AS MATERIALIZED (
  SELECT o.id, u.city,
         round(100.0 * o.amount
               / (SELECT sum(o2.amount) FROM orders o2
                  JOIN users u2 ON u2.id = o2.user_id
                  WHERE u2.city = u.city AND o2.status <> 'cancelled'), 4) AS 占比
  FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
)
SELECT 'only_in_a' AS 差异方向, count(*) FROM (
  SELECT id, 占比 FROM a EXCEPT ALL SELECT id, 占比 FROM b
) t
UNION ALL
SELECT 'only_in_b', count(*) FROM (
  SELECT id, 占比 FROM b EXCEPT ALL SELECT id, 占比 FROM a
) t;
text
 差异方向  | count
-----------+-------
 only_in_a |     0
 only_in_b |     0
(2 rows)

注意事项 ​

  1. 窗口函数不能出现在 WHERE、GROUP BY、HAVING 中。必须在子查询或 CTE 里先算出来，外层再过滤：
sql
-- 错误！窗口函数不能用于 WHERE
SELECT * FROM orders WHERE rank() OVER (ORDER BY amount DESC) <= 3;

-- 正确
SELECT * FROM (
  SELECT *, rank() OVER (ORDER BY amount DESC) AS rk FROM orders
) t WHERE rk <= 3;
  1. OVER () 与 OVER (ORDER BY ...) 语义完全不同。不加 ORDER BY 时，整个分区是一个整体窗口；加了 ORDER BY 后，默认帧变成「分区起点到当前行」：
sql
-- 全分区总和（每行都一样）
SELECT amount, sum(amount) OVER () AS 全额 FROM orders LIMIT 3;

-- 累计和（逐行递增）
SELECT amount, sum(amount) OVER (ORDER BY created_at, id) AS 累计 FROM orders LIMIT 3;
text
-- 前者：每行的「全额」相同
-- 后者：第 1 行等于第 1 行金额，第 2 行等于前 2 行之和，依此类推
  1. PARTITION BY 与 GROUP BY 不能互相替代。PARTITION BY 不减少行数，GROUP BY 减少行数：
sql
-- 不推荐：想用 PARTITION BY 得到「每个城市一行」
SELECT DISTINCT city, count(*) OVER (PARTITION BY city) FROM users;

-- 推荐
SELECT city, count(*) FROM users GROUP BY city;
  1. 窗口函数对 NULL 的排序遵循 PostgreSQL 的默认规则：ASC 时 NULL 在最后，DESC 时 NULL 在最前。可用 NULLS FIRST / NULLS LAST 调整：
sql
SELECT amount, rank() OVER (ORDER BY amount DESC NULLS LAST) FROM orders;
  1. 多个窗口函数尽量共用同一个 OVER 定义。若两个窗口函数的 PARTITION BY 与 ORDER BY 完全一致，PostgreSQL 只做一次排序；若不一致，可能触发多次排序：
sql
-- 好：共用一次排序
SELECT
  rank() OVER w,
  dense_rank() OVER w
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC);

-- 差：两个不同的窗口定义，可能各排一次
SELECT
  rank() OVER (PARTITION BY user_id ORDER BY amount DESC),
  rank() OVER (PARTITION BY user_id ORDER BY created_at DESC)
FROM orders;
  1. 窗口函数的代价主要来自排序。PARTITION BY + ORDER BY 的列组合如果已有匹配索引，可以省掉排序；否则会出现 Sort 甚至 external merge。给高频窗口查询建复合索引，方向要与 ORDER BY 一致。

最佳实践 ​

  1. 先问三个问题再决定用不用窗口函数：
问题若答案为「是」若答案为「否」
需要保留明细行吗？窗口函数考虑 GROUP BY
需要行序（排名、前后行）吗？窗口函数考虑 GROUP BY 或标量子查询
只需要分组的单一汇总值吗？用 GROUP BY + 连接可以继续考虑窗口函数
  1. 窗口定义用 WINDOW 子句命名，避免重复：
sql
SELECT
  order_no,
  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);
  1. 把窗口函数放在最外层的 SELECT，过滤条件放在外层包裹的查询里，这样可读性最好：
sql
SELECT * FROM (
  SELECT user_id, order_no, amount,
         row_number() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;
  1. 窗口查询优先保证排序有索引支持：
sql
-- 若经常按用户内金额排名
CREATE INDEX ON orders (user_id, amount DESC);
  1. 避免在窗口函数里做大范围 DISTINCT 聚合。count(DISTINCT x) OVER (...) 在 PostgreSQL 中不被支持（会报错），需要先在子查询里聚合再去重：
sql
-- 错误！窗口函数中不支持 DISTINCT 聚合
SELECT count(DISTINCT user_id) OVER (PARTITION BY city) FROM orders;
-- 报错：DISTINCT is not implemented for window functions

  1. 需要「每行附带的组内汇总」且分组基数很小，可以先用 GROUP BY 聚合再连接，代价通常低于对全表做窗口排序。

  2. 调试窗口函数时先看 EXPLAIN 里有没有多余的 Sort 节点，那通常是性能瓶颈所在：

text
-- 期望看到
WindowAgg
  ->  Index Scan using orders_user_id_amount_idx on orders

-- 需要警惕
WindowAgg
  ->  Sort  (Sort Method: external merge  Disk: ...)

下一节

继续学习：OVER 子句详解

参考链接 ​