友好型SQL – DuckDB 数据库

搜索快捷键 cmd + k | ctrl + k

文档 / SQL / DuckDB 的 SQL 方言

友好的 SQL

DuckDB 提供了一些高级 SQL 特性和语法糖，使 SQL 查询更简洁。我们通俗地称之为“友好型 SQL”。

其中一些特性也受其他系统支持，而有些（目前）是 DuckDB 独有的。

子句

创建表和插入数据
- CREATE OR REPLACE TABLE：避免脚本中出现 DROP TABLE IF EXISTS 语句。
- CREATE TABLE ... AS SELECT (CTAS)：从表的输出创建新表，而无需手动定义模式。
- INSERT INTO ... BY NAME：此 INSERT 语句的变体允许使用列名而非位置。
- INSERT OR IGNORE INTO ...：插入不会因 UNIQUE 或 PRIMARY KEY 约束而导致冲突的行。
- INSERT OR REPLACE INTO ...：插入不会因 UNIQUE 或 PRIMARY KEY 约束而导致冲突的行。对于发生冲突的行，将现有行的列替换为待插入行的新值。
描述表和计算统计信息
- DESCRIBE：提供表或查询模式的简洁摘要。
- SUMMARIZE：返回表或查询的摘要统计信息。
使 SQL 子句更紧凑和可读
- 以 FROM 为先的语法，带可选的 SELECT 子句：DuckDB 允许使用 FROM tbl 形式的查询，它会选择所有列（执行 SELECT * 语句）。
- GROUP BY ALL：通过从 SELECT 子句中的属性列表推断，省略分组列。
- ORDER BY ALL：对所有列进行排序的简写（例如，确保确定性结果）。
- SELECT * EXCLUDE：EXCLUDE 选项允许从 * 表达式中排除特定列。
- SELECT * REPLACE：REPLACE 选项允许在 * 表达式中用不同的表达式替换特定列。
- UNION BY NAME：根据列名执行 UNION 操作（而不是依赖于位置）。
- 在 SELECT 和 FROM 子句中使用前缀别名：写 x: 42 而不是 42 AS x 以提高可读性。
转换表
- PIVOT 将长表转换为宽表。
- UNPIVOT 将宽表转换为长表。
定义 SQL 级变量
- SET VARIABLE
- RESET VARIABLE

查询特性

在 WHERE、GROUP BY 和 HAVING 中使用列别名。（请注意，列别名不能用于 JOIN 子句的 ON 子句中。）
COLUMNS() 表达式可用于在多列上执行相同的表达式
可重用列别名（也称为“侧向列别名”），例如：SELECT i + 1 AS j, j + 2 AS k FROM range(0, 3) t(i)
用于分析 (OLAP) 查询的高级聚合功能
- FILTER 子句
- GROUPING SETS、GROUP BY CUBE、GROUP BY ROLLUP 子句
count() 简写，等同于 count(*)
用于列表和映射的 IN 运算符
为公共表表达式 (WITH) 指定列名
在 JOIN 子句中指定列名
在 JOIN 子句中使用 VALUES
在公共表表达式的锚点部分使用 VALUES

字面量和标识符

数据类型

数据导入

自动检测 CSV 文件的头部和模式
直接查询 CSV 文件和 Parquet 文件
替换扫描:
- 您可以使用 FROM 'my.csv'、FROM 'my.csv.gz'、FROM 'my.parquet' 等语法从文件加载。
- 在 Python 中，您可以使用 FROM df 访问 Pandas 数据帧。
文件名展开（通配），例如：FROM 'my-data/part-*.parquet'

函数和表达式

用于函数链式调用的点运算符：SELECT ('hello').upper()
字符串格式化器：使用 fmt 语法的 format() 函数和 printf() 函数
列表推导
列表切片和从末尾索引 ([-1])
字符串切片
STRUCT.* 符号
使用方括号创建 LIST
简单的 LIST 和 STRUCT 创建
更新 STRUCT 的模式

连接类型

尾随逗号

DuckDB 允许使用尾随逗号，无论是在列出实体（例如列名和表名）时，还是在构造LIST 项时。例如，以下查询有效

SELECT
    42 AS x,
    ['a', 'b', 'c',] AS y,
    'hello world' AS z,
;

“组内 Top-N”查询

计算按某些条件排序的“组内 Top-N 行”是 SQL 中常见的任务，但遗憾的是，它通常需要涉及窗口函数和/或子查询的复杂查询。

为帮助解决此问题，DuckDB 提供了聚合函数 max(arg, n)、min(arg, n)、arg_max(arg, val, n)、arg_min(arg, val, n)、max_by(arg, val, n) 和 min_by(arg, val, n)，可有效根据特定列按升序或降序返回组中的“顶部” n 行。

例如，我们使用以下表格

SELECT * FROM t1;

┌─────────┬───────┐
│   grp   │  val  │
│ varchar │ int32 │
├─────────┼───────┤
│ a       │     2 │
│ a       │     1 │
│ b       │     5 │
│ b       │     4 │
│ a       │     3 │
│ b       │     6 │
└─────────┴───────┘

我们希望获取每个 grp 组中前 3 个 val 值的列表。传统的方法是在子查询中使用窗口函数

SELECT array_agg(rs.val), rs.grp
FROM
    (SELECT val, grp, row_number() OVER (PARTITION BY grp ORDER BY val DESC) AS rid
    FROM t1 ORDER BY val DESC) AS rs
WHERE rid < 4
GROUP BY rs.grp;

┌───────────────────┬─────────┐
│ array_agg(rs.val) │   grp   │
│      int32[]      │ varchar │
├───────────────────┼─────────┤
│ [3, 2, 1]         │ a       │
│ [6, 5, 4]         │ b       │
└───────────────────┴─────────┘

但在 DuckDB 中，我们可以更简洁（且高效！）地完成此操作

SELECT max(val, 3) FROM t1 GROUP BY grp;

┌─────────────┐
│ max(val, 3) │
│   int32[]   │
├─────────────┤
│ [3, 2, 1]   │
│ [6, 5, 4]   │
└─────────────┘

子句