DuckDB 提供了一些高级 SQL 功能和语法糖，使 SQL 查询更加简洁。我们通俗地将其称为“友好 SQL”。

其中一些功能由 DuckDB 首先引入，而另一些则受到其他系统的启发。许多最初由 DuckDB 引入的功能（例如 GROUP BY ALL）后来已被其他系统采用。

子句

• 创建表和插入数据
  • 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 ...：插入不会产生冲突的行。对于产生冲突的行，将现有行的列替换为待插入行的新值。
• 描述表和计算统计信息
  • DESCRIBE：提供表或查询架构的简洁摘要。
  • SUMMARIZE：返回表或查询的汇总统计信息。
• 使 SQL 子句更紧凑、更易读
  • FROM-first 语法（带有可选的 SELECT 子句）：DuckDB 允许使用 FROM tbl 形式的查询，这将选择所有列（执行 SELECT * 语句）。
  • GROUP BY ALL：省略 group-by 列，通过从 SELECT 子句中的属性列表推断得出。
  • ORDER BY ALL：按所有列排序的简写（例如，确保结果确定性）。
  • SELECT * EXCLUDE：EXCLUDE 选项允许从 * 表达式中排除特定列。
  • SELECT * REPLACE：REPLACE 选项允许在 * 表达式中用不同的表达式替换特定列。
  • UNION BY NAME：沿列名（而不是依赖位置）执行 UNION 操作。
  • SELECT 和 FROM 子句中的前缀别名：书写 x: 42 而非 42 AS x，以提高可读性。
  • 为 LIMIT 子句指定表大小的百分比：书写 LIMIT 10% 可返回查询结果的 10%。
• 转换表
  • PIVOT 将长表转为宽表。
  • UNPIVOT 将宽表转为长表。
• 定义 SQL 级变量
  • SET VARIABLE
  • RESET VARIABLE

查询功能

• WHERE、GROUP BY 和 HAVING 中的列别名。（请注意，列别名不能在 JOIN 子句的 ON 子句中使用。）
• COLUMNS() 表达式可用于在多列上执行相同的表达式
  • 支持正则表达式
  • 支持 EXCLUDE 和 REPLACE
  • 支持 Lambda 函数
• 可重用列别名（也称为“横向列别名”），例如：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

字面量与标识符

• 在保持目录中实体大小写的同时实现不区分大小写
• 标识符去重
• 数值字面量中的下划线作为数字分隔符

数据类型

• MAP 数据类型
• UNION 数据类型

数据导入

• 自动检测 CSV 文件的标题和架构
• 直接查询 CSV 文件和 Parquet 文件
• 替换扫描 (Replacement scans):
  • 您可以使用语法 FROM 'my.csv', FROM 'my.csv.gz', FROM 'my.parquet' 等从文件中加载数据。
  • 在 Python 中，您可以使用 FROM df 访问 Pandas 数据帧。
• 文件名扩展 (globbing)，例如：FROM 'my-data/part-*.parquet'

函数与表达式

• 用于函数链的点运算符：SELECT ('hello').upper()
• 字符串格式化：带有 fmt 语法的 format() 函数以及 printf() 函数
• 列表推导式
• 列表切片和从后索引 ([-1])
• 字符串切片
• STRUCT.* 标记
• 使用方括号创建 LIST
• 简单的 LIST 和 STRUCT 创建
• 更新 STRUCT 的架构

连接类型

• ASOF 连接
• LATERAL 连接
• POSITIONAL 连接

尾随逗号

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

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

“组内 Top-N”查询

计算按某种标准排序的“组内前 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 中 val 的前 3 大值。传统方法是在子查询中使用窗口函数

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]   │
└─────────────┘

相关博客文章

• “Friendlier SQL with DuckDB” 博客文章
• “Even Friendlier SQL with DuckDB” 博客文章
• “SQL Gymnastics: Bending SQL into Flexible New Shapes” 博客文章