让 SQL 更友好，与 DuckDB 同行

Alex Monahan

2022-05-04 · 11 分钟

优雅的用户体验是 DuckDB 的一个关键设计目标。这一目标指导了 DuckDB 的大部分架构：它易于安装，可与 Pandas、Arrow 和 R Dataframes 等其他数据结构无缝集成，并且无需任何依赖项。并行化自动进行，如果计算超出可用内存，数据将优雅地缓冲到磁盘。当然，DuckDB 的处理速度也使其更容易完成更多工作。

然而，SQL 并不以其用户友好性而闻名。DuckDB 旨在改变这一点！DuckDB 既包含用于数据框式计算的关系型 API，也包含高度兼容 Postgres 的 SQL 版本。如果您偏好数据框式计算，我们期待您对我们的路线图提出反馈。如果您是 SQL 爱好者，请继续阅读，了解 DuckDB 如何将创新与实用主义相结合，让在 DuckDB 中编写 SQL 比其他任何地方都更容易。请通过GitHub或Discord联系我们，告诉我们还有哪些功能可以简化您的 SQL 工作流程。加入我们，一同赋予旧事物新功能吧！

SELECT * EXCLUDE

传统的 SQL SELECT 查询要求明确指定所需的列，但有一个显著的例外：* 通配符。SELECT * 允许 SQL 返回所有相关列。这极大地增加了灵活性，尤其是在层层构建查询时。然而，我们通常对几乎所有列都感兴趣。在 DuckDB 中，只需指定要 EXCLUDE（排除）的列即可。

SELECT * EXCLUDE (jar_jar_binks, midichlorians) FROM star_wars;

现在，我们可以节省重复输入所有列的时间，提高代码可读性，并在底层表添加新列时保持灵活性。

DuckDB 对这一概念的实现甚至可以在单个语句中处理来自多个表的排除。

SELECT 
    sw.* EXCLUDE (jar_jar_binks, midichlorians),
    ff.* EXCLUDE cancellation
FROM star_wars sw, firefly ff;

SELECT * REPLACE

同样，我们经常希望使用表中的所有列，除了进行一些小调整。这也会阻止使用 *，并且需要列出所有列，包括那些未编辑的列。在 DuckDB 中，使用 REPLACE 可以轻松地对少量列应用更改。

SELECT 
    * REPLACE (movie_count+3 AS movie_count, show_count*1000 AS show_count)
FROM star_wars_owned_by_disney;

这使得视图、CTE 或子查询能够以高度简洁的方式相互构建，同时又能适应新的底层列。

GROUP BY ALL

重复冗长的 SQL 代码的一个常见原因是需要在 SELECT 子句和 GROUP BY 子句中都指定列。理论上这增加了 SQL 的灵活性，但实际上它很少增加价值。DuckDB 现在提供了我们初学 SQL 时都期待的 GROUP BY——只需对 SELECT 子句中所有未包含在聚合函数中的列进行 GROUP BY ALL！

SELECT
    systems,
    planets,
    cities,
    cantinas,
    sum(scum + villainy) AS total_scum_and_villainy
FROM star_wars_locations
GROUP BY ALL;
-- GROUP BY systems, planets, cities, cantinas

现在，对查询的更改只需在一个地方进行，而不是两个地方！此外，这还能防止许多错误，例如从 SELECT 列表中删除列，但未从 GROUP BY 中删除，从而导致重复。

这不仅极大地简化了许多查询，还使得上述 EXCLUDE 和 REPLACE 子句在更多情况下变得有用。想象一下，如果我们想调整上述查询，不再考虑每个特定小酒馆（cantina）的卑鄙与邪恶程度：

SELECT
    * EXCLUDE (cantinas, booths, scum, villainy),
    sum(scum + villainy) AS total_scum_and_villainy
FROM star_wars_locations
GROUP BY ALL;
-- GROUP BY systems, planets, cities

这就是一些简洁灵活的 SQL！您的多少 GROUP BY 子句可以用这种方式重写呢？

ORDER BY ALL

SQL 中重复的另一个常见原因是 ORDER BY 子句。DuckDB 和其他 RDBMS 之前通过允许查询指定要 ORDER BY 的列号（例如，ORDER BY 1, 2, 3）来解决这个问题。然而，通常的目标是从左到右按查询中的所有列排序，并且在添加或删除列时维护该数字列表容易出错。在 DuckDB 中，只需 ORDER BY ALL 即可。

SELECT
    age,
    sum(civility) AS total_civility
FROM star_wars_universe
GROUP BY ALL
ORDER BY ALL;
-- ORDER BY age, total_civility

这在构建摘要时特别有用，因为许多其他客户端工具都会以这种方式自动排序结果。DuckDB 还支持 ORDER BY ALL DESC 以反向顺序对每列进行排序，并支持指定 NULLS FIRST 或 NULLS LAST 的选项。

WHERE / GROUP BY / HAVING 中的列别名

在许多 SQL 方言中，SELECT 子句中定义的别名除了在该语句的 ORDER BY 子句中使用外，不能在其他任何地方使用。这通常导致冗长的 CTE 或子查询才能利用这些别名。在 DuckDB 中，SELECT 子句中的非聚合别名可以直接在 WHERE 和 GROUP BY 子句中使用，而聚合别名可以在 HAVING 子句中使用，即使在相同的查询深度也是如此。无需子查询！

SELECT
    only_imperial_storm_troopers_are_so_precise AS nope,
    turns_out_a_parsec_is_a_distance AS very_speedy,
    sum(mistakes) AS total_oops
FROM oops
WHERE
    nope = 1
GROUP BY
    nope,
    very_speedy
HAVING
    total_oops > 0;

大小写不敏感，同时保留大小写

DuckDB 允许查询对大小写不敏感，同时在数据流入和流出系统时保持指定的大小写。这简化了 DuckDB 内部的查询，同时确保了与外部库的兼容性。

CREATE TABLE mandalorian AS SELECT 1 AS "THIS_IS_THE_WAY";
SELECT this_is_the_way FROM mandalorian;

友好的错误消息

无论专业水平如何，尽管 DuckDB 尽力理解我们的意图，我们都会在 SQL 查询中犯错。许多 RDBMS 会让你尝试使用原力来检测错误。在 DuckDB 中，如果您在列名或表名上出现拼写错误，您将收到关于最相似名称的有用建议。不仅如此，您还会收到一个箭头，直接指向查询中出错的位置。

SELECT * FROM star_trek;

Error: Catalog Error: Table with name star_trek does not exist!
Did you mean "star_wars"?
LINE 1: SELECT * FROM star_trek;
                      ^

（别担心，鸭子和以鸭子为主题的数据库也同样喜欢《星际迷航》）。

DuckDB 的建议甚至具有上下文相关性。在这里，我们收到一个建议，使用我们正在查询的表中与最相似的列。

SELECT long_ago FROM star_wars;

Error: Binder Error: Referenced column "long_ago" not found in FROM clause!
Candidate bindings: "star_wars.long_long_ago"
LINE 1: SELECT long_ago FROM star_wars;
               ^

字符串切片

即使作为 SQL 爱好者，我们也知道 SQL 可以从更新的语言中学到一些东西。在 DuckDB 中，您可以使用括号语法来切片字符串，而不是使用笨重的 SUBSTRING 函数。请注意，SQL 要求是 1-indexed（从 1 开始索引），这与其他语言略有不同（尽管它使 DuckDB 内部保持一致并与其他数据库相似）。

SELECT 'I love you! I know'[:-3] AS nearly_soloed;

简单列表和结构体创建

DuckDB 提供嵌套类型，以允许比纯关系模型更灵活的数据结构，同时保持高性能。为了使其尽可能易于使用，创建 LIST（数组）或 STRUCT（对象）的语法比其他 SQL 系统更简单。数据类型会自动推断。

SELECT
    ['A-Wing', 'B-Wing', 'X-Wing', 'Y-Wing'] AS starfighter_list,
    {name: 'Star Destroyer', common_misconceptions: 'Can''t in fact destroy a star'} AS star_destroyer_facts;

列表切片

括号语法也可用于切片 LIST。同样请注意，为了 SQL 兼容性，这也是 1-indexed（从 1 开始索引）的。

SELECT 
    starfighter_list[2:2] AS dont_forget_the_b_wing 
FROM (SELECT ['A-Wing', 'B-Wing', 'X-Wing', 'Y-Wing'] AS starfighter_list);

结构体（Struct）点表示法

使用方便的点表示法来访问 DuckDB STRUCT 列中特定键的值。如果键包含空格，可以使用双引号。

SELECT 
    planet.name,
    planet."Amount of sand" 
FROM (SELECT {name: 'Tatooine', 'Amount of sand': 'High'} AS planet);

尾随逗号

您是否曾从 SQL SELECT 中删除了最后一列，却遇到了错误，才发现您还需要删除尾随逗号？！从未有过？好吧，您真是绝地武士……更严肃地说，这个功能是 DuckDB 对社区响应能力的体现。在一条推特上看到这个问题（甚至不是关于 DuckDB 的！）后不到 2 天，这个功能就已经被构建、测试并合并到主分支中。您可以在查询的许多地方包含尾随逗号，我们希望这能将您从最无聊但最令人沮丧的错误中解救出来！

SELECT
    x_wing,
    proton_torpedoes,
    --targeting_computer
FROM luke_whats_wrong
GROUP BY
    x_wing,
    proton_torpedoes,
;

其他数据库的函数别名

对于许多函数，DuckDB 支持多个名称，以便与其他数据库系统保持一致。毕竟，鸭子非常多才多艺——它们会飞、会游泳、会走路！最常见的是，DuckDB 支持 PostgreSQL 函数名称，但也支持许多 SQLite 名称以及其他一些系统的名称。如果您正在将工作负载迁移到 DuckDB，并且不同的函数名称会有所帮助，请与我们联系——只要行为相同，添加它们就非常容易！有关详细信息，请参阅我们的函数文档。

SELECT
    'Use the Force, Luke'[:13] AS sliced_quote_1,
    substr('I am your father', 1, 4) AS sliced_quote_2,
    substring('Obi-Wan Kenobi, you''re my only hope', 17, 100) AS sliced_quote_3;

自动递增重复列名

在构建连接相似表的查询时，您经常会遇到重复的列名。如果查询是最终结果，DuckDB 将直接返回重复的列名而不进行修改。然而，如果查询用于创建表，或者嵌套在子查询或公共表表达式（其他数据库禁止重复列！）中，DuckDB 将自动为重复的列分配新名称，以使查询原型设计更加容易。

SELECT
    *
FROM (
    SELECT
        s1.tie_fighter,
        s2.tie_fighter
    FROM squadron_one s1
    CROSS JOIN squadron_two s2
    ) theyre_coming_in_too_fast;

隐式类型转换

DuckDB 坚信使用特定的数据类型以获得最佳性能，但会在必要时尝试在类型之间自动转换。例如，当在整数和 varchar 之间进行连接时，DuckDB 将自动将它们转换为相同类型并成功完成连接。List 或 IN 表达式也可以使用混合类型创建，并且它们也会自动转换。此外，INTEGER 和 BIGINT 可以互换，并且由于 DuckDB 新的存储压缩，BIGINT 通常甚至不会占用任何额外空间！现在您可以将数据存储为最佳数据类型，同时轻松使用以获得两全其美的效果！

CREATE TABLE sith_count_int AS SELECT 2::INTEGER AS sith_count;
CREATE TABLE sith_count_varchar AS SELECT 2::VARCHAR AS sith_count;

SELECT
    * 
FROM sith_count_int s_int 
JOIN sith_count_varchar s_char 
  ON s_int.sith_count = s_char.sith_count;

其他友好功能

DuckDB 还有许多其他功能，让使用 SQL 分析数据变得更加容易！

DuckDB 在许多方面简化了时间处理，包括接受多种不同的语法（来自其他数据库）来表示用于指定时间长度的 INTERVAL 数据类型。

DuckDB 还实现了传统核心子句之外的多个 SQL 子句，包括用于快速选择数据随机子集的 SAMPLE 子句，以及允许过滤窗口函数结果的 QUALIFY 子句（很像 HAVING 子句对聚合函数的作用）。

DISTINCT ON 子句允许 DuckDB 选择 SELECT 子句中部分列的唯一组合，同时返回未检查唯一性的列的第一行数据。

未来展望

除了已经实现的功能，还提出了一些其他改进建议。如果其中有您认为特别有用的，请告诉我们——我们的路线图是灵活的！如果您想贡献，我们非常欢迎拉取请求（PRs），并且欢迎您提前通过GitHub或Discord联系我们，讨论新功能的设计。

• 通过正则表达式选择列
  • 使用模式而非显式指定列来决定选择哪些列
  • ClickHouse 通过 COLUMNS 表达式支持此功能
• 增量列别名
  • 在后续的计算列中引用先前定义的别名，而不是重新指定计算
• JSON 类型的点运算符
  • JSON 扩展是全新的（请参阅我们的文档！），并且已经实现了友好的 -> 和 ->> 语法

感谢您关注 DuckDB！愿原力与你同在……