PHP小码农

Appearance

公共表表达式基础（CTE） ​

本节讲解公共表表达式（Common Table Expression，CTE）的基本语法、它与派生表与视图的差别、PostgreSQL 12 引入的内联（Inlining）优化行为，以及用 CTE 提升可读性与复用性的实用套路。学完本节，你将能写出既清晰又不拖慢查询的 WITH 语句。

前置知识

阅读本节前，建议先了解：集合操作 EXCEPT（差集）

基础概念 ​

公共表表达式（Common Table Expression，CTE） 是写在查询开头的临时命名结果集，用 WITH 子句定义，作用域仅限于紧跟其后的那一条语句。

概念术语说明
公共表表达式Common Table ExpressionWITH 定义的命名子查询
子句WITH Clause定义 CTE 的语法结构
内联Inlining优化器把 CTE 展开到主查询
物化Materialization先算完 CTE 再供主查询使用
临时表Temp Table跨语句存活的临时对象
派生表Derived Table写在 FROM 中的子查询
引用Reference在主查询中提到 CTE 的名字
多引用Multiple Reference同一 CTE 被引用多次

CTE、派生表、视图三者的定位：

维度CTE派生表视图（VIEW）
书写位置查询开头FROM 中独立对象
作用域单条语句单个 FROM全局，持久
可否多次引用✅ 同一语句内❌✅
能否递归✅❌❌
是否持久化❌❌✅
可否独立授权❌❌✅

CTE 与临时表的关键差异：

维度CTE临时表
生命周期语句结束即消失会话结束才消失
能否建索引❌✅
能否跨语句使用❌✅
是否写 ANALYZE 统计❌✅
开销无写盘可能写盘

语法与用法 ​

text
-- 单个 CTE
WITH cte_名称 AS (
  SELECT ...
)
SELECT ... FROM cte_名称 WHERE ...;

-- 多个 CTE：后面的可以引用前面的
WITH a AS (SELECT ...),
     b AS (SELECT ... FROM a ...)
SELECT ... FROM b;

-- 显式控制优化行为（PostgreSQL 12+）
WITH a AS MATERIALIZED     (SELECT ...) SELECT ...;
WITH a AS NOT MATERIALIZED (SELECT ...) SELECT ...;

-- 数据修改语句也可以作为 CTE
WITH moved AS (
  DELETE FROM orders WHERE status = 'closed' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

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

sql
-- 用户表
CREATE TABLE users (
  id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT    NOT NULL,
  city TEXT    NOT NULL,
  vip  BOOLEAN NOT NULL DEFAULT false
);

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

-- 商品表
CREATE TABLE products (
  id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku   TEXT           NOT NULL UNIQUE,
  title TEXT           NOT NULL,
  price NUMERIC(10, 2) NOT NULL
);

-- 归档订单表：结构同 orders
CREATE TABLE orders_archive (
  LIKE orders INCLUDING ALL
);

造数：

sql
INSERT INTO users (name, city, vip)
SELECT '用户' || g, (ARRAY['北京','上海','广州','深圳'])[1 + g % 4], g % 5 = 0
FROM generate_series(1, 20000) AS g;

INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT
  'SO-' || lpad(g::text, 6, '0'),
  1 + g % 20000,
  (random() * 1000)::numeric(10, 2),
  (ARRAY['paid','shipped','done','closed'])[1 + g % 4],
  DATE '2025-01-01' + (g % 365)
FROM generate_series(1, 80000) AS g;

INSERT INTO products (sku, title, price)
SELECT 'SKU-' || lpad(g::text, 4, '0'), '商品' || g, (random() * 500)::numeric(10, 2)
FROM generate_series(1, 500) AS g;

ANALYZE users, orders, products;

详细说明 ​

CTE 让复杂查询分段可读 ​

CTE 最大的价值是把嵌套的查询拆成分步的、可命名的阶段。对比两段等效查询：

sql
-- 写法 A：三层嵌套，从内往外读
SELECT c.city, c.user_cnt, c.total_amount
FROM (
  SELECT u.city, count(DISTINCT u.id) AS user_cnt, sum(o.amount) AS total_amount
  FROM (
    SELECT id, city FROM users WHERE vip
  ) AS u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status <> 'closed'
  GROUP BY u.city
) AS c
ORDER BY c.total_amount DESC;
sql
-- 写法 B：CTE 分步命名，从上往下读
WITH vip_users AS (
  SELECT id, city FROM users WHERE vip
),
paid_orders AS (
  SELECT user_id, amount FROM orders WHERE status <> 'closed'
)
SELECT u.city,
       count(DISTINCT u.id) AS user_cnt,
       sum(o.amount)        AS total_amount
FROM vip_users u
JOIN paid_orders o ON o.user_id = u.id
GROUP BY u.city
ORDER BY total_amount DESC;
text
  city | user_cnt | total_amount
-------+----------+--------------
 上海  |      801 |    199230.44
 北京  |      792 |    197118.02
 广州  |      786 |    195204.87
 深圳  |      785 |    194881.31
(4 rows)

两者结果完全相同，但写法 B 的每个阶段都有名字，阅读时不需要在括号之间来回跳转。这是 CTE 的主要用途：可读性，而非性能。

PG 12 之前：CTE 总是物化 ​

在 PostgreSQL 12 之前，CTE 一律作为优化栅栏（Optimization Fence）：先完整算出 CTE 的结果，再让主查询使用。这带来两个后果：

  1. 谓词无法下推：主查询 WHERE 中的条件不能传进 CTE 内部。
  2. 无法利用索引：CTE 内部可能被迫全表扫描。

用 MATERIALIZED 可以强制复现这种行为：

sql
EXPLAIN (ANALYZE, BUFFERS)
WITH recent AS MATERIALIZED (
  SELECT order_no, user_id, amount FROM orders
)
SELECT * FROM recent WHERE user_id = 42;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 CTE Scan on recent  (cost=4812.00..6812.00 rows=400 width=19) (actual time=31.2..38.4 rows=4 loops=1)
   Filter: (user_id = 42)
   Rows Removed by Filter: 79996
   CTE recent
     ->  Seq Scan on orders  (actual time=0.01..16.4 rows=80000 loops=1)
 Execution Time: 39.102 ms

CTE Scan on recent + Rows Removed by Filter: 79996：CTE 被完整物化，随后才在外层过滤掉 79996 行。如果 user_id 上有索引，这个计划是巨大的浪费。

PG 12 之后：单引用 CTE 默认内联 ​

PostgreSQL 12 引入了内联：当 CTE 只被引用一次、且不含易变（volatile）函数或数据修改语句时，优化器会把它像派生表一样展开。

sql
EXPLAIN (ANALYZE, BUFFERS)
WITH recent AS (
  SELECT order_no, user_id, amount FROM orders
)
SELECT * FROM recent WHERE user_id = 42;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Index Scan using idx_orders_user_amount on orders  (actual time=0.02..0.03 rows=4 loops=1)
   Index Cond: (user_id = 42)
 Execution Time: 0.074 ms

CTE Scan 消失了，WHERE 条件被下推成 Index Cond。同样的 SQL，从 39ms 降到 0.07ms。

内联触发条件：

条件是否内联
只被引用 1 次✅ 默认内联
被引用多次❌ 默认物化
含数据修改（INSERT/UPDATE/DELETE）❌ 强制物化
含易变函数（random/nextval）❌ 强制物化（PG 12~17 行为）
显式写 MATERIALIZED❌ 强制物化
显式写 NOT MATERIALIZED✅ 强制内联
递归 CTE❌ 强制物化

用 NOT MATERIALIZED 可以强制内联被多次引用的 CTE：

sql
EXPLAIN (ANALYZE, BUFFERS)
WITH base AS NOT MATERIALIZED (
  SELECT user_id, amount FROM orders WHERE status <> 'closed'
)
SELECT count(*) FROM base WHERE user_id = 42;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=8.62..8.63 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=1)
   ->  Index Only Scan using idx_orders_user_amount on orders  (actual time=0.01..0.02 rows=4 loops=1)
         Index Cond: (user_id = 42)
         Filter: (status <> 'closed'::text)
         Heap Fetches: 0
 Execution Time: 0.061 ms

注意：NOT MATERIALIZED 是建议——如果 CTE 含数据修改语句，优化器会忽略它。

多个 CTE 的依赖与复用 ​

后面的 CTE 可以引用前面的 CTE，形成流水线：

sql
WITH
-- 阶段 1：筛选高价值订单
hv_orders AS (
  SELECT user_id, amount FROM orders WHERE amount > 800 AND status <> 'closed'
),
-- 阶段 2：按用户汇总（引用阶段 1）
user_totals AS (
  SELECT user_id, count(*) AS cnt, sum(amount) AS total FROM hv_orders GROUP BY user_id
),
-- 阶段 3：筛选达标用户（引用阶段 2）
qualified AS (
  SELECT user_id, cnt, total FROM user_totals WHERE total > 2000
)
SELECT u.city, count(*) AS qualified_users, sum(q.total) AS amount_sum
FROM qualified q
JOIN users u ON u.id = q.user_id
GROUP BY u.city
ORDER BY amount_sum DESC;
text
  city | qualified_users | amount_sum
-------+-----------------+------------
 上海  |             428 |  267180.55
 北京  |             419 |  261902.10
 广州  |             407 |  254118.36
 深圳  |             394 |  246029.88
(4 rows)

同一条语句内多次引用同一 CTE 时，默认会物化一次并复用：

sql
EXPLAIN (ANALYZE, BUFFERS)
WITH t AS (
  SELECT user_id, amount FROM orders WHERE created_at >= '2025-10-01'
)
SELECT
  (SELECT count(*)        FROM t)                                AS cnt,
  (SELECT sum(amount)     FROM t)                                AS total,
  (SELECT count(DISTINCT user_id) FROM t)                        AS user_cnt;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Result  (cost=2412.02..2412.03 rows=1 width=40) (actual time=18.4..18.4 rows=1 loops=1)
   CTE t
     ->  Seq Scan on orders  (cost=0.00..1916.00 rows=5990 width=12) (actual time=0.01..9.42 rows=5990 loops=1)
           Filter: (created_at >= '2025-10-01'::date)
           Rows Removed by Filter: 74010
   InitPlan 1 (returns $0)
     ->  Aggregate  (actual time=2.10..2.12 rows=1 loops=1)
           ->  CTE Scan on t  (actual time=0.02..1.42 rows=5990 loops=1)
   InitPlan 2 (returns $1)
     ->  Aggregate  (actual time=2.31..2.32 rows=1 loops=1)
           ->  CTE Scan on t  (actual time=0.02..1.51 rows=5990 loops=1)
   InitPlan 3 (returns $2)
     ->  Aggregate  (actual time=3.01..3.05 rows=1 loops=1)
           ->  CTE Scan on t  (actual time=0.02..2.12 rows=5990 loops=1)
 Execution Time: 19.204 ms

计划中的 CTE t 节点只出现一次：orders 表只被扫描一次，三次聚合各自从 CTE Scan on t 读取物化结果。这是多引用 CTE 的核心收益——避免重复计算。

数据修改型 CTE ​

CTE 中可以写 INSERT / UPDATE / DELETE / MERGE，配合 RETURNING 实现「一次扫描完成迁移」：

sql
-- 把已关闭订单搬到归档表，并返回搬移明细
WITH moved AS (
  DELETE FROM orders
  WHERE status = 'closed' AND created_at < '2025-04-01'
  RETURNING order_no, user_id, amount, status, created_at
)
INSERT INTO orders_archive (order_no, user_id, amount, status, created_at)
SELECT order_no, user_id, amount, status, created_at FROM moved;
text
INSERT 0 14940

重要约束：数据修改型 CTE 之间的可见性有限——同一语句中的多个修改型 CTE 看不到彼此对表的修改，它们看到的是语句开始前的快照。

sql
-- 危险！第二个 CTE 看到的是 orders 的原始快照
WITH d AS (
  DELETE FROM orders WHERE status = 'closed' RETURNING id
),
u AS (
  UPDATE orders SET status = 'done' WHERE status = 'closed' RETURNING id
)
SELECT (SELECT count(*) FROM d) AS deleted, (SELECT count(*) FROM u) AS updated;

数据修改型 CTE 在 PostgreSQL 中强制执行物化，无法内联。

CTE 与索引的组合 ​

由于 CTE 内部不能建索引，要利用索引，就必须让谓词下推进去。当 CTE 被多次引用时，可以通过重构把过滤条件放进 CTE：

sql
-- 不好：外层过滤，CTE 内部全表扫描
WITH all_orders AS MATERIALIZED (SELECT * FROM orders)
SELECT city, count(*) FROM all_orders o JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2025-10-01'
GROUP BY city;
sql
-- 更好：过滤条件放进 CTE，索引可用
WITH recent_orders AS (
  SELECT user_id, amount FROM orders WHERE created_at >= '2025-10-01'
)
SELECT city, count(*) FROM recent_orders o JOIN users u ON u.id = o.user_id
GROUP BY city;

实战示例 ​

目标：构建一张「城市 × 订单状态」的经营看板，需要同时输出明细统计与全局占比，并对结果做多级校验。

步骤 1：建立分步 CTE

sql
WITH
-- 阶段 1：基数过滤，尽早缩小数据量
valid_orders AS (
  SELECT id, user_id, amount, status
  FROM orders
  WHERE created_at >= '2025-06-01'
    AND user_id IS NOT NULL
    AND status <> 'closed'
),
-- 阶段 2：关联城市
city_orders AS (
  SELECT u.city, v.status, v.amount
  FROM valid_orders v
  JOIN users u ON u.id = v.user_id
),
-- 阶段 3：按城市与状态汇总
by_city_status AS (
  SELECT city, status, count(*) AS cnt, sum(amount) AS amount_sum
  FROM city_orders
  GROUP BY city, status
)
SELECT * FROM by_city_status ORDER BY city, status;
text
  city | status  | cnt  | amount_sum
-------+---------+------+------------
 上海  | done    | 3712 | 1856012.44
 上海  | paid    | 3698 | 1848302.10
 上海  | shipped | 3705 | 1854109.87
 北京  | done    | 3684 | 1842103.02
 北京  | paid    | 3691 | 1845102.55
 北京  | shipped | 3678 | 1839201.31
 广州  | done    | 3655 | 1828601.44
 广州  | paid    | 3672 | 1836201.87
 广州  | shipped | 3661 | 1831102.02
 深圳  | done    | 3641 | 1821103.44
 深圳  | paid    | 3648 | 1824201.10
 深圳  | shipped | 3652 | 1826102.87
(12 rows)

步骤 2：用 CTE 计算占比（引用上一步的结果）

sql
WITH
valid_orders AS (
  SELECT id, user_id, amount, status
  FROM orders
  WHERE created_at >= '2025-06-01' AND user_id IS NOT NULL AND status <> 'closed'
),
by_city AS (
  SELECT u.city, count(*) AS cnt, sum(v.amount) AS amount_sum
  FROM valid_orders v
  JOIN users u ON u.id = v.user_id
  GROUP BY u.city
),
total AS (
  SELECT sum(cnt) AS all_cnt, sum(amount_sum) AS all_amount FROM by_city
)
SELECT
  c.city,
  c.cnt,
  c.amount_sum,
  round(c.cnt * 100.0 / t.all_cnt, 2)          AS cnt_pct,
  round(c.amount_sum * 100.0 / t.all_amount, 2) AS amount_pct
FROM by_city c
CROSS JOIN total t
ORDER BY c.amount_sum DESC;
text
  city |  cnt  | amount_sum  | cnt_pct | amount_pct
-------+-------+-------------+---------+------------
 上海  | 11115 |  5558424.41 |   25.08 |      25.08
 北京  | 11053 |  5526406.88 |   24.94 |      24.93
 广州  | 10988 |  5495905.33 |   24.80 |      24.79
 深圳  | 10941 |  5471407.41 |   24.69 |      24.68
(4 rows)

CROSS JOIN total 把单行汇总结果广播给每一行——这是「计算全局占比」的标准写法。

步骤 3：用 CTE 组织多指标报表

sql
WITH
-- 指标 1：订单总量
order_stat AS (
  SELECT count(*) AS order_cnt, sum(amount) AS order_amount FROM orders
),
-- 指标 2：用户总量
user_stat AS (
  SELECT count(*) AS user_cnt, count(*) FILTER (WHERE vip) AS vip_cnt FROM users
),
-- 指标 3：商品总量
product_stat AS (
  SELECT count(*) AS product_cnt, avg(price) AS avg_price FROM products
)
SELECT
  o.order_cnt,
  o.order_amount,
  u.user_cnt,
  u.vip_cnt,
  p.product_cnt,
  round(p.avg_price, 2) AS avg_price
FROM order_stat o, user_stat u, product_stat p;
text
 order_cnt | order_amount | user_cnt | vip_cnt | product_cnt | avg_price
-----------+--------------+----------+---------+-------------+-----------
     80000 |  39998121.44 |    20000 |    4000 |         500 |    250.31
(1 row)

步骤 4：对比 CTE 与嵌套写法的计划差异

sql
EXPLAIN (ANALYZE, BUFFERS)
WITH t AS (
  SELECT user_id, amount FROM orders WHERE created_at >= '2025-10-01'
)
SELECT count(*) FROM t WHERE user_id = 42;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=8.62..8.63 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=1)
   ->  Index Only Scan using idx_orders_user_amount on orders  (actual time=0.01..0.02 rows=4 loops=1)
         Index Cond: (user_id = 42)
         Filter: (created_at >= '2025-10-01'::date)
         Heap Fetches: 0
 Execution Time: 0.058 ms

没有 CTE Scan 节点：单引用 CTE 被内联，外层条件变成了 Index Cond。只要不写 MATERIALIZED，PG 12+ 的默认行为就是对的。

步骤 5：验证 CTE 写法与嵌套写法结果一致

sql
WITH a AS (
  SELECT u.city, count(DISTINCT u.id) AS user_cnt, sum(o.amount) AS total_amount
  FROM (SELECT id, city FROM users WHERE vip) AS u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status <> 'closed'
  GROUP BY u.city
), b AS (
  WITH vip_users AS (SELECT id, city FROM users WHERE vip),
       paid_orders AS (SELECT user_id, amount FROM orders WHERE status <> 'closed')
  SELECT u.city, count(DISTINCT u.id) AS user_cnt, sum(o.amount) AS total_amount
  FROM vip_users u JOIN paid_orders o ON o.user_id = u.id
  GROUP BY u.city
)
SELECT (SELECT count(*) FROM (SELECT * FROM a EXCEPT SELECT * FROM b) d) AS only_in_a,
       (SELECT count(*) FROM (SELECT * FROM b EXCEPT SELECT * FROM a) d) AS only_in_b;
text
 only_in_a | only_in_b
-----------+-----------
         0 |         0
(1 row)

注意事项 ​

  1. 不要在 WITH 中写 MATERIALIZED 除非确实需要。它会让谓词无法下推，代价可能是数量级的。
sql
-- 不推荐：强制物化，索引失效
WITH t AS MATERIALIZED (SELECT * FROM orders)
SELECT * FROM t WHERE user_id = 42;
sql
-- 正确：让它自然内联
WITH t AS (SELECT * FROM orders)
SELECT * FROM t WHERE user_id = 42;
  1. 数据修改型 CTE 互相看不见对方的效果，不要用两个 CTE 对同一张表做「先删后改」：
sql
-- 错误！UPDATE 看到的仍是旧快照
WITH d AS (DELETE FROM orders WHERE status = 'closed' RETURNING id),
     u AS (UPDATE orders SET status = 'done' WHERE status = 'closed' RETURNING id)
SELECT (SELECT count(*) FROM d), (SELECT count(*) FROM u);
  1. CTE 不能被索引，也不能被 ANALYZE。需要索引时改用临时表：
sql
CREATE TEMP TABLE tmp_hot ON COMMIT DROP AS
SELECT user_id, amount FROM orders WHERE amount > 800;

CREATE INDEX ON tmp_hot (user_id);
ANALYZE tmp_hot;
  1. CTE 不能跨语句复用。它只在紧跟的那一条语句中有效：
sql
-- 错误！第二条语句找不到 CTE
WITH t AS (SELECT 1 AS v) SELECT * FROM t;
SELECT * FROM t;
text
ERROR:  relation "t" does not exist
LINE 1: SELECT * FROM t;
  1. 不要用一个 CTE 只是想「给表起个别名」，这会牺牲可读性而毫无收益：
sql
-- 冗余
WITH o AS (SELECT * FROM orders) SELECT * FROM o;
  1. 含易变函数的 CTE 会被强制物化，即使只引用一次：
sql
-- random() 使内联失效
WITH t AS (SELECT id, random() AS r FROM orders)
SELECT * FROM t WHERE id = 42;

最佳实践 ​

  1. 把 CTE 当作可读性工具，而非性能工具。 分段命名复杂逻辑；如果发现性能下降，先检查是否被意外物化。

  2. 让每个 CTE 只做一件事，并用有业务含义的名字命名：valid_orders、vip_users、by_city_status。

  3. 尽早过滤，把能缩小数据量的条件写进第一个 CTE 内部，让索引发挥作用：

sql
WITH valid_orders AS (
  SELECT id, user_id, amount, status FROM orders
  WHERE created_at >= '2025-06-01' AND user_id IS NOT NULL AND status <> 'closed'
)
  1. 只有确实需要「算一次、用多次」时才依赖物化，并清楚此时谓词不会下推：
sql
-- 三次聚合共享一次扫描，物化是划算的
WITH t AS (SELECT user_id, amount FROM orders WHERE created_at >= '2025-10-01')
SELECT (SELECT count(*) FROM t), (SELECT sum(amount) FROM t), (SELECT count(DISTINCT user_id) FROM t);
  1. 数据迁移用「修改型 CTE + RETURNING」，一次扫描完成搬移：
sql
WITH moved AS (
  DELETE FROM orders WHERE status = 'closed' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
  1. 需要索引支持时用临时表而非 CTE：
sql
CREATE TEMP TABLE tmp_hot ON COMMIT DROP AS SELECT user_id, amount FROM orders WHERE amount > 800;
CREATE INDEX ON tmp_hot (user_id);
  1. 用 EXPLAIN 确认是否出现了 CTE Scan，它能立刻告诉你 CTE 是被内联了还是被物化了。

下一节

继续学习：递归 CTE

参考链接 ​