使用 DuckDB 让 SQL 更加友好

Alex Monahan

2023-08-23 · 18 分钟

谁说 SQL 应该停滞不前，被束缚在 1999 年的规范版本上？作为比较，大家还记得 JavaScript 在 Promises 之前是怎样的吗？Promises 直到 2012 年才推出！很明显，编程语法层的创新可以对整个语言生态系统产生深远的积极影响。

我们认为 SQL 语言的创新有许多正当理由，其中包括简化基本查询和实现更动态分析的机会。其中许多特性都源于社区的建议！请在 Discord 或 GitHub 上告诉我们您的 SQL 痛点，加入我们，共同改变编写 SQL 的体验！

如果您还没有阅读本系列的第一部分，请快速浏览之前的博文：“使用 DuckDB 让 SQL 更加友好”。

未来已来

此列表中的前几项增强功能已包含在之前文章的“未来构想”部分。

可重用列别名

在 SELECT 语句中使用增量计算表达式时，传统的 SQL 方言迫使您要么为每个列写出完整的表达式，要么在计算的每个步骤周围创建一个公共表表达式 (CTE)。现在，任何列别名都可以被同一 SELECT 语句中的后续列重用。不仅如此，这些别名也可以在 WHERE 和 ORDER BY 子句中使用。

旧方法 1：重复自己

SELECT 
    'These are the voyages of the starship Enterprise...' AS intro,
    instr('These are the voyages of the starship Enterprise...', 'starship')
        AS starship_loc
    substr('These are the voyages of the starship Enterprise...',
    instr('These are the voyages of the starship Enterprise...', 'starship')
        + len('starship') + 1) AS trimmed_intro;

旧方法 2：各种 CTE

WITH intro_cte AS (
    SELECT
        'These are the voyages of the starship Enterprise...' AS intro
), starship_loc_cte AS (
    SELECT
        intro,
        instr(intro, 'starship') AS starship_loc
    FROM intro_cte
)
SELECT
    intro,
    starship_loc,
    substr(intro, starship_loc + len('starship') + 1) AS trimmed_intro
FROM starship_loc_cte;

新方法

SELECT 
     'These are the voyages of the starship Enterprise...' AS intro,
     instr(intro, 'starship') AS starship_loc,
     substr(intro, starship_loc + len('starship') + 1) AS trimmed_intro;

动态列选择

数据库通常偏好列定义的严格性以及行数的灵活性。这有助于强制执行数据类型并记录列级别元数据。然而，在数据科学工作流和其他地方，动态生成列（例如在特征工程期间）是非常常见的。

您不再需要预先知道所有列名！DuckDB 可以根据正则表达式模式匹配、EXCLUDE 或 REPLACE 修饰符，甚至 lambda 函数来选择和修改列（有关详细信息，请参阅下面的 lambda 函数部分！）

让我们看看关于《星际迷航》第一季的一些事实。使用 DuckDB 的 httpfs 扩展，我们可以直接从 GitHub 查询 CSV 数据集。它有几列，所以我们来 DESCRIBE 它。

INSTALL httpfs;
LOAD httpfs;

CREATE TABLE trek_facts AS
    SELECT *
    FROM 'https://blobs.duckdb.org/data/Star_Trek-Season_1.csv';

DESCRIBE trek_facts;

COLUMNS() 与正则表达式

COLUMNS 表达式可以接受一个作为正则表达式的字符串参数，并将返回所有匹配该模式的列名。第一季中曲速如何变化？让我们检查任何包含 warp 字样的列名。

SELECT
    episode_num,
    COLUMNS('.*warp.*')
FROM trek_facts;

COLUMNS 表达式也可以被其他函数包装，以将这些函数应用于每个选定的列。让我们简化上面的查询，以查看所有剧集中的最大值。

SELECT
    max(COLUMNS('.*warp.*'))
FROM trek_facts;

我们还可以创建一个适用于多列的 WHERE 子句。所有列都必须匹配过滤条件，这等同于使用 AND 将它们组合起来。哪些剧集至少有 2 个曲速指令，且曲速级别至少为 2？

SELECT
    episode_num,
    COLUMNS('.*warp.*')
FROM trek_facts
WHERE
    COLUMNS('.*warp.*') >= 2;
    -- cnt_warp_speed_orders >= 2 
    -- AND 
    -- highest_warp_speed_issued >= 2

COLUMNS() 与 EXCLUDE 和 REPLACE

在对其应用计算之前，单个列也可以被排除或替换。例如，由于我们的数据集只包含第 1 季，我们不需要查找该列的 max 值。那将是非常不合逻辑的。

SELECT
    max(COLUMNS(* EXCLUDE season_num))
FROM trek_facts;

当应用于动态列集时，REPLACE 语法也很有用。在此示例中，我们希望在查找每列的最大值之前将日期转换为时间戳。以前，这需要一个完整的子查询或 CTE 来预处理单个列！

SELECT
    max(COLUMNS(* REPLACE aired_date::timestamp AS aired_date))
FROM trek_facts;

COLUMNS() 与 Lambda 函数

查询动态列集最灵活的方式是通过 lambda 函数。这允许将任何匹配标准应用于列名，而不仅仅是正则表达式。有关 lambda 函数的更多详细信息，请参见下文。

例如，如果使用 LIKE 语法更方便，我们可以选择匹配 LIKE 模式的列，而不是使用正则表达式。

SELECT
    episode_num,
    COLUMNS(col -> col LIKE '%warp%')
FROM trek_facts
WHERE
    COLUMNS(col -> col LIKE '%warp%') >= 2;

JSON 自动转换为嵌套类型

本系列的第一部分提到了 JSON 点表示法引用作为未来的工作。然而，团队走得更远！现在，JSON 可以自动解析为 DuckDB 的原生类型，从而显著提高性能、压缩效率，以及友好的点表示法，而无需使用点表示法引用 JSON 类型列！

首先，如果您的客户端没有捆绑 httpfs 和 json 扩展，请安装并加载它们。然后直接查询远程 JSON 文件，就像它是一个表一样！

INSTALL httpfs;
LOAD httpfs;
INSTALL json;
LOAD json;

SELECT 
     starfleet[10].model AS starship 
FROM 'https://raw.githubusercontent.com/vlad-saling/star-trek-ipsum/master/src/content/content.json';

现在，来看看超越之前文章构想的一些新 SQL 功能！

SELECT 语句中的 FROM 优先

在构建查询时，您首先需要知道数据来自何处 (FROM)。那么，为什么它会是 SELECT 语句中的第二个子句呢？不再如此！DuckDB 正在构建 SQL 应该一直以来的样子——将 FROM 子句放在首位！这解决了 SQL 长期以来最受诟病的问题之一，DuckDB 团队在两天内就实现了它。

FROM my_table SELECT my_column;

不仅如此，SELECT 语句可以完全省略，DuckDB 将假定应 SELECT 所有列。现在，查看一个表就像这样简单：

FROM my_table;
-- SELECT * FROM my_table

其他语句，如 COPY，也得到了简化。

COPY (FROM trek_facts) TO 'phaser_filled_facts.parquet';

这除了节省击键次数和保持开发流畅状态外，还有一个额外的好处：当您开始选择要查询的列时，自动补全将具有更多的上下文。帮助 AI 一臂之力！

请注意，此语法是完全可选的，因此您的 SELECT * FROM 键盘快捷键仍然安全，即使它们已经过时了…… 🙂

函数链式调用

许多 SQL 博客建议使用 CTE 而不是子查询。除了其他好处外，它们的可读性更高。操作被分隔成离散的块，可以从上到下按顺序阅读，而不是强迫读者从内到外地理解。

DuckDB 为每个标量函数实现了相同的可解释性提升！使用点操作符将函数链式连接起来，就像在 Python 中一样。链中的前一个表达式用作后续函数的第一个参数。

SELECT 
     ('Make it so')
          .upper()
          .string_split(' ')
          .list_aggr('string_agg','.')
          .concat('.') AS im_not_messing_around_number_one;

现在与旧方法比较一下……

SELECT 
     concat(
          list_aggr(
               string_split(
                    upper('Make it stop'),
               ' '),
          'string_agg','.'),
     '.') AS oof;

按名称联合 (Union by Name)

DuckDB 旨在融合数据库和数据帧的最佳特性。这种新语法受到了 Pandas 中 concat 函数的启发。它不是基于列位置垂直堆叠表，而是按名称匹配列并相应堆叠。只需将 UNION 替换为 UNION BY NAME，或将 UNION ALL 替换为 UNION ALL BY NAME。

例如，我们在《下一代》中不得不添加一些新的外星物种谚语：

CREATE TABLE proverbs AS
     SELECT 
          'Revenge is a dish best served cold' AS klingon_proverb 
     UNION ALL BY NAME 
     SELECT 
          'You will be assimilated' AS borg_proverb,
          'If winning is not important, why keep score?' AS klingon_proverb;

FROM proverbs;

这种方法还有额外的好处。如上所示，不仅可以合并具有不同列顺序的表，还可以合并列数完全不同的表。这对于模式迁移很有帮助，并且对于 DuckDB 的多文件读取功能特别有用。

按名称插入 (Insert by Name)

在 SQL 中，列顺序严格的另一个常见情况是向表中插入数据时。要么列必须完全匹配顺序，要么所有列名都必须在查询中的两个位置重复。

相反，在插入时，在表名后添加关键字 BY NAME。表中的任何列子集都可以按任何顺序插入。

INSERT INTO proverbs BY NAME 
     SELECT 'Resistance is futile' AS borg_proverb;

SELECT * FROM proverbs;

动态 PIVOT 和 UNPIVOT

从历史上看，数据库不适合透视操作。然而，DuckDB 的 PIVOT 和 UNPIVOT 子句可以创建或堆叠动态列名，实现真正灵活的透视功能！除了这种灵活性，DuckDB 还提供了 SQL 标准语法和更友好的简写。

例如，让我们看看地球-罗慕兰战争刚开始时的一些采购预测数据：

CREATE TABLE purchases (item VARCHAR, year INTEGER, count INTEGER);

INSERT INTO purchases
    VALUES ('phasers', 2155, 1035),
           ('phasers', 2156, 25039),
           ('phasers', 2157, 95000),
           ('photon torpedoes', 2155, 255),
           ('photon torpedoes', 2156, 17899),
           ('photon torpedoes', 2157, 87492);

FROM purchases;

如果每年的数据在视觉上更接近，比较我们的相位器需求和光子鱼雷需求会更容易。让我们将其透视成更友好的格式！每年都应该有自己的列（但查询中不需要指定每一年！），我们想汇总总 count，并且我们仍然希望为每个 item 保留一个单独的组（行）。

CREATE TABLE pivoted_purchases AS
     PIVOT purchases 
          ON year 
          USING sum(count) 
          GROUP BY item;

FROM pivoted_purchases;

看起来光子鱼雷打折了……

现在想象一下相反的情况。工程部的斯科蒂一直在视觉分析和手动构建他的采购预测。他喜欢透视过的东西，因为更容易阅读。现在您需要将其重新放回数据库中！这场战争可能会持续一段时间，所以明年您可能需要再做一次。让我们编写一个 UNPIVOT 查询，以返回到可以处理任何年份的原始格式。

COLUMNS 表达式将使用除 item 之外的所有列。堆叠后，包含 pivoted_purchases 中列名的列应重命名为 year，并且这些列中的值代表 count。结果与原始数据集相同。

UNPIVOT pivoted_purchases
     ON COLUMNS(* EXCLUDE item)
     INTO
          NAME year
          VALUE count;

更多示例包含在我们的DuckDB 0.8.0 发布公告中，PIVOT 和 UNPIVOT 文档页面也重点介绍了更复杂的查询。

敬请期待未来的一篇博文，我们将探讨幕后发生的事情！

列表 Lambda 函数

列表 lambda 允许将操作应用于列表中的每个项。这些操作不需要预先定义——它们在查询中即时创建。

在此示例中，lambda 函数与 list_transform 函数结合使用，以缩短每个官方飞船名称。

SELECT 
     (['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031'])
          .list_transform(x -> x.string_split(' ')[1]) AS short_name;

Lambda 也可以用于过滤列表中的项。lambda 返回一个布尔值列表，list_filter 函数使用它来选择特定项。contains 函数使用的是前面描述的函数链式调用。

SELECT 
     (['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031'])
          .list_filter(x -> x.contains('1701')) AS the_original;

列表推导式

如果有一种简单的语法可以同时修改和过滤列表呢？DuckDB 从 Python 的列表推导式方法中汲取灵感，极大地简化了上述示例。列表推导式是语法糖——这些查询在幕后被重写为 lambda 表达式！

在方括号内，首先指定所需的转换，然后指明要迭代的列表，最后包含过滤条件。

SELECT 
     [x.string_split(' ')[1] 
     FOR x IN ['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031'] 
     IF x.contains('1701')] AS ready_to_boldly_go;

展开结构体 (Exploding Struct.*)

DuckDB 中的结构体是一组键/值对。在幕后，结构体以每个键一个单独列的方式存储。因此，将结构体展开成单独的列在计算上很容易，现在在语法上也变得简单了！这是允许 SQL 处理动态列名的又一个示例。

WITH damage_report AS (
     SELECT {'gold_casualties':5, 'blue_casualties':15, 'red_casualties': 10000} AS casualties
) 
FROM damage_report
SELECT 
     casualties.*;

自动结构体创建

DuckDB 提供了一种简单的方法，可以将任何表转换为单列结构体。无需 SELECT 列名，只需 SELECT 表名本身。

WITH officers AS (
     SELECT 'Captain' AS rank, 'Jean-Luc Picard' AS name 
     UNION ALL 
     SELECT 'Lieutenant Commander', 'Data'
) 
FROM officers 
SELECT officers;

联合数据类型 (Union Data Type)

DuckDB 利用强类型提供高性能并强制执行数据质量。然而，DuckDB 也尽可能地宽容，使用诸如隐式转换之类的方法，以避免总是需要在数据类型之间进行转换。

DuckDB 增强灵活性的另一种方式是新的 UNION 数据类型。UNION 数据类型允许单个列包含多种类型的值。这可以被视为“选择加入”SQLite 的灵活数据类型规则（与 SQLite 最近宣布的严格表的方向相反）。

默认情况下，DuckDB 在组合表时会寻求数据类型的公分母。下面的查询结果是 VARCHAR 列：

SELECT 'The Motion Picture' AS movie UNION ALL 
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 'First Contact';

然而，如果使用 UNION 类型，每一行都将保留其原始数据类型。UNION 是使用键值对定义的，其中键是名称，值是数据类型。这还允许将特定数据类型提取为单独的列。

CREATE TABLE movies (
     movie UNION(num INTEGER, name VARCHAR)
);
INSERT INTO movies VALUES
     ('The Motion Picture'), (2), (3), (4), (5), (6), ('First Contact');

FROM movies 
SELECT 
     movie,
     union_tag(movie) AS type,
     movie.name,
     movie.num;

其他友好功能

还有其他几个值得一提的友好功能，有些强大到足以独立成一篇博客文章。

DuckDB 从 Pandas 中的 describe 函数中借鉴了灵感，并实现了 SUMMARIZE 关键字，该关键字将计算数据集中每个列的各种统计信息，以提供快速、高层次的概览。只需在任何表或 SELECT 语句前加上 SUMMARIZE 即可。

请参阅相关子查询博文，了解如何使用相互引用列的子查询。DuckDB 的高级优化器将相关子查询的性能提高了几个数量级，从而使查询能够尽可能自然地表达。曾经因性能原因而被视为反模式的功能现在可以自由使用了！

DuckDB 增加了更多 JOIN 表的方式，使常见计算的表达变得更加容易。其中一些，如 LATERAL、ASOF、SEMI 和 ANTI 连接，存在于其他系统中，但在 DuckDB 中有高性能的实现。DuckDB 还添加了新的 POSITIONAL 连接，它通过匹配每个表中的行号来组合，以匹配 Pandas 中常用的基于行号索引的连接功能。有关详细信息，请参阅 JOIN 文档，并留意一篇描述 DuckDB 最先进的 ASOF 连接的博客文章！

总结与未来工作

DuckDB 旨在成为最易用的数据库。作为进程内数据库、零依赖以及强类型等基本架构决策都有助于实现这一目标，但其 SQL 方言的友好性也产生了强大影响。通过扩展行业标准的 PostgreSQL 方言，DuckDB 旨在提供最简单的方式来表达您所需的数据转换。这些更改包括改变古老的 SELECT 语句子句顺序，使其以 FROM 开头，允许通过链式调用以全新方式使用函数，以及列表推导式等高级嵌套数据类型计算。所有这些功能都已在 0.8.1 版本中提供。

未来更友好的 SQL 工作包括：

• 具有多个参数的 Lambda 函数，例如 list_zip
• 下划线作为数字分隔符（例如：1_000_000 而不是 1000000）
• 扩展用户体验，包括自动加载
• 文件全局匹配的改进
• 您的建议！

请告诉我们 SQL 的哪些方面可以改进！我们欢迎您在 Discord 或 GitHub 上提供反馈。

生生不息，繁荣昌盛！🖖