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

SELECT 
    value,
    CASE 
        WHEN value < 33 THEN 'low'
        WHEN value < 66 THEN 'medium'
        ELSE 'high'
    END as level
FROM test_data;
```sql

第二种是简单 CASE，适合等值匹配：

```sql
SELECT 
    status,
    CASE status
        WHEN 'pending' THEN '待处理'
        WHEN 'completed' THEN '已完成'
        WHEN 'cancelled' THEN '已取消'
        ELSE '未知状态'
    END as status_cn
FROM orders;
```sql

这两种写法性能差不多，主要看哪种更易读。我个人偏好第一种，因为条件可以写得更灵活。

## 布尔类型与三值逻辑

DuckDB 的布尔类型遵循 SQL 标准的三值逻辑：TRUE、FALSE、NULL。

这里有个关键点：NULL 不是 FALSE。

```sql
-- AND 运算
SELECT TRUE AND NULL;   -- 结果: NULL
SELECT FALSE AND NULL;  -- 结果: FALSE  -- 关键！

-- OR 运算
SELECT TRUE OR NULL;    -- 结果: TRUE   -- 关键！
SELECT FALSE OR NULL;   -- 结果: NULL
```sql

看到规律了吗？FALSE AND 任何值都是 FALSE，TRUE OR 任何值都是 TRUE。这是三值逻辑的核心。

在 WHERE 子句中，只有 TRUE 才会通过，NULL 和 FALSE 都会被过滤掉：

```sql
-- 假设 status 可能是 NULL
SELECT * FROM orders WHERE status = 'completed';  -- NULL 的行不会返回
```sql

这个特性在处理脏数据时很重要，后面会详细说。

## 条件函数速查

除了 CASE WHEN，DuckDB 还有几个常用的条件函数：

```sql
-- COALESCE: 返回第一个非 NULL 值
SELECT COALESCE(NULL, NULL, 'third', 'fourth');  -- 结果: 'third'

-- NULLIF: 相等时返回 NULL
SELECT NULLIF(1, 1);  -- 结果: NULL
SELECT NULLIF(1, 2);  -- 结果: 1

-- IF: CASE WHEN 的简写
SELECT IF(value > 0, 'positive', 'negative') FROM test_data;

-- GREATEST/LEAST: 最大值/最小值
SELECT GREATEST(1, 5, 3);  -- 结果: 5
SELECT LEAST(1, 5, 3);     -- 结果: 1
```sql

IF 函数本质上是 CASE WHEN 的语法糖，性能一样。我个人更喜欢用 IF，因为代码更简洁。

## 100 万行性能测试

光说语法没意思，来看看真实性能数据。

测试环境：macOS 13.7.6，Python 3.12.13，DuckDB 1.5.5，100 万行数据。

```python
import duckdb
import time

con = duckdb.connect()

# 创建 100 万行测试数据
con.execute("""
    CREATE TABLE test_data AS
    SELECT 
        i as id,
        random() * 100 as value,
        CASE WHEN random() > 0.7 THEN 'A'
             WHEN random() > 0.4 THEN 'B'
             ELSE 'C'
        END as category
    FROM generate_series(1, 1000000) t(i)
""")

# 测试简单 CASE WHEN
start = time.time()
result = con.execute("""
    SELECT 
        CASE 
            WHEN value < 33 THEN 'low'
            WHEN value < 66 THEN 'medium'
            ELSE 'high'
        END as level,
        COUNT(*) as cnt
    FROM test_data
    GROUP BY 1
""").fetchall()
print(f"简单 CASE WHEN: {time.time() - start:.3f}秒")

# 测试嵌套 CASE WHEN
start = time.time()
result = con.execute("""
    SELECT 
        CASE 
            WHEN value < 50 THEN 
                CASE WHEN value < 25 THEN 'very_low' ELSE 'low' END
            ELSE 
                CASE WHEN value < 75 THEN 'high' ELSE 'very_high' END
        END as level,
        COUNT(*) as cnt
    FROM test_data
    GROUP BY 1
""").fetchall()
print(f"嵌套 CASE WHEN: {time.time() - start:.3f}秒")

# 测试 CASE WHEN 在 WHERE 中
start = time.time()
result = con.execute("""
    SELECT COUNT(*) FROM test_data
    WHERE CASE WHEN value > 50 THEN TRUE ELSE FALSE END
""").fetchall()
print(f"CASE WHEN in WHERE: {time.time() - start:.3f}秒")

# 测试 CASE WHEN 在 ORDER BY 中
start = time.time()
result = con.execute("""
    SELECT * FROM test_data
    ORDER BY 
        CASE 
            WHEN category = 'A' THEN 1
            WHEN category = 'B' THEN 2
            ELSE 3
        END
    LIMIT 100
""").fetchall()
print(f"CASE WHEN in ORDER BY: {time.time() - start:.3f}秒")

# 测试 IF vs CASE WHEN
start = time.time()
result = con.execute("""
    SELECT 
        IF(value > 50, 'high', 'low') as level,
        COUNT(*) as cnt
    FROM test_data
    GROUP BY 1
""").fetchall()
print(f"IF 函数: {time.time() - start:.3f}秒")

start = time.time()
result = con.execute("""
    SELECT 
        CASE WHEN value > 50 THEN 'high' ELSE 'low' END as level,
        COUNT(*) as cnt
    FROM test_data
    GROUP BY 1
""").fetchall()
print(f"CASE WHEN: {time.time() - start:.3f}秒")

con.close()
```sql

实测结果：

| 测试场景 | 耗时 | 说明 |
|---------|------|------|
| 简单 CASE WHEN | 0.031 秒 | 3 个分支的范围判断 |
| 嵌套 CASE WHEN | 0.012 秒 | 多层条件判断 |
| CASE WHEN 在 WHERE 中 | 0.012 秒 | 条件过滤 |
| CASE WHEN 在 ORDER BY 中 | 0.022 秒 | 自定义排序 |
| IF 函数 | 0.007 秒 | 简写形式 |
| CASE WHEN | 0.008 秒 | 标准形式 |

几个关键发现：

1. 100 万行数据的 CASE WHEN 查询，耗时都在 30ms 以内。DuckDB 的向量化执行确实厉害。
2. 嵌套 CASE 不会显著影响性能，这点出乎我意料。
3. IF 和 CASE WHEN 性能几乎一样，可以互换使用。
4. CASE WHEN 在 WHERE 和 ORDER BY 中都有良好表现。

## 高级用法实战

### 条件聚合

这是 CASE WHEN 最常用的场景之一。比如统计每个类别的完成订单数和收入：

```sql
SELECT 
    category,
    COUNT(*) as total_orders,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as completed_revenue,
    AVG(CASE WHEN status = 'completed' THEN amount END) as avg_completed_amount
FROM orders
GROUP BY category;
```text

实测结果：

```sql
category      total  completed  revenue   avg_amt
electronics   3      2          250.00    125.00
clothing      2      1          50.00     50.00
food          1      1          30.00     30.00
```sql

注意 COUNT 和 SUM 的区别。COUNT(CASE WHEN ... THEN 1 END) 只统计非 NULL 的行，而 SUM 需要显式处理 ELSE 0，否则 NULL 会被忽略。

### 行转列（Pivot）

把行数据转换为列，这在报表中很常见：

```sql
SELECT 
    quarter,
    SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) as north_sales,
    SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) as south_sales,
    SUM(sales) as total_sales
FROM monthly_sales
GROUP BY quarter;
```text

实测结果：

```text
quarter  north  south  total
Q1       100    150    250
Q2       120    180    300
Q3       110    160    270
```sql

这种写法比用多个 JOIN 要简洁得多，性能也更好。

### 布尔聚合函数

DuckDB 提供了 bool_and 和 bool_or 函数：

```sql
SELECT 
    group_name,
    bool_and(passed) as all_passed,    -- 全部通过？
    bool_or(passed) as any_passed,     -- 有通过的？
    COUNT(CASE WHEN passed THEN 1 END) as pass_count
FROM test_results
GROUP BY group_name;
```text

实测结果：

```sql
group    all_passed  any_passed  pass_count
group1   TRUE        TRUE        3
group2   FALSE       TRUE        2
group3   FALSE       FALSE       0
```sql

bool_and 要求所有值都为 TRUE 才返回 TRUE，bool_or 只要有一个 TRUE 就返回 TRUE。这在批量验证场景中很有用。

### 自定义排序

按业务优先级排序，而不是字母顺序：

```sql
SELECT *
FROM tasks
ORDER BY 
    CASE 
        WHEN priority = 'urgent' THEN 1
        WHEN priority = 'high' THEN 2
        WHEN priority = 'medium' THEN 3
        ELSE 4
    END,
    created_at DESC;
```sql

这样 urgent 的任务会排在最前面，而不是按字母顺序排成 high、medium、urgent。

## 踩坑记录

### 坑 1：NULL 的陷阱

```sql
-- ❌ 错误：这样写不会匹配 NULL
SELECT * FROM users WHERE status = NULL;

-- ✅ 正确：必须用 IS NULL
SELECT * FROM users WHERE status IS NULL;

-- ✅ 或者用 COALESCE 处理
SELECT COALESCE(status, 'unknown') FROM users;
```sql

这个问题我踩过好几次。记住，NULL 不能用 = 比较，必须用 IS NULL。

### 坑 2：CASE WHEN 中的 NULL 处理

```sql
-- ❌ 错误：NULL 不会被 WHEN 匹配
SELECT 
    CASE status
        WHEN NULL THEN 'no status'  -- 永远不会匹配！
        ELSE 'has status'
    END
FROM users;

-- ✅ 正确：用 IS NULL 判断
SELECT 
    CASE 
        WHEN status IS NULL THEN 'no status'
        ELSE 'has status'
    END
FROM users;
```sql

简单 CASE 形式（CASE expression WHEN value）无法匹配 NULL，因为 NULL = NULL 返回 NULL 而不是 TRUE。必须用搜索型 CASE 配合 IS NULL。

### 坑 3：CASE WHEN 在 WHERE 中的性能

```sql
-- ⚠️ 不推荐：在 WHERE 中用 CASE WHEN
SELECT * FROM orders
WHERE CASE WHEN status = 'completed' THEN TRUE ELSE FALSE END;

-- ✅ 推荐：直接用条件表达式
SELECT * FROM orders
WHERE status = 'completed';
```sql

虽然性能测试显示 CASE WHEN 在 WHERE 中表现不错，但直接写条件更清晰，也更容易被优化器处理。

### 坑 4：布尔类型转换

```sql
-- DuckDB 支持多种布尔转换
SELECT 
    TRUE::BOOLEAN,           -- TRUE
    'true'::BOOLEAN,         -- TRUE
    'false'::BOOLEAN,        -- FALSE
    1::BOOLEAN,              -- TRUE
    0::BOOLEAN,              -- FALSE
    '1'::BOOLEAN,            -- TRUE
    '0'::BOOLEAN;            -- FALSE
```sql

DuckDB 的布尔转换很灵活，字符串 'true'/'false'、数字 1/0 都可以转成布尔值。这在处理 CSV 导入时很有用。

## 最佳实践

经过这些测试，总结几条经验：

1. **优先用 IF 函数**：对于简单的二选一场景，IF 比 CASE WHEN 更简洁，性能一样。

2. **处理 NULL 要谨慎**：用 COALESCE 提供默认值，用 IS NULL 检测空值，不要用 = NULL。

3. **条件聚合很实用**：COUNT(CASE WHEN ... THEN 1 END) 是统计特定条件行数的标准写法。

4. **避免在 WHERE 中用 CASE WHEN**：虽然性能没问题，但直接写条件更清晰。

5. **利用向量化优势**：DuckDB 的 CASE WHEN 采用向量化执行，即使嵌套多层也不会显著影响性能。

## 总结

CASE WHEN 是 SQL 中最常用的条件表达式之一，DuckDB 的实现既标准又高效。

关键点：
- 两种语法形式（搜索型和简单型），性能一致
- 三值逻辑（TRUE/FALSE/NULL），NULL 不是 FALSE
- 100 万行数据查询在 30ms 以内
- 条件聚合、行转列、自定义排序是常见用法
- NULL 处理要特别小心

希望这些内容能帮到正在用 DuckDB 的朋友。如果有任何疑问，欢迎在评论区讨论。

## 参考资源

1. [DuckDB 官方文档 - CASE 表达式](https://duckdb.org/docs/sql/expressions/case)
2. [DuckDB 官方文档 - 布尔类型](https://duckdb.org/docs/sql/datatypes/boolean)
3. [DuckDB 官方文档 - 工具函数](https://duckdb.org/docs/sql/functions/utility)