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；而WITH子句本身附加到主语句，主语句可以是SELECT、INSERT、UPDATE、DELETE或MERGE。

7.8.1WITH中的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），然后是一个递归项，其中只有递归项可以包含对查询自身输出的引用。此类查询的执行方式如下

递归查询评估

1. 评估非递归项。对于 UNION（但不是 UNION ALL），丢弃重复行。将所有剩余行包括在递归查询的结果中，并将它们放置在临时 工作表 中。

2. 只要工作表不为空，就重复以下步骤

   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 实现。此方法仅仅提供了一种方便的方式来事后对结果进行排序。

要创建深度优先顺序，我们为每个结果行计算一个我们迄今为止已访问的行数组。例如，考虑使用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中使用数据修改语句时，实际发生的指定更新的顺序是不可预测的。所有语句都使用相同的快照执行（请参阅第 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规则。