PostgreSQL 17 文档: 7.8. WITH 查询（通用表表达式）

7.8. `WITH` 查询（通用表表达式）
上一个	向上	第 7 章查询	主页	下一个

7.8. `WITH` 查询（通用表表达式） #

7.8.1. SELECT in WITH
7.8.2. 递归查询
7.8.3. 通用表表达式的具象化
7.8.4. WITH 中的数据修改语句

WITH提供了一种方法，可用于编写辅助语句，以便在较大的查询中使用。这些语句通常称为公共表表达式或CTE，可以认为是只为一个查询存在的临时表的定义。WITH子句中的每个辅助语句可以是SELECT、INSERT、UPDATE、DELETE或MERGE；而WITH子句本身附加到还可以是SELECT、INSERT、UPDATE、DELETE或MERGE的主语句。

7.8.1. `WITH`中的`SELECT`#

WITH中的SELECT的基本价值在于将复杂查询细分为更简单的部分。一个示例是

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

该示例只在顶级销售区域中显示按产品划分的销售总额。WITH子句定义了两个辅助语句，分别名为regional_sales和top_regions，其中regional_sales的输出用于top_regions，而top_regions的输出用于SELECT主查询。这个示例原本也可以不用WITH来编写，但我们需要两级嵌套的子SELECT。使用此方法更容易理解。

7.8.2. 递归查询#

可选的RECURSIVE修饰符让WITH不仅仅是语法上的便利性，而且成为一项功能，可以实现标准 SQL 中无法实现的功能。通过使用RECURSIVE，WITH查询可以引用其自身的输出。一个非常简单的示例是这个用来对 1 到 100 之间的整数进行求和的查询

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

递归WITH查询的一般形式永远是非递归项，然后是UNION（或UNION ALL），然后再是递归项，其中只有递归项可以包含对查询自己的输出的引用。此类查询按照如下方式执行

递归查询评估

评估非递归项。对于UNION（但对于UNION ALL则不然），丢弃重复的行。将所有剩余行包含在递归查询的结果中，还将它们放置在一个临时工作表中。
只要工作表不为空，就重复以下步骤
1. 评估递归术语，以工作表中的当前内容替代递归自引用。对于 UNION（但不是 UNION ALL），丢弃重复行和重复任何先前结果行的行。将所有剩余行包含在递归查询结果中，并将它们放置在临时中间表中。
2. 用中间表的内容替换工作表的内容，然后清空中间表。

注意

虽然 RECURSIVE 允许递归指定查询，但内部会迭代评估此类查询。

在上述示例中，工作表在每一步中只有一行，并且它在连续的步骤中获取 1 到 100 的值。在第 100 步中，由于 WHERE 子句，没有输出，因此查询终止。

递归查询通常用于处理层次或树形结构化数据。一个有用的示例是仅在一个显示直接包含关系的表中，查找产品的全部直接和间接子部件的查询

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity * pr.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

7.8.2.1. 搜索顺序 #

在使用递归查询计算树遍历时，你可能想要按照深度优先或广度优先顺序对结果进行排序。这可以通过在其他数据列旁边计算一个排序列并在最后用它对结果进行排序来完成。请注意，这实际上不会控制查询评估访问行的顺序；它始终根据 SQL 实现而定。此方法只是提供了一种在随后对结果进行排序的便捷方法。

要创建一个深度优先顺序，我们将针对每个结果行计算一个我们 bisher 访问过的行数组。例如，考虑使用 link 字段搜索表格 tree 的以下查询

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree;

要添加深度优先排序信息，可以编写此信息

WITH RECURSIVE search_tree(id, link, data, path) AS (
    SELECT t.id, t.link, t.data, ARRAY[t.id]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || t.id
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

在需要使用多个字段来识别行的常规情况下，请使用一个行数组。例如，如果我们需要跟踪字段 f1 和 f2

WITH RECURSIVE search_tree(id, link, data, path) AS (
    SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2)
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

提示

在只需要跟踪一个字段的常见情况下，省略 ROW() 语法。这允许使用一个简单数组而不是复合类型数组，从而提高效率。

要创建广度优先顺序，你可以添加一个跟踪搜索深度的列，例如

WITH RECURSIVE search_tree(id, link, data, depth) AS (
    SELECT t.id, t.link, t.data, 0
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, depth + 1
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY depth;

要获取稳定排序，请添加数据列作为辅助排序列。

提示

递归查询评估算法以广度优先搜索顺序生成输出。但是，这是一个实现细节，并且依赖于它也许是不合理的。每一级中的行的顺序肯定是不确定的，因此在任何情况下都可能需要一些明确的排序。

有内置语法来计算深度或广度优先排序列。例如

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH BREADTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

此语法在内部扩展为与上述手写形式类似的内容。SEARCH 子句指定是需要深度优先搜索还是广度优先搜索、要追踪用于排序的列列表，以及将包含可用于排序的结果数据的列名称。该列将隐式添加到 CTE 的输出行中。

7.8.2.2. 循环检测 #

在使用递归查询时，务必确保查询的递归部分最终不会返回任何元组，否则查询将无限循环。有时，使用 UNION 代替 UNION ALL 可以通过丢弃重复先前输出行的行来实现此目的。但是，通常循环并不涉及完全重复的输出行：可能需要只检查一个或几个字段，看看是否以前已经到达过同一点。处理此类情况的标准方法是计算已访问值的一个数组。例如，再次考虑使用 link 字段搜索表 graph 的以下查询

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 0
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
)
SELECT * FROM search_graph;

如果 link 关系包含循环，则此查询将循环。因为我们需要一个 “depth” 输出，所以仅仅将 UNION ALL 更改为 UNION 不会消除循环。相反，我们需要识别在沿着特定链接路径时是否再次到达了同一行。我们将两列 is_cycle 和 path 添加到易循环查询中

WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
    SELECT g.id, g.link, g.data, 0,
      false,
      ARRAY[g.id]
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      g.id = ANY(path),
      path || g.id
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT is_cycle
)
SELECT * FROM search_graph;

除了防止循环之外，数组值本身通常还可用作表示到达任何特定行的 “path”。

在一般情况下，需要检查多个字段才能识别循环时，使用行数组。例如，如果我们需要比较字段 f1 和 f2

WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS (
    SELECT g.id, g.link, g.data, 0,
      false,
      ARRAY[ROW(g.f1, g.f2)]
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      ROW(g.f1, g.f2) = ANY(path),
      path || ROW(g.f1, g.f2)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT is_cycle
)
SELECT * FROM search_graph;

提示

在只需要检查一个字段即可识别循环的常见情况下，省略 ROW() 语法。这允许使用简单数组而不是复合类型数组，从而提高效率。

内置语法简化了循环检测。上面的查询也可以这样写

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;

它会内部重写为上面的形式。CYCLE 子句首先指定用于循环检测的列列表，然后指定一个显示是否检测到循环的列名，最后指定一个将跟踪路径的另一列名。循环和路径列将隐式添加到 CTE 的输出行中。

提示

循环路径列的计算方式与上一部分中显示的深度优先排序列相同。一个查询既可以包含 SEARCH 子句，也可以包含 CYCLE 子句，但深度优先搜索规范和循环检测规范将创建冗余计算，因此仅使用 CYCLE 子句并按路径列排序更有效率。如果需要广度优先排序，则同时指定 SEARCH 和 CYCLE 会很有用。

当不确定查询是否会循环时，一个有用的测试查询技巧是在父查询中放置一个 LIMIT。例如，如果没有 LIMIT，此查询将无限循环

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

这样做有效，因为 PostgreSQL 的实现只计算实际由父查询提取的 WITH 查询的行数。不建议在生产中使用此技巧，因为其他系统可能以不同的方式工作。此外，如果您让外部查询对递归查询的结果进行排序或将其连接到其他表，它通常不起作用，因为在这种情况下，外部查询通常会尝试提取所有 WITH 查询的输出。

7.8.3. 公用表表达式具体化 #

对 WITH 查询很有用的一个特性是，这些查询通常只在父级查询执行一次，即使它们被父级查询或兄弟 WITH 查询引用多次也是如此。因此，可以在 WITH 查询中放置在多个地方需要的开销很大的计算，以避免冗余工作。另一个可能的应用是防止对副作用函数进行不需要的多次评估。但是，这个硬币的另一面是，优化器无法将父级查询中的约束下推到一个多次引用的 WITH 查询中，因为这可能影响到 WITH 查询输出的所有用途，而它应该只影响其中一个。多次引用的 WITH 查询将按照书写方式进行评估，不会抑制父级查询随后可能丢弃的行。（但如上所述，如果对该查询的引用只需要有限数量的行，则评估可能会提前停止。）

但是，如果 WITH 查询是不可递归且无副作用的（即，它是一个不包含不稳定函数的 SELECT），那么它可以折叠到父级查询中，从而允许对这两个查询级别进行联合优化。默认情况下，如果父级查询引用 WITH 查询一次，就会发生这种情况，但如果父级查询多次引用 WITH 查询，就不会发生。您可以通过指定 MATERIALIZED 来强制分开计算 WITH 查询，或通过指定 NOT MATERIALIZED 来强制将其合并到父级查询中来覆盖该决策。后者选择有进行 WITH 查询的重复计算的风险，但如果 WITH 查询的每个用法只需要 WITH 查询的完整输出的一小部分，它仍然可以产生净节约。

这些规则的一个简单示例是

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

这个 WITH 查询将被折叠，生成与之相同的执行计划

SELECT * FROM big_table WHERE key = 123;

特别地，如果在 key 上有一个索引，它可能会被用于仅获取 key = 123 的行。另一方面，在

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

中，WITH 查询将被具体化，生成 big_table 的一个临时副本，然后将其与自身联接——没有任何索引的好处。如果写成

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

那么该查询将被更有效地执行，这样父级查询的约束就可以直接应用到对 big_table 的扫描中。

其中 NOT MATERIALIZED 可能不理想的一个示例是

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

在这里，WITH 查询的具体化确保 very_expensive_function 只针对每行表评估一次，而不是两次。

上面的示例只展示了将 WITH 与 SELECT 一起使用，但它可以以相同的方式附加到 INSERT、UPDATE、DELETE 或 MERGE。在每种情况下，它实际上都提供了一个临时表，可以在主命令中引用。

7.8.4. 在 `WITH` 中的数据修改语句 #

您可以在 WITH 中使用数据修改语句（INSERT、UPDATE、DELETE 或 MERGE）。这允许您在同一个查询中执行多个不同的操作。一个示例是

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

此查询实际上将行从 products 移动到 products_log。在 WITH 中的 DELETE 从 products 中删除指定的行，通过其 RETURNING 从句返回其内容；然后主查询读取该输出并将其插入到 products_log 中。

上述示例的一个重点是 WITH 从句附加到 INSERT，而不是 INSERT 中的子 SELECT。这是必要的，因为仅允许在附加到顶级语句的 WITH 从句中使用数据修改语句。但是，应用正常的 WITH 可见性规则，因此可以从子 SELECT 引用 WITH 语句的输出。

在 WITH 中的数据修改语句通常带有 RETURNING 从句（参见第 6.4 节），如上面的示例所示。形成临时表并可供查询的其余部分引用的是 RETURNING 从句的输出，而不是 数据修改语句的目标表。如果 WITH 中的数据修改语句缺少 RETURNING 从句，则它不会形成临时表，也无法在查询的其余部分中引用。此类语句仍将执行。一个不太有用的示例是

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

此示例将删除表 foo 和 bar 中的所有行。向客户端报告的影响行数将仅包括从 bar 中删除的行。

递归性自引用不可用于数据修改语句中。在某些情况下，可以引用递归 WITH 的输出结果绕过此限制，例如

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

此查询将移除产品的全部直接或间接子部件。

WITH 中的数据修改语句只执行一次，且始终执行完毕，与主要查询是否读取全部（或任何）其输出结果无关。请注意，这与 WITH 中 SELECT 的规则不同：如上一节中所述，执行 SELECT 仅运行到主要查询对其输出结果提出需求时为止。

WITH 中的子语句会相互并发执行，并与主查询并发执行。因此，当在 WITH 中使用数据修改语句时，指定更新的实际发生顺序不可预测。全部语句都使用相同的 snapshot （参见第 13 章）执行，因此它们无法““查看””对方对目标表的修改。这减轻了行更新实际顺序不可预测的影响，且意味着 RETURNING 数据是不同 WITH 子语句和主查询之间沟通更改的唯一方法。这方面的一个示例是，在

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

外部 SELECT 将在 UPDATE 的操作之前返回原先的价格，而在

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

外部 SELECT 将返回更新后的数据。

不支持在单个语句中两次尝试更新同一行。仅执行一次修改，但很难（有时甚至不可能）准确预测修改哪一次。这同样适用于删除在同一语句中已经更新的行：仅执行更新。因此，通常应避免在单个语句中尝试两次修改同一行。特别是，不要编写可能影响主要语句或同级子语句更改的同一行的 WITH 子语句。此类语句的影响不可预测。

目前，任何用作 WITH 中数据修改语句的目标的表不得具有条件规则或 ALSO 规则，也不能具有扩展到多个语句的 INSTEAD 规则。