7.8. WITH 查询(公用表表达式)

• 7 .8.1. 在 WITH 中选择

• 7 .8.2. WITH 中的数据修改语句

WITH提供了一种编写用于较大查询的辅助语句的方法。这些语句(通常称为“公用表表达式”或 CTE)可以被认为是定义仅用于一个查询的临时表。 WITH子句中的每个辅助语句可以是SELECT，INSERT，UPDATE或DELETE； WITH子句本身附加到主语句，该主语句也可以是SELECT，INSERT，UPDATE或DELETE。

7 .8.1. 在 WITH 中选择

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。遵循这种方式会容易一些。

可选的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)，请丢弃重复的行。将所有剩余的行包括在递归查询的结果中，并将它们放置在临时的“工作表”中。

• 只要工作表不为空，请重复以下步骤：

• 评估递归项，将工作表的当前内容替换为递归自引用。对于UNION(但不是UNION ALL)，请丢弃重复的行以及与任何先前结果行重复的行。将所有剩余的行包括在递归查询的结果中，并将它们放置在临时中间表中。

  • 用中间表的内容替换工作表的内容，然后清空中间表。

在上面的示例中，工作表在每个步骤中只有一行，并且在连续的步骤中采用 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
    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

使用递归查询时，重要的是要确保查询的递归部分最终将不返回任何 Tuples，否则查询将无限期地循环。有时，使用UNION而不是UNION ALL可以通过丢弃与以前的输出行重复的行来实现。但是，一个循环通常不包含完全重复的输出行：可能有必要仅检查一个或几个字段，以查看之前是否到达同一点。处理此类情况的标准方法是计算一个已访问值的数组。例如，考虑以下使用link字段搜索表graph的查询：

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
)
SELECT * FROM search_graph;

如果link关系包含循环，则此查询将循环。因为我们需要“深度”输出，所以仅将UNION ALL更改为UNION并不能消除循环。相反，我们需要识别在遵循特定链接路径时是否再次到达同一行。我们向容易循环的查询中添加两列path和cycle：

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

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

在通常情况下，需要检查多个字段以识别一个循环，请使用一组行。例如，如果我们需要比较字段f1和f2：

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

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

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

之所以可行，是因为 PostgreSQL 的实现只计算WITH查询的行数，而不是父查询实际获取的行数。不建议在 Producing 使用此技巧，因为其他系统可能会以不同的方式工作。另外，如果您使外部查询对递归查询的结果进行排序或将它们连接到其他表，则通常将不起作用，因为在这种情况下，外部查询通常将始终尝试获取所有WITH查询的输出。

WITH查询的一个有用属性是，即使父查询或同级WITH查询多次引用了它们，但每次执行父查询仅对它们进行一次评估。因此，可以在WITH查询中放置多个位置所需的昂贵计算，以避免多余的工作。另一个可能的应用是防止对副作用进行不必要的多重评估。但是，另一方面，与普通子查询相比，优化器将限制从父查询向下推到WITH查询的能力也较小。 WITH查询通常将被评估为已写入，而不会抑制父查询之后可能会丢弃的行。 (但是，如上所述，如果对查询的引用仅需要有限的行数，则评估可能会提前停止.)

上面的示例仅显示WITH与SELECT一起使用，但是可以以相同的方式将其连接到INSERT，UPDATE或DELETE。在每种情况下，它都有效地提供了可以在 main 命令中引用的临时表。

7 .8.2. WITH 中的数据修改语句

您可以在WITH中使用数据修改语句(INSERT，UPDATE或DELETE)。这使您可以在同一查询中执行几个不同的操作。一个例子是：

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子句的输出(不是*数据修改语句的目标表)形成了可由其余查询引用的临时表。如果WITH中的数据修改语句缺少RETURNING子句，则它不会形成临时表，因此无法在其余查询中引用。尽管如此，仍将执行这样的语句。一个不是特别有用的示例是：

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

本示例将从表foo和bar删除所有行。向 Client 端报告的受影响的行数仅包括从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中使用数据修改语句时，指定更新实际发生的 Sequences 是不可预测的。所有语句均使用相同的快照(请参见Chapter 13)执行，因此它们无法“看到”彼此对目标表的影响。这减轻了行更新的实际 Sequences 的不可预测性的影响，并且意味着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个子语句。这种 Statements 的影响是不可预测的。

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