PHP小码农

Appearance

聚合窗口函数（SUM / AVG / COUNT） ​

本节讲解如何把任意聚合函数（sum、avg、count、min、max、stddev、array_agg、string_agg、json_agg、bool_and、percentile_cont…）加上 OVER 变成窗口函数，讲清它与 GROUP BY 的本质分工、累积求和（Running Total）的默认帧来源、明细与汇总同屏、移动平均、FILTER 组合、DISTINCT 限制与两层聚合的拆分手法。学完本节，你将能在一份报表里同时输出明细、组内汇总与全表占比。

前置知识

阅读本节前，建议先了解：LAG / LEAD / FIRST_VALUE / LAST_VALUE

基础概念 ​

聚合窗口函数（Aggregate Window Function） 指「聚合函数 + OVER」的组合。它与 GROUP BY 用的是同一批聚合函数，唯一区别是是否折叠行：GROUP BY 把多行折叠成一行，聚合窗口函数一行不删，把聚合结果附在每一行旁边。

概念术语说明
聚合函数Aggregate Functionsum / avg / count / min / max 等把多行算成一个值
聚合窗口函数Aggregate Window Function加了 OVER 的聚合函数，不折叠行
明细与汇总同屏Detail Plus Aggregate每行同时带明细列与本组汇总列
累积求和Running Total按排序逐行累加，依赖默认帧
移动平均Moving Average固定长度滑动窗口的平均值
窗口聚合Window Aggregate在窗口内求聚合，作用域由帧决定
分组聚合Group AggregateGROUP BY 后的聚合，折叠行
占比Share / Ratio当前值 ÷ 组内或全表总额
全表行数Total Rowscount(*) OVER () 一次拿到总数
筛选子句FILTER Clause只对满足条件的行做聚合

GROUP BY 与窗口聚合的本质区别：

对比项GROUP BY聚合窗口函数
输出行数每组一行与输入行数相同
原始明细列不能保留（除分组键）全部保留
能否同时看明细与汇总不能能
与其他窗口函数混用需子查询可直接混用
与 HAVING 配合支持不支持，须外套子查询
典型场景汇总报表明细 + 汇总额同屏

一个最直观的对照：

sql
-- GROUP BY：只剩 5 行汇总
SELECT city, count(*) AS 订单数, round(sum(amount), 2) AS 总额
FROM orders GROUP BY city ORDER BY city;

-- 窗口聚合：20 万行明细全部保留，每行带上城市汇总
SELECT
  order_no, city, amount,
  count(*) OVER (PARTITION BY city)          AS 城市订单数,
  round(sum(amount) OVER (PARTITION BY city), 2) AS 城市总额
FROM orders
ORDER BY city, order_no
LIMIT 4;
text
  order_no  | city | amount  | 城市订单数 |  城市总额
------------+------+---------+------------+------------
 SO00000002 | 上海 |  736.10 |      40021 | 22012668.30
 SO00000007 | 上海 |  180.20 |      40021 | 22012668.30
 SO00000012 | 上海 |  654.60 |      40021 | 22012668.30
 SO00000017 | 上海 |  703.70 |      40021 | 22012668.30
(4 rows)

哪些函数能加 OVER：

类别函数说明
基础聚合sum / avg / count / min / max最常用
统计聚合stddev / stddev_pop / variance / var_pop方差标准差
有序集聚合percentile_cont / percentile_disc / mode需 WITHIN GROUP，不能直接加 OVER
集合聚合array_agg / string_agg / json_agg / jsonb_agg组内明细拼接
布尔聚合bool_and / bool_or / every组内条件判断
不支持窗口count(DISTINCT ...)报错，见「注意事项」

注意：有序集聚合（percentile_cont / mode 等）语法上是 percentile_cont(0.5) WITHIN GROUP (ORDER BY x)，不能写成 OVER 形式；要按窗口求中位数必须用 percentile_cont 的窗口变体形式——实际上 PostgreSQL 不支持，需改用 nth_value 或 CTE + 有序集聚合。

ORDER BY 决定默认帧，进而决定聚合语义——这是本节第二个必须记牢的点：

窗口定义默认帧sum(amount) 的语义
OVER ()整个结果集全表总额，每行相同
OVER (PARTITION BY city)整个分区城市总额，组内每行相同
OVER (ORDER BY created_at)分区起点 → 当前行累计和，逐行递增
OVER (PARTITION BY city ORDER BY created_at)分区起点 → 当前行城市内累计和
sql
-- 同一列，三种窗口定义，三种完全不同的聚合语义
SELECT
  id,
  amount,
  round(sum(amount) OVER (), 2)               AS 全表总额,
  round(sum(amount) OVER (PARTITION BY status), 2) AS 同状态总额,
  round(sum(amount) OVER (ORDER BY id), 2)    AS 全表累计
FROM orders
ORDER BY id
LIMIT 4;
text
 id | amount |   全表总额    | 同状态总额 | 全表累计
----+--------+---------------+------------+----------
  1 | 736.10 | 247362941.08  | 61780502.30 |   736.10
  2 | 180.20 | 247362941.08  | 61780502.30 |   916.30
  3 | 654.60 | 247362941.08  | 61735328.10 |  1570.90
  4 | 703.70 | 247362941.08  | 61735328.10 |  2274.60
(4 rows)

全表总额 四行相同、全表累计 逐行递增、同状态总额 在同状态内相同——三种语义差异一目了然。

语法与用法 ​

text
聚合函数名(表达式) OVER (
  [PARTITION BY 分组表达式 [, ...]]
  [ORDER BY 排序表达式 [ASC | DESC] [, ...]]
  [帧子句 ROWS | RANGE | GROUPS ...]
)

-- 带 FILTER 的聚合窗口函数
聚合函数名(表达式) FILTER (WHERE 条件) OVER (...)

-- 命名窗口复用
聚合函数名(表达式) OVER 窗口名
...
WINDOW 窗口名 AS (PARTITION BY ... [ORDER BY ...])
组成是否必填不写时的行为写了以后的影响
PARTITION BY可选整个结果集一个分区聚合在分区内封闭
ORDER BY可选帧为整个分区帧变为「起点到当前行」，聚合变累积
帧子句可选见上表精确控制参与聚合的行
FILTER可选所有行都参与聚合只对满足条件的行聚合

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

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, created_at, 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;

详细说明 ​

明细与汇总同屏：每行附带组内总额 ​

这是聚合窗口函数最核心的用途。业务系统里「订单列表里显示该用户的消费总额」「商品列表里显示该品类的总销量」都是这个模式。

sql
-- 每个城市金额最高的 3 笔订单，同时带上该城市的订单数与总额
SELECT
  order_no,
  city,
  amount,
  count(*) OVER (PARTITION BY city)              AS 城市订单数,
  round(sum(amount)  OVER (PARTITION BY city), 2) AS 城市总额,
  round(avg(amount)  OVER (PARTITION BY city), 2) AS 城市均值,
  round(100.0 * amount
    / sum(amount) OVER (PARTITION BY city), 2)    AS 占城市比
FROM orders
WHERE status <> 'cancelled'
ORDER BY city, amount DESC
LIMIT 6;
text
  order_no  | city | amount | 城市订单数 |  城市总额   | 城市均值 | 占城市比
------------+------+--------+------------+-------------+----------+----------
 SO00000003 | 上海 | 999.99 |      30018 | 16504661.20 |   549.82 |     0.01
 SO00000007 | 上海 | 999.99 |      30018 | 16504661.20 |   549.82 |     0.01
 SO00000011 | 上海 | 999.98 |      30018 | 16504661.20 |   549.82 |     0.01
 SO00000001 | 北京 | 999.99 |      29988 | 16487491.80 |   549.80 |     0.01
 SO00000009 | 北京 | 999.97 |      29988 | 16487491.80 |   549.80 |     0.01
 SO00000013 | 北京 | 999.96 |      29988 | 16487491.80 |     0.01 |     0.01
(6 rows)

这个查询如果改用 GROUP BY 实现，必须写成「子查询聚合 + 连接」两步，多一次扫描：

sql
-- 等效的 GROUP BY 写法：一个聚合子查询 + 一次连接
SELECT o.order_no, u.city, o.amount, c.城市订单数, c.城市总额
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN (
  SELECT u.city, count(*) AS 城市订单数, sum(o.amount) AS 城市总额
  FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
  GROUP BY u.city
) c ON c.city = u.city;

「明细 + 汇总同屏」的三种实现对比：

方案扫描次数可读性保留明细
窗口聚合 OVER (PARTITION BY ...)1 次高✅
聚合子查询 + 连接2 次中✅
相关子查询N+1 次低✅

累积求和（Running Total） ​

只要 OVER 里有 ORDER BY，默认帧就是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，于是 sum 自然变成「从分区起点加到当前行」的累计值。这是累积求和不需要写帧子句的原因，也是「加了 ORDER BY 后 sum 结果突然变掉」的原因。

sql
-- 单用户流水：累计金额、累计占比
SELECT
  order_no,
  created_at,
  amount,
  round(sum(amount) OVER w, 2) AS 累计金额,
  round(100.0 * sum(amount) OVER w
    / sum(amount) OVER (PARTITION BY user_id), 2) AS 累计占比
FROM orders
WHERE user_id = 42 AND status <> 'cancelled'
WINDOW w AS (ORDER BY created_at, id)
ORDER BY created_at, id;
text
  order_no  | created_at | amount | 累计金额 | 累计占比
------------+------------+--------+----------+----------
 SO00000043 | 2024-06-03 | 736.10 |   736.10 |    20.92
 SO00000045 | 2024-07-01 | 654.60 |  1390.70 |    39.53
 SO00000047 | 2024-08-15 | 180.90 |  1571.60 |    44.67
 SO00000049 | 2024-09-20 | 233.10 |  1804.70 |    51.30
 SO00020003 | 2025-04-11 | 703.70 |  2508.40 |    71.30
 SO00040005 | 2025-06-02 | 512.30 |  3020.70 |   100.00
(6 rows)

累积占比达到 80% 的订单贡献了绝大部分金额——这就是帕累托（Pareto）分析。把累计占比画在图表上就是洛伦兹曲线。

sql
-- 帕累托：按金额降序累计，找出贡献前 80% 的「关键少数」
WITH ranked AS (
  SELECT
    order_no,
    amount,
    round(100.0 * sum(amount) OVER (ORDER BY amount DESC, id)
      / sum(amount) OVER (), 2) AS 累计占比
  FROM orders
  WHERE status = 'paid'
)
SELECT
  order_no,
  amount,
  累计占比
FROM ranked
WHERE 累计占比 <= 80
ORDER BY amount DESC
LIMIT 10;

累积求和的三种帧写法差异：

帧写法语义并列值处理
ORDER BY x（默认 RANGE）含当前同行集一起累加并列值得到相同累计值
ORDER BY x ROWS UNBOUNDED PRECEDING严格逐行累加并列值逐个递增
ORDER BY x, id ROWS UNBOUNDED PRECEDING严格逐行，且顺序确定可复现
sql
-- 并列值场景下，RANGE 与 ROWS 的累计结果不同
WITH t(v) AS (VALUES (100), (100), (90), (80))
SELECT
  v,
  sum(v) OVER (ORDER BY v DESC)                              AS range累计,
  sum(v) OVER (ORDER BY v DESC ROWS UNBOUNDED PRECEDING)      AS rows累计
FROM t
ORDER BY v DESC;
text
  v  | range累计 | rows累计
-----+-----------+----------
 100 |       200 |      100
 100 |       200 |      200
  90 |       290 |      290
  80 |       370 |      370
(4 rows)

两个 100 在 RANGE 下都得到 200（同行集一起算），在 ROWS 下得到 100、200（严格逐行）。

移动平均与滑动窗口 ​

移动平均（Moving Average）用固定长度的帧实现。7 日移动平均是报表里最常见的需求：

sql
-- 按天汇总后，计算 7 日移动平均
WITH daily AS (
  SELECT
    created_at,
    round(sum(amount), 2) AS 日销售额
  FROM orders
  WHERE status = 'paid'
  GROUP BY created_at
)
SELECT
  created_at,
  日销售额,
  round(avg(日销售额) OVER (
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS 七日移动均值,
  round(sum(日销售额) OVER (
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS 七日滚动合计
FROM daily
ORDER BY created_at
LIMIT 6;
text
 created_at | 日销售额 | 七日移动均值 | 七日滚动合计
------------+----------+--------------+--------------
 2024-06-01 | 62981.40 |     62981.40 |     62981.40
 2024-06-02 | 61540.20 |     62260.80 |    124521.60
 2024-06-03 | 63820.90 |     62780.83 |    188342.50
 2024-06-04 | 60710.50 |     62263.25 |    249053.00
 2024-06-05 | 64390.80 |     62688.76 |    313443.80
 2024-06-06 | 61990.10 |     62572.32 |    375433.90
(6 rows)

前几行的均值是「不足 7 天的均值」——ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 在行数不足时会自动缩短窗口，不会报错也不会补 0。若业务要求「必须凑满 7 天才输出」，需要额外判断：

sql
-- 严格模式下不足 7 天输出 NULL
CASE WHEN count(*) OVER (
       ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
     ) = 7
     THEN round(avg(日销售额) OVER (
            ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
          ), 2)
     ELSE NULL
END AS 严格七日均值

常用滑动窗口速查：

需求帧写法说明
含当前行共 7 行ROWS BETWEEN 6 PRECEDING AND CURRENT ROW7 日移动平均
前后各 3 行共 7 行ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING居中平滑
近 30 天（按日期值）RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW按日期滑动，不受缺数据影响
当前行到末尾ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING剩余量
整个分区ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING组内总额

用 count(*) OVER () 做分页总数 ​

分页时除了返回当页数据，还需要返回总行数。传统做法是发两条 SQL，用窗口函数可以一次查询同时拿到数据与总数：

sql
-- 一次查询同时返回当页数据与符合条件的总行数
SELECT
  order_no,
  amount,
  count(*) OVER () AS 总行数          -- 过滤后的总行数，不带 LIMIT 影响
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC, id
LIMIT 10 OFFSET 20;
text
  order_no  | amount | 总行数
------------+--------+--------
 SO00000031 | 999.99 |  50012
 SO00000035 | 999.99 |  50012
 SO00000039 | 999.99 |  50012
 SO00000043 | 999.99 |  50012
 ...（共 10 行）
(10 rows)

count(*) OVER () 计算的是过滤后、LIMIT 前的行数，因此每一页拿到的是同一个总数。相比「count 一条 + 数据一条」的两次查询，它少了往返开销与两次扫描。

分页总数方案查询次数一致性代价
count(*) + 数据查询2两次之间可能有新数据两次扫描
count(*) OVER ()1同一快照一次扫描，需算全院
单独缓存总数1（缓存命中）可能过期需维护缓存

注意：count(*) OVER () 需要扫描所有符合条件的行（因为要算总数），所以对「深分页 + 大结果集」并不比 count(*) 更省。数据量大且总数变化不频繁时，缓存总数仍是更好的选择。

FILTER 子句与窗口函数结合 ​

FILTER (WHERE ...) 让聚合只统计满足条件的行。与 OVER 结合后，可以在一行里输出多个维度的统计：

sql
-- 每个城市的订单数、已付款数、已取消数，一行搞定
SELECT
  city,
  count(*)                                  AS 订单总数,
  count(*) FILTER (WHERE status = 'paid')   AS 已付款数,
  count(*) FILTER (WHERE status = 'pending') AS 待处理数,
  round(sum(amount) FILTER (WHERE status = 'paid'), 2) AS 已付款金额
FROM orders
GROUP BY city
ORDER BY city;
text
 city | 订单总数 | 已付款数 | 待处理数 | 已付款金额
------+----------+----------+----------+-------------
 上海 |    40021 |    10020 |     9881 |  5503120.40
 北京 |    40015 |     9991 |    10012 |  5491218.60
 广州 |    39991 |     9989 |    10007 |  5488107.20
 深圳 |    40012 |    10010 |     9990 |  5502981.90
 杭州 |    39961 |     9990 |    10001 |  5489440.10
(5 rows)

FILTER 在窗口函数上的用法——保留明细行，同时给出组内分状态统计：

sql
-- 每行订单都带上「该用户已付款订单数」与「该用户已付款金额」
SELECT
  o.order_no,
  o.user_id,
  o.amount,
  o.status,
  count(*) FILTER (WHERE o.status = 'paid') OVER (PARTITION BY o.user_id) AS 用户已付款数,
  round(sum(o.amount) FILTER (WHERE o.status = 'paid')
    OVER (PARTITION BY o.user_id), 2) AS 用户已付款金额
FROM orders o
WHERE o.user_id = 42
ORDER BY o.created_at, o.id;

FILTER 与 CASE 的对比：

写法可读性是否推荐
count(*) FILTER (WHERE status = 'paid')高✅ 推荐
count(CASE WHEN status = 'paid' THEN 1 END)低仅旧版本兼容用
sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END)中FILTER 无法直接表达时使用（如保留 0）

组内明细拼接：array_agg / string_agg / json_agg ​

集合聚合函数加 OVER 后，可以把组内所有值拼成一个数组 / 字符串 / JSON，附在每行上：

sql
-- 每个用户把订单号拼成数组（保留明细行）
SELECT
  order_no,
  user_id,
  amount,
  -- 注意：array_agg 受帧影响，默认帧只拼到当前行
  array_agg(order_no) OVER (PARTITION BY user_id ORDER BY created_at, id) AS 截至当前订单串
FROM orders
WHERE user_id = 42
ORDER BY user_id, created_at, id;
text
  order_no  | user_id | amount |      截至当前订单串
------------+---------+--------+----------------------------
 SO00000043 |      42 | 736.10 | {SO00000043}
 SO00000045 |      42 | 654.60 | {SO00000043,SO00000045}
 SO00000047 |      42 | 180.90 | {SO00000043,SO00000045,SO00000047}
 ...（后续行继续追加）
(6 rows)

array_agg / string_agg 同样受默认帧影响。想要「组内全部明细」必须显式写全分区帧：

sql
-- 组内全部订单号拼接（不受当前行限制）
array_agg(order_no) OVER (
  PARTITION BY user_id
  ORDER BY created_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS 全部订单号
sql
-- string_agg 需要显式分隔符；组内明细拼接成字符串
SELECT
  order_no,
  string_agg(order_no, ', ' ORDER BY created_at, id) OVER (
    PARTITION BY user_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS 用户全部订单
FROM orders
WHERE user_id = 42
ORDER BY created_at, id
LIMIT 1;
text
  order_no  |                    用户全部订单
------------+-------------------------------------------------------
 SO00000043 | SO00000043, SO00000045, SO00000047, SO00000049, SO00020003, SO00040005
(1 row)

json_agg / jsonb_agg 更适合直接把组内明细序列化给前端：

sql
-- 组内明细以 JSON 数组形式附在每行上（PostgreSQL 9.4+）
SELECT DISTINCT
  user_id,
  jsonb_agg(
    jsonb_build_object('no', order_no, 'amount', amount, 'date', created_at)
    ORDER BY created_at, id
  ) OVER (PARTITION BY user_id) AS 订单明细
FROM orders
WHERE user_id IN (42, 44)
ORDER BY user_id;
text
 user_id |                                      订单明细
---------+------------------------------------------------------------------------------------
      42 | [{"no": "SO00000043", "date": "2024-06-03", "amount": 736.10}, {"no": "SO00000045", ...
      44 | [{"no": "SO00000044", "date": "2024-06-04", "amount": 220.10}, {"no": "SO00000046", ...
(2 rows)

上面用了 SELECT DISTINCT 去重，因为 jsonb_agg ... OVER (PARTITION BY user_id) 在同组每一行都会输出完整 JSON。若只想每组一行，直接用 GROUP BY + jsonb_agg 更合适。窗口聚合适合「明细行附带组内聚合」，而不是「按组输出一行」。

DISTINCT 与聚合窗口函数不可混用 ​

count(DISTINCT x) OVER () 在 PostgreSQL 中直接报错：

sql
-- 错误！DISTINCT 聚合不支持窗口形式
SELECT
  city,
  count(DISTINCT user_id) OVER (PARTITION BY city) AS 去重用户数
FROM orders;
-- 报错：DISTINCT is not implemented for window functions

三个替代方案：

sql
-- 方案 1：两层聚合（先把去重数算出来，再连接回明细）
WITH city_uniq AS (
  SELECT city, count(DISTINCT user_id) AS 去重用户数
  FROM orders
  GROUP BY city
)
SELECT o.order_no, u.city, c.去重用户数
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN city_uniq c ON c.city = u.city;

-- 方案 2：先用子查询去重，再用普通窗口聚合
WITH uniq AS (
  SELECT DISTINCT u.city, o.user_id
  FROM orders o JOIN users u ON u.id = o.user_id
)
SELECT
  city,
  count(*) OVER (PARTITION BY city) AS 去重用户数
FROM uniq;

-- 方案 3：用 dense_rank 的最大值代替计数（适合「排名即编号」场景）
SELECT
  city,
  max(dense_rank() OVER (PARTITION BY city ORDER BY user_id)) AS 去重用户数
FROM orders o JOIN users u ON u.id = o.user_id
GROUP BY city;
方案复杂度适用场景
两层聚合 + 连接O(N + M)去重值也要在明细行显示
先去重再窗口O(N log N)只需组内去重计数
dense_rank 最大值O(N log N)去重值恰好是排序键

两层聚合必须用 CTE 拆开 ​

窗口函数不能嵌套，sum(sum(x)) OVER () 这种「先分组再求窗口和」的写法非法：

sql
-- 错误！窗口函数不能嵌套，也不能直接包住普通聚合
SELECT
  city,
  date_trunc('month', created_at) AS 月份,
  sum(sum(amount)) OVER (PARTITION BY city) AS 城市总额   -- 非法
FROM orders
GROUP BY city, date_trunc('month', created_at);
-- 报错：aggregate function calls cannot be nested

正确做法是先 GROUP BY 出一层结果，再在外层窗口聚合：

sql
-- 正确：CTE 拆成两层，先按月聚合，再算城市总额与占比
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 = 'paid'
  GROUP BY u.city, date_trunc('month', o.created_at)
)
SELECT
  城市, 月份, 月销售额,
  城市总额,
  round(100.0 * 月销售额 / 城市总额, 2) AS 占城市比
FROM (
  SELECT
    city AS 城市,
    月份,
    月销售额,
    round(sum(月销售额) OVER (PARTITION BY city), 2) AS 城市总额,
    sum(月销售额) OVER (PARTITION BY city ORDER BY 月份) AS 城市累计
  FROM monthly
) t
ORDER BY 城市, 月份
LIMIT 8;
text
  城市 |    月份    | 月销售额  |  城市总额   | 占城市比
-------+------------+-----------+-------------+----------
 上海  | 2024-06-01 | 184920.30 | 2201266.80  |     8.40
 上海  | 2024-07-01 | 179110.60 | 2201266.80  |     8.14
 上海  | 2024-08-01 | 186420.90 | 2201266.80  |     8.47
 上海  | 2024-09-01 | 178330.20 | 2201266.80  |     8.10
 上海  | 2024-10-01 | 181990.50 | 2201266.80  |     8.27
 上海  | 2024-11-01 | 176880.40 | 2201266.80  |     8.04
 上海  | 2024-12-01 | 180220.10 | 2201266.80  |     8.19
 上海  | 2025-01-01 | 183470.80 | 2201266.80  |     8.33
(8 rows)

两层聚合的顺序不可颠倒：GROUP BY 是「先折叠再算窗口」，窗口函数是「不折叠算聚合」，两者只能分处两层。

ORDER BY 中窗口函数的嵌套限制 ​

窗口函数可以出现在最外层 ORDER BY 中（用于按计算结果排序），但不能出现在窗口函数自己的 ORDER BY 里：

sql
-- 合法：最外层 ORDER BY 用排名结果排序
SELECT order_no, amount,
       row_number() OVER (ORDER BY amount DESC) AS rn
FROM orders
ORDER BY rn
LIMIT 5;

-- 错误！窗口定义内部的 ORDER BY 不能引用窗口函数
SELECT order_no,
       row_number() OVER (
         ORDER BY sum(amount) OVER (PARTITION BY user_id)   -- 非法
       ) AS rn
FROM orders;
-- 报错：window function calls cannot be nested

-- 正确：用 CTE 先算出聚合值
WITH t AS (
  SELECT order_no, sum(amount) OVER (PARTITION BY user_id) AS 用户总额
  FROM orders
)
SELECT order_no, 用户总额,
       row_number() OVER (ORDER BY 用户总额 DESC) AS rn
FROM t;

窗口函数与普通聚合可以共存于同一层（只要不是嵌套关系）：

sql
-- 合法：GROUP BY 出的聚合值作为窗口函数的输入
SELECT
  user_id,
  count(*) AS 订单数,
  sum(count(*)) OVER () AS 全部订单数,     -- count(*)+OVER，外层聚合 sum 的输入是 count(*) 结果
  round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS 订单占比
FROM orders
GROUP BY user_id
ORDER BY 订单数 DESC
LIMIT 5;
text
 user_id | 订单数 | 全部订单数 | 订单占比
---------+--------+------------+----------
       1 |     11 |     200000 |     0.01
       2 |     10 |     200000 |     0.01
       3 |     10 |     200000 |     0.01
       4 |     10 |     200000 |     0.01
       5 |     10 |     200000 |     0.01
(5 rows)

这个模式在「GROUP BY 后加占比列」时极其常用：窗口函数的输入可以是同层 GROUP BY 产生的聚合值，但不能是另一个窗口函数。

实战示例 ​

场景：电商运营日报。需要一张表同时呈现：每个城市每天的销售额、该城市的月累计销售额、该城市占全站的比例、7 日移动平均，以及各状态订单的数量。要求单次查询完成，结果可复现。

步骤 1：准备带缺失日期的日汇总数据 ​

sql
-- 先做日 × 城市的汇总，注意某些城市某天可能没有数据
CREATE TABLE daily_sales AS
SELECT
  u.city,
  o.created_at AS stat_date,
  count(*) AS order_cnt,
  count(*) FILTER (WHERE o.status = 'paid')    AS paid_cnt,
  count(*) FILTER (WHERE o.status = 'pending') AS pending_cnt,
  round(sum(o.amount) FILTER (WHERE o.status = 'paid'), 2) AS paid_amount
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.city, o.created_at;

CREATE UNIQUE INDEX ON daily_sales (city, stat_date);
ANALYZE daily_sales;

步骤 2：一次查询输出全部日报指标 ​

sql
SELECT
  city                                                      AS 城市,
  stat_date                                                 AS 日期,
  order_cnt                                                 AS 订单数,
  paid_amount                                               AS 已付款金额,
  -- 全站当日总额（不分区，即整个结果集）
  round(sum(paid_amount) OVER (PARTITION BY stat_date), 2)  AS 全站当日总额,
  -- 该城市占全站当日比例
  round(100.0 * paid_amount
    / nullif(sum(paid_amount) OVER (PARTITION BY stat_date), 0), 2) AS 占全站比,
  -- 该城市按月累计
  round(sum(paid_amount) OVER (
    PARTITION BY city, date_trunc('month', stat_date)::date
    ORDER BY stat_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ), 2)                                                     AS 该城月累计,
  -- 该城市 7 日移动平均
  round(avg(paid_amount) OVER (
    PARTITION BY city
    ORDER BY stat_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2)                                                     AS 七日移动均值
FROM daily_sales
WHERE city = '杭州'
ORDER BY stat_date
LIMIT 6;
text
  城市 |    日期    | 订单数 | 已付款金额 | 全站当日总额 | 占全站比 | 该城月累计 | 七日移动均值
-------+------------+--------+------------+--------------+----------+------------+--------------
 杭州  | 2024-06-01 |    138 |   62981.40 |    314907.00  |    20.00 |   62981.40 |     62981.40
 杭州  | 2024-06-02 |    132 |   60540.20 |    302701.00  |    20.00 |  123521.60 |     61760.80
 杭州  | 2024-06-03 |    141 |   63820.90 |    319104.50  |    20.00 |  187342.50 |     62447.50
 杭州  | 2024-06-04 |    130 |   60710.50 |    303552.50  |    20.00 |  248053.00 |     62013.25
 杭州  | 2024-06-05 |    139 |   64390.80 |    321954.00  |    20.00 |  312443.80 |     62488.76
 杭州  | 2024-06-06 |    135 |   61990.10 |    309950.50  |    20.00 |  374433.90 |     62572.32
(6 rows)

要点：

  • 一个查询里同时用了 4 个不同的窗口：PARTITION BY stat_date、PARTITION BY city, month、PARTITION BY city ORDER BY stat_date——它们各自需要一次排序。
  • date_trunc('month', stat_date)::date 作为分区键，让「月累计」在月初自动归零。
  • nullif(..., 0) 防止全站当日总额为 0 时除零。

步骤 3：观察多窗口对执行计划的影响 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  city,
  stat_date,
  sum(paid_amount) OVER (PARTITION BY stat_date)                     AS 全站当日,
  sum(paid_amount) OVER (PARTITION BY city ORDER BY stat_date)       AS 城市累计,
  avg(paid_amount) OVER (PARTITION BY city ORDER BY stat_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)                        AS 七日均值
FROM daily_sales;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 WindowAgg  (cost=... rows=...)   -- 七日均值
   ->  WindowAgg  (cost=... rows=...)   -- 城市累计（与七日均值共用同一排序）
         ->  WindowAgg  (cost=... rows=...)   -- 全站当日
               ->  Sort  (cost=... rows=...)   -- 按 stat_date 排序
                     ->  Sort  (cost=... rows=...)   -- 按 city, stat_date 排序
                           ->  Seq Scan on daily_sales

计划中每多一个不同的排序键就多一层 WindowAgg + 一次 Sort。所以日报这类查询要尽量控制窗口数量，或提前物化中间结果。

步骤 4：与 GROUP BY 版实现对比行数 ​

sql
-- 窗口聚合：与输入行数相同
SELECT count(*) AS 窗口版行数 FROM (
  SELECT city, stat_date,
         sum(paid_amount) OVER (PARTITION BY city) AS x
  FROM daily_sales
) t;

-- GROUP BY：每组一行
SELECT count(*) AS 分组版行数 FROM (
  SELECT city, sum(paid_amount) FROM daily_sales GROUP BY city
) t;
text
 窗口版行数 | 分组版行数
------------+------------
       2000 |          5
(1 row)

步骤 5：count(*) OVER () 与 count(*) 的代价对比 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, amount, count(*) OVER () AS 总行数
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC, id
LIMIT 10;
text
 Limit
   ->  WindowAgg
         ->  Sort
               ->  Index Scan using orders_status_amount_id_idx on orders
                     Index Cond: (status = 'paid')

WindowAgg 必须消费所有 5 万行才能得出总数，因此 LIMIT 10 无法提前终止。这是 count(*) OVER () 的固有成本：省了一次查询，但放弃了 LIMIT 的剪枝能力。

注意事项 ​

  1. ORDER BY 会让 sum 从「总额」悄悄变成「累计」，这是最高频的语义陷阱：
sql
-- 返回城市总额（组内每行相同）
sum(amount) OVER (PARTITION BY city)

-- 返回城市内按时间累计（逐行递增）——加了 ORDER BY 就变了语义
sum(amount) OVER (PARTITION BY city ORDER BY created_at)

-- 想两者都要，写两个窗口
sum(amount) OVER (PARTITION BY city)                              AS 城市总额,
sum(amount) OVER (PARTITION BY city ORDER BY created_at, id)      AS 城市累计
  1. count(DISTINCT x) OVER () 直接报错，必须拆成两层：
sql
-- 错误！
SELECT count(DISTINCT user_id) OVER (PARTITION BY city) FROM orders;
-- 报错：DISTINCT is not implemented for window functions
  1. 窗口函数不能嵌套，两层聚合必须用 CTE 拆开：
sql
-- 错误！
SELECT sum(sum(amount)) OVER () FROM orders GROUP BY city;
-- 报错：aggregate function calls cannot be nested
  1. array_agg / string_agg / json_agg 也受帧影响。默认帧只聚合到当前行，要全分区必须显式声明：
sql
-- 不推荐：只拼到当前行
array_agg(order_no) OVER (PARTITION BY user_id ORDER BY created_at)

-- 推荐：拼完整分区
array_agg(order_no) OVER (
  PARTITION BY user_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
  1. 有序集聚合（percentile_cont / mode / percentile_disc）不能用 OVER：
sql
-- 正确：有序集聚合写法
SELECT city, percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS 中位数
FROM orders o JOIN users u ON u.id = o.user_id
GROUP BY city;

-- 错误！ordered-set aggregate 不支持窗口形式
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY city) FROM orders;
-- 报错：OVER specified, but percentile_cont is not a window function nor an aggregate function
  1. min / max 在窗口里加 ORDER BY 后不再「组内极值」，而变成「截至当前行的极值」（前向极值）：
sql
-- 组内最高金额（每行相同）
max(amount) OVER (PARTITION BY user_id)

-- 截至当前行的历史最高金额（逐行可能变化）
max(amount) OVER (PARTITION BY user_id ORDER BY created_at, id)
  1. FILTER 与窗口聚合组合时，FILTER 写在函数后、OVER 前：
sql
-- 正确顺序
count(*) FILTER (WHERE status = 'paid') OVER (PARTITION BY user_id)

-- 错误顺序
count(*) OVER (PARTITION BY user_id) FILTER (WHERE status = 'paid')

  1. 不要用 sum(amount) OVER () 在明细查询里做全表占比——它强制扫描并聚合全表，数据量大时是性能杀手。占比类需求应先在窄结果集上聚合。

  2. avg 的结果是 numeric / double precision，count 是 bigint。用 round(avg(...), 2) 时若输入是 integer，avg 会返回 numeric，可正常取整；但 sum(integer) 返回 bigint，与 numeric 混算时注意隐式转换。

最佳实践 ​

  1. 「明细 + 汇总同屏」一律用窗口聚合，别用聚合子查询 + 连接：
sql
-- 推荐
SELECT
  order_no, amount,
  round(sum(amount) OVER (PARTITION BY user_id), 2) AS 用户总额
FROM orders;

-- 不推荐（多一次扫描）
SELECT o.order_no, o.amount, t.用户总额
FROM orders o
JOIN (SELECT user_id, sum(amount) AS 用户总额 FROM orders GROUP BY user_id) t
  ON t.user_id = o.user_id;
  1. 累积求和显式写 ROWS 并补唯一排序键，保证并列值场景可复现：
sql
sum(amount) OVER (
  PARTITION BY user_id
  ORDER BY created_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS 累计
  1. 移动平均与占比的骨架固定下来，见下：
sql
-- 移动平均
avg(x) OVER (ORDER BY t ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- 组内占比
round(100.0 * x / nullif(sum(x) OVER (PARTITION BY g), 0), 2)

-- 全表占比
round(100.0 * x / nullif(sum(x) OVER (), 0), 2)

  1. 分页总数用 count(*) OVER () 只适合中小结果集；大表深分页建议缓存总数或使用游标分页（Keyset Pagination）。

  2. FILTER 优先于 CASE WHEN，语义更直白：

sql
count(*) FILTER (WHERE status = 'paid')          -- 推荐
count(CASE WHEN status = 'paid' THEN 1 END)      -- 不推荐
  1. 多个窗口尽量共用同一个 WINDOW 定义，减少排序次数：
sql
SELECT
  order_no,
  round(sum(amount) OVER w, 2) AS 组内总额,
  rank() OVER w AS 组内排名
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC, id);
  1. 给窗口排序键建复合索引，让 WindowAgg 省掉 Sort：
窗口定义建议索引
PARTITION BY user_id ORDER BY created_at, id(user_id, created_at, id)
PARTITION BY city ORDER BY amount DESC(city, amount DESC, id)
PARTITION BY status ORDER BY amount DESC(status, amount DESC, id)
  1. 需要「每组一行 + 组内聚合 + 组间占比」时，先 GROUP BY 再用窗口函数，这个组合比纯窗口更快也更省内存：
sql
SELECT
  city,
  round(sum(paid_amount), 2) AS 城市总额,
  round(100.0 * sum(paid_amount) / sum(sum(paid_amount)) OVER (), 2) AS 全站占比
FROM daily_sales
GROUP BY city;

下一节

继续学习：FRAME 子句（ROWS / RANGE / GROUPS）

参考链接 ​