理解 KingbaseES 中的递归查询

概述：通常递归查询是一个有难度的话题，尽管如此，它们仍使您能够完成在 SQL 中无法实现的操作。本文通过示例进行了简单介绍，并展示了与 PL/SQL的递归查询实现的差异。

一、公用表表达式（WITH子句）

公用表表达式（CTE）可以被看作是一个视图，只适用于一个单一的查询：

1 2 3 4 5

WITH ctename AS (    SELECT ... ) SELECT ... FROM ctename ...

这也可以写成 中的子查询FROM，但使用 CTE 有一些优点：

• 查询变得更具可读性。
• 您可以在查询中多次引用 CTE，并且只会计算一次。
• 您可以在 CTE 中使用数据修改语句（通常带有RETURNING子句）。

请注意，在 V8R3 ，总是物化 CTE。这意味着，CTE 是独立于包含查询计算的。从 V8R6 开始，CTE 可以“内联”到查询中，这提供了进一步的优化潜力。

二、递归查询的语法

 递归查询是使用递归 CTE编写的，即包含RECURSIVE关键字的CTE ：

1 2 3 4 5 6 7

WITH RECURSIVE ctename AS (    SELECT /* non-recursive branch, cannot reference "ctename" */    UNION [ALL]    SELECT /* recursive branch referencing "ctename" */ ) SELECT ... FROM ctename ...

 三、如何处理递归查询

KingbaseES内部使用 WorkTable 来处理递归 CTE。这种处理并不是真正的递归，而是迭代： 

首先，通过执行 CTE 的非递归分支来初始化WorkTable 。CTE 的结果也用这个结果集初始化。如果递归 CTE 使用UNION而不是UNION ALL，则删除重复的行。

然后，KingbaseES重复以下操作，直到WorkTable 为空：

• 评估 CTE 的递归分支，用WorkTable 替换对 CTE 的引用。
• 将所有结果行添加到 CTE 结果。如果UNION用于合并分支，则丢弃重复的行。
• 用上一步中的所有新行替换WorkTable （不包括任何已删除的重复行）。

请注意，到目前为止，CTE的自引用分支并未使用完整的 CTE 结果执行，而是仅使用自上次迭代（WorkTable ）以来的新行。

必须意识到这里无限循环的危险：如果迭代永远不会结束，查询将一直运行直到结果表变得足够大以导致错误。有两种方法可以处理：

• 通常，您可以通过使用 UNION来避免无限递归，这会删除重复的结果行（但当然需要额外的处理工作）。
• 另一种方法是LIMIT在使用 CTE 的查询上放置一个子句，因为如果递归 CTE 计算的行数与父查询获取的行数一样多，KingbaseES将停止处理。请注意，此技术不可移植到其他符合标准的数据库。

请看实际执行计划：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

test=# explain WITH RECURSIVE ctename AS ( test(# SELECT empno, ename test(# FROM emp test(# WHERE empno = 7566 test(# UNION ALL test(# SELECT emp.empno, emp.ename test(# FROM emp JOIN ctename ON emp.mgr = ctename.empno test(# ) test-# SELECT * FROM ctename;   --------------------------------------------------------------------------------------------------  CTE Scan on ctename  (cost=417.62..489.74 rows=3606 width=36)    CTE ctename      ->  Recursive Union  (cost=0.00..417.62 rows=3606 width=36)            ->  Seq Scan on emp  (cost=0.00..25.00 rows=6 width=36)                  Filter: (empno = 7566)            ->  Hash Join  (cost=1.95..32.05 rows=360 width=36)                  Hash Cond: (emp_1.mgr = ctename_1.empno)                  ->  Seq Scan on emp emp_1  (cost=0.00..22.00 rows=1200 width=40)                  ->  Hash  (cost=1.20..1.20 rows=60 width=4)                        ->  WorkTable Scan on ctename ctename_1  (cost=0.00..1.20 rows=60 width=4)

四、一个简单的例子

让我们假设一个像这样的自引用表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

TABLE emp;     empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno -------+--------+-----------+------+------------+---------+---------+--------   7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10   7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30   7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10   7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20   7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20   7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20   7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30   7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30   7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30   7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30   7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30   7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10 (12 rows)

我们要查找人员 7566 的所有下属，包括人员本身。查询的非递归分支将是：

1 2 3

SELECT empno, ename FROM emp WHERE empno = 7566;

递归分支会找到WorkTable中所有条目的所有下级：

1 2	SELECT emp.empno, emp.ename FROM emp JOIN ctename ON emp.mgr = ctename.empno;

可以假设依赖项不包含循环（没有人是他或她自己的经理，直接或间接）。所以可以将查询与 UNION ALL 结合起来，因为不会发生重复。所以完整查询将是：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

WITH RECURSIVE ctename AS (       SELECT empno, ename       FROM emp       WHERE empno = 7566    UNION ALL       SELECT emp.empno, emp.ename       FROM emp JOIN ctename ON emp.mgr = ctename.empno ) SELECT * FROM ctename;     empno | ename -------+-------   7566 | JONES   7902 | FORD   7369 | SMITH (3 rows)

五、添加生成的列

有时您想添加更多信息，例如层级。您可以通过将起始级别添加为非递归分支中的常量来实现。在递归分支中，您只需将 1 添加到级别：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

WITH RECURSIVE ctename AS (       SELECT empno, ename,              0 AS level       FROM emp       WHERE empno = 7566    UNION ALL       SELECT emp.empno, emp.ename,              ctename.level + 1       FROM emp          JOIN ctename ON emp.mgr = ctename.empno ) SELECT * FROM ctename;     empno | ename | level -------+-------+-------   7566 | JONES |     0   7902 | FORD  |     1   7369 | SMITH |     2 (3 rows)

如果UNION在循环引用的情况下使用避免重复行，则不能使用此技术。这是因为添加level会使之前相同的行不同。但在那种情况下，分层级别无论如何都没有多大意义，因为一个条目可能出现在无限多个级别上。

另一个常见的要求是收集“路径”中的所有祖先：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

WITH RECURSIVE ctename AS (       SELECT empno, ename,              ename AS path       FROM emp       WHERE empno = 7566    UNION ALL       SELECT emp.empno, emp.ename,              ctename.path || ' -> ' || emp.ename       FROM emp          JOIN ctename ON emp.mgr = ctename.empno ) SELECT * FROM ctename;     empno | ename |          path          -------+-------+------------------------   7566 | JONES | JONES   7902 | FORD  | JONES -> FORD   7369 | SMITH | JONES -> FORD -> SMITH

六、与 PLSQL 的比较

PLSQL对于不符合 SQL 标准的递归查询有不同的语法。原始示例如下所示：

1 2 3 4 5 6 7 8 9 10

SELECT empno, ename FROM emp START WITH empno = 7566 CONNECT BY PRIOR empno = mgr;         EMPNO ENAME ---------- ----------       7566 JONES       7902 FORD       7369 SMITH

这种语法更简洁，但不如递归 CTE 强大。对于涉及连接的更复杂的查询，它可能变得困难和混乱。将 PLSQL “分层查询”转换为递归 CTE 总是很容易的：

• 非递归分支是不带CONNECT BY子句但包含START WITH子句的 Oracle 查询。
• 递归分支是不带START WITH子句但包含CONNECT BY子句的 Oracle 查询。添加具有递归 CTE 名称的PRIOR联接，并用来自该联接 CTE 的列替换所有列。
• 如果 Oracle 查询使用CONNECT BY NOCYCLE，则使用UNION，否则使用UNION ALL。

一般把connect by语法称为递归查询，然而严格来说这是一个错误的叫法。因为它无法把当前层所计算得到的值传递到下一层，所以对它的称呼都是Hierarchical Queries in Oracle (CONNECT BY) 。

七、递归查询的真正实力

如果没有递归 CTE，很多可以用过程语言编写的东西就不能用 SQL 编写。这通常影响数据库的使用，因为 SQL 是用来查询数据库的。但是递归 CTE 使 SQL过程代码更完善，也就是说，它可以执行与任何其他编程语言相同的计算。前面的示例表明递归 CTE 可以完成您在 SQL 中无法执行的有用工作。

作为递归查询功能的示例，这里是一个递归 CTE，它计算斐波那契数列的第一个元素：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

WITH RECURSIVE t(n,last_n,cnt) AS (   SELECT 1,0,1 FROM DUAL   UNION ALL   SELECT t.n+t.last_n, t.n, t.cnt+1     FROM t     ) SELECT * FROM T limit 10    n  | last_n | cnt ----+--------+-----   1 |      0 |   1   1 |      1 |   2   2 |      1 |   3   3 |      2 |   4   5 |      3 |   5   8 |      5 |   6  13 |      8 |   7  21 |     13 |   8  34 |     21 |   9  55 |     34 |  10