7.8. WITH 查询 (通用表表达式) · PostgreSQL 中文文档 9.3

# 7.8\. `WITH` 查询 (通用表表达式) `WITH`提供了一种在更大的查询中编写辅助语句的方式。这个通常称为通用表表达式或CTEs的辅助语句可以认为是定义只存在于一个查询中的临时表。每个`WITH`子句中的辅助语句可以是一个`SELECT`,`INSERT`, `UPDATE` 或 `DELETE`；并且`WITH`子句本身附加到的初级语句可以是一个`SELECT`, `INSERT`, `UPDATE`或`DELETE`。 ## 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`查询。用这种方法更容易理解。可选的`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_，其中只有递归的术语可以包含一个对查询自己输出的引用。这样一个查询像下面那样执行： **递归查询评估** 1. 评估非递归的术语。使用`UNION`（而不是`UNION ALL`）去除重复的行。包括在递归查询结果中所有剩余的行，并将它们放入临时的_工作表_。 2. 只要工作表不为空，那么将重复这些步骤： 1. 评估递归术语，为递归自我参照替换当前工作表内容。用`UNION`(并不是`UNION ALL`)，去除重复的行和与以前结果行重复的行。包括所有在递归查询结果中剩余的行，并将它们放入一个临时的_中间表_。 2. 用中间表的内容替换工作表的内容，然后清空中间表。 > **Note:** 严格的说，该过程是迭代而不是递归，但是`RECURSIVE`是通过 SQL 标准委员会选择的术语。在上面的例子中，在每一步中仅有一个工作表行，并且在后续的步骤中它的值将从 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 ``` 当使用递归查询的时候，确保查询的递归部分最终不会返回元组是很重要的，否则查询将会无限的循环下去。有时，通过使用`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; ``` > **Tip:** 在常见的情况下，当只需要检查一个字段来识别循环的时候忽略`ROW()`语法。这允许使用一个简单的数组而不是一个复杂类型的数组，增加查询的效率。 > **Tip:** 递归查询评估算法产生以广度优先搜索顺序的输出。您可以按照深度优先查询排序通过外部查询`ORDER BY`一个"path"列来显示结果。当您不能确定它们是否会循环的时候，在一个父查询中放置`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` 查询的输出。一个有用的`WITH`查询属性是每个父查询执行一次它们只做一次评估，即使它们不止一次地通过父查询或`WITH`查询引用。所以，昂贵的需要在多个地方放置的计算可以通过设置`WITH`查询来避免冗余工作。另一个可能的应用是防止不必要的副作用函数的多个评估。然而，另一方面，比起普通的子查询，优化器不能够避开父查询拆分为一个`WITH`查询的限制。通常`WITH` 查询将如上评估，没有行限制的父查询可能丢失。（但是，正如上面所说，如果查询参考只需要数量有限的行，评估可能会很早终止。）上面的例子只显示了`WITH`在`SELECT`中的使用，但是它也可以用同样的方式附加到`INSERT`, `UPDATE`或 `DELETE`。在每种情况下它都有效的提供可以在主要的命令中引用的临时表。 ## 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`子句，就像上面的例子一样。它是`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); ``` 这个查询将删除一个产品所有直接或非直接的subparts。 `WITH`中的数据修改语句被直接执行一次，并且总是完成，独立的主查询读取所有（或者实际上是任意）它们的输出。注意，这和在`WITH`中`SELECT`的规则不同：就像前一节规定的那样，`SELECT`的执行直到首级查询需要它的输出时才实施。 `WITH`中的子语句之间和与主查询之间兼容的执行。因此，当在`WITH`中使用数据修改语句时，其他的指定的更新实际上是不可预知发生的。所有的语句都在相同的_快照_中执行（见[Chapter 13](#calibre_link-444)），所以他们不能"看见"彼此对目标表的影响。这样减轻了实际行更新的不可预知的影响，并且意味着`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`规则。