1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564

CREATE TABLE org AS
WITH RECURSIVE tree(node_id, parent_id, depth, path_arr) AS (
    SELECT 1::BIGINT, NULL::BIGINT, 0, [1::BIGINT]
    UNION ALL
    SELECT p.node_id * 5 + c.i,
           p.node_id,
           p.depth + 1,
           array_append(p.path_arr, p.node_id * 5 + c.i)
    FROM tree AS p, range(0, 5) AS c(i)
    WHERE p.depth < 8
)
SELECT node_id, parent_id, depth, path_arr FROM tree;
```sql


锚点是根节点 `(1, NULL, 0, [1])`。递归项用 `FROM tree AS p, range(0, 5) AS c(i)` 做横向展开——每个父节点生出 5 个子节点，`node_id * 5 + i` 这套编号在二进制堆的数组表示里很常见，好处是父子关系可以从编号反推。`WHERE p.depth < 8` 就是终止条件。

实测建表 41.4ms，`count(*)` 返回 488,281，`max(depth)` 返回 8，路径数组最长 9 个元素（深度加一）。节点数按等比数列求和 `(5^9 - 1) / 4 = 488281`，对得上。

有意思的是，生成测试数据这件事本身就是一次递归 CTE 实战。以前造层级测试数据要写 Python 脚本循环，现在一条 SQL 搞定，顺便还把递归语法练了一遍。

## 场景一：祖先链，从叶子往根爬

最常见的需求：给一个节点 ID，把它到根节点的整条路径列出来。比如查某个员工往上有多少级主管，或者查一个零件最终装在哪个成品里。

树的递归是可以双向走的。往下走是 `WHERE o.parent_id = w.node_id`，往上爬就把两边对调：

```sql
WITH RECURSIVE anc(node_id, parent_id, steps) AS (
    SELECT node_id, parent_id, 0 FROM org WHERE node_id = 390625
    UNION ALL
    SELECT o.node_id, o.parent_id, a.steps + 1
    FROM org AS o JOIN anc AS a ON o.node_id = a.parent_id
)
SELECT * FROM anc ORDER BY steps;
```text


结果 9 行，从 390625 一路爬到根节点：

```text
390625 → 78125 → 15625 → 3125 → 625 → 125 → 25 → 5 → 1
```sql


耗时 6.4ms。

同样的结果用传统写法也能拿到，就是手写 9 层 `LEFT JOIN`，每一层套一层：

```sql
SELECT n8.node_id, n7.parent_id, ...
FROM org n0
LEFT JOIN org n1 ON n1.node_id = n0.parent_id
LEFT JOIN org n2 ON n2.node_id = n1.parent_id
-- ... 一直到 n8
WHERE n0.node_id = 390625;
```sql


9 层 JOIN 跑下来 75.1ms，递归写法是 6.4ms，快了 11.7 倍。

这个差距为什么这么大？因为手写 JOIN 那版，DuckDB 得老老实实从内到外执行 9 次列式扫描和哈希探测，每一层都要物化一遍中间结果。递归版本里，第一轮的输入是单行（`node_id = 390625`），而且原始连接条件筛选之后的候选集始终只有一行——实际做的计算量小得多。DuckDB 的递归执行器在每一轮之间还能复用哈希表。

不过这个 11.7 倍的对比得说明白一点：它成立的前提是**那条链本身就只有 9 个节点**。换成从根往下遍历全树，故事就完全不一样了，后面会说。

## 场景二：子树与分层统计

方向反过来，给一个中间节点，列出它所有的后代，并按相对深度分组统计。

```sql
WITH RECURSIVE sub(node_id, parent_id, rel_depth) AS (
    SELECT node_id, parent_id, 0 FROM org WHERE node_id = 5
    UNION ALL
    SELECT o.node_id, o.parent_id, s.rel_depth + 1
    FROM org AS o JOIN sub AS s ON o.parent_id = s.node_id
)
SELECT rel_depth, count(*) FROM sub GROUP BY rel_depth ORDER BY rel_depth;
```text


18.7ms 跑完，分层结果非常规整：

| rel_depth | 节点数 |
|---|---|
| 0 | 1 |
| 1 | 5 |
| 2 | 25 |
| 3 | 125 |
| 4 | 625 |
| 5 | 3,125 |
| 6 | 15,625 |
| 7 | 78,125 |

从 node_id = 5 出发，最多只能往下走 7 层（根节点所在的 depth 是 0，最深到 8），所以每一层的数量正好是 5 的 `rel_depth` 次方递减。合计 97,656 个节点（含起点自身）。

这个路径在真实业务里就是"某个部门下所有人"、"某个分类下所有商品"的标准解法。用 `rel_depth` 做维度，还能顺手画出组织架构的分层柱状图。

有个细节值得留意：递归项里的 `s.rel_depth + 1` 不能换成 `max(s.rel_depth) + 1` 这种聚合写法。看起来等价，实际会死循环——这是第一个大坑，放到后面细说。

## 场景三：BOM 成本递归展开

前两个场景是树的基本操作，第三个稍微复杂一点：物料清单（BOM）的成本汇总。这是制造业里最经典的递归场景，也是最能体现递归 CTE 价值的地方。

业务逻辑是这样的：成品自行车由若干零件组成，每个零件有数量和单价。但零件本身可能也是"组装件"，要继续往下拆。要算出成品的总物料成本，得把每一层的"父级数量 × 子级数量"一路乘下去，再乘以叶子节点的单价。

先造一棵 3 叉的 BOM 树：

```sql
CREATE TABLE bom AS
WITH RECURSIVE t(id, parent_id, name, qty, unit_cost) AS (
    SELECT 1::BIGINT, NULL::BIGINT, 'bike', 1, 0.0::DOUBLE
    UNION ALL
    SELECT p.id * 3 + c.i, p.id, 'part_' || (p.id * 3 + c.i),
           1 + (c.i % 3),
           round(0.5 + (((p.id * 3 + c.i) % 17) * 1.7)::DOUBLE, 2)
    FROM t AS p, range(1, 4) AS c(i)
    WHERE p.id < 1000
)
SELECT * FROM t;
```text


1,093 行。锚点里那个 `0.0::DOUBLE` 下面会专门讲，写成 `0.0` 会炸。

然后是展开查询。关键在 `exploded_qty` 这一列——它把从根到当前节点的所有 `qty` 乘在一起：

```sql
WITH RECURSIVE explode(id, parent_id, qty, unit_cost, exploded_qty, lvl) AS (
    SELECT id, parent_id, qty, unit_cost, qty, 0 FROM bom WHERE parent_id IS NULL
    UNION ALL
    SELECT b.id, b.parent_id, b.qty, b.unit_cost,
           e.exploded_qty * b.qty, e.lvl + 1
    FROM explode AS e JOIN bom AS b ON b.parent_id = e.id
)
SELECT count(*) AS rows_,
       round(sum(exploded_qty * unit_cost), 2) AS total_cost,
       max(exploded_qty), max(lvl)
FROM explode;
```sql


结果：1,093 行，总成本 794,387.00，最大展开数量 729，最大层级 6。耗时 5.1 到 8.7ms（两个 DuckDB 版本结果完全一致）。

分层统计也符合 3 叉树的特征：lvl=0 是 1 行（成品自己），lvl=1 是 3 行，lvl=2 是 9 行，lvl=3 是 27 行。

`e.exploded_qty * b.qty` 这一行是整个查询的精髓。它把"累积数量"作为状态沿着树往下传——这就是递归 CTE 相比普通 JOIN 真正强的地方：**它允许你携带一个随路径变化的状态量**。JOIN 做不到这件事，因为 JOIN 没有"上一轮"的概念。

真实生产环境里，这个模式还能扩展成：BOM 展开后的关键路径分析（哪个零件的提前期最长）、多版本 BOM 差异对比、成本构成占比下钻。核心都是一个递归展开加一个沿路径累积的状态列。

## 诚实对比：递归并不总是更快

网上关于递归 CTE 的文章，很多会营造一种"用了递归就快"的印象。我自己实测下来不是这样，这里必须把话说清楚。

做了一个同口径对比：从根节点出发，取 depth = 8 那一层的全部节点，两种写法：

| 写法 | 输出行数 | 耗时 |
|---|---|---|
| 9 层 INNER 自连接（固定 8 跳） | 390,625 | 21.7ms |
| 递归取 depth=8 一层 | 390,625 | 22.9ms |
| 递归取全部 9 层（0..8） | 488,281 | 28.8ms |
| 递归祖先链（反向，单条路径） | 9 | 6.4ms |
| 手写 9 层 LEFT JOIN（同一结果） | 9 | 75.1ms |

**在固定深度、同口径的前提下，递归 CTE 和手写自连接的速度基本持平**（21.7ms vs 22.9ms），递归甚至还慢了 1.2ms，因为它多维护了一列 `depth`。

递归 CTE 的价值不在速度，在另外两个地方：

一是**深度未知**。业务说"可能有 8 层，也可能有 80 层"，手写 JOIN 就得写 80 层，递归 CTE 一行都不用改。这个前提下没有可比性，因为手写版本根本写不出来。

二是**可读性**。9 层 JOIN 那版 SQL 我已经不想再看第二眼，而递归版本 6 行就描述完了整件事。三个月后回来改需求，递归版本能看懂，JOIN 版本只能重写。

只有"单条链"这种场景（祖先链），递归是实打实的快（6.4ms vs 75.1ms，11.7 倍），原因前面解释过了——每轮输入只有一行。

那什么时候该用递归？判断标准很简单：**如果深度是固定的、而且层数不多（三层以内），直接 JOIN 就好，不用绕**。递归 CTE 是给"深度不可知"准备的。

## 环与收敛：递归 CTE 最硬的部分

树是没有环的，每一层往下走必然收敛。但图有环，而现实里的关系大部分是图。

评论楼中楼理论上可以有环（虽然业务上不该有），社交关系链一定有环（A 认识 B，B 认识 C，C 认识 A），路由表更是天然有环。一旦有环又没写对护栏，`WITH RECURSIVE` 就是一个精心设计的死循环。

### 坑 1：递归项里做聚合，永不收敛

这是最隐蔽的坑，因为它长得特别像正确的 SQL。第一眼看到这句的时候，完全没觉得哪里有问题：

```sql
-- ❌ 永远跑不完（实测 25 秒强制超时）
WITH RECURSIVE t(i) AS (
    SELECT 1
    UNION ALL
    SELECT max(i) + 1 FROM t WHERE i < 5
)
SELECT * FROM t;
```text


从 `i=1` 开始，每轮取最大值加一，`i<5` 时继续，看起来就是生成 1 到 5。实际它会一直跑下去。

根因在终止条件的定义上。递归的终止条件是"递归项产出的**候选集为空**"，不是"查不到行"。这两个说法听起来差不多，但空集上的聚合仍然会产出一行——`max(i)` 在一张空表上返回 `NULL`，`count(*)` 返回 `0`。`0 + 1` 就是 `1`，于是这一行被当成新一轮的输入送回去，无限循环。

为了确认这个推断，我做了三组对照实验：

```sql
-- 实验 1：空集上做聚合确实会产出 NULL 行
WITH RECURSIVE t(i) AS (
    SELECT 1
    UNION ALL
    SELECT NULL FROM t WHERE i < 5
)
SELECT * FROM t;
-- 立刻返回 [(1,), (None,)] —— NULL 行确实进了递归

-- 实验 2：把空集在 HAVING 里过滤掉，正常收敛
WITH RECURSIVE t(i) AS (
    SELECT 1
    UNION ALL
    SELECT max(i) + 1 FROM t WHERE i < 5 HAVING max(i) IS NOT NULL
)
SELECT * FROM t;
-- 结果 1,2,3,4,5 ✓

-- 实验 3：换成 UNION（去重），也能收敛
WITH RECURSIVE t(i) AS (
    SELECT 1
    UNION
    SELECT max(i) + 1 FROM t WHERE i < 5
)
SELECT * FROM t;
-- 结果 1,2,3,4,5,None ✓ （多一个 NULL，但至少停了）

-- 变体 count(*)+1 同样死循环
-- 空集 count=0 → 产出 1 → 1<5 成立 → 又转回去 → 1→2→3→4→5→1→... 无限
```sql


三条路都能活：**递归项里不做聚合；必须聚合就先 `HAVING` 掉空集；或者把 `UNION ALL` 换成 `UNION`。** 最稳的是第一条，把聚合搬到递归外面去。

### 坑 2：UNION 去重防环，结果带了个 step 列，去重直接失效

知道"用 `UNION` 去重可以防环"之后，很容易顺手把深度也带出来，写成这样：

```sql
-- 图：1→2→3→4→5→3（有环）
CREATE TABLE cyc AS SELECT * FROM (VALUES (1,2),(2,3),(3,4),(4,5),(5,3)) AS t(a,b);

-- ❌ 死循环（实测 15 秒超时）
WITH RECURSIVE w(node, step) AS (
    SELECT 1, 0
    UNION
    SELECT c.b, w.step + 1 FROM cyc c JOIN w ON c.a = w.node
)
SELECT count(*), max(step) FROM w;
```sql


`UNION` 的集合去重是按整行元组比的。带上了 `step` 列，`(3, 2)` 和 `(3, 6)` 和 `(3, 10)` 在 DuckDB 眼里是三行不同的数据，去重完全失效，环上的节点每轮都被当成新节点重新入队。

把 `step` 列去掉就收敛了：

```sql
-- ✅ 收敛（4.8ms，5 个节点）
WITH RECURSIVE w(node) AS (
    SELECT 1
    UNION
    SELECT c.b FROM cyc c JOIN w ON c.a = w.node
)
SELECT count(*), min(node), max(node) FROM w;
```sql


结论很直白：**去重的列集里不能有随迭代递增的列**。如果需要保留深度信息，就得另外加显式的深度护栏，而不是靠 `UNION`。

如果既要防环又要完整路径，官方文档给的写法是路径数组加 `list_position`：

```sql
-- ✅ 收敛（6.4ms），且能拿到完整路径
WITH RECURSIVE w(node, path) AS (
    SELECT 1, [1]
    UNION ALL
    SELECT c.b, array_append(w.path, c.b)
    FROM cyc c JOIN w ON c.a = w.node
    WHERE list_position(w.path, c.b) IS NULL
)
SELECT node, path FROM w ORDER BY length(path);
```sql


`list_position(w.path, c.b) IS NULL` 意思是"这个节点还没出现在当前路径里"。注意这里用的是 `UNION ALL`，因为防环已经由 `WHERE` 完成了，不需要再去重；而且用 `UNION ALL` 不会误杀"同一节点通过不同路径到达"的合法情况。

### 坑 3：USING KEY 不是自动收敛开关

DuckDB 1.3.0 引入了 `USING KEY`，官方博客里那个例子（424 个节点、1,446 条边的图，传统递归处理近 6 亿行、`USING KEY` 只处理 19,213 行）看着就像是递归查询的银弹。很容易产生一个错觉：加了 `USING KEY` 就不会死循环了。

看到官方博客里那个例子的时候，第一反应是"这不就是个自动收敛开关吗"。实测完发现完全不是。

```sql
-- ❌ 有环图 + USING KEY + 无护栏 → 15 秒超时
WITH RECURSIVE w(node, step) USING KEY(node) AS (
    SELECT 1, 0
    UNION ALL
    SELECT c.b, w.step + 1 FROM cyc c JOIN w ON c.a = w.node
)
SELECT count(*), max(step) FROM w;
```sql


`USING KEY` 保证的是**状态表不膨胀**（同一个 key 只保留一行，新行覆盖旧行），不是**候选集收敛**。每轮 `step` 都在变大，产出的行在"键值覆盖"的语义下永远是新的，所以循环不会停。

加个深度护栏就好了，`WHERE w.step < 10` 之后 7.6ms 收敛。

这两件事得分开理解：`USING KEY` 管的是内存占用和重复计算，护栏管的是终止。它们解决的是两个不同的问题。

### 坑 4：最短路径只在 min() 上打转

想算最短路径，很自然的想法是对每个节点取最小的距离，我一开始也是这么写的：

```sql
-- ❌ 有环图（2→4→5→2）+ 只用 min 聚合 → 15 秒超时
WITH RECURSIVE r(node, dist) USING KEY(node) AS (
    SELECT 1, 0
    UNION
    SELECT g.b, min(1 + r.dist) FROM r JOIN g ON r.node = g.a GROUP BY g.b
)
SELECT node, dist FROM r;
```text


这个写法在无环 DAG 上能跑（5.2ms），答案也对（1→0, 2→1, 3→1, 4→2, 5→3）。但图里一旦有环，`min()` 就不足以阻止"绕一圈回来发现距离更小"的循环——尤其在带权图上，每一圈都可能被认为找到了更优解。

官方博客 2025 年 5 月那篇介绍 `USING KEY` 的文章里，给的 DVR（Distance Vector Routing）写法带了一个关键守卫：

```sql
WITH RECURSIVE dvr(here, there, via, len) USING KEY(here, there) AS (
    SELECT n.person1id AS here, n.person2id AS there, n.person2id AS via, 1::DOUBLE AS len
    FROM knows AS n
    UNION
    (SELECT n.person1id AS here, dvr.there, dvr.here AS via, 1 + dvr.len AS len
     FROM dvr                                   -- working table: 上一轮的更新
     JOIN knows AS n ON (n.person2id = dvr.here
                         AND n.person1id <> dvr.there)
     LEFT JOIN recurring.dvr AS rec ON (rec.here = n.person1id AND rec.there = dvr.there)
     WHERE 1 + dvr.len < coalesce(rec.len, 'Infinity'::DOUBLE)   -- ← 只在"更优"时才继续传播
     ORDER BY len)
)
SELECT * FROM dvr ORDER BY len, here, there;
```sql


（来源：<https://duckdb.org/2025/05/23/using-key.html>，抓取时间 2026-10-11 22:52 CST）

关键就是那个 `WHERE 1 + dvr.len < coalesce(rec.len, 'Infinity'::DOUBLE)`。`recurring.dvr` 是累积表的引用（注意它只在 `USING KEY` 的 CTE 里可用），`LEFT JOIN` 上去之后比较：只有新算出来的距离比累积表里已有的更短，才继续往下传播；否则这一行直接被过滤掉，不进入下一轮。

把这个守卫搬到小图上验证：

```sql
CREATE TABLE g AS SELECT * FROM (VALUES (1,2),(1,3),(2,4),(3,4),(4,5),(5,2)) AS t(a,b);  -- 有环 2→4→5→2
WITH RECURSIVE r(node, dist) USING KEY(node) AS (
    SELECT 1, 0
    UNION
    (SELECT g.b, 1 + r.dist AS dist
     FROM r JOIN g ON r.node = g.a
     LEFT JOIN recurring.r AS rec ON rec.node = g.b
     WHERE 1 + r.dist < coalesce(rec.dist, 'Infinity'::DOUBLE)
     ORDER BY dist)
)
SELECT node, dist FROM r ORDER BY dist, node;
-- 结果：1→0, 2→1, 3→1, 4→2, 5→3   耗时 9.3ms  ✓ 收敛且答案正确
```sql


9.3ms，收敛，答案正确。

这里有个容易看漏的细节：递归项被一对括号包起来了，而且里面带 `ORDER BY`。括号是为了让 `ORDER BY` 合法地作用在递归成员内部；`ORDER BY dist` 则让距离小的候选先处理，配合 `USING KEY` 的覆盖语义，可以更早拿到正确的最终值。如果按照 `recurring` 里的值继续剪枝，顺序会影响中间过程的行数。

一句话总结这块：**带环图上的最短路径，"只在更优时继续传播"这个条件是必须的，不是优化。**

### 传统递归在图上的行数爆炸

上面的坑看着都是自己的 SQL 写错，但就算写对了，传统递归（非 `USING KEY`）在图上也有一道过不去的坎。

官方博客给的三张图，同一套最短路径算法，处理的行数对比：

| 图 | 节点 | 边 | USING KEY | 传统递归 |
|---|---|---|---|---|
| A | 184 | 233 | 744 | 352,906 |
| B | 322 | 903 | 8,232 | 40,732,577 |
| C | 424 | 1,446 | 19,213 | 605,859,791 |

（来源：<https://duckdb.org/2025/05/23/using-key.html>）

Graph C 只有 424 个节点、1,446 条边，传统递归处理了 6 亿行，`USING KEY` 只处理了不到 2 万行，差了 3 万倍。官方原文的措辞是 "the REC approach is already approaching out-of-memory conditions"。

这个爆炸的原因不难理解：传统递归的累积表是只增不减的，每一条新路径都是一行。图越密，从起点到终点的路径数量增长得越快，而不同路径大量重复经过同样的节点。`USING KEY` 用"每个 key 只留一行"把这种重复从物理上消除了——这正是 `USING KEY` 那篇 SIGMOD 论文里说的 "accumulative semantics—which collects all intermediate results in a union table—may incur substantial space and runtime overhead"。

## 坑 5：递归 CTE 的列类型由锚点决定

这个坑是在写 BOM 那个查询的时候撞上的，前后折腾了半个多小时才定位到，报错长这样：

```text
Conversion Error: Casting value "10.7" to type DECIMAL(2,1) failed: value is out of range!
```sql


查了半天才发现问题出在锚点里那个 `0.0`。DuckDB 把字面量 `0.0` 推导成了 `DECIMAL(1,1)`——一位整数、一位小数，最大值 9.9。递归 CTE 的列类型由第一轮（锚点）决定，之后递归项产出的值必须能塞进这个类型。跑到某个节点算出 `10.7` 的时候，就超范围了。

把锚点改成 `0.0::DOUBLE` 之后，1,093 行的 BOM 和成本汇总全部跑通。

同类问题还有 `NULL` 列。锚点里写 `NULL` 而不带类型，DuckDB 推成 `INTEGER`，后面递归项塞进去一个 `BIGINT` 大数就报错。正确写法是 `NULL::BIGINT` 或者 `CAST(NULL AS BIGINT)`——前面生成组织树的时候，`NULL::BIGINT` 就是刻意写的。

规则很朴素：**锚点里的字面量会把整列类型钉死，要么显式标注类型，要么保证两侧类型完全对齐。**

## 坑 6、7：两个官方不支持

这两个不是坑，是边界，但值得单独列出来，因为第一反应都会觉得"这应该能用吧"。

**`UNION BY NAME` 不能用于递归 CTE。** 试了：

```sql
WITH RECURSIVE t(i) USING KEY(i) AS (
    SELECT 1
    UNION BY NAME
    SELECT i + 1 FROM t WHERE i < 5
)
SELECT * FROM t;
```text


报错：

```text
Binder Error: Circular reference to CTE "t", use WITH RECURSIVE to use recursive CTEs.
```sql


明明写了 `WITH RECURSIVE`，它还是把 `UNION BY NAME` 这一支当成非递归 CTE 处理。`USING KEY` 和 `UNION BY NAME` 的组合不在 DuckDB 的解析器识别范围内。

**互相递归 CTE 不支持。** 官方文档写得很明确：

> Limitations: DuckDB does not support mutually recursive CTEs.

实测 `a` 引用 `b`、`b` 引用 `a`，报 `Catalog Error: Table with name b does not exist`，连绑定阶段都过不去。

想模拟互相递归（比如上下文无关语法分析、双向图搜索），只能退回到手写迭代或者用 `USING KEY` 加状态列来凑。

## 能力边界逐项实测

顺手把递归项里能写什么、不能写什么挨个试了一遍，一共 18 个写法，看看边界到底在哪。挑几个有代表性的：

| 写法 | 结果 |
|---|---|
| 递归项内直接聚合 `max(i)+1` | ❌ 25s 超时（不收敛） |
| 递归项内 `count(*)+1` | ❌ 20s 超时（不收敛） |
| 递归项内 `DISTINCT` | ✅ 1..5 |
| 递归项内 `ORDER BY`（子查询包裹） | ✅ 1..5 |
| 递归项内 `LIMIT`（子查询包裹） | ✅ 1..5 |
| 递归项内 `LEFT JOIN` 递归引用 | ✅ 1..5 |
| 递归项内窗口函数 `row_number()` | ✅ 1..5 |
| 递归项内 `GROUP BY`（无空集风险时） | ✅ 1..5 |
| 递归项两次引用自身（`FROM t a, t b`） | ✅ 得 1,2,4（每轮双倍，`a.i<4` 截断） |
| 递归项内标量子查询 | ✅ 1,3,5（步长 2） |
| `CASE` 控制递归深度 | ✅ 1..5 |
| `UNION` 去重替代深度护栏 | ✅ 1..5 |
| 引用 `recurring.t` 但不声明 `USING KEY` | ✅ 能跑通（读到累积表） |
| `UNION BY NAME` + 递归 | ❌ `Binder Error: Circular reference to CTE` |
| 互相递归 CTE | ❌ `Catalog Error: Table with name b does not exist` |

两个意外发现：

窗口函数、`GROUP BY`、`DISTINCT` 这些在递归项里都能用。文档没明说能用，但解析器不拦。当然能写不代表该写——`GROUP BY` 和聚合一样有"空集产出非空行"的风险，前面坑 1 已经演示过了。

`recurring.t` 不声明 `USING KEY` 也能读。按官方文档的说法，`recurring.T` 是"`USING KEY` 的 CTE 里用来引用累积表"的语法。实测不声明 `USING KEY` 直接写 `recurring.t`，查询能跑通并且读到了累积的行。这算是个未文档化的行为，生产代码里不该依赖它。

## USING KEY 与 DuckDB 2.0：同一句 SQL，38 倍

这一节是整篇里数据最硬的部分。

DuckDB 官方博客 2026 年 8 月 25 日那篇《How DuckDB Runs Recursive CTEs Faster》（作者 Denis Hirn）公布了一组基准，用一个 100 万条边、10 万个节点的图测可达性：

```sql
CREATE TABLE edges AS
SELECT (range % 100_000)::INTEGER AS src,
       ((range * 13 + 7) % 100_000)::INTEGER AS dst
FROM range(1_000_000);

WITH RECURSIVE reachable(node) AS (
    SELECT 0
    UNION
    SELECT dst FROM edges, reachable WHERE src = node
)
SELECT count(*) FROM reachable;
```sql

（来源：<https://duckdb.org/2026/08/25/how-duckdb-runs-recursive-ctes-faster.html>，抓取时间 2026-10-11 22:40 CST）

原文的结论是：

> median runtime falls from 4.051 seconds with DuckDB v1.5.5 to 0.095 seconds with the DuckDB v2.0 preview, a 42.6× speedup without changing the SQL.

官方在它自己的硬件上测出来是 4.051s 到 0.095s，42.6 倍。这个数字值得自己复现一遍，所以在那台腾讯云的机器上装了 1.5.5 和 2.0.0-dev 两个 venv，跑同一句 SQL，同一台机器，各跑三轮。第一次看到 19 秒和 0.5 秒并排出现在同一个终端里，确实愣了一下——同一台机器，同一句 SQL，只有版本号不一样。

| 版本 | run1 | run2 | run3 | 中位数 |
|---|---|---|---|---|
| DuckDB 1.5.5 | 19.034s | 19.162s | 19.630s | **19.162s** |
| DuckDB 2.0.0-dev | 0.495s | 0.504s | 0.498s | **0.498s** |

本机加速比 **38.5 倍**。可达节点数两边都是 20,000，和官方描述一致。

绝对耗时和官方差了一截（19.16s vs 4.05s），这个差异来自硬件——那台机器是老 EPYC 7K62 四核、7.5GB 内存，还同时跑着 Docker 和其他好几个服务。但加速比这个相对数字对得上（38.5× vs 42.6×），说明优化确实落到了执行器上，不是硬件噪音。

官方博客还解释了提速的来源，其中一条的数字很能说明问题：

> DuckDB v1.5.5 reports `operator_rows_scanned: 19,718,328,320` on the edges scan, equivalent in row volume to about 19,718 complete scans, while the DuckDB v2.0 preview reports the table's one million rows once.

1.5.5 在 edges 表上扫了 197 亿行，相当于把整张表完整扫了 19,718 遍；2.0 预览版只扫了一遍——100 万行。差别在于 2.0 换了执行策略，不再每轮重新扫一遍基础表。

第二组基准测的是 `USING KEY` 的有键状态（100 万个 key，每轮推进 1,000 个 key，跑 20 轮）：

| 版本 | 中位数 | 结果校验 |
|---|---|---|
| DuckDB 1.5.5 | 0.991s | keys=1,000,000 / value_sum=99,920,000 |
| DuckDB 2.0.0-dev | 0.139s | keys=1,000,000 / value_sum=99,920,000 |

本机加速比 7.1 倍（官方 0.401s → 0.040s，10 倍），两边结果完全一致。

官方对这条的解释是 "replaces them with 20,000 direct probes, a 1,000× reduction in recurring-state rows examined"——把逐行扫描换成 2 万次直接探测，检查的递归状态行数减少 1,000 倍。

第三组是自己造的：1,111,111 个节点的树，建表加全树遍历。

| 版本 | 建表 | 全树遍历 |
|---|---|---|
| DuckDB 1.5.5 | 96ms | 0.063s |
| DuckDB 2.0.0-dev | 55ms | 0.037s |

树遍历本身没有巨大差距，因为树的递归本来就没有重复扫描的问题。这也侧面说明 2.0 的提速主要针对"图/重复扫描"这个场景，不是普适的。

有一个语义变更要留意。官方博客里提到，2.0 里 `USING KEY` 配合 `UNION` 的行为变了：

> for `USING KEY`, `UNION` now makes new keys and keys whose finalized payload changed visible to the next iteration.

在 1.5.x 里 `USING KEY ... UNION` 被标记为 deprecated，行为和 `UNION ALL` 等价——实测印证了这一点：在那张 5 节点小 DAG 上，`USING KEY(node)` 配 `UNION` 和配 `UNION ALL`，结果都是 5 行，耗时都是 4.8ms 到 5.0ms，完全一致。

到了 2.0，`UNION` 才有"只让新增的 key 和被修改的 key 进入下一轮"的语义。所以如果代码里用了 `USING KEY ... UNION`，从 1.5 升到 2.0 的时候结果可能会变，得重新验一遍。

## 三行速查

递归 CTE 的坑基本可以归纳成三句话：

**防环**：树不需要，图必须防。最稳的是路径数组 + `list_position` 判重，其次是用 `UNION` 去重但保证去重的列集里不含递增列。

**护栏**：不要指望数据自己收敛，尤其是图。加一个 `WHERE depth < N` 的成本是零，漏掉它的成本是十二分钟后手动 `Ctrl+C`。

**类型**：锚点决定列类型。字面量全部显式标注（`0.0::DOUBLE`、`1::BIGINT`、`NULL::BIGINT`），别让 DuckDB 去猜。

再加一条经验：如果数据是固定深度的三层以内结构，老老实实 JOIN，别为了优雅上递归。递归 CTE 解决的是"深度不可知"的问题，不是"SQL 不好看"的问题。

## 参考资源

- DuckDB 官方文档 · WITH 子句（Recursive CTEs / USING KEY / Limitations）：<https://duckdb.org/docs/stable/sql/query_syntax/with.html>（正文抓取自同内容的 LTS 版本页 <https://duckdb.org/docs/lts/sql/query_syntax/with.html>）
- DuckDB 官方博客 · USING KEY in Recursive CTEs（2025-05-23）：<https://duckdb.org/2025/05/23/using-key.html>
- DuckDB 官方博客 · How DuckDB Runs Recursive CTEs Faster（2026-08-25，Denis Hirn）：<https://duckdb.org/2026/08/25/how-duckdb-runs-recursive-ctes-faster.html>
- SIGMOD 论文 · USING KEY in Recursive CTEs：<https://duckdb.org/library/bamberg-using-key-sigmod>
- 作者版本页：<https://db.cs.uni-tuebingen.de/publications/2025/using-key>

本文全部性能数据来自本机实测（腾讯云 4 核 AMD EPYC 7K62 / 7.5GB RAM / Ubuntu 24.04 / DuckDB 1.5.5 与 2.0.0-dev），官方公布的数字与本机实测数字在文中均已分别标注来源。