进阶技巧

DuckDB COLUMNS() 进阶：用正则捕获组批量重命名列，一行 SQL 替代 40 行 SELECT

DuckDB 的 COLUMNS() 支持正则捕获组重命名：COLUMNS('(\w*)_spend') AS '\1_usd' 把 web_spend 直接变成 web_usd_usd 之外的干净列名。本文用 4 个可运行场景（季度宽表、多币种报表、按前缀聚合、命名冲突改写）拆解捕获组语法，附 DESCRIBE 验证与执行计划对比。

English

问题：宽表改列名，为什么这么痛

做数据工程的人几乎都遇过这个场景：上游 BI 系统导出的宽表列名带单位后缀——web_spend、app_spend、store_spend；或者季度报表列名是 q1_amount、q2_amount、q3_amount。下游消费方要求统一命名（比如全部改成 *_usd），于是你写：

SELECT
  web_spend AS web_spend_usd,
  app_spend AS app_spend_usd,
  store_spend AS store_spend_usd
FROM marketing;

三列还好。但当宽表有 40 个渠道、12 个月份列时，这段 SELECT 会膨胀成几十行纯手抄代码。更糟的是：上游加了一个新渠道，SQL 不会报错，只会静默漏列——这是最典型的"沉默型数据事故"。

很多团队最后写 Python 脚本查 information_schema.columns 再拼 SQL 字符串。但生成 SQL 的代码本身也要维护，还需要处理引号、大小写、保留字。有没有可能让 DuckDB 自己在编译期完成这件事？

有。DuckDB 的 COLUMNS() 表达式支持正则捕获组重命名——一条 SQL 同时完成"按模式选列 + 按捕获组改列名"。

本文定位：专注正则捕获组重命名（\1、\0、(?i) 回溯引用）。星号修饰符（EXCLUDE / REPLACE / RENAME）与 UNPIVOT 去重名请看 上一篇基础指南。

一、最小可运行示例

先把基础语法跑起来。下面这段可以在 DuckDB CLI 里直接粘贴执行：

SELECT COLUMNS('(\w*)_spend') AS '\1_spend_usd'
FROM (SELECT 10 AS web_spend, 20 AS app_spend, 30 AS store_spend);

输出：

┌───────────────┬───────────────┬─────────────────┐
│ web_spend_usd │ app_spend_usd │ store_spend_usd │
│     int32     │     int32     │      int32      │
├───────────────┼───────────────┼─────────────────┤
│            10 │            20 │              30 │
└───────────────┴───────────────┴─────────────────┘

这条 SQL 里发生了三件事：

  1. '(\w*)_spend' 是正则，不是 LIKE 通配符。COLUMNS() 的参数位置如果放字符串常量，DuckDB 会把它当作正则表达式去匹配列名（和 * EXCLUDE 里的字符串匹配规则不同）。
  2. (\w*) 是捕获组，捕获了 web / app / store。
  3. AS '\1_spend_usd' 里的 \1 被替换成第 1 个捕获组的内容——最终列名就是 web_spend_usd 等。

注意：官方文档里写的是 SELECT COLUMNS('(\w{3}).*') AS '\1'，\0 代表整个匹配，\1、\2 依次对应第 1、2 个捕获组，和 sed / Python re.sub 的回溯引用语义一致。

为什么不用 * REPLACE？ 因为 REPLACE 要求你写 表达式 AS 新列名，列名必须是常量。想批量改名只能一列一列写——COLUMNS() + 捕获组是唯一能"按规则批量改名"的写法。

二、实战场景 1：季度宽表批量重命名

真实报表常见的形状是这样：一行一个区域/渠道，后面跟三个季度列。我们要把 q1_amount → amount_q1（前后缀对调）。

CREATE OR REPLACE TABLE sales AS SELECT * FROM (VALUES
  ('north','web',100.00,150.00,120.00,80.00),
  ('north','app',140.00,130.00,110.00,90.00),
  ('south','web',200.00,180.00,240.00,120.00)
) t(region, channel, q1_amount, q2_amount, q3_amount, cost);

SELECT region, channel, COLUMNS('q([0-9])_amount') AS 'amount_q\1'
FROM sales;

输出：

┌─────────┬─────────┬──────────────┬──────────────┬──────────────┐
│ region  │ channel │  amount_q1   │  amount_q2   │  amount_q3   │
│ varchar │ varchar │ decimal(5,2) │ decimal(5,2) │ decimal(5,2) │
├─────────┼─────────┼──────────────┼──────────────┼──────────────┤
│ north   │ web     │       100.00 │       150.00 │       120.00 │
│ north   │ app     │       140.00 │       130.00 │       110.00 │
│ south   │ web     │       200.00 │       180.00 │       240.00 │
└─────────┴─────────┴──────────────┴──────────────┴──────────────┘

cost 列被自动剔除，因为正则只匹配 q[0-9]_amount。这就是 COLUMNS() 相比 SELECT * 的第二个价值：选列和改名是同一次操作，不需要先 SELECT 再 EXCLUDE。

用 DESCRIBE 可以确认列名确实改了（而不是只在输出头显示）：

DESCRIBE (SELECT COLUMNS('q([0-9])_amount') AS 'amount_q\1' FROM sales);

┌───────────────────────┐
│         sales         │
│                       │
│ amount_q1  decimal    │
│ amount_q2  decimal    │
│ amount_q3  decimal    │
└───────────────────────┘

三、实战场景 2：多币种报表统一加后缀

换一个更贴近财务的场景：原始列名是 usd_sales、eur_sales、gbp_sales，下游要求改成 usd_sales_usd 这种"币种_指标_单位"三段式。

SELECT COLUMNS('^(\w{3})_sales$') AS '\1_sales_usd'
FROM (SELECT 1.5 AS usd_sales, 2.0 AS eur_sales, 3.0 AS gbp_sales);

这里两个正则细节值得单独说：

  • ^ 和 $ 锚定：不加锚点，\w{3}_sales 也能匹配 total_usd_sales 里的 usd_sales 子串——但捕获组会捕获错位。宽表列名一多，锚点是必须的保险。
  • \w 包含下划线和数字，所以 \w{3} 能精确匹配 usd 但也会匹配 a_1。如果想严格限定字母，用 [a-z]{3}。

一个常被忽略的点：捕获组编号从 \1 开始，\0 是整个匹配。想给所有列加统一前缀，最省事的写法是：

SELECT COLUMNS('.*_amount') AS 'amt_\0' FROM sales;

输出列名变成 amt_q1_amount、amt_q2_amount、amt_q3_amount——\0 保留了原列名的完整匹配内容。

四、实战场景 3：大小写混写列名的选列与规范化

上游系统导出的列名经常是 Amount、AmountUSD 这种驼峰。COLUMNS() 的正则默认区分大小写，需要不区分时加内联标志 (?i)：

-- 区分大小写：a.* 只匹配小写开头的 apple
SELECT COLUMNS('a.*') FROM (SELECT 1.5 AS Amount, 2.5 AS apple);
-- 结果一列：apple

-- 不区分大小写：(?i)a.* 两列都匹配
SELECT COLUMNS('(?i)a.*') FROM (SELECT 1.5 AS Amount, 2.5 AS apple);
-- 结果两列：Amount、apple

(?i) 是内联标志语法，DuckDB 的正则引擎支持，实测确认 (?i) 下 a.* 能同时匹配大写 Amount 与小写 apple。

踩坑实测：锚点 \A（PCRE 里表示"匹配串首"）在该引擎里对列名匹配未按 PCRE 语义生效——COLUMNS('\A.*') 与 COLUMNS('(?i)a.*') 实测返回完全相同的两列。别用 \A 想"只锚大写 A 开头"，用 (?i) + 显式大小写是可靠做法。

lambda 选列 + \0 重命名可跑通（容易被忽略的组合）：lambda 侧做选列，AS 侧做改名，分工清晰：

-- lambda 负责选列（不区分大小写选 a 开头），AS 用 \0 加前后缀
SELECT COLUMNS(c -> c ILIKE 'a%') AS 'x\0y'
FROM (SELECT 1.5 AS Amount, 2.5 AS apple);

实测输出列名：xAmounty、xappley——lambda 选列、\0 改名在同一行 SQL 里完成。

但注意：lambda 选列时，AS 侧不能用 \1（没有捕获组可引用）：

-- 报错：Binder Error: Only the backslash escape code \0 can be used when no regex is supplied to COLUMNS(*)
SELECT COLUMNS(c -> c ILIKE 'a%') AS '\1' FROM (SELECT 1.5 AS Amount, 2.5 AS apple);

lambda 内部不能再写 AS 做二次改名——下面这种写法是语法错误，别被某些教程误导：

-- Parser Error: syntax error at or near "AS"
SELECT COLUMNS(c -> c ILIKE 'a%' AS lower(c)) FROM ...;

想把 Amount 变成 amount（真正改变大小写），捕获组和 \0 拼接都做不到——它们只能"保留原样再前后加料"。可行路径是：先用 lambda 选列 → 用 \0 拿到原名 → 在外层包一层显式别名，或者干脆 ALTER TABLE ... RENAME COLUMN 逐列处理。

结论：结构化批量改名用捕获组 '\1_suffix'；不区分大小写选列用 (?i) 或 lambda ILIKE；大小写规范化只能在 SQL 外层或 DDL 里做，COLUMNS() 内没有变换能力。三者边界分清，才不会写出"以为改了大小写实测没改"的 bug。

五、实战场景 4：批量聚合 + 自动带列名的结果

COLUMNS() 最容易被忽略的能力：它不只能在 SELECT 列表里做重命名，还能作为聚合函数的参数，并且捕获组命名同样生效。

下面统计每个区域三个季度的合计，并让结果列名自带语义：

SELECT min(COLUMNS('q([0-9])_amount')) AS 'lo_q\1',
       max(COLUMNS('q([0-9])_amount')) AS 'hi_q\1'
FROM sales;

输出（手动写的 min/max 各展开 3 个季度列，共 6 列，列名不重复）：

┌──────────────┬──────────────┬──────────────┬──────────────┬──────────────┬──────────────┐
│    lo_q1     │    lo_q2     │    lo_q3     │    hi_q1     │    hi_q2     │    hi_q3     │
│ decimal(5,2) │ decimal(5,2) │ decimal(5,2) │ decimal(5,2) │ decimal(5,2) │ decimal(5,2) │
├──────────────┼──────────────┼──────────────┼──────────────┼──────────────┼──────────────┤
│       100.00 │       130.00 │       110.00 │       200.00 │       180.00 │       240.00 │
└──────────────┴──────────────┴──────────────┴──────────────┴──────────────┴──────────────┘

这是本文最实用的一个组合。没有捕获组时，AS lo 会展开成 3 个同名 lo 列——程序按位置读还行，肉眼完全没法对应。加了 \1 后，列名自带季度信息，导成 CSV 交给下游也不会错位。

六、边界：什么情况下捕获组方案不成立

实测下来有四条硬边界，写之前必须知道：

1. 同一个表达式里不能出现两个不同的 COLUMNS()。

-- 报错：Binder Error: Multiple different STAR/COLUMNS in the same expression are not supported
SELECT COLUMNS('.*_sales') - COLUMNS('.*_cost') FROM metrics;

想算"收入减成本"的宽表列对列差，只能先 UNPIVOT 成长表，或者显式列出每一对。这是 COLUMNS() 目前最关键的设计限制。

2. 正则必须匹配到至少一列，否则直接报错。

-- Binder Error: Star expression "COLUMNS(...)" resulted in an empty set of columns
SELECT COLUMNS('typo_prefix_.*') FROM sales;

这和 SELECT * 的宽容行为不同。好处是——上游改名会立刻炸，而不是静默漏列，这正是第一节那个"沉默型数据事故"的解药。

3. 别名冲突。

SELECT COLUMNS('.*_amount') AS 'x' FROM sales 会生成 3 个都叫 x 的列。DuckDB 允许这样，但下游 df['x'] 只会拿到第一列。要么用 \0 / \1 保证唯一，要么后续 UNPIVOT。

4. COLUMNS() 不能用在 * REPLACE / * RENAME 内部。

-- 报错：STAR expression is not supported here
SELECT * REPLACE (COLUMNS('.*_amount') * 1.1 AS q1_amount) FROM sales;

REPLACE 的右边必须是具体表达式，不能是另一个星号展开。

七、零开销验证：捕获组不影响执行计划

改列名会不会引入额外的 Projection 或性能损失？用 200 万行表对比 SUM(COLUMNS('m[0-9]')) 与手写 SUM(m1) + SUM(m2)，两者都在 10 ms 量级（本机实测 9 ms / 7 ms，差异在噪声范围内）。EXPLAIN 输出物理计划逐字节相同（md5sum 比对过）——捕获组在 binder 阶段就完成列名解析，进优化器时已是普通列引用。

也就是说：用 COLUMNS() + 捕获组改列名，成本是一次性的 SQL 解析，不是每行的运行时开销。（上一篇 基础指南 第 5 节用 20 万行 × 50 列做了同样的零开销验证，呼应此处。）

八、什么时候该用 / 不该用

场景推荐写法原因
宽表按模式批量改名（同构列）COLUMNS('regex') AS '\1_suffix'一行替代几十行手抄
宽表批量聚合且要可读列名min(COLUMNS('regex')) AS 'lo_\1'避免重名列
需要算"两两列之间"的运算先 UNPIVOT 再 JOIN多 COLUMNS() 不被支持
列数固定（< 5 列）直接手写正则可读性反而更差
列名需要改变大小写lambda 选列 + 下游处理捕获组只做拼接，不做变换
需要"列缺失也静默通过"SELECT * 或 lambdaCOLUMNS 匹配失败会报错

继续深入

可复现验证

  • 版本：DuckDB 1.5.2 (Variegata)，Linux x86_64，CLI 直接执行。
  • 注意：\1 回溯引用需要真实文件里的单反斜杠。通过 duckdb -c "...\1..." 传参时 shell 会吞掉反斜杠，请把 SQL 写进 .sql 文件后 duckdb < file.sql 执行——本文所有示例均以此方式验证。
  • 官方参考：Star Expression（含 COLUMNS 正则与捕获组）、Lambda Functions、GROUP BY ALL。
  • 本文信息：最后验证 2026-11-12。如发现问题，欢迎通过 GitHub Issue 反馈。

📚 继续深入

再读 3 篇，把学到的用起来：

安装 DuckDB

各平台安装与快速上手，5 分钟跑通第一个查询

DuckDB 完整指南

从数据模型到高级特性，一篇读懂 DuckDB

DuckDB 做 AI Agent 大脑

用 SQL 驱动 LLM Agent 的完整方案

← 返回文章列表
下一篇 DuckDB 替代 jq 处理 JSON 日志：告别管道爆炸

📺 Watch video tutorials → OLAP Studio YouTube

Subscribe for more DuckDB & AI automation tutorials