为 DuckDB 创建仅支持 SQL 的 Excel 样式透视扩展

Alex Monahan

2024-09-27 · 22 min

仅支持 SQL 的扩展的强大功能

SQL 并非一门新语言。因此，它在历史上一直缺少我们习以为常的一些现代便利功能。DuckDB 在 1.1 版本中推出了社区扩展，将包管理器不可思议的强大功能带入了 SQL 语言。我们的一个大胆目标是让 DuckDB 成为一种便捷的方式，能够封装任何 C++ 库，就像 Python 今天所做的那样，但适用于任何带有 DuckDB 客户端的语言。

对于扩展开发者而言，编译和分发变得更加容易。对于用户社区而言，安装仅需两条命令般简单

INSTALL pivot_table FROM community;
LOAD pivot_table;

然后，该扩展可以通过 SQL 函数在任何查询中使用。

然而，**我们并非都是 C++ 开发者**！作为 SQL 社区，我们能否构建一套 SQL 辅助函数？仅使用 *SQL* 来构建这些扩展需要什么条件？

可重用性

传统上，SQL 高度依赖于其编写时所基于的数据库模式。我们能否使其可重用？在 SQL Gymnasics 帖子中讨论了一些可重用性技术，但现在我们可以更进一步。凭借 1.1 版本，DuckDB 世界一流的友好 SQL 方言使得创建可应用于以下情况的宏成为可能：

• 任何表
• 任何列
• 任何函数

能够在**任何表**上工作的新功能得益于 query 和 query_table 函数！query 函数是一种安全地执行由 SQL 字符串定义的 SELECT 语句的方式，而 query_table 是一种使 FROM 子句能够同时从多个表拉取数据的方式。当它们与 COLUMNS 表达式和 LIST lambda 函数等其他友好 SQL 功能结合使用时，会非常强大。

社区扩展作为中央存储库

传统上，不同数据库之间没有 SQL 函数的中央存储库，更不用说不同公司之间了！DuckDB 的社区扩展可以成为这个知识库。DuckDB 扩展可以在所有带有 DuckDB 客户端的语言中使用，包括 Python、NodeJS、Java、Rust、Go，甚至 WebAssembly (Wasm)！

如果您是 DuckDB 爱好者和 SQL 用户，您可以通过扩展将您的专业知识回馈给社区。本文将向您展示如何做到！无需 C++ 知识——只需一点点复制/粘贴，GitHub Actions 就能处理所有编译工作。如果我能做到，您也能做到！

强大的 SQL

话虽如此，一个 SQL MACRO 究竟能有多大价值呢？我们能否做得不仅仅是创建一些小片段？我将以 pivot_table 扩展为例，说明您可以在 DuckDB SQL 中执行相当复杂和强大的操作。pivot_table 函数允许进行 Excel 样式的透视，包括 subtotals（小计）、grand_totals（总计）等。它也与 Pandas 的 pivot_table 函数非常相似，但具备 DuckDB 的所有可伸缩性和速度优势。它包含了超过 **250 个测试**，因此其设计目标不仅仅是一个示例！

为了实现这种级别的灵活性，pivot_table 扩展使用了许多友好且高级的 SQL 功能

• 用于执行 SQL 字符串的 query 函数
• 用于查询表列表的 query_table 函数
• 用于选择动态列列表的 COLUMNS 表达式
• 列表 lambda 函数，用于构建传递给 query 的 SQL 语句
  • 用于字符串操作（如引用）的 list_transform
  • 用于连接字符串的 list_reduce
  • 用于汇总多列并识别小计和总计行的 list_aggregate
• 用于字符串切片的方括号表示法
• UNION ALL BY NAME 用于按列名堆叠数据，以实现小计和总计
• SELECT * REPLACE 用于动态清理小计列
• SELECT * EXCLUDE 用于从最终结果中移除内部生成的列
• GROUPING SETS 和 ROLLUP 用于生成小计和总计
• UNNEST 用于将列表转换为单独的行，以实现 values_axis := 'rows'
• MACROs 用于模块化代码
• ORDER BY ALL 用于动态排序结果
• ENUMs 用于确定哪些列进行水平透视
• 当然还有用于水平透视的 PIVOT 函数！

DuckDB 创新的语法使得此扩展成为可能！

因此，我们现在拥有了所需的所有三个要素：一个中央包管理器、可重用宏以及足够的语法灵活性来完成有价值的工作。

创建您自己的 SQL 扩展

让我们逐步了解如何创建您自己的仅支持 SQL 的扩展。

编写扩展

扩展设置

第一步是通过点击 Use this template 从 DuckDB SQL 扩展模板创建您自己的 GitHub 仓库。

然后使用终端将您的新仓库克隆到本地机器上

git clone --recurse-submodules \
    https://github.com/⟨your_github_username⟩/⟨your_extension_repo⟩.git

请注意，--recurse-submodules 将确保拉取 DuckDB，这是构建扩展所必需的。

接下来，通过运行下面的 Python 脚本，在所有正确的位置将示例扩展的名称替换为您的扩展名称。

注意：如果您没有安装 Python，请访问 python.org 并按照说明操作。此脚本不需要任何库，因此您只需 Python 即可！（无需设置任何环境。）

python3 ./scripts/bootstrap-template.py ⟨extension_name_you_want⟩

初始扩展测试

此时，如果您愿意，可以按照 README 中的说明在本地构建和测试。然而，更简单的方法是，您可以直接将更改提交到 Git 并推送到 GitHub，GitHub Actions 会为您完成编译！GitHub Actions 还会对您的扩展运行测试，以验证其是否正常工作。

注意：这些说明并非为 Windows 用户编写，因此在这种情况下，我们建议使用 GitHub Actions！

git add -A
git commit -m "Initial commit of my SQL extension!"
git push

编写您的 SQL 宏

如果您直接在 DuckDB 中测试宏，迭代速度可能会更快一些。编写好 SQL 后，我们将它移入扩展中。我们将使用的示例演示了如何从动态表名（或视图名！）中提取一组动态列。

CREATE OR REPLACE MACRO select_distinct_columns_from_table(table_name, columns_list) AS TABLE (
    SELECT DISTINCT
        COLUMNS(column_name -> list_contains(columns_list, column_name))
    FROM query_table(table_name)
    ORDER BY ALL
);

FROM select_distinct_columns_from_table('duckdb_types', ['type_category']);

添加 SQL 宏

严格来说，这是 C++ 部分，但我们将通过复制/粘贴并使用 GitHub Actions 进行编译，这样就不会有那种感觉了！

DuckDB 支持标量宏和表宏，它们的语法略有不同。扩展模板在名为 ⟨your_extension_name⟩.cpp 的文件中为每种宏都提供了一个示例（也包含代码注释！）。我们在这里添加一个表宏，因为它更复杂。我们将复制示例并对其进行修改！

static const DefaultTableMacro ⟨your_extension_name⟩_table_macros[] = {
    {DEFAULT_SCHEMA, "times_two_table", {"x", nullptr}, {{"two", "2"}, {nullptr, nullptr}}, R"(SELECT x * two AS output_column;)"},
    {
        DEFAULT_SCHEMA, // Leave the schema as the default
        "select_distinct_columns_from_table", // Function name
        {"table_name", "columns_list", nullptr}, // Parameters
        {{nullptr, nullptr}}, // Optional parameter names and values (we choose not to have any here)
        // The SQL text inside of your SQL Macro, wrapped in R"( )", which is a raw string in C++
        R"(
            SELECT DISTINCT
                COLUMNS(column_name -> list_contains(columns_list, column_name))
            FROM query_table(table_name)
            ORDER BY ALL
        )"
    },
    {nullptr, nullptr, {nullptr}, {{nullptr, nullptr}}, nullptr}
    };

就是这样！我们所需要提供的只是函数名称、参数名称以及我们的 SQL 宏文本。

测试扩展

我们还建议将一些测试添加到您的扩展的 ⟨your_extension_name⟩.test 文件中。这使用了 sqllogictest 仅用 SQL 进行测试！让我们添加上面的示例。

注意：在 sqllogictest 中，query I 表示结果中将有 1 列。然后我们添加 ---- 和以制表符分隔的结果集，不带列名。

query I
FROM select_distinct_columns_from_table('duckdb_types', ['type_category']);
----
BOOLEAN
COMPOSITE
DATETIME
NUMERIC
STRING
NULL

现在，像之前一样，只需添加、提交并推送您的更改到 GitHub，GitHub Actions 就会编译您的扩展并对其进行测试！

如果您想对您的扩展进行进一步的临时测试，您可以从您的 GitHub Actions 运行的工件中下载该扩展，然后按照这些步骤在本地安装它。

上传到社区扩展仓库

一旦您对您的扩展满意，就该与 DuckDB 社区分享了！请按照社区扩展帖子中的步骤操作。这些步骤的摘要如下：

1. 发送一个包含扩展描述的元数据文件 description.yml 的 PR。例如，h3 社区扩展使用以下 YAML 配置：

   extension:
     name: h3
     description: Hierarchical hexagonal indexing for geospatial data
     version: 1.0.0
     language: C++
     build: cmake
     license: Apache-2.0
     maintainers:
       - isaacbrodsky
   
   repo:
     github: isaacbrodsky/h3-duckdb
     ref: 3c8a5358e42ab8d11e0253c70f7cc7d37781b2ef
   

2. 等待维护者的批准。

大功告成！您已经创建了一个可共享的 DuckDB 社区扩展。现在，让我们以 pivot_table 扩展为例，看看仅支持 SQL 的扩展能有多强大。

pivot_table 扩展的功能

pivot_table 扩展支持高级透视功能，这些功能以前只能在电子表格、数据框库或自定义宿主语言函数中实现。它使用 Excel 透视 API：values、rows、columns 和 filters——处理每个参数的 0 个或更多实例。不仅如此，它还支持 subtotals（小计）和 grand_totals（总计）。如果传入多个 values，values_axis 参数允许用户选择每个值是获得自己的列还是自己的行。

为什么这是 DuckDB 如何超越传统 SQL 的一个好例子？Excel 透视 API 需要根据所使用的参数采用截然不同的 SQL 语法。如果没有列被透视出去，只需一个 GROUP BY。但是，一旦涉及列，就需要一个 PIVOT。

此函数可以对作为参数传入的一个或多个 table_names 进行操作。任何一组表（或视图！）将首先垂直堆叠，然后进行透视。

pivot_table 使用示例

在此查看在 DuckDB Wasm shell 中使用该扩展的实时示例!

CREATE OR REPLACE TABLE business_metrics (
    product_line VARCHAR,
    product VARCHAR,
    year INTEGER,
    quarter VARCHAR,
    revenue INTEGER,
    cost INTEGER
);

INSERT INTO business_metrics VALUES
    ('Waterfowl watercraft', 'Duck boats', 2022, 'Q1', 100, 100),
    ('Waterfowl watercraft', 'Duck boats', 2022, 'Q2', 200, 100),
    ('Waterfowl watercraft', 'Duck boats', 2022, 'Q3', 300, 100),
    ('Waterfowl watercraft', 'Duck boats', 2022, 'Q4', 400, 100),
    ('Waterfowl watercraft', 'Duck boats', 2023, 'Q1', 500, 100),
    ('Waterfowl watercraft', 'Duck boats', 2023, 'Q2', 600, 100),
    ('Waterfowl watercraft', 'Duck boats', 2023, 'Q3', 700, 100),
    ('Waterfowl watercraft', 'Duck boats', 2023, 'Q4', 800, 100),

    ('Duck Duds', 'Duck suits', 2022, 'Q1', 10, 10),
    ('Duck Duds', 'Duck suits', 2022, 'Q2', 20, 10),
    ('Duck Duds', 'Duck suits', 2022, 'Q3', 30, 10),
    ('Duck Duds', 'Duck suits', 2022, 'Q4', 40, 10),
    ('Duck Duds', 'Duck suits', 2023, 'Q1', 50, 10),
    ('Duck Duds', 'Duck suits', 2023, 'Q2', 60, 10),
    ('Duck Duds', 'Duck suits', 2023, 'Q3', 70, 10),
    ('Duck Duds', 'Duck suits', 2023, 'Q4', 80, 10),

    ('Duck Duds', 'Duck neckties', 2022, 'Q1', 1, 1),
    ('Duck Duds', 'Duck neckties', 2022, 'Q2', 2, 1),
    ('Duck Duds', 'Duck neckties', 2022, 'Q3', 3, 1),
    ('Duck Duds', 'Duck neckties', 2022, 'Q4', 4, 1),
    ('Duck Duds', 'Duck neckties', 2023, 'Q1', 5, 1),
    ('Duck Duds', 'Duck neckties', 2023, 'Q2', 6, 1),
    ('Duck Duds', 'Duck neckties', 2023, 'Q3', 7, 1),
    ('Duck Duds', 'Duck neckties', 2023, 'Q4', 8, 1),
;

FROM business_metrics;

接下来，我们从社区仓库安装扩展

INSTALL pivot_table FROM community;
LOAD pivot_table;

现在我们可以构建如下的透视表。这需要一些样板代码，其工作原理的详细信息将很快解释。

DROP TYPE IF EXISTS columns_parameter_enum;

CREATE TYPE columns_parameter_enum AS ENUM (
    FROM build_my_enum(['business_metrics'],    -- table_names
                       ['year', 'quarter'],     -- columns
                       [])                      -- filters
);

FROM pivot_table(['business_metrics'],          -- table_names
                 ['sum(revenue)', 'sum(cost)'], -- values
                 ['product_line', 'product'],   -- rows
                 ['year', 'quarter'],           -- columns
                 [],                            -- filters
                 subtotals := 1,
                 grand_totals := 1,
                 values_axis := 'rows'
                 );

pivot_table 扩展的工作原理

pivot_table 扩展是多个标量和表 SQL 宏的集合。这使得逻辑可以模块化。您可以在下面看到，这些函数被用作构建更复杂函数的基石。这在 SQL 中通常很难做到，但在 DuckDB 中却很容易！

函数及其简要描述如下。

构建块标量函数

• nq：“无引号”——转义字符串中的分号以防止 SQL 注入
• sq：“单引号”——用单引号包裹字符串并转义嵌入的单引号
• dq：“双引号”——用双引号包裹并转义嵌入的双引号
• nq_list：转义列表中每个字符串的分号。使用 nq。
• sq_list：用单引号包裹列表中的每个字符串。使用 sq。
• dq_list：用双引号包裹列表中的每个字符串。使用 dq。
• nq_concat：连接字符串列表并进行分号转义。使用 nq_list。
• sq_concat：连接字符串列表，每个字符串用单引号包裹。使用 sq_list。
• dq_concat：连接字符串列表，每个字符串用双引号包裹。使用 dq_list。

为模块化重构时创建的函数

• totals_list：构建列表，作为启用 subtotals（小计）和 grand_totals（总计）的一部分。
• replace_zzz：排序后重命名 subtotal（小计）和 grand_total（总计）指示符，使其更友好。

核心透视逻辑函数

• build_my_enum：确定水平透视时要创建哪些新列。返回一个表。详见下文。
• pivot_table：根据输入，决定调用 no_columns、columns_values_axis_columns 还是 columns_values_axis_rows。对生成的 SQL 字符串执行 query。返回一个表。详见下文。
  • no_columns：当没有列被透视出去时，构建 query 要执行的 SQL 字符串。
  • columns_values_axis_columns：当水平透视且 values 中的每个条目都获得单独的列时，构建 query 要执行的 SQL 字符串。
  • columns_values_axis_rows：当水平透视且 values 中的每个条目都获得单独的行时，构建 query 要执行的 SQL 字符串。
• pivot_table_show_sql：返回 query 将要执行的 SQL 字符串，用于调试目的。

build_my_enum 函数

使用 pivot_table 扩展功能的第一步是定义一个 ENUM（用户定义类型），其中包含水平透视时要创建的所有新列名，该 ENUM 被命名为 columns_parameter_enum。DuckDB 的自动 PIVOT 语法可以自动定义此 ENUM，但在我们的情况下，我们需要两个明确的步骤。原因是自动透视在后台运行两个语句，但 MACRO 必须只包含一个语句。如果 columns 参数未使用，此步骤本质上是一个空操作，因此可以省略或为了保持一致性而包含（推荐）。

query 和 query_table 函数仅支持 SELECT 语句（出于安全原因），因此 ENUM 创建的动态部分发生在 build_my_enum 函数中。如果这种用法变得普遍，DuckDB 可能会添加功能，以支持 ENUM 类型的 CREATE OR REPLACE 语法，甚至可能是临时枚举。这将把这种模式从 3 个语句减少到 2 个。请告诉我们您的想法！

build_my_enum 函数结合使用 query_table 从多个输入表中拉取数据，以及 query 函数，以便在传入表名列表之前完成双引号（和正确的字符转义）。它使用与核心 pivot_table 函数类似的模式：将 SQL 查询构建为字符串，然后用 query 调用它。SQL 字符串是使用列表 lambda 函数和用于引用的构建块函数构建的。

pivot_table 函数

pivot_table 函数的核心是根据所使用的参数确定生成所需透视表所需的 SQL。

由于这个 SQL 语句最终是一个字符串，我们可以使用标量 SQL 宏的层次结构，而不是一个大的单一宏。这是 SQL 中一个常见的传统问题——它往往不够模块化或可重用，但我们能够借助 DuckDB 的语法将我们的逻辑分隔开来。

注意：如果非可选参数未使用，应传入一个空列表（[]）。

• table_names：要聚合或透视的表或视图名称列表。多个表在任何其他处理之前会通过 UNION ALL BY NAME 组合。
• values：聚合指标列表，格式为 ['agg_fn_1(col_1)', 'agg_fn_2(col_2)', ...]。
• rows：要 SELECT 和 GROUP BY 的列名列表。
• columns：要水平透视的列名列表，原始列中的每个值都会透视成一个单独的列。如果传入多个列名，则只透视数据集中出现的唯一数据组合。
  • 示例：如果传入像 ['continent', 'country'] 这样的 columns 参数，则只包含有效的 continent / country 对。
  • （不会生成 Europe_Canada 列）。
• filters：要应用于原始数据集的 WHERE 子句表达式列表，格式为 ['col_1 = 123', 'col_2 LIKE ''woot%''', ...]。
  • filters 使用 AND 组合。
• values_axis（可选）：如果传入多个 values，确定是为每个值创建单独的行还是列。可以是 rows 或 columns，默认为 columns。
• subtotals（可选）：如果启用，根据 rows 参数在多个详细级别计算聚合指标。为 0 或 1，默认为 0。
• grand_totals（可选）：如果启用，除了按 rows 定义的粒度计算聚合指标外，还计算原始数据中所有行的聚合指标。为 0 或 1，默认为 0。

无水平透视（columns 未使用）

如果未使用 columns 参数，则无需水平透视任何列。因此，使用 GROUP BY 语句。如果使用 subtotals，则使用 ROLLUP 表达式在不同粒度级别计算 values。如果使用 grand_totals 但未使用 subtotals，则使用 GROUPING SETS 表达式而不是 ROLLUP 来评估所有行。

在此示例中，我们构建了每个 product_line 和 product 的收入和成本摘要。

FROM pivot_table(['business_metrics'],
                 ['sum(revenue)', 'sum(cost)'],
                 ['product_line', 'product'],
                 [],
                 [],
                 subtotals := 1,
                 grand_totals := 1,
                 values_axis := 'columns'
                 );

水平透视，values 中每个指标一列

构建一个 PIVOT 语句，该语句将透视出 columns 参数中原始数据值的所有有效组合。如果使用 subtotals 或 grand_totals，则创建输入数据的多个副本，但将 rows 参数中相应的列名替换为字符串常量。将 values 中的所有表达式传递给 PIVOT 语句的 USING 子句，以便每个表达式都获得自己的列。

我们增强了之前的示例，为每个 year / value 组合透视出一个单独的列

DROP TYPE IF EXISTS columns_parameter_enum;

CREATE TYPE columns_parameter_enum AS ENUM (
    FROM build_my_enum(['business_metrics'],
                       ['year'],
                       [])
);

FROM pivot_table(['business_metrics'],
                 ['sum(revenue)', 'sum(cost)'],
                 ['product_line', 'product'],
                 ['year'],
                 [],
                 subtotals := 1,
                 grand_totals := 1,
                 values_axis := 'columns'
                 );

水平透视，values 中每个指标一行

为 values 中的每个指标构建一个单独的 PIVOT 语句，并使用 UNION ALL BY NAME 将它们组合起来。如果使用 subtotals 或 grand_totals，则创建输入数据的多个副本，但将 rows 参数中相应的列名替换为字符串常量。

为了稍微简化外观，我们调整了之前查询中的一个参数，并设置 values_axis := 'rows'

DROP TYPE IF EXISTS columns_parameter_enum;

CREATE TYPE columns_parameter_enum AS ENUM (
    FROM build_my_enum(['business_metrics'],
                       ['year'],
                       [])
);

FROM pivot_table(['business_metrics'],
                 ['sum(revenue)', 'sum(cost)'],
                 ['product_line', 'product'],
                 ['year'],
                 [],
                 subtotals := 1,
                 grand_totals := 1,
                 values_axis := 'rows'
                 );

结论

有了 DuckDB 1.1，与社区分享您的 SQL 知识变得前所未有的容易！DuckDB 的社区扩展仓库真正是 SQL 语言的包管理器。DuckDB 中的宏现在具有高度可重用性（得益于 query 和 query_table），并且 DuckDB 的 SQL 语法提供了足够的能力来完成复杂任务。

请告诉我们 pivot_table 扩展是否对您有所帮助——我们欢迎贡献和功能请求！我们可以一起将终极透视功能编写一次，然后在任何地方使用它。

未来，我们计划进一步简化 SQL 扩展的创建。当然，我们非常乐意听取您的反馈！请在 Discord 的 community-extensions 频道加入我们。

祝您分析愉快！