数据仓库服务 GAUSSDB(DWS)-WITH表达式:WITH递归查询

时间：2024-04-12 10:20:16

数据仓库服务 GAUSSDB(DWS) 数据读取

WITH递归查询

通过声明RECURSIVE关键字，一个WITH查询可以引用它自己的输出。

递归WITH查询的通常形式如下：

  
       non_recursive_term UNION [ALL] recursive_term

其中：UNION在合并集合时会执行去重操作，而UNION ALLL则直接将结果集合并、不执行去重；只有递归项能够包含对于查询自身输出的引用。

使用递归WITH时，必须确保查询的递归项最终不会返回元组，否则查询将无限循环。

使用表tree来存储下图中的所有节点信息：

点击放大

表定义语句如下：

  
       CREATE TABLE tree(id INT, parentid INT);

表中数据如下：

  
       INSERT INTO tree VALUES(1,0),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,4),(9,4),(10,6),(11,6),(12,10);

SELECT * FROM tree;
 id | parentid
----+----------
|        0
|        1
|        1
|        2
|        2
|        3
|        3
|        4
|        4
|        6
|        6
|       10
(12 rows)

通过以下WITH RECURSIVE语句，我们可以返回从顶层1号节点开始，整个树的节点，以及层次信息：

  
       WITH RECURSIVE nodeset AS
(
-- recursive initializing query
SELECT id, parentid, 1 AS level FROM tree
WHERE id = 1
UNION ALL
-- recursive join query
SELECT tree.id, tree.parentid, level + 1 FROM tree, nodeset 
WHERE tree.parentid = nodeset.id 
)
SELECT * FROM nodeset ORDER BY id;

上述查询中，我们可以看出，一个典型的WITH RECURSIVE表达式包含至少一个递归查询的CTE，该CTE中的定义为一个UNION ALL集合操作，第一个分支为递归起始查询，第二个分支为递归关联查询，需要自引用第一部分进行不断递归关联。该语句执行时，递归起始查询执行一次，关联查询执行若干次并将结果叠加到起始查询结果集中，直到某一些关联查询结果为空，则返回。

上述查询的执行结果如下：

 id | parentid | level
----+----------+-------
  1 |        0 |     1
  2 |        1 |     2
  3 |        1 |     2
  4 |        2 |     3
  5 |        2 |     3
  6 |        3 |     3
  7 |        3 |     3
  8 |        4 |     4
  9 |        4 |     4
 10 |        6 |     4
 11 |        6 |     4
 12 |       10 |     5
(12 rows)

从返回结果可以看出，起始查询结果包含level=1的结果集，关联查询执行了五次，前四次分别输出level=2,3,4,5的结果集，在第五次执行时，由于没有parentid和输出结果集id相等的记录，也就是再没有多余的孩子节点，因此查询结束。