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
| -- 标准语法
SELECT CAST('123' AS INTEGER); -- 结果: 123
SELECT CAST('123.45' AS DOUBLE); -- 结果: 123.45
SELECT CAST(123 AS VARCHAR); -- 结果: '123'
SELECT CAST('2024-01-15' AS DATE); -- 结果: 2024-01-15
SELECT CAST(true AS INTEGER); -- 结果: 1
```sql
也支持 PostgreSQL 风格的简写：
```sql
SELECT '456'::INTEGER; -- 结果: 456
```sql
看起来没什么问题。但只要数据里混进一条脏数据，CAST 就会让整个查询挂掉：
```sql
-- 假设表里有一行 age = 'thirty'
SELECT CAST(age AS INTEGER) FROM users;
-- ❌ 错误：InvalidInputError: 无法将 'thirty' 转换为 INTEGER
-- 整条查询失败，一行都拿不到
```sql
这就是 CAST 的脾气——要么全对，要么全挂。
## TRY_CAST：数据清洗的救星
TRY_CAST 和 CAST 的区别只有一点：转换失败时不报错，返回 NULL。
就这一点区别，在数据清洗场景下却是天壤之别。
```sql
-- 同样的脏数据
SELECT TRY_CAST(age AS INTEGER) FROM users;
-- ✅ 正常数据返回整数，'thirty' 那行返回 NULL
-- 查询不会中断
```sql
| 输入 | 目标类型 | CAST 结果 | TRY_CAST 结果 |
|------|----------|-----------|---------------|
| '123' | INTEGER | 123 | 123 |
| 'abc' | INTEGER | ❌ 报错 | NULL |
| '2024-13-45' | DATE | ❌ 报错 | NULL |
| '' | INTEGER | ❌ 报错 | NULL |
| NULL | INTEGER | NULL | NULL |
一个实际的数据清洗场景：
```sql
-- 原始脏数据
-- user_id | age | salary | join_date | is_active
-- 1001 | 25 | 50000.50 | 2023-01-15 | true
-- 1002 | thirty | 60000 | 2023-02-20 | 1
-- 1003 | 28 | invalid | 2023-03-10 | false
-- 1004 | 35 | 75000.75 | invalid-date | true
SELECT
user_id,
TRY_CAST(age AS INTEGER) as age_clean,
TRY_CAST(salary AS DOUBLE) as salary_clean,
TRY_CAST(join_date AS DATE) as join_date_clean,
CASE
WHEN LOWER(is_active) IN ('true', '1', 'yes') THEN true
WHEN LOWER(is_active) IN ('false', '0', 'no') THEN false
ELSE NULL
END as is_active_clean
FROM dirty_data;
```text
结果：
```text
user_id | age_clean | salary_clean | join_date_clean | is_active_clean
--------|-----------|--------------|-----------------|----------------
1001 | 25 | 50000.5 | 2023-01-15 | true
1002 | NULL | 60000.0 | 2023-02-20 | true
1003 | 28 | NULL | 2023-03-10 | false
1004 | 35 | 75000.75 | NULL | true
```sql
脏数据被安全地转成 NULL，其余正常数据不受影响。
## DuckDB 不支持隐式转换
这个坑我踩了不止一次。
从 PostgreSQL 切过来的同学可能习惯了这种写法：
```sql
-- PostgreSQL 里完全合法
SELECT '123' + 1; -- 结果: 124（隐式把字符串转成整数）
```sql
在 DuckDB 里：
```sql
SELECT '123' + 1;
-- ❌ 错误：operator does not exist: varchar + integer
```sql
DuckDB 要求你显式转换，没有任何商量余地：
```sql
SELECT CAST('123' AS INTEGER) + 1; -- ✅ 结果: 124
```sql
另一个容易踩的坑——DATE 加 BIGINT：
```sql
-- ❌ 报错：DATE + BIGINT 不支持
SELECT DATE '2024-01-01' + (range % 365);
-- ✅ 需要显式转成 INTEGER
SELECT DATE '2024-01-01' + CAST((range % 365) AS INTEGER);
```sql
这种严格是好事。隐式转换在 PostgreSQL 里方便，但也制造了很多难以排查的 bug。DuckDB 选择了"让你写清楚"而不是"帮你猜"。
## 性能测试：100 万行数据
为了搞清楚类型转换对查询性能的影响，跑了一组 100 万行的测试。
### WHERE 条件中的转换
| 转换方式 | 执行时间 | 相对基线 |
|----------|----------|----------|
| 无转换（基线） | 0.0064 秒 | 1.0x |
| VARCHAR → INTEGER | 0.0095 秒 | 1.5x |
| DOUBLE → INTEGER | 0.0227 秒 | 3.5x |
| DATE → VARCHAR | 0.0081 秒 | 1.3x |
结论很明确：**避免在 WHERE 条件中对字段做类型转换**。
```sql
-- ❌ 差：对字段做转换，无法利用索引，全表扫描
SELECT * FROM users WHERE CAST(age AS VARCHAR) = '25';
-- ✅ 好：直接比较，索引可用
SELECT * FROM users WHERE age = 25;
```sql
### 批量转换：CAST vs TRY_CAST
| 方法 | 100 万行耗时 |
|------|-------------|
| CAST | 0.5375 秒 |
| TRY_CAST | 0.4838 秒 |
有个意外发现：TRY_CAST 比 CAST 快了大约 10%。原本以为 TRY_CAST 因为有错误处理逻辑会更慢，实测反而更快。可能是 DuckDB 内部对 TRY_CAST 做了更好的向量化优化。
## 数据质量检查
TRY_CAST 还有一个好用的地方——快速统计数据质量：
```sql
SELECT
COUNT(*) as total_rows,
COUNT(TRY_CAST(age AS INTEGER)) as valid_age,
COUNT(*) - COUNT(TRY_CAST(age AS INTEGER)) as invalid_age,
ROUND(100.0 * COUNT(TRY_CAST(age AS INTEGER)) / COUNT(*), 2) as age_valid_rate
FROM dirty_data;
```sql
这条查询能直接告诉你：100 万行数据里，有多少 age 字段是合法的整数，有多少是脏数据，合格率是多少。
在真正做数据清洗之前先跑一遍这种统计，心里有个底，比一头扎进去逐行排查要高效得多。
## 踩坑记录
### 坑1：逗号分隔的数字
```sql
-- ❌ 返回 NULL，DuckDB 不认逗号分隔符
SELECT TRY_CAST('123,456.78' AS DOUBLE);
-- ✅ 先去掉逗号
SELECT TRY_CAST(REPLACE('123,456.78', ',', '') AS DOUBLE);
```sql
从美国格式的 CSV 导入数据时经常遇到这个问题。
### 坑2：CAST 中断整个查询
```sql
-- ❌ 只要有一行转换失败，整条查询挂掉
SELECT CAST(str_val AS INTEGER) FROM table_with_bad_data;
-- ✅ 用 TRY_CAST 容错
SELECT TRY_CAST(str_val AS INTEGER) FROM table_with_bad_data;
```sql
如果确实需要用 CAST 做严格校验，可以先用 TRY_CAST 扫一遍看看有多少脏数据，再决定怎么处理。
### 坑3：科学计数法
```sql
-- ✅ DuckDB 支持科学计数法
SELECT TRY_CAST('1.23e5' AS INTEGER); -- 结果: 123000
```sql
这个不算坑，但值得知道。有些日志系统输出的数字是科学计数法格式，TRY_CAST 能直接处理。
### 坑4：超大整数溢出
```sql
-- 返回 NULL，不会报错
SELECT TRY_CAST('999999999999999999999' AS INTEGER);
```sql
TRY_CAST 的好处就在这里——溢出了也不会炸，安静地返回 NULL。
## 最佳实践
用了一段时间下来，总结几条原则：
**数据探索和清洗阶段**，一律用 TRY_CAST。不会因为一行脏数据导致整个查询挂掉，还能顺便统计数据质量。
**ETL 管道的中间步骤**，也用 TRY_CAST。容错性比严格性更重要。管道跑完了再检查 NULL 比例。
**最终输出和严格校验**，用 CAST。确保数据质量达标，有问题早发现。
**避免在 WHERE 中做类型转换**。如果查询条件涉及类型转换，考虑在数据导入时就转好，或者建一个转换后的视图。
**用 CTE 避免重复转换**：
```sql
-- ❌ 每行转换两次
SELECT
TRY_CAST(val AS INTEGER),
TRY_CAST(val AS INTEGER) * 2
FROM data;
-- ✅ 用 CTE 转一次
WITH converted AS (
SELECT TRY_CAST(val AS INTEGER) as val_int
FROM data
)
SELECT val_int, val_int * 2
FROM converted;
```text
## 参考资源
- [DuckDB Data Types Overview](https://duckdb.org/docs/current/sql/data_types/overview.html)
- [DuckDB Cast Functions](https://duckdb.org/docs/current/sql/functions/cast.html)
- [DuckDB Type Conversion](https://duckdb.org/docs/current/sql/expressions/cast.html)
测试环境：DuckDB 1.5.5，macOS 13.7.6，Python 3.12.13。
|
