Postgresql 中文操作指南

7.8. WITH Queries (Common Table Expressions) #

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

7.8.1. SELECT in WITH #

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

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 就写出来，但我们需要两级嵌套子选择。这样更容易理解。

7.8.2. Recursive Queries #

可选的 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 查询的一般形式始终是 non-recursive term，然后是 UNION（或 UNION ALL），然后是 recursive term，其中只有递归项可以包含对查询自己的输出的引用。此类查询的执行方式如下：

Recursive Query Evaluation

Note

虽然 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. Search Order #

在使用递归查询计算树遍历时，你可能希望按深度优先或广度优先按顺序对结果进行排序。可通过在其他数据列旁边计算一个排序列并使用该列在最后对结果进行排序来执行此操作。注意，这并不能实际控制查询评估以何种顺序访问行；在 SQL 中，这是始终因实现而异的。此方法只是提供了一种方便的方式来随后对结果进行排序。

要创建深度优先顺序，我们为每行结果计算一个数组，其中包含到目前为止我们已访问的行。例如，请考虑使用 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;

Tip

在只需要跟踪一个字段的常见情况下省略 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;

为获得稳定排序，将数据列添加为次要排序列。

Tip

递归查询评估算法会以广度优先搜索的顺序生成输出。然而，这是一个实现细节，因此依靠它可能不健全。毫无疑问，每个级别中的行顺序是未定义的，因此在任何情况下可能需要一些明确的顺序。

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

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. Cycle Detection #

在使用递归查询时，必须确保查询的递归部分最终不会返回元组，否则查询将无限循环。有时，使用 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 关系包含循环，此查询将循环。由于我们需要“深度”输出，仅将 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;

除了防止循环外，数组值本身通常也很有用，因为它表示到达任何特定行的“路径”。

在需要检查多个字段才能识别循环的一般情况下，请使用行数组。例如，如果我们需要比较字段 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;

Tip

在只需要检查一个字段即可识别循环的常见情况下，省略 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 的输出行中。

Tip

循环路径列的计算方式与上一节中显示的深度优先排序列相同。一个查询既可以有 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. Common Table Expression Materialization #

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. Data-Modifying Statements in 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_子句（参见 Section 6.4），如上例所示。是对 _RETURNING 子句的输出，not_数据修改语句的目标表，该表形成了其余查询可引用的临时表。如果 _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_执行（参见 Chapter 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 规则。