友好 SQL

Goose 提供了多项高级 SQL 特性和语法糖，让 SQL 查询更简洁。我们通常将这些能力称为“friendly SQL”。

这些特性中有一些最早由 Goose 引入，另一些则受到其他系统的启发。 Goose 最初引入的许多特性（例如 GROUP BY ALL）后来也被其他系统采用。

子句

• 创建表与插入数据：
  • CREATE OR REPLACE TABLE：避免在脚本中编写 DROP TABLE IF EXISTS 语句。
  • CREATE TABLE ... AS SELECT (CTAS)：无需手动定义 schema，即可基于查询输出创建新表。
  • INSERT INTO ... BY NAME：INSERT 的这一变体允许按列名而非位置插入。
  • INSERT OR IGNORE INTO ...：插入不会因 UNIQUE 或 PRIMARY KEY 约束冲突而失败的行。
  • INSERT OR REPLACE INTO ...：插入不会产生 UNIQUE 或 PRIMARY KEY 冲突的行；对于冲突行，则用待插入行的新值替换现有行对应列。
• 描述表与计算统计信息：
  • DESCRIBE：提供表或查询 schema 的简明摘要。
  • SUMMARIZE：返回表或查询的汇总统计信息。
• 让 SQL 子句更紧凑、更易读：
  • 带可选 SELECT 子句的 FROM-first 语法：Goose 支持 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，提升可读性。
  • 为 LIMIT 子句指定表大小百分比：写成 LIMIT 10% 可返回查询结果的 10%。
• 表转换：
  • PIVOT 将长表转换为宽表。
  • UNPIVOT 将宽表转换为长表。
• 定义 SQL 层变量：
  • SET VARIABLE
  • RESET VARIABLE

查询特性

• WHERE、GROUP BY 和 HAVING 中的列别名。（注意：列别名不能在 JOIN 子句 的 ON 子句中使用。）
• COLUMNS() 表达式 可用于在多列上执行同一表达式：
  • 配合正则表达式
  • 配合 EXCLUDE 与 REPLACE
  • 配合 lambda 函数
• 可复用列别名（也称“lateral column aliases”），例如：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 data type
• UNION data type

数据导入

• 自动检测 CSV 文件的表头与 schema
• 直接查询 CSV 文件 与 Parquet 文件
• Replacement scans：
  • 你可以使用 FROM 'my.csv'、FROM 'my.csv.gz'、FROM 'my.parquet' 等语法直接从文件加载。
  • 在 Python 中，你可以通过 FROM df 访问 Pandas DataFrame。
• 文件名展开（globbing），例如：FROM 'my-data/part-*.parquet'

函数与表达式

• 用于函数链式调用的点运算符：SELECT ('hello').upper()
• 字符串格式化： 使用带 fmt 语法的 format() 函数和 printf() 函数
• 列表推导式
• 列表切片与反向索引（[-1]）
• 字符串切片
• STRUCT.* 记法
• 使用方括号创建 LIST
• 简化的 LIST 与 STRUCT 创建方式
• 更新 STRUCT 的 schema

Join 类型

• ASOF joins
• LATERAL joins
• POSITIONAL joins

尾随逗号

Goose 允许使用尾随逗号， 无论是在枚举实体（例如列名与表名）时，还是在构造 LIST 项时都可以使用。 例如，下列查询可以正常执行：

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

“组内 Top-N” 查询

在 SQL 中，按某个标准计算“组内 top-N 行”是常见需求，但通常需要包含窗口函数和/或子查询的复杂语句。

为此，Goose 提供了聚合函数 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)，可基于某一列的升序或降序，高效返回每组中“top” n 行。

例如，使用下表：

SELECT * FROM t1;

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

我们希望得到每个 grp 组中 top-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       │
└───────────────────┴─────────┘

但在 Goose 中，可以更简洁（也更高效）地完成：

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

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

相关博客文章

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