PostgreSQL教程 -》查询 -》WITH中的SELECT

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在一个查询中存在的临时表。在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查询的通常形式总是一个非递归项，然后是UNION(或者UNION ALL)，再然后是一个递归项，其中只有递归项能够包含对于查询自身输出的引用。这样一个查询可以被这样执行：

递归查询求值

计算非递归项。对UNION(但不对UNION ALL)，抛弃重复行。把所有剩余的行包括在递归查询的结果中，并且也把它们放在一个临时的工作表中。

只要工作表不为空，重复下列步骤：

计算递归项，用当前工作表的内容替换递归自引用。对UNION(不是UNION ALL)，抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中，并且也把它们放在一个临时的中间表中。

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

注意

严格来说，这个处理是迭代而不是递归，但是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关系包含环，这个查询将会循环。因为我们要求一个“depth”输出，仅仅将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;

提示

在通常情况下只有一个域需要被检查来识别一个环，可以省略ROW()语法。这允许使用一个简单的数组而不是一个组合类型数组，可以获得效率。提示

递归查询计算算法使用宽度优先搜索顺序产生它的输出。你可以通过让外部查询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查询通常将会被按照所写的方式计算，而不抑制父查询以后可能会抛弃的行(但是，如上所述，如果对查询的引用只请求有限数目的行，计算可能会提前停止)。

但是，如果 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每个表行只计算一次，而不是两次。

以上的例子只展示了和SELECT一起使用的WITH，但是它可以被以相同的方式附加在INSERT、UPDATE或DELETE上。在每一种情况中，它实际上提供了可在主命令中引用的临时表。