DuckDB：星号（*）表达式完整指南

本文介绍DuckDB星号的用法，尤其是与其他数据库不同的用法，掌握它可以快速帮你选择和转换列。

常规用法

首先我们介绍星号表达式的常规用法，这与其他数据库功能一样。

# 选择from子句中所有列
SELECT * FROM table_name;

# 计算表中的行数, DuckDB支持省略*号
SELECT count(*) FROM table_name;
SELECT count() FROM table_name;

# 查询表的所有列
SELECT table_name.*
FROM table_name
JOIN other_table_name USING (id);

下面继续学习DuckDB提供的，其他数据库不常见的功能。

列表达式

EXCLUDE 子句

EXCLUDE允许我们从*表达式中排除特定的列，实现反向选择：

SELECT * EXCLUDE (col)
FROM tbl;

REPLACE子句

REPLACE允许我们替换表达式指定的列中的特定值。

SELECT * REPLACE (col / 1_000 AS col)
FROM tbl;

COLUMNS表达式

COLUMNS表达式可用于对多个列中的值执行相同的表达式。例如:

CREATE TABLE numbers (id INTEGER, number INTEGER);
INSERT INTO numbers VALUES (1, 10), (2, 20), (3, NULL);
SELECT min(COLUMNS(*)), count(COLUMNS(*)) FROM numbers;

返回结果：

┌───────┬────────┬───────┬────────┐
│  id   │ number │  id   │ number │
│ int32 │ int32  │ int64 │ int64  │
├───────┼────────┼───────┼────────┤
│     1 │     10 │     3 │      2 │
└───────┴────────┴───────┴────────┘

COLUMNS语句中的*表达式也可以包含EXCLUDE或REPLACE，类似于正则星型表达式。

SELECT
    min(COLUMNS(* REPLACE (number + id AS number))),
    count(COLUMNS(* EXCLUDE (number)))
FROM numbers;

输出结果：

┌───────┬──────────────────────────────┬───────┐
│  id   │ min(number := (number + id)) │  id   │
│ int32 │            int32             │ int64 │
├───────┼──────────────────────────────┼───────┤
│     1 │                           11 │     3 │
└───────┴──────────────────────────────┴───────┘

COLUMNS表达式也可以组合，只要COLUMNS包含相同的（星号）表达式：

SELECT COLUMNS(*) + COLUMNS(*) FROM numbers;

返回结果：

┌───────┬────────┐
│  id   │ number │
│ int32 │ int32  │
├───────┼────────┤
│     2 │     20 │
│     4 │     40 │
│     6 │        │
└───────┴────────┘

COLUMNS表达式也可以用在WHERE子句中。条件应用于所有列，并使用逻辑与运算符进行组合。

SELECT *
FROM (
    SELECT 0 AS x, 1 AS y, 2 AS z
    UNION ALL
    SELECT 1 AS x, 2 AS y, 3 AS z
    UNION ALL
    SELECT 2 AS x, 3 AS y, 4 AS z
)
WHERE COLUMNS(*) > 1; -- equivalent to: x > 1 AND y > 1 AND z > 1

返回结果：

┌───────┬───────┬───────┐
│   x   │   y   │   z   │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│     2 │     3 │     4 │
└───────┴───────┴───────┘

列正则表达式

COLUMNS支持将正则表达式作为字符串常量传入：

SELECT COLUMNS('(id|numbers?)') FROM numbers;

返回结果：

┌───────┬────────┐
│  id   │ number │
│ int32 │ int32  │
├───────┼────────┤
│     1 │     10 │
│     2 │     20 │
│     3 │        │
└───────┴────────┘

• 结合columns表达式示例

## 查询匹配给定字符串列
SELECT COLUMNS(c -> c LIKE '%num%')
FROM addresses;

## 查询number后连接数字列
SELECT COLUMNS('number\d+')
FROM addresses;

## 通过列表指定
SELECT COLUMNS(['city', 'zip_code'])
FROM addresses;

• 使用columns表达式重命名列

捕获组的匹配可用于重命名正则表达式选择的列。捕获组是单索引的；\0是原来的列名。

# 例如，要选择列名的前三个字母：
SELECT COLUMNS('(\w{3}).*') AS '\1' FROM numbers;

# 要删除列名中间的冒号（:）字符：
CREATE TABLE tbl ("Foo:Bar" INTEGER, "Foo:Baz" INTEGER, "Foo:Qux" INTEGER);
SELECT COLUMNS('(\w*):(\w*)') AS '\1\2' FROM tbl;

列lamda表达式

COLUMNS还支持传入lambda函数。将对from子句中出现的所有列求值lambda函数，并且只返回与lambda函数匹配的列。这允许执行任意表达式来选择和重命名列。

SELECT COLUMNS(c -> c LIKE '%num%') FROM numbers;

*COLUMNS展开列

*COLUMNS子句是COLUMNS的一个变体，它支持前面提到的所有功能。不同之处在于表达方式的扩展。*COLUMNS将就地展开，这意味着表达式扩展为父表达式。

下面的例子显示了COLUMNS和*COLUMNS之间的区别：

SELECT coalesce(COLUMNS(['a', 'b', 'c'])) AS result
FROM (SELECT NULL a, 42 b, true c);

返回结果：

┌────────┬────────┬─────────┐
│ result │ result │ result  │
│ int32  │ int32  │ boolean │
├────────┼────────┼─────────┤
│        │     42 │ true    │
└────────┴────────┴─────────┘

下面代码中通过*column展开值，coalesce函数返回第一个非null值。

SELECT coalesce(*COLUMNS(['a', 'b', 'c'])) AS result FROM (SELECT NULL AS a, 42 AS b, true AS c);

输出结果：

┌────────┐
│ result │
│ int32  │
├────────┤
│     42 │
└────────┘

STRUCT.*选择结构体

表达式还可以用于从结构体中检索作为单独列的所有键。当先前的操作创建了形状未知的结构体，或者查询必须处理任何潜在的结构体键时，这一点特别有用。

SELECT st.* FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS st);

输出结果：

┌───────┬───────┬───────┐
│   x   │   y   │   z   │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│     1 │     2 │     3 │
└───────┴───────┴───────┘