WITH查询（公用表表达式）

WITH子句提供在一个更大的SELECT查询中，使用子查询或执行数据修改操作的方式。你可以在INSERT, UPDATE, 或 DELETE 命令中使用WITH子句。

在WITH子句中的查询 在WITH子句中使用SELECT的相关信息

WITH子句中的数据修改语句, 在WITH子句中使用INSERT, UPDATE, or DELETE

Note: 这些是使用WITH子句的限制。

• 对于包含WITH子句的SELECT命令，该子句最多只能包含一个修改表数据的子句（INSERT, UPDATE, 或 DELETE命令）。
• 对于包含WITH子句的数据修改命令（INSERT, UPDATE, 或 DELETE），该子句只能包含SELECT命令，WITH子句不能包含数据修改命令。

默认情况下，将启用WITH子句的RECURSIVE关键字。通过将服务器配置参数gp“recursive”设置为false，可以禁用递归。By default, the RECURSIVE keyword for the WITH clause is enabled. 通过将服务器配置参数gp_recursive_cte 设置为false，WITH子句的RECURSIVE关键字被禁用。

Parent topic: 查询数据

在WITH子句中的查询

子查询通常被称为公共表表达式或CTE，可以认为是为查询定义临时表。这些示例显示了与SELECT命令一起使用的WITH子句。带WITH子句的示例可以以插入、更新或删除的相同方式使用。在每种情况下，WITH子句都有效地提供了可以在主命令中引用的临时表。

WITH子句中的SELECT命令在每次执行父查询时只计算一次，即使父查询或WITH子句的同级多次引用了该命令。因此，需要在多个地方进行的昂贵计算可以放在WITH子句中，以避免重复工作。另一个可能的应用是防止对具有副作用的函数进行不必要的多次计算。然而，这种情况的另一方面是，与普通的子查询相比，优化器无法将来自父查询的限制向下推送到WITH查询中。 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子句编写查询，但需要两级嵌套子选择。相比较来说，WITH子句比较容易。

当启用可选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子句（使用WITH关键字的WITH子句）的一般形式是一个非递归术语，后跟一个 UNION（或 UNION ALL），然后是一个递归术语，其中只有递归术语可以包含对查询输出的引用。

non_recursive_term UNION [ ALL ] recursive_term

包含 UNION [ ALL ]的递归WITH 查询执行如下：

1. 计算非递归项。对于UNION （而不是UNION ALL），丢弃重复的行。包括递归查询结果中的所有剩余行，并将它们放在临时工作表中。
2. 只要工作台不是空的，重复以下步骤：
   1. 计算递归项，将工作表的当前内容替换为递归自引用。对于 UNION（而不是UNION ALL），放弃重复的行和复制任何先前结果行的行。在递归查询的结果中包含所有剩余的行，并将它们放在临时中间表中。
   2. 将当前工作表的内容替换为中间表的内容，然后清空中间表。

Note: 严格来说，过程是迭代而不是递归的，但是RECURSIVE是由SQL标准委员会选择的术语。

递归WITH查询通常用于处理层次结构或树结构数据。例如，该查询查找产品的所有直接和间接子部分，只给出一个显示直接包含内容的表：

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 ;

使用递归WITH时，必须确保查询的递归部分最终不会返回元组，否则查询将无限期循环。 在计算整数和的示例中，工作表在每个步骤中包含一行，并在连续步骤中接受从1到100的值。 在第100步中，由于WHERE子句没有输出，查询终止。

对于某些查询，使用 UNION而不是 UNION ALL可以通过丢弃重复以前输出行的行来确保查询的递归部分最终不返回元组。然而，一个循环通常不涉及完全重复的输出行：只检查一个或几个字段就足够了，以查看以前是否达到了相同的点。处理这种情况的标准方法是计算访问值的数组。 例如，考虑使用链接字段搜索表图形的以下查询：

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;

如果链接关系包含循环，则此查询将循环。因为查询需要深度输出，所以将 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;

除了检测循环之外， path的数组值本身也很有用，因为它表示到达任何特定行所需的路径。

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

Tip: 如果只需要检查一个字段来识别循环，则忽略ROW()语法。它使用简单的数组而不是复合类型的数组，从而提高了效率。

Tip: 递归查询评估算法按广度优先搜索顺序生成输出。通过按这种方式构造的路径列对外部查询进行ORDER BY，可以按深度优先搜索顺序显示结果。

当您不确定查询是否可能无限循环时，测试查询的一种有用技术是在父查询中设置 LIMIT 。例如，如果不使用 LIMIT 子句，则此查询将永远循环：

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

该技术之所以有效，是因为递归WITH实现只计算WITH查询中实际由父查询提取的行数。不建议在生产中使用此技术，因为其他系统的工作方式可能不同。 另外，如果外部查询使用结果对递归进行排序或将结果连接到另一个表，则该技术可能不起作用。

WITH子句中的数据修改语句

对于SELECT命令，可以在 WITH子句中使用数据修改命令 INSERT, UPDATE, 或DELETE。这允许您在同一查询中执行几个不同的操作

WITH子句中的数据修改语句只执行一次，并且始终执行到完成，这与主查询是否读取所有（或任何）输出无关。这与在WITH子句中使用 SELECT 时的规则不同，只有在主查询要求输出时，才会继续执行 SELECT 。

这个简单的CTE查询从products中删除行。 WITH子句中的 DELETE从产品中删除指定的行，并通过其返回子句返回其内容。

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

WITH子句中的数据修改语句必须有RETURNING 子句，如前一个示例所示。它是RETURNING 子句的输出，而不是数据修改语句的目标表，形成了可以被查询的其余部分引用的临时表。如果 WITH中的数据修改语句缺少RETURNING子句，则返回错误。

如果启用可选的RECURSIVE关键字，则不允许在数据修改语句中进行递归自引用。在某些情况下，可以通过引用递归的输出来绕过这个限制。例如，此查询将删除产品的所有直接和间接子部分。

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中使用数据修改语句时，将在快照中执行该语句。语句的效果在目标表上不可见。RETURNING的数据是在不同子语句和主查询之间传递更改的唯一方法。在本例中，外部SELECT返回WITH子句中UPDATE 操作之前的原始价格。

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

在本例中，外部SELECT返回更新的数据。

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

不支持在单个语句中更新同一行两次。这种语句的效果是不可预测的。只有一个修改发生了，但是不容易（有时也不可能）预测哪一个发生修改。

在WITH子句中用作数据修改语句目标的任何表都不能有条件规则、也不能有条件规则或扩展为多个语句的 INSTEAD规则。