Kingbase-查询-WITH 查询（公共表表达式）

8 WITH 查询（公共表表达式）

WITH 提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或

CTE ，它们可以被看成是定义只在一个查询中存在的临时表。在 WITH 子句中的每一个辅助语句可以是一个

SELECT 、 INSERT 、 UPDATE 或 DELETE ，并且 WITH 子句本身也可以被附加到一个主语句，主语句也可以

是 SELECT 、 INSERT 、 UPDATE 或 DELETE 。

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 ），再然后是一个递

归项，其中只有递归项能够包含对于查询自身输出的引用。这样一个查询可以被这样执行：

递归查询求值

1. 计算非递归项。对 UNION （但不对 UNION ALL ），抛弃重复行。把所有剩余的行包括在递归查询的

结果中，并且也把它们放在一个临时的 工作表 中。

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

a. 计算递归项，用当前工作表的内容替换递归自引用。对 UNION （不是 UNION ALL ），抛弃重复

行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中，并且也把它们放

在一个临时的中间表中。

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

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 关系包含环，这个查询将会循环。因为我们要求一个“ 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 ,

(continues on next page)

(continued from previous page)

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() 语法。这允许使用一个简单的

数组而不是一个组合类型数组，可以获得效率。

递归查询计算算法使用宽度优先搜索顺序产生它的输出。你可以通过让外部查询 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 ;

这会起作用，因为 KingbaseES 的实现只计算 WITH 查询中被父查询实际取到的行。不推荐在生产中使用这

个技巧，因为其他系统可能以不同方式工作。同样，如果你让外层查询排序递归查询的结果或者把它们连接

成某种其他表，这个技巧将不会起作用，因为在这些情况下外层查询通常将尝试取得 WITH 查询的所有输

出。

WITH 查询的一个有用的特性是在每一次父查询的执行中它们通常被计算一次，即使它们被父查询或兄弟

WITH 查询引用了超过一次。因此，在多个地方需要的昂贵计算可以被放在一个 WITH 查询中来避免冗余工

作。另一种可能的应用是阻止不希望的多个函数计算产生副作用。然而 , 硬币的另一面是 , 优化器不能将父查

询的限制推入一个多基准 WITH 查询 , 因为这可能会影响所有使用 WITH 查询的输出 , 它应该只影响一个。多

基准的 WITH 查询将会被按照所写的方式计算，而不抑制父查询以后可能会抛弃的行（但是，如上所述，如

果对查询的引用只请求有限数目的行，计算可能会提前停止）。

但是，如果一个 WITH 查询是非递归的，并且没有副作用 ( 也就是说，它是一个 SELECT 不包含 volatile 函数 ) ，

那么它可以被折叠到父查询中，从而允许两个查询级别的联合优化。默认情况下，它发生于父查询引用 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 上。在每一种情况中，它实际上提供了可在主命令中引用的临时表。

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);

这个查询将会移除一个产品的所有直接或间接子部件。

WITH 中的数据修改语句只被执行一次，并且总是能结束，而不管主查询是否读取它们所有（或者任何）的

输出。注意这和 WITH 中 SELECT 的规则不同：正如前一小节所述，直到主查询要求 SELECT 的输出时，

SELECT 才会被执行。

WITH 中的子语句彼此并发执行，并与主查询一起执行。因此在使用 WITH 中的数据修改语句时，指定更新

的顺序实际是以不可预测的方式发生的。所有的语句都使用同一个 snapshot 执行，因此它们不能“看见”在

目标表上另一个执行的效果。这减轻了行更新的实际顺序的不可预见性的影响，并且意味着 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 中一个数据修改语句中被用作目标的任何表不能有条件规则、 ALSO 规则或 INSTEAD 规则，

这些规则会扩展成为多个语句。