SQL 健身：将 SQL 弯曲成灵活的新形态

Alex Monahan

2024-03-01 · 18 分钟

DuckDB 特别 友好 的 SQL 方言 简化了常见的查询操作。然而，这些功能也开启了编写高级 SQL 的全新灵活方式！在这篇文章中，我们将结合多个友好功能，既能更接近实际用例，又能拓展你的想象力。这些查询本身就很有用，但它们的组成部分对你的工具箱来说更有价值。

你用 SQL 构建过的最疯狂的东西是什么？我们想听听！在 X（以前称为 Twitter 的网站）上标记 DuckDB 或在 LinkedIn 上标记，并加入 DuckDB Discord 社区。

传统 SQL 过于死板，无法重用

SQL 查询通常是针对数据库中独有的表专门编写的。这限制了可重用性。例如，你见过高级 SQL 辅助函数库吗？SQL 作为一种语言，通常不够灵活，无法构建可重用的函数。今天，我们正在飞向一个更灵活的未来！

动态聚合宏

在 SQL 中，通常需要单独指定要 SELECT 和 GROUP BY 的列。然而，在许多商业智能工作负载中，分组和聚合函数必须易于用户调整。想象一个交互式图表工作流——首先我想绘制公司总收入随时间的变化。然后，如果我在第一张图中看到收入下降，我想调整图表，按业务部门对收入进行分组，以查看是公司的哪个部分导致了问题。这通常需要模板化 SQL，使用一种编译为 SQL 的语言（如 Malloy），或者使用另一种编程语言构建 SQL 字符串。我们仅凭 SQL 能做多少呢？

让我们看看一个灵活的纯 SQL 方法，然后分解它的构造方式。

CREATE OR REPLACE TABLE example AS 
    SELECT x % 11 AS col1, x % 5 AS col2, x % 2 AS col3, 1 AS col4
    FROM range(1, 11) t(x);
FROM example;

创建宏

下面的宏接受要包含或排除的列列表、要聚合的列列表以及要应用的聚合函数。所有这些都可以作为参数从查询数据库的主语言中传入。

-- We use a table macro (or function) for reusability
CREATE OR REPLACE MACRO dynamic_aggregates(
        included_columns,
        excluded_columns,
        aggregated_columns,
        aggregate_function
    ) AS TABLE (
    FROM example 
    SELECT 
        -- Use a COLUMNS expression to only select the columns
        -- we include or do not exclude
        COLUMNS(c -> (
            -- If we are not using an input parameter (list is empty),
            -- ignore it
            (list_contains(included_columns, c) OR
             len(included_columns) = 0)
            AND
            (NOT list_contains(excluded_columns, c) OR
             len(excluded_columns) = 0)
            )),
        -- Use the list_aggregate function to apply an aggregate
        -- function of our choice
        list_aggregate(
            -- Convert to a list (to enable the use of list_aggregate)
            list(
                -- Use a COLUMNS expression to choose which columns
                -- to aggregate
                COLUMNS(c -> list_contains(aggregated_columns, c))
            ), aggregate_function
        )
    GROUP BY ALL -- Group by all selected but non-aggregated columns
    ORDER BY ALL -- Order by each column from left to right 
);

执行宏

现在我们可以将该宏用于许多不同的聚合操作。出于说明目的，下面的 3 个查询展示了实现相同结果的不同方法。

选择 col3 和 col4，并取 col1 和 col2 的最小值

FROM dynamic_aggregates(
    ['col3', 'col4'], [], ['col1', 'col2'], 'min'
);

选择除 col1 和 col2 之外的所有列，并取 col1 和 col2 的最小值

FROM dynamic_aggregates(
    [], ['col1', 'col2'], ['col1', 'col2'], 'min'
);

如果同一列既在包含列表中又在排除列表中，则将其排除（排除优先）。如果包含 col2、col3 和 col4，但排除 col2，则效果就像只包含 col3 和 col4

FROM dynamic_aggregates(
    ['col2', 'col3', 'col4'], ['col2'], ['col1', 'col2'], 'min'
);

执行其中任何一个查询都将返回此结果

理解设计

我们灵活的 表宏 的第一步是使用 DuckDB 的 FROM-first 语法 选择一个特定的表。嗯，那不是很动态！如果愿意，我们可以通过为要向应用程序公开的每个表创建此宏的副本。但是，我们将在下一个示例中展示另一种方法，并在后续的博客文章中通过一个正在开发的 DuckDB 功能完全解决这个问题。敬请期待！

然后我们根据传入的列表参数 SELECT 我们的分组列。COLUMNS 表达式 将执行一个 lambda 函数 来决定哪些列符合被选择的标准。

lambda 函数的第一部分检查列名是否在 included_columns 列表中传入。但是，如果我们选择不使用包含规则（通过传入一个空白的 included_columns 列表），我们希望忽略该参数。如果列表为空，len(included_columns) = 0 将评估为 true，并有效禁用对 included_columns 的过滤。这是一种常见的可选过滤模式，在各种 SQL 查询中普遍有用。（感谢我的导师和朋友 Paul Bloomquist 教我这种模式！）

我们对 excluded_columns 重复该模式，以便如果它被填充则使用，如果留空则忽略。excluded_columns 列表也将优先，因此如果一列同时出现在两个列表中，它将被排除。

接下来，我们将聚合函数应用于要聚合的列。从内到外地理解查询的这一部分逻辑是最容易的。COLUMNS 表达式将获取 aggregated_columns 列表中的列。然后，我们做一些小小的“健身”（总要发生一些这样的事……）。

如果我们应用一个典型的聚合函数（如 sum 或 min），它需要静态地在我们的宏中指定。要将其作为字符串动态传入（可能一直从调用此 SQL 语句的应用程序代码中传入），我们利用了 list_aggregate 函数 的一个独特属性。它在第二个参数中接受一个函数名（作为字符串）。因此，为了使用这个独特属性，我们使用 list 聚合函数 将每个组内的所有值转换为一个列表。然后我们使用 list_aggregate 函数将我们传入宏的 aggregate_function 应用于每个列表。

快完成了！现在 GROUP BY ALL 将自动选择按第一个 COLUMNS 表达式返回的列进行分组。ORDER BY ALL 表达式将按升序排列每个列，从左到右。

我们做到了！

加分题！在 DuckDB 的下一个版本 0.10.1 中，我们将能够对 COLUMNS 表达式的结果 应用动态别名。例如，每个新的聚合列都可以重命名为 agg_[原始列名] 的模式。这将解锁串联这类宏的能力，因为命名将是可预测的。

要点

此宏中使用的几种方法可以在您的 SQL 工作流中以多种方式应用。结合 lambda 函数和 COLUMNS 表达式，您可以选择任意列列表。OR len(my_list) = 0 技巧允许在列表参数为空时被忽略。一旦您拥有了任意的列集，您甚至可以使用 list 和 list_aggregate 对这些列应用动态选择的聚合函数。

但是，我们仍然必须在开头指定一个表。我们也仅限于可用于 list_aggregate 的聚合函数。让我们放宽这两个限制！

创建宏的第 2 版

这种方法利用了两个关键概念

• 宏可以用来创建临时聚合函数
• 宏可以查询执行期间范围内的 公共表表达式 (CTE) / WITH 子句

CREATE OR REPLACE MACRO dynamic_aggregates_any_cte_any_func(
    included_columns,
    excluded_columns,
    aggregated_columns
    /* No more aggregate_function */
) AS TABLE (
    FROM any_cte -- No longer a fixed table!
    SELECT 
        COLUMNS(c -> (
            (list_contains(included_columns, c) OR
            len(included_columns) = 0)
            AND 
            (NOT list_contains(excluded_columns, c) OR
            len(excluded_columns) = 0)
            )),
        -- We no longer convert to a list, 
        -- and we refer to the latest definition of any_func 
        any_func(COLUMNS(c -> list_contains(aggregated_columns, c))) 
    GROUP BY ALL 
    ORDER BY ALL 
);

执行第 2 版

当我们调用这个宏时，会增加额外的复杂性。我们不再执行单个语句，并且我们的逻辑不再完全可参数化（因此需要一些模板或 SQL 构建）。但是，我们可以针对任何任意 CTE 执行此宏，使用任何任意聚合函数。非常强大且非常可重用！

-- We can define or redefine any_func right before calling the macro 
CREATE OR REPLACE TEMP FUNCTION any_func(x)
    AS 100.0 * sum(x) / count(x);

-- Any table structure is valid for this CTE!
WITH any_cte AS (
    SELECT
        x % 11 AS id,
        x % 5 AS my_group,
        x % 2 AS another_group,
        1 AS one_big_group
    FROM range(1, 101) t(x)
)
FROM dynamic_aggregates_any_cte_any_func(
    ['another_group', 'one_big_group'], [], ['id', 'my_group']
);

理解第 2 版

我们不再查询大胆命名的 example 表，而是查询可能更通用命名的 any_cte。请注意，any_cte 的模式与我们之前的示例不同——any_cte 中的列可以是任何东西！当宏创建时，any_cte 甚至不存在。当宏执行时，它会搜索名为 any_cte 的表状对象，并且它是在宏被调用时在 CTE 中定义的。

同样，any_func 最初也不存在。它只需要在宏执行之前的某个时间点创建（或重新创建）。它的唯一要求是它是一个对单个列操作的聚合函数。

FUNCTION 和 MACRO 在 DuckDB 中是同义词，可以互换使用！

第 2 版的要点

宏可以在调用时通过使用 CTE 对任何任意表进行操作。这使得我们的宏更具可重用性——它可以在任何表上工作！不仅如此，还可以使用任何自定义聚合函数。

看看我们把 SQL 拓展到了多远——我们创造了一个真正可重用的 SQL 函数！表是动态的，分组列是动态的，聚合列是动态的，聚合函数也是动态的。我们的日常健身拉伸收到了回报。不过，请继续关注未来文章中以更简单方法实现类似结果的方式。

任何数据集的自定义摘要

接下来我们有一个真正的生产级示例！此查询为 MotherDuck Web UI 的 列浏览器 组件提供了一部分功能。 Hamilton Ulmer 领导了此组件的创建，也是此查询的作者！列浏览器和此查询的目的是尽可能快速轻松地获取数据集中所有列数据的概览。

DuckDB 有一个内置的 SUMMARIZE 关键字，可以在整个表上计算类似的指标。然而，对于大型数据集，SUMMARIZE 可能需要几秒钟才能加载。此查询提供了自定义汇总功能，可以根据您最感兴趣的数据属性进行定制。

传统上，数据库要求显式引用每个列，并且当数据以单独的列排列时效果最佳。此查询利用了 DuckDB 将函数应用于所有列的能力，其 UNPIVOT（或堆叠）列的能力，以及其 STRUCT 数据类型来存储键/值对。结果是一个干净、透视的表中所有行和列的摘要。

让我们看看整个函数，然后逐个分解。

这个示例数据集来自Hugging Face，它托管了许多数据集的DuckDB 可访问的 Parquet 文件。首先，我们从这个远程 Parquet 文件创建一个本地表。

创建

CREATE OR REPLACE TABLE spotify_tracks AS
    FROM 'https://hugging-face.cn/datasets/maharshipandya/spotify-tracks-dataset/resolve/refs%2Fconvert%2Fparquet/default/train/0000.parquet?download=true';

然后我们创建并执行我们的 custom_summarize 宏。我们使用上面相同的 any_cte 技巧，使其可以在任何查询结果或表上重用。

CREATE OR REPLACE MACRO custom_summarize() AS TABLE (
    WITH metrics AS (
        FROM any_cte 
        SELECT 
            {
                name: first(alias(COLUMNS(*))),
                type: first(typeof(COLUMNS(*))),
                max: max(COLUMNS(*))::VARCHAR,
                min: min(COLUMNS(*))::VARCHAR,
                approx_unique: approx_count_distinct(COLUMNS(*)),
                nulls: count(*) - count(COLUMNS(*)),
            }
    ), stacked_metrics AS (
        UNPIVOT metrics 
        ON COLUMNS(*)
    )
    SELECT value.* FROM stacked_metrics
);

执行

spotify_tracks 数据集实际上被重命名为 any_cte，然后进行汇总。

WITH any_cte AS (FROM spotify_tracks)
FROM custom_summarize();

结果包含原始数据集中每个列的一行，以及几列汇总统计数据。

那么这个查询是如何构建的呢？让我们一步步分解每个 CTE。

逐步分解

指标 CTE

首先让我们看看 metrics CTE 和返回的数据形状

FROM any_cte 
SELECT 
    {
        name: first(alias(COLUMNS(*))),
        type: first(typeof(COLUMNS(*))),
        max: max(COLUMNS(*))::VARCHAR,
        min: min(COLUMNS(*))::VARCHAR,
        approx_unique: approx_count_distinct(COLUMNS(*)),
        nulls: count(*) - count(COLUMNS(*)),
    };

这个中间结果保持与原始数据集相同的列数，但只返回一行汇总统计数据。由于列名长度，列名被截断。DuckDB 0.10.1 中将改进 COLUMNS 表达式的默认命名，因此名称将更加清晰！

每列中的数据都组织成键值对的 STRUCT。你还可以看到，由于使用了 alias 函数，原始列的清晰名称存储在 STRUCT 中。虽然我们已经计算了汇总统计数据，但这些统计数据的格式难以直观解释。

该查询通过使用 COLUMNS(*) 表达式将多个摘要指标应用于所有列，并使用 {...} 语法创建 STRUCT 来实现此结构。struct 的键表示指标的名称（以及我们希望在最终结果中用作列名的内容）。我们使用这种方法，因为我们希望将列转置为行，然后将摘要指标拆分为自己的列。

stacked_metrics CTE

接下来，数据被取消透视，将表从一行多列重塑为两列多行。

UNPIVOT metrics 
ON COLUMNS(*);

通过对 COLUMNS(*) 进行反透视，我们将所有列向下透视成两列：一列用于自动生成的列 name，另一列用于该列中的 value。

返回结果

最后一步是此查询中最像“健身”的部分。我们使用 STRUCT.* 语法 展开 value 列的 struct 格式，以便每个键都成为自己的列。这是另一种使查询更少依赖列名的方法——拆分会根据 struct 中的键自动进行。

SELECT value.*
FROM stacked_metrics;

我们现在已将数据拆分为多个列，因此汇总指标清晰易懂。

结论

我们已经表明，现在可以以高度灵活的方式构建可重用的 SQL 宏。您现在可以构建一个宏，它：

• 操作任何数据集
• 选择任何列
• 按任何列分组
• 使用任何函数聚合任意数量的列。

呼！

在此过程中，我们介绍了一些有用的技巧，可以添加到您的工具箱中：

• 使用 CTE 将宏应用于任何数据集
• 通过将 COLUMNS 表达式与 lambda 和 list_contains 函数结合使用来选择动态列列表
• 使用 list_aggregate 将聚合函数作为字符串传入
• 在宏中应用任何自定义聚合函数
• 使用 OR len(list_parameter) = 0 使列表参数可选
• 将 alias 函数与 COLUMNS 表达式一起使用，以存储所有列的原始名称
• 汇总所有列，然后使用 UNPIVOT 和 STRUCT.* 转置该汇总

这些友好型 SQL 功能的组合比单独使用任何一个功能都更强大。我们希望我们已经激励您将 SQL 发挥到新的极限！

一如既往，我们欢迎您的反馈和建议。我们还在考虑更多的灵活性，这将在未来的文章中展示。请分享您以富有想象力的方式拓展 SQL 的经历！

祝您分析愉快！