WITH 子句递归
包含 RECURSIVE 选项的 WITH 子句可以重复执行 UNION 或 UNION ALL 查询，从而迭代其自身的输出。递归查询在处理分层结构（例如，经理下属关系）或树状结构数据（如分类法）等自引用数据时十分有用。
配置参数 WithClauseRecursionLimit（默认设置为 8）将设置递归的最大深度。您可以分别使用 ALTER DATABASE 和 ALTER SESSION 在数据库和会话范围内设置此参数。递归将会继续，直到达到配置的最大深度为止，或者直到最后一次迭代返回没有数据为止。
可以按如下方式指定递归 WITH 子句：
WITH [ /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ ] RECURSIVE
cte‑identifier [ ( column-aliases ) ] AS (
non-recursive-term
UNION [ ALL ]
recursive-term
)
非递归项和递归项由 UNION 或 UNION ALL 分隔：
-
non-recursive-term 查询将其结果集设置在 cte-identifier，在 recursive-term 中递归。
-
UNION 语句的 recursive-term 以递归方式迭代其自身输出。当递归完成时，所有迭代的结果均会编译并在 cte-identifier 中设置。
例如：
=> ALTER SESSION SET PARAMETER WithClauseRecursionLimit=4; -- maximum recursion depth = 4
=> WITH RECURSIVE nums (n) AS (
SELECT 1 -- non-recursive (base) term
UNION ALL
SELECT n+1 FROM nums -- recursive term
)
SELECT n FROM nums; -- primary query
此简单查询按如下方式执行：
-
执行 WITH RECURSIVE 子句：
-
评估非递归项 SELECT 1，并将结果集 (1) 放入
nums。 -
迭代 UNION ALL 查询 (SELECT n+1)，直到迭代次数大于配置参数 WithClauseRecursionLimit。
-
合并所有 UNION 查询的结果，并将结果集设置在
nums中，然后退出到主要查询。
-
-
执行主要查询 SELECT n FROM nums：
n --- 1 2 3 4 5 (5 rows)
在本例中，根据 WithClauseRecursionLimit，WITH RECURSIVE 子句在四次迭代后退出。如果将 WithClauseRecursionLimit 还原为其默认值 8，则子句在八次迭代后退出：
=> ALTER SESSION CLEAR PARAMETER WithClauseRecursionLimit;
=> WITH RECURSIVE nums (n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM nums
)
SELECT n FROM nums;
n
---
1
2
3
4
5
6
7
8
9
(9 rows)
重要
请务必将 WithClauseRecursionLimit 设置为遍历最深层次结构所需的高度。Vertica 对此参数没有设置限制；但是，较高的值会产生相当大的开销，从而对性能产生不利影响并耗尽系统资源。
如果需要较高的递归计数，则考虑启用实体化。有关详细信息，请参阅 WITH RECURSIVE 实体化。
限制
存在以下限制：
-
非递归项的 SELECT 列表不能包含通配符
*（星号）或函数 MATCH_COLUMNS。 -
递归项只能引用目标 CTE 一次。
-
递归引用不能出现在外联接中。
-
递归引用不能出现在子查询中。
-
WITH 子句不支持 UNION 选项 ORDER BY、LIMIT 和 OFFSET。
示例
一家小型软件公司维护以下有关员工及其经理的数据：
=> SELECT * FROM personnel.employees ORDER BY emp_id;
emp_id | fname | lname | section_id | section_name | section_leader | leader_id
--------+-----------+-----------+------------+---------------------+------------------+-----------
0 | Stephen | Mulligan | 0 | | |
1 | Michael | North | 201 | Development | Zoe Black | 3
2 | Megan | Berry | 202 | QA | Richard Chan | 18
3 | Zoe | Black | 101 | Product Development | Renuka Patil | 24
4 | Tim | James | 203 | IT | Ebuka Udechukwu | 17
5 | Bella | Tucker | 201 | Development | Zoe Black | 3
6 | Alexandra | Climo | 202 | QA | Richard Chan | 18
7 | Leonard | Gray | 203 | IT | Ebuka Udechukwu | 17
8 | Carolyn | Henderson | 201 | Development | Zoe Black | 3
9 | Ryan | Henderson | 201 | Development | Zoe Black | 3
10 | Frank | Tucker | 205 | Sales | Benjamin Glover | 29
11 | Nathan | Ferguson | 102 | Sales Marketing | Eric Redfield | 28
12 | Kevin | Rampling | 101 | Product Development | Renuka Patil | 24
13 | Tuy Kim | Duong | 201 | Development | Zoe Black | 3
14 | Dwipendra | Sing | 204 | Tech Support | Sarah Feldman | 26
15 | Dylan | Wijman | 206 | Documentation | Kevin Rampling | 12
16 | Tamar | Sasson | 207 | Marketing | Nathan Ferguson | 11
17 | Ebuka | Udechukwu | 101 | Product Development | Renuka Patil | 24
18 | Richard | Chan | 101 | Product Development | Renuka Patil | 24
19 | Maria | del Rio | 201 | Development | Zoe Black | 3
20 | Hua | Song | 204 | Tech Support | Sarah Feldman | 26
21 | Carmen | Lopez | 204 | Tech Support | Sarah Feldman | 26
22 | Edgar | Mejia | 206 | Documentation | Kevin Rampling | 12
23 | Riad | Salim | 201 | Development | Zoe Black | 3
24 | Renuka | Patil | 100 | Executive Office | Stephen Mulligan | 0
25 | Rina | Dsouza | 202 | QA | Richard Chan | 18
26 | Sarah | Feldman | 101 | Product Development | Renuka Patil | 24
27 | Max | Mills | 102 | Sales Marketing | Eric Redfield | 28
28 | Eric | Redfield | 100 | Executive Office | Stephen Mulligan | 0
29 | Benjamin | Glover | 102 | Sales Marketing | Eric Redfield | 28
30 | Dominic | King | 205 | Sales | Benjamin Glover | 29
32 | Ryan | Metcalfe | 206 | Documentation | Kevin Rampling | 12
33 | Piers | Paige | 201 | Development | Zoe Black | 3
34 | Nicola | Kelly | 207 | Marketing | Nathan Ferguson | 11
(34 rows)
您可以通过 WITH RECURSIVE 查询此数据以了解员工与经理的关系。例如，以下查询的 WITH RECURSIVE 子句获取员工 Eric Redfield（包括他的所有直接下属和间接下属员工）的员工-经理关系：
WITH RECURSIVE managers (employeeID, employeeName, sectionID, section, lead, leadID)
AS (SELECT emp_id, fname||' '||lname, section_id, section_name, section_leader, leader_id
FROM personnel.employees WHERE fname||' '||lname = 'Eric Redfield'
UNION
SELECT emp_id, fname||' '||lname AS employee_name, section_id, section_name, section_leader, leader_id FROM personnel.employees e
JOIN managers m ON m.employeeID = e.leader_id)
SELECT employeeID, employeeName, lead AS 'Reports to', section, leadID from managers ORDER BY sectionID, employeeName;
WITH RECURSIVE 子句定义了 CTE managers，然后分两个阶段执行：
-
非递归项使用从
personnel.employees查询的数据填充managers。 -
递归项的 UNION 查询迭代它自己的输出，直到在第四个循环中找不到更多数据。然后，所有迭代结果进行编译并设置在
managers中，WITH CLAUSE 退出到主要查询。
主要查询从 managers 返回三个级别的数据（每个递归迭代一个级别）：
类似地，以下查询遍历相同的数据以获取员工 Richard Chan（他在公司管理层中比 Eric Redfield 低一级）的所有员工-经理关系：
WITH RECURSIVE managers (employeeID, employeeName, sectionID, section, lead, leadID)
AS (SELECT emp_id, fname||' '||lname, section_id, section_name, section_leader, leader_id
FROM personnel.employees WHERE fname||' '||lname = 'Richard Chan'
UNION
SELECT emp_id, fname||' '||lname AS employee_name, section_id, section_name, section_leader, leader_id FROM personnel.employees e
JOIN managers m ON m.employeeID = e.leader_id)
SELECT employeeID, employeeName, lead AS 'Reports to', section, leadID from managers ORDER BY sectionID, employeeName;
WITH RECURSIVE 子句像以前一样执行，但这次它在两次迭代后找不到更多数据而退出。因此，主要查询从 managers 返回两个级别的数据：
WITH RECURSIVE 实体化
默认情况下，实体化处于禁用状态。在这种情况下，Vertica 将 WITH RECURSIVE 查询重写为子查询，其数量与所需的递归级别一样多。
如果递归非常深，则大量查询重写可能会产生相当大的开销，从而对性能产生不利影响并耗尽系统资源。在这种情况下，请考虑使用配置参数 WithClauseMaterialization 或提示 ENABLE_WITH_CLAUSE_MATERIALIZATION 来启用实体化。在任何一种情况下，来自所有递归级别的中间结果集都将写入本地临时表。当递归完成时，所有临时表中的中间结果都进行编译并传递给主要查询。