理解 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
上一篇:git@github.com: Permission denied (publickey).


下一篇:Springboot&security基于前后端分离的RSA密码加密登录流程