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
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646

-- 三个月前写的，现在完全看不懂
SELECT * FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers
    WHERE tier IN (
        SELECT tier FROM tier_config WHERE is_vip = true
    )
)
```sql

后来发现了 CTE（Common Table Expression），也就是 `WITH` 子句。同样的逻辑，写法完全不一样：

```sql
-- 同一个查询，用 CTE 重写
WITH vip_tiers AS (
    SELECT tier FROM tier_config WHERE is_vip = true
),
vip_customers AS (
    SELECT customer_id FROM customers WHERE tier IN (SELECT tier FROM vip_tiers)
)
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM vip_customers)
```sql

可读性一下子就不一样了。但 CTE 只是"看起来舒服"吗？性能到底怎么样？

花了个下午做了个完整的对比测试，从基础子查询到递归 CTE，从 10 万行到 50 万行。结论先行：**CTE 不仅更好读，还更快**。

## 测试环境

- **系统**：macOS 13.7.6
- **DuckDB**：1.5.5
- **Python**：3.12.13
- **数据规模**：10 万行订单 + 100 个客户
- **数据集**：电商订单（orders）+ 客户表（customers）

数据生成：

```python
import duckdb
import time

conn = duckdb.connect(':memory:')

# 10 万行订单
conn.execute("""
    CREATE TABLE orders AS
    SELECT 
        row_number() OVER () as order_id,
        'CUST' || (random() * 100 + 1)::INTEGER as customer_id,
        (random() * 1000 + 10)::DECIMAL(10,2) as amount,
        TIMESTAMP '2024-01-01' + INTERVAL (random() * 365) DAY as order_date
    FROM generate_series(1, 100000)
""")

# 100 个客户，分 VIP / Regular / New 三个等级
conn.execute("""
    CREATE TABLE customers AS
    SELECT 
        'CUST' || row_number() OVER () as customer_id,
        'Customer ' || row_number() OVER () as name,
        CASE (random() * 3)::INTEGER
            WHEN 0 THEN 'VIP'
            WHEN 1 THEN 'Regular'
            ELSE 'New'
        END as tier
    FROM generate_series(1, 100)
""")
```sql

## 测试一：子查询 vs CTE

场景很常见——查 VIP 客户的订单统计。

```python
# 方式 1：子查询
start = time.time()
result1 = conn.execute("""
    SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_amount
    FROM orders
    WHERE customer_id IN (
        SELECT customer_id FROM customers WHERE tier = 'VIP'
    )
    GROUP BY customer_id
    ORDER BY total_amount DESC
""").fetchall()
time1 = time.time() - start
print(f"子查询: {time1*1000:.2f}ms")

# 方式 2：CTE
start = time.time()
result2 = conn.execute("""
    WITH vip_customers AS (
        SELECT customer_id FROM customers WHERE tier = 'VIP'
    ),
    customer_orders AS (
        SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_amount
        FROM orders
        WHERE customer_id IN (SELECT customer_id FROM vip_customers)
        GROUP BY customer_id
    )
    SELECT * FROM customer_orders
    ORDER BY total_amount DESC
""").fetchall()
time2 = time.time() - start
print(f"CTE: {time2*1000:.2f}ms")
```sql

结果：

| 方式 | 耗时 | 差距 |
|------|------|------|
| 子查询 | 23.21ms | - |
| CTE | 6.95ms | **快 70%** |

看到这个数据的时候我愣了一下。之前一直觉得 CTE 只是"写法更好看"，没想到性能差距这么大。后来想明白了——DuckDB 的查询优化器对 CTE 的处理更友好，可以更好地做谓词下推和连接重排。

## 测试二：相关子查询 vs CTE + JOIN

相关子查询是个经典坑。写法看起来很直觉，但性能往往是灾难。

```python
# 方式 1：相关子查询（每行都执行一次子查询）
start = time.time()
result3 = conn.execute("""
    SELECT 
        c.customer_id, c.name, c.tier,
        (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) as order_count,
        (SELECT COALESCE(SUM(amount), 0) FROM orders o WHERE o.customer_id = c.customer_id) as total_amount
    FROM customers c
    WHERE c.tier = 'VIP'
    ORDER BY total_amount DESC
""").fetchall()
time3 = time.time() - start
print(f"相关子查询: {time3*1000:.2f}ms")

# 方式 2：CTE + JOIN
start = time.time()
result4 = conn.execute("""
    WITH order_stats AS (
        SELECT 
            customer_id,
            COUNT(*) as order_count,
            COALESCE(SUM(amount), 0) as total_amount
        FROM orders
        GROUP BY customer_id
    )
    SELECT 
        c.customer_id, c.name, c.tier,
        os.order_count, os.total_amount
    FROM customers c
    LEFT JOIN order_stats os ON c.customer_id = os.customer_id
    WHERE c.tier = 'VIP'
    ORDER BY os.total_amount DESC
""").fetchall()
time4 = time.time() - start
print(f"CTE + JOIN: {time4*1000:.2f}ms")
```sql

结果：

| 方式 | 耗时 | 差距 |
|------|------|------|
| 相关子查询 | 17.30ms | - |
| CTE + JOIN | 4.96ms | **快 71%** |

相关子查询的问题在于，外层查询每返回一行，内层子查询就要重新执行。100 个客户里有 16 个 VIP，每个 VIP 都要扫两次 orders 表（一次 COUNT，一次 SUM）。换成 CTE + JOIN 之后，orders 表只扫一次就做完了聚合。

## 测试三：嵌套 CTE 做多步分析

这才是 CTE 真正发光的场景。把复杂分析拆成一步步，每步都有名字，读起来像文档。

场景：按客户等级分组，统计每个等级的客户数、平均订单数、平均客单价、总营收，再算排名。

```python
start = time.time()
result5 = conn.execute("""
    WITH 
    -- 第一步：每个客户的订单统计
    customer_stats AS (
        SELECT 
            customer_id,
            COUNT(*) as order_count,
            AVG(amount) as avg_amount,
            SUM(amount) as total_amount
        FROM orders
        GROUP BY customer_id
    ),
    -- 第二步：按等级分组
    tier_stats AS (
        SELECT 
            c.tier,
            COUNT(DISTINCT c.customer_id) as customer_count,
            AVG(cs.order_count) as avg_orders_per_customer,
            AVG(cs.avg_amount) as avg_order_value,
            SUM(cs.total_amount) as tier_total
        FROM customers c
        JOIN customer_stats cs ON c.customer_id = cs.customer_id
        GROUP BY c.tier
    ),
    -- 第三步：排名
    tier_ranking AS (
        SELECT 
            tier, customer_count, avg_orders_per_customer,
            avg_order_value, tier_total,
            RANK() OVER (ORDER BY tier_total DESC) as revenue_rank
        FROM tier_stats
    )
    SELECT * FROM tier_ranking
    ORDER BY revenue_rank
""").fetchall()
time5 = time.time() - start
print(f"嵌套 CTE: {time5*1000:.2f}ms")
```text

11.96ms 跑完三步分析。结果：

```text
New:      52 个客户, 平均 1003 单, 总营收 ¥26,604,910  (排名第 1)
Regular:  32 个客户, 平均 993 单,  总营收 ¥16,204,991  (排名第 2)
VIP:      16 个客户, 平均 969 单,  总营收 ¥7,916,703   (排名第 3)
```sql

如果这个查询用子查询来写，大概会变成四层嵌套，每层都要靠缩进来判断归属。CTE 的好处就是——每一步都是一个独立的"小表"，逻辑清清楚楚。

## 测试四：递归 CTE 查组织架构

树形结构查询是子查询根本做不到的事情。`WITH RECURSIVE` 是处理组织架构、BOM 物料清单、分类层级的唯一正解。

```python
# 组织架构数据
conn.execute("""
    CREATE TABLE employees AS
    SELECT * FROM (VALUES
        (1, 'CEO', NULL),
        (2, 'CTO', 1),
        (3, 'CFO', 1),
        (4, 'Dev Manager', 2),
        (5, 'QA Manager', 2),
        (6, 'Finance Manager', 3),
        (7, 'Developer', 4),
        (8, 'Tester', 5),
        (9, 'Accountant', 6)
    ) AS t(id, name, manager_id)
""")

start = time.time()
result6 = conn.execute("""
    WITH RECURSIVE org_chart AS (
        -- 基础：CEO
        SELECT id, name, manager_id, 1 as level, name as path
        FROM employees
        WHERE manager_id IS NULL
        
        UNION ALL
        
        -- 递归：下属
        SELECT e.id, e.name, e.manager_id,
               oc.level + 1,
               oc.path || ' > ' || e.name
        FROM employees e
        JOIN org_chart oc ON e.manager_id = oc.id
    )
    SELECT * FROM org_chart
    ORDER BY level, id
""").fetchall()
time6 = time.time() - start
print(f"递归 CTE: {time6*1000:.2f}ms")
```text

6.20ms，输出完整的组织树：

```text
Level 1: CEO
Level 2: CEO > CTO
Level 2: CEO > CFO
Level 3: CEO > CTO > Dev Manager
Level 3: CEO > CTO > QA Manager
Level 3: CEO > CFO > Finance Manager
Level 4: CEO > CTO > Dev Manager > Developer
Level 4: CEO > CTO > QA Manager > Tester
Level 4: CEO > CFO > Finance Manager > Accountant
```sql

递归 CTE 的思路其实很简单：先找到根节点（CEO），然后不断找"谁的 manager 是已经找到的人"，直到找不到为止。写起来比想象中简单，但第一次用的时候，递归终止条件没写对，差点搞出死循环——这个坑后面会说。

## 测试五：窗口函数 + CTE 做时序分析

月度销售排名、环比增长、累计金额——这三个指标放一起，用子查询写会疯掉。CTE + 窗口函数就优雅很多。

```python
start = time.time()
result7 = conn.execute("""
    WITH monthly_orders AS (
        SELECT 
            customer_id,
            DATE_TRUNC('month', order_date) as month,
            COUNT(*) as order_count,
            SUM(amount) as monthly_amount
        FROM orders
        GROUP BY customer_id, DATE_TRUNC('month', order_date)
    ),
    customer_ranking AS (
        SELECT 
            customer_id, month, order_count, monthly_amount,
            RANK() OVER (
                PARTITION BY month ORDER BY monthly_amount DESC
            ) as month_rank,
            LAG(monthly_amount) OVER (
                PARTITION BY customer_id ORDER BY month
            ) as prev_month_amount,
            SUM(monthly_amount) OVER (
                PARTITION BY customer_id 
                ORDER BY month 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) as cumulative_amount
        FROM monthly_orders
    )
    SELECT * FROM customer_ranking
    WHERE month_rank <= 5
    ORDER BY month DESC, month_rank
""").fetchall()
time7 = time.time() - start
print(f"窗口函数 + CTE: {time7*1000:.2f}ms")
```text

35.58ms，输出 60 行（12 个月 × 每月 Top 5）。12 月的 Top 5：

```text
CUST24: 101 单, ¥54,823, 月排名第 1, 上月 ¥40,734, 累计 ¥538,628
CUST58: 92 单,  ¥53,101, 月排名第 2, 上月 ¥41,509, 累计 ¥513,065
CUST27: 100 单, ¥51,587, 月排名第 3, 上月 ¥35,040, 累计 ¥531,288
CUST91: 99 单,  ¥50,824, 月排名第 4, 上月 ¥50,780, 累计 ¥514,505
CUST12: 95 单,  ¥50,377, 月排名第 5, 上月 ¥34,092, 累计 ¥498,633
```sql

这种查询用 CTE 拆开之后，每一步都很清晰：先按月聚合，再加窗口函数算排名和累计。如果硬塞进子查询里，光括号就能数到眼花。

## 测试六：CTE vs 临时表

有人可能会问：既然 CTE 这么好，那跟临时表比呢？

```python
# CTE
start = time.time()
conn.execute("""
    WITH high_value_orders AS (
        SELECT customer_id, amount FROM orders WHERE amount > 500
    ),
    customer_summary AS (
        SELECT customer_id, COUNT(*) as high_value_count, AVG(amount) as avg_high_value
        FROM high_value_orders GROUP BY customer_id
    )
    SELECT * FROM customer_summary WHERE high_value_count > 5
    ORDER BY avg_high_value DESC
""").fetchall()
time_cte = time.time() - start

# 临时表
start = time.time()
conn.execute("CREATE TEMP TABLE temp_hv AS SELECT customer_id, amount FROM orders WHERE amount > 500")
conn.execute("CREATE TEMP TABLE temp_sum AS SELECT customer_id, COUNT(*) as cnt, AVG(amount) as avg FROM temp_hv GROUP BY customer_id")
conn.execute("SELECT * FROM temp_sum WHERE cnt > 5 ORDER BY avg DESC").fetchall()
time_temp = time.time() - start

print(f"CTE: {time_cte*1000:.2f}ms, 临时表: {time_temp*1000:.2f}ms")
```sql

| 方式 | 耗时 | 差距 |
|------|------|------|
| CTE | 6.05ms | - |
| 临时表 | 16.42ms | **慢 2.7 倍** |

CTE 不需要写磁盘，DuckDB 可以在内存里直接流水线执行。临时表要先创建、写入、再读取，多了两次 I/O。除非中间结果需要被多个独立查询复用，否则 CTE 基本是更优选择。

## 测试七：物化 CTE 的时机

CTE 有个容易被忽略的特性：`AS MATERIALIZED`。当一个 CTE 被引用多次时，DuckDB 默认可能会把它展开成子查询执行多次。用 `AS MATERIALIZED` 可以强制只算一次。

```python
# 非物化 CTE（可能被计算两次）
start = time.time()
conn.execute("""
    WITH customer_stats AS (
        SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_amount
        FROM orders GROUP BY customer_id
    )
    SELECT * FROM customer_stats WHERE order_count > 100
    UNION ALL
    SELECT * FROM customer_stats WHERE total_amount > 50000
""").fetchall()
time_non_mat = time.time() - start

# 物化 CTE（只计算一次）
start = time.time()
conn.execute("""
    WITH customer_stats AS MATERIALIZED (
        SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_amount
        FROM orders GROUP BY customer_id
    )
    SELECT * FROM customer_stats WHERE order_count > 100
    UNION ALL
    SELECT * FROM customer_stats WHERE total_amount > 50000
""").fetchall()
time_mat = time.time() - start

print(f"非物化: {time_non_mat*1000:.2f}ms, 物化: {time_mat*1000:.2f}ms")
```sql

| 方式 | 耗时 | 差距 |
|------|------|------|
| 非物化 CTE | 8.59ms | - |
| 物化 CTE | 4.70ms | **快 45%** |

规则很简单：**CTE 被引用超过一次，就加 `AS MATERIALIZED`**。代价是多一点内存占用，但省掉的重复计算通常更值。

## 测试八：复杂业务场景 - 客户生命周期价值

最后来个实战场景：识别高价值客户，计算客户生命周期价值（LTV）。

```python
start = time.time()
result = conn.execute("""
    WITH 
    -- 月度订单汇总
    monthly_orders AS (
        SELECT 
            customer_id,
            DATE_TRUNC('month', order_date) as month,
            COUNT(*) as order_count,
            SUM(amount) as monthly_amount
        FROM orders
        GROUP BY customer_id, DATE_TRUNC('month', order_date)
    ),
    -- 高价值客户筛选（月均 >5 单且月均金额 >3000）
    high_value_customers AS (
        SELECT customer_id,
            AVG(order_count) as avg_monthly_orders,
            AVG(monthly_amount) as avg_monthly_amount
        FROM monthly_orders
        GROUP BY customer_id
        HAVING AVG(order_count) > 5 AND AVG(monthly_amount) > 3000
    ),
    -- 计算 LTV
    customer_ltv AS (
        SELECT 
            hvc.customer_id,
            hvc.avg_monthly_orders,
            hvc.avg_monthly_amount,
            COUNT(DISTINCT mo.month) as active_months,
            hvc.avg_monthly_amount * COUNT(DISTINCT mo.month) as lifetime_value
        FROM high_value_customers hvc
        JOIN monthly_orders mo ON hvc.customer_id = mo.customer_id
        GROUP BY hvc.customer_id, hvc.avg_monthly_orders, hvc.avg_monthly_amount
    )
    SELECT 
        customer_id,
        ROUND(avg_monthly_orders, 2) as avg_monthly_orders,
        ROUND(avg_monthly_amount, 2) as avg_monthly_amount,
        active_months,
        ROUND(lifetime_value, 2) as lifetime_value
    FROM customer_ltv
    ORDER BY lifetime_value DESC
    LIMIT 10
""").fetchall()
time8 = time.time() - start
print(f"复杂业务分析: {time8*1000:.2f}ms")
```text

18.88ms，Top 5 高价值客户：

```text
CUST83: 月均 88 单, 月均 ¥45,900, 活跃 12 月, LTV ¥550,801
CUST93: 月均 87 单, 月均 ¥45,624, 活跃 12 月, LTV ¥547,494
CUST7:  月均 86 单, 月均 ¥44,789, 活跃 12 月, LTV ¥537,474
CUST45: 月均 86 单, 月均 ¥44,703, 活跃 12 月, LTV ¥536,437
CUST90: 月均 85 单, 月均 ¥44,265, 活跃 12 月, LTV ¥531,188
```sql

四步分析逻辑，用 CTE 拆开之后每一步都很直观。这种查询如果写成嵌套子查询，我估计写完之后自己都要花十分钟才能读懂。

## 大数据量验证：50 万行

为了确保结论可靠，又加了个 50 万行的测试：

```python
conn.execute("""
    CREATE TABLE large_orders AS
    SELECT 
        row_number() OVER () as order_id,
        'CUST' || (random() * 500 + 1)::INTEGER as customer_id,
        (random() * 1000 + 10)::DECIMAL(10,2) as amount,
        TIMESTAMP '2024-01-01' + INTERVAL (random() * 365) DAY as order_date
    FROM generate_series(1, 500000)
""")

# 子查询
start = time.time()
conn.execute("""
    SELECT customer_id, COUNT(*), SUM(amount)
    FROM large_orders
    WHERE customer_id IN (
        SELECT DISTINCT customer_id FROM large_orders WHERE amount > 800
    )
    GROUP BY customer_id
""").fetchall()
time_sub = time.time() - start

# CTE
start = time.time()
conn.execute("""
    WITH high_value AS (
        SELECT DISTINCT customer_id FROM large_orders WHERE amount > 800
    )
    SELECT lo.customer_id, COUNT(*), SUM(lo.amount)
    FROM large_orders lo
    JOIN high_value hv ON lo.customer_id = hv.customer_id
    GROUP BY lo.customer_id
""").fetchall()
time_cte = time.time() - start

print(f"50 万行 - 子查询: {time_sub*1000:.2f}ms, CTE: {time_cte*1000:.2f}ms")
```sql

| 方式 | 耗时 | 差距 |
|------|------|------|
| 子查询 | 20.17ms | - |
| CTE | 19.55ms | 快 3.1% |

有意思——数据量大了之后，差距反而缩小了。DuckDB 的优化器在大数据量下对子查询的处理也更好。但 CTE 仍然略优，而且可读性优势不变。

## 性能数据汇总

| 测试场景 | 子查询 | CTE | CTE 提升 |
|---------|--------|-----|---------|
| VIP 客户订单统计 | 23.21ms | 6.95ms | **70%** |
| 相关子查询 vs CTE+JOIN | 17.30ms | 4.96ms | **71%** |
| CTE vs 临时表 | - | 6.05ms vs 16.42ms | **63%** |
| 物化 vs 非物化 CTE | - | 4.70ms vs 8.59ms | **45%** |
| 50 万行大数据 | 20.17ms | 19.55ms | 3.1% |

其他场景的 CTE 耗时：

| 场景 | 耗时 |
|------|------|
| 嵌套 CTE（三步分析） | 11.96ms |
| 递归 CTE（组织架构） | 6.20ms |
| 窗口函数 + CTE（时序分析） | 35.58ms |
| 复杂业务分析（LTV 计算） | 18.88ms |

## 踩坑记录

### DATE_TRUNC 的参数顺序

第一次写月度聚合的时候，`DATE_TRUNC` 的参数写反了：

```sql
-- 错误
GROUP BY customer_id, DATE_TRUNC('order_date')

-- 正确
GROUP BY customer_id, DATE_TRUNC('month', order_date)
```sql

`DATE_TRUNC` 需要两个参数：时间粒度和时间字段。少了粒度参数会直接报错。

### 递归 CTE 死循环

写组织架构递归查询的时候，第一次忘了在 `JOIN` 条件里限制 `manager_id`，结果递归停不下来。DuckDB 默认没有递归深度限制，会一直跑到内存爆掉。

解决方案：确保递归条件正确，或者加个 `level` 上限：

```sql
WITH RECURSIVE tree AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN tree t ON e.manager_id = t.id
    WHERE t.level < 10  -- 限制递归深度
)
SELECT * FROM tree;
```sql

### CTE 作用域

CTE 只在定义它的那条查询里有效。如果想在多条查询里复用中间结果，要么用临时表，要么把 CTE 复制过去。这个跟临时表的行为不一样，刚开始用的时候容易搞混。

## 什么时候用 CTE，什么时候用子查询

经过这轮测试，我的经验是：

**用 CTE 的场景：**
- 查询逻辑超过两层嵌套
- 同一个中间结果被引用多次（记得加 `AS MATERIALIZED`）
- 需要递归（树形结构、BOM 展开）
- 多步骤分析（先聚合、再排名、再筛选）
- 需要配合窗口函数做时序分析

**子查询还行的场景：**
- 简单的 `WHERE ... IN (SELECT ...)` 过滤
- 只出现一次的标量子查询
- 数据量特别大且逻辑简单（差距会缩小）

**绝对要避免的：**
- 相关子查询（每行都执行一次子查询，性能灾难）
- 超过三层的嵌套子查询（可读性为零）

## 参考资源

- [DuckDB CTE 官方文档](https://duckdb.org/docs/sql/query_syntax/with)
- [DuckDB 递归查询](https://duckdb.org/docs/sql/query_syntax/with#recursive-ctes)
- [DuckDB 物化 CTE](https://duckdb.org/docs/sql/query_syntax/with#materialized-ctes)
- [DuckDB 窗口函数](https://duckdb.org/docs/sql/window_functions)


### CTE 作用域

CTE 只在定义它的那条查询里有效。如果想在多条查询里复用中间结果，要么用临时表，要么把 CTE 复制过去。这个跟临时表的行为不一样，刚开始用的时候容易搞混。

## 什么时候用 CTE，什么时候用子查询

经过这轮测试，我的经验是：

**用 CTE 的场景：**
- 查询逻辑超过两层嵌套
- 同一个中间结果被引用多次（记得加 `AS MATERIALIZED`）
- 需要递归（树形结构、BOM 展开）
- 多步骤分析（先聚合、再排名、再筛选）
- 需要配合窗口函数做时序分析

**子查询还行的场景：**
- 简单的 `WHERE ... IN (SELECT ...)` 过滤
- 只出现一次的标量子查询
- 数据量特别大且逻辑简单（差距会缩小）

**绝对要避免的：**
- 相关子查询（每行都执行一次子查询，性能灾难）
- 超过三层的嵌套子查询（可读性为零）

## 参考资源

- [DuckDB CTE 官方文档](https://duckdb.org/docs/sql/query_syntax/with)
- [DuckDB 递归查询](https://duckdb.org/docs/sql/query_syntax/with#recursive-ctes)
- [DuckDB 物化 CTE](https://duckdb.org/docs/sql/query_syntax/with#materialized-ctes)
- [DuckDB 窗口函数](https://duckdb.org/docs/sql/window_functions)