理解 KingbaseES 中的递归查询

关键字：SQL，CTE，递归查询

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

一、公用表表达式(WITH子句)

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

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

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

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

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

二、递归查询的语法

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

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将停止处理。请注意，此技术不可移植到其他符合标准的数据库。

请看实际执行计划：

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)

四、一个简单的例子

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

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 的所有下属，包括人员本身。查询的非递归分支将是：

SELECT empno, ename
FROM emp
WHERE empno = 7566;

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

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

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

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 添加到级别：

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会使之前相同的行不同。但在那种情况下，分层级别无论如何都没有多大意义，因为一个条目可能出现在无限多个级别上。

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

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 标准的递归查询有不同的语法。原始示例如下所示：

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

七、递归查询的真正实力

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

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

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