探索DuckDB：将 SQL 弯曲成灵活的新形状
原文：SQL Gymnastics: Bending SQL into flexible new shapes[1]
翻译：Gemini
校对：alitrack
SQL 体操：将 SQL 弯曲成灵活的新形状
Alex Monahan 2024-03-01
TL；DR：结合 DuckDB 友好 SQL 的多个特性，可以实现高度灵活的查询，这些查询可以在表中重复使用。
DuckDB 的 特别[2] 友好 SQL 方言[3] 简化了常见的查询操作。 同时，这些特性也解锁了编写高级 SQL 的新方法和灵活方法！ 在这篇文章中，我们将结合多个友好特性，既可以更接近实际用例，又可以发挥你的想象力。 这些查询本身很有用，但它们的组成部分对你来说更有价值。
传统 SQL 太僵化，无法重复使用
SQL 查询通常是专门针对数据库中唯一的表设计的。 这限制了可重用性。 例如，你是否见过高级 SQL 帮助器函数库？ SQL 作为一门语言通常不够灵活，无法构建可重用函数。 今天，我们正朝着更灵活的未来迈进！
动态聚合宏
在 SQL 中，通常必须单独指定要 SELECT 和 GROUP BY 的列。 但是，在许多商业智能工作负载中，分组和聚合函数必须易于用户调整。 想象一下一个交互式图表工作流 - 首先我想绘制一段时间内的公司总收入。 然后，如果我在第一个图中看到收入下降，我想调整图，按业务部门对收入进行分组，以查看公司的哪个部门造成了这个问题。 这通常需要使用模板化 SQL，使用编译为 SQL 的语言（如 Malloy[4])，或使用另一种编程语言构建 SQL 字符串。 我们仅使用 SQL 能做多少？
让我们看一下仅限 SQL 的灵活方法，然后分解其构建方式。
首先，我们将创建一个示例数据表。col1 在每一行上都是唯一的，但其他列是行的各种分组。
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;
| col1 | col2 | col3 | col4 |
| 1 | 1 | 1 | 1 |
| 2 | 2 | 0 | 1 |
| 3 | 3 | 1 | 1 |
| 4 | 4 | 0 | 1 |
| 5 | 0 | 1 | 1 |
| 6 | 1 | 0 | 1 |
| 7 | 2 | 1 | 1 |
| 8 | 3 | 0 | 1 |
| 9 | 4 | 1 | 1 |
| 10 | 0 | 0 | 1 |
创建宏
下面的宏接受要包括或排除的列列表、要聚合的列列表以及要应用的聚合函数。 所有这些都可以从查询数据库的主机语言中作为参数传递。
-- 我们使用表宏（或函数）来实现可重用性
CREATE OR REPLACE MACRO dynamic_aggregates(
included_columns,
excluded_columns,
aggregated_columns,
aggregate_function
) AS TABLE (
FROM example
SELECT
-- 使用 COLUMNS 表达式仅选择我们包括或不排除的列
COLUMNS(c -> (
-- 如果我们不使用输入参数（列表为空），
-- 则忽略它
(list_contains(included_columns, c) OR
len(included_columns) = 0)
AND
(NOT list_contains(excluded_columns, c) OR
len(excluded_columns) = 0)
)),
-- 使用 list_aggregate 函数应用我们选择的聚合
-- 函数
list_aggregate(
-- 转换为列表（以启用 list_aggregate 的使用）
list(
-- 使用 COLUMNS 表达式选择要聚合的列
COLUMNS(c -> list_contains(aggregated_columns, c))
), aggregate_function
)
GROUP BY ALL -- 按所有选定的但未聚合的列分组
ORDER BY ALL -- 按从左到右的每一列排序
);
执行宏
现在，我们可以将该宏用于许多不同的聚合操作。 出于说明目的，以下 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'
);
执行其中任何一个查询都将返回以下结果：
| col3 | col4 | list_aggregate(list(example.col1), ‘min’) | list_aggregate(list(example.col2), ‘min’) |
| 0 | 1 | 2 | 0 |
| 1 | 1 | 1 | 0 |
理解设计
我们灵活的 table-macros[5] 的第一步是使用 DuckDB 的 FROM-first 语法[6] 选择一个特定表。 好吧，这不太灵活！ 如果我们愿意，我们可以通过为我们想要向应用程序公开的每个表创建此宏的副本来解决此问题。 但是，我们将在下一个示例中展示另一种方法，并在后续博客文章中使用正在开发的 DuckDB 特性彻底解决此问题。 敬请期待！
然后，我们根据传入的参数列表 SELECT 我们的分组列。 COLUMNS 表达式[7] 将执行一个 lambda 函数[8] 来决定哪些列符合要选择的条件。
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 函数[9] 的 因此，为了使用此唯一属性，我们使用 list 聚合函数[10] 将每个组中的所有值转换为列表。 然后，我们使用 list_aggregate 函数将我们传递到宏中的 aggregate_function 应用到每个列表。
几乎完成了！
现在 GROUP BY ALL[11] 将自动选择按第一个 COLUMNS 表达式返回的列进行分组。 ORDER BY ALL[12] 表达式将按升序对每列进行排序，从左到右移动。
我们成功了！
附加积分！在 DuckDB 的下一个版本 0.10.1 中，我们将能够对
COLUMNS表达式的结果 应用动态别名[13]。 例如，每个新的聚合列都可以在模式agg_[原始列名]中重命名。 这将解锁将这些类型的宏链接在一起的能力，因为命名将是可预测的。
要点
此宏中使用的一些方法可以在 SQL 工作流中以多种方式应用。 将 lambda 函数与 COLUMNS 表达式结合使用，可以选择任意列列表。 OR len(my_list) = 0 技巧允许在空白时忽略列表参数。 一旦有了任意列集，你甚至可以使用 list 和 list_aggregate 对这些列应用动态选择的聚合函数。
但是，我们仍然必须在开始时指定一个表。 我们还仅限于可与 list_aggregate 一起使用的聚合函数。让我们放松这两个约束！
创建宏版本 2
此方法利用了两个关键概念：
• 宏可用于创建临时聚合函数
• 宏可以在执行期间查询范围内的 公共表表达式 (CTE) /
WITH子句[14]
CREATE OR REPLACE MACRO dynamic_aggregates_any_cte_any_func(
included_columns,
excluded_columns,
aggregated_columns
/* 不再有 aggregate_function */
) AS TABLE (
FROM any_cte -- 不再是固定表！
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)
)),
-- 我们不再转换为列表，
-- 并且我们引用 any_func 的最新定义
any_func(COLUMNS(c -> list_contains(aggregated_columns, c)))
GROUP BY ALL
ORDER BY ALL
);
执行版本 2
当我们调用此宏时，会有额外的复杂性。 我们不再执行单个语句，并且我们的逻辑不再完全可参数化（因此需要一些模板或 SQL 构造）。 但是，我们可以针对任何任意 CTE 使用此宏，使用任何任意聚合函数。 非常强大且可重复使用！
-- 我们可以在调用宏之前定义或重新定义 any_func
CREATE OR REPLACE TEMP FUNCTION any_func(x)
AS 100.0 * sum(x) / count(x);
-- 任何表结构都对这个 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']
);
| another_group | one_big_group | any_func(any_cte.id) | any_func(any_cte.my_group) |
| 0 | 1 | 502.0 | 200.0 |
| 1 | 1 | 490.0 | 200.0 |
理解版本 2
我们不查询非常大胆命名的 example 表，而是查询可能更通用命名的 my_cte。 请注意，my_cte 具有与我们之前的示例不同的架构 - my_cte 中的列可以是任何内容！ 当创建宏时，my_cte 甚至不存在。 当执行宏时，它会搜索名为 my_cte 的类似表的对象，并且它在调用宏时在 CTE 中定义。
类似地，any_func 最初不存在。 它只需要在执行宏之前某个时间点创建（或重新创建）。 它的唯一要求是成为对单列进行操作的聚合函数。
FUNCTION和MACRO在 DuckDB 中是同义词，可以互换使用！
版本 2 的要点
宏可以通过在调用时使用 CTE 对任意表进行操作。 这使得我们的宏更具可重用性 - 它可以在任何表上工作！ 不仅如此，还可以使用任何自定义聚合函数。
看看我们已经将 SQL 扩展到了什么程度 - 我们已经创建了一个真正可重用的 SQL 函数！ 表是动态的，分组列是动态的，聚合列是动态的，聚合函数也是动态的。 我们每天的体操伸展运动得到了回报。 但是，请继续关注，在未来的帖子中，我们将找到一种使用更简单的方法来实现类似的结果。
任何数据集的自定义摘要
接下来，我们有一个真正的生产级示例！ 此查询为 MotherDuck Web UI 的 列浏览器[15] 组件的一部分提供支持。 汉密尔顿·乌尔默[16] 领导了此组件的创建，也是此查询的作者！ 列浏览器和此查询的目的是尽可能快速、轻松地获取数据集内所有列中数据的概览。
DuckDB 有一个内置的 SUMMARIZE 关键字[17]，它可以计算整个表中的类似指标。 但是，对于较大的数据集，SUMMARIZE 可能需要几秒钟才能加载。 此查询提供了一种自定义汇总功能，可以根据你最感兴趣的数据属性进行定制。
传统上，数据库要求显式引用每一列，并且当数据排列在单独的列中时效果最佳。 此查询利用了 DuckDB 一次对所有列应用函数、UNPIVOT[18]（或堆叠）列以及 STRUCT[19] 数据类型来存储键/值对的能力。 结果是对表中的所有行和列进行的干净、透视的汇总。
让我们看一下整个函数，然后逐个分解它。
此 示例数据集[20] 来自 Hugging Face[21]，它为其许多数据集托管 DuckDB 可访问的 Parquet 文件[22]。 首先，我们创建一个从这个远程 Parquet 文件填充的本地表。
创建
CREATE OR REPLACE TABLE spotify_tracks AS (
FROM 'https://huggingface.co/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 结果包含原始数据集中每列的一行，以及几列汇总统计信息。
| 名称 | 类型 | 最大值 | 最小值 | 近似唯一值 | 空值 |
| Unnamed: 0 | BIGINT | 113999 | 0 | 114089 | 0 |
| track_id | VARCHAR | 7zz7iNGIWhmfFE7zlXkMma | 0000vdREvCVMxbQTkS888c | 89815 | 0 |
| artists | VARCHAR | 龍藏Ryuzo | !nvite | 31545 | 1 |
| album_name | VARCHAR | 당신이 잠든 사이에 Pt. 4 Original Television Soundtrack | ! ! ! ! ! Whispers ! ! ! ! ! | 47093 | 1 |
| track_name | VARCHAR | 행복하길 바래 | !I’ll Be Back! | 72745 | 1 |
| popularity | BIGINT | 100 | 0 | 99 | 0 |
| duration_ms | BIGINT | 5237295 | 0 | 50168 | 0 |
| explicit | BOOLEAN | true | false | 2 | 0 |
| danceability | DOUBLE | 0.985 | 0.0 | 1180 | 0 |
| energy | DOUBLE | 1.0 | 0.0 | 2090 | 0 |
| key | BIGINT | 11 | 0 | 12 | 0 |
| loudness | DOUBLE | 4.532 | -49.531 | 19436 | 0 |
| mode | BIGINT | 1 | 0 | 2 | 0 |
| speechiness | DOUBLE | 0.965 | 0.0 | 1475 | 0 |
| acousticness | DOUBLE | 0.996 | 0.0 | 4976 | 0 |
| instrumentalness | DOUBLE | 1.0 | 0.0 | 5302 | 0 |
| liveness | DOUBLE | 1.0 | 0.0 | 1717 | 0 |
| valence | DOUBLE | 0.995 | 0.0 | 1787 | 0 |
| tempo | DOUBLE | 243.372 | 0.0 | 46221 | 0 |
| time_signature | BIGINT | 5 | 0 | 5 | 0 |
| track_genre | VARCHAR | world-music | acoustic | 115 | 0 |
那么，这个查询是如何构建的？ 让我们逐个分解每个 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(*)),
}
| main.struct_pack(“name” := first(alias(subset.”Unnamed: 0”)), … | main.struct_pack(“name” := first(alias(subset.track_id)), … | … | main.struct_pack(“name” := first(alias(subset.time_signature)), … | main.struct_pack(“name” := first(alias(subset.track_genre)), … |
| {‘name’: Unnamed: 0, ‘type’: BIGINT, ‘max’: 113999, ‘min’: 0, ‘approx_unique’: 114089, ‘nulls’: 0} | {‘name’: track_id, ‘type’: VARCHAR, ‘max’: 7zz7iNGIWhmfFE7zlXkMma, ‘min’: 0000vdREvCVMxbQTkS888c, ‘approx_unique’: 89815, ‘nulls’: 0} | … | {‘name’: time_signature, ‘type’: BIGINT, ‘max’: 5, ‘min’: 0, ‘approx_unique’: 5, ‘nulls’: 0} | {‘name’: track_genre, ‘type’: VARCHAR, ‘max’: world-music, ‘min’: acoustic, ‘approx_unique’: 115, ‘nulls’: 0} |
这个中间结果保留了与原始数据集相同数量的列，但只返回一行汇总统计信息。 列的名称因其长度而被截断。 COLUMNS 表达式的默认命名将在 DuckDB 0.10.1 中得到改进，因此名称将更加简洁！
每列中的数据被组织成一个键值对的 STRUCT。 您还可以看到，由于使用了 alias 函数，原始列的干净名称存储在 STRUCT 中。 虽然我们已经计算了汇总统计信息，但这些统计信息的格式很难直观地解释。
查询使用 COLUMNS(*) 表达式将多个汇总指标应用于所有列，并使用 {...} 语法创建 STRUCT 来实现此结构。 结构的键表示指标的名称（以及我们希望用作最终结果中列名称的内容）。 我们使用这种方法，因为我们希望将列转置为行，然后将汇总指标拆分为它们自己的列。
Stacked_metrics CTE
接下来，取消数据透视以将表从一行多列重塑为两行多行。
UNPIVOT metrics
ON COLUMNS(*)
| 名称 | 值 |
| main.struct_pack(“name” := first(alias(spotify_tracks.”Unnamed: 0”)), … | {‘name’: Unnamed: 0, ‘type’: BIGINT, ‘max’: 113999, ‘min’: 0, ‘approx_unique’: 114089, ‘nulls’: 0} |
| main.struct_pack(“name” := first(alias(spotify_tracks.track_id)), … | {‘name’: track_id, ‘type’: VARCHAR, ‘max’: 7zz7iNGIWhmfFE7zlXkMma, ‘min’: 0000vdREvCVMxbQTkS888c, ‘approx_unique’: 89815, ‘nulls’: 0} |
| … | … |
| main.struct_pack(“name” := first(alias(spotify_tracks.time_signature)), … | {‘name’: time_signature, ‘type’: BIGINT, ‘max’: 5, ‘min’: 0, ‘approx_unique’: 5, ‘nulls’: 0} |
| main.struct_pack(“name” := first(alias(spotify_tracks.track_genre)), … | {‘name’: track_genre, ‘type’: VARCHAR, ‘max’: world-music, ‘min’: acoustic, ‘approx_unique’: 115, ‘nulls’: 0} |
通过对 COLUMNS(*) 取消透视，我们获取所有列并将它们向下透视为两列：一列用于列的自动生成 name，另一列用于该列中的 value。
返回结果
最后一步是此查询中最像体操的部分。 我们使用 STRUCT.* 语法[23]分解 value 列的结构格式，以便每个键成为其自己的列。 这是使查询不那么依赖于列名称的另一种方法——拆分会根据结构中的键自动进行。
SELECT value.*
FROM stacked_metrics
我们现在已将数据拆分为多列，因此汇总指标非常直观且易于解释。
| name | type | max | min | approx_unique | nulls |
| Unnamed: 0 | BIGINT | 113999 | 0 | 114089 | 0 |
| track_id | VARCHAR | 7zz7iNGIWhmfFE7zlXkMma | 0000vdREvCVMxbQTkS888c | 89815 | 0 |
| artists | VARCHAR | 龍藏Ryuzo | !nvite | 31545 | 1 |
| album_name | VARCHAR | 당신이 잠든 사이에 Pt. 4 Original Television Soundtrack | ! ! ! ! ! Whispers ! ! ! ! ! | 47093 | 1 |
| track_name | VARCHAR | 행복하길 바래 | !I’ll Be Back! | 72745 | 1 |
| popularity | BIGINT | 100 | 0 | 99 | 0 |
| duration_ms | BIGINT | 5237295 | 0 | 50168 | 0 |
| explicit | BOOLEAN | true | false | 2 | 0 |
| danceability | DOUBLE | 0.985 | 0.0 | 1180 | 0 |
| energy | DOUBLE | 1.0 | 0.0 | 2090 | 0 |
| key | BIGINT | 11 | 0 | 12 | 0 |
| loudness | DOUBLE | 4.532 | -49.531 | 19436 | 0 |
| mode | BIGINT | 1 | 0 | 2 | 0 |
| speechiness | DOUBLE | 0.965 | 0.0 | 1475 | 0 |
| acousticness | DOUBLE | 0.996 | 0.0 | 4976 | 0 |
| instrumentalness | DOUBLE | 1.0 | 0.0 | 5302 | 0 |
| liveness | DOUBLE | 1.0 | 0.0 | 1717 | 0 |
| valence | DOUBLE | 0.995 | 0.0 | 1787 | 0 |
| tempo | DOUBLE | 243.372 | 0.0 | 46221 | 0 |
| time_signature | BIGINT | 5 | 0 | 5 | 0 |
| track_genre | VARCHAR | world-music | acoustic | 115 | 0 |
结论
我们已经展示出以高度灵活的方式构建可重用 SQL 宏现在成了可能。 你现在可以构建一个宏，用它：
• 对任何数据集进行操作
• 选择任何列
• 按任何列分组
• 使用任何函数汇总任意数量的列。
太棒了！
在此过程中，我们介绍了一些有用的技巧，你可以将它们添加到你的工具箱中：
• 使用 CTE 将宏应用于任何数据集
• 通过将
COLUMNS表达式与 lambda 和list_contains函数结合起来，选择动态列列表• 使用
list_aggregate以字符串形式传入聚合函数• 在宏中应用任何自定义聚合函数
• 使用
OR len(list_parameter) = 0使得列表参数可选• 使用
alias函数与COLUMNS表达式一起，存储所有列的原始名称• 汇总所有列，然后使用
UNPIVOT和STRUCT.*转置该汇总
这些友好的 SQL 特性的组合比单独使用其中任何一个都要强大。 我们希望我们已经激发你将你的 SQL 提升到新的高度！
引用链接
[1] SQL Gymnastics: Bending SQL into flexible new shapes: https://duckdb.org/2024/03/01/sql-gymnastics.html[2] 特别: https://duckdb.org/2022/05/04/friendlier-sql[3] SQL 方言: https://duckdb.org/docs/guides/sql_features/friendly_sql[4] Malloy: https://www.malloydata.dev/[5] table-macros: https://duckdb.org/docs/sql/statements/create_macro#table-macros[6] FROM-first 语法: https://duckdb.org/2023/08/23/even-friendlier-sql#from-first-in-select-statements[7] COLUMNS 表达式: https://duckdb.org/docs/sql/expressions/star#columns-expression[8] lambda 函数: https://duckdb.org/docs/sql/functions/lambda[9] list_aggregate 函数: https://duckdb.org/docs/sql/functions/nested#list-aggregates[10] list 聚合函数: https://duckdb.org/docs/sql/aggregates#general-aggregate-functions[11] GROUP BY ALL: https://duckdb.org/docs/sql/query_syntax/groupby#group-by-all[12] ORDER BY ALL: https://duckdb.org/docs/sql/query_syntax/orderby#order-by-all[13] 应用动态别名: https://github.com/duckdb/duckdb/pull/10774[14] 公共表表达式 (CTE) / WITH 子句: https://duckdb.org/docs/sql/query_syntax/with[15] 列浏览器: https://motherduck.com/blog/introducing-column-explorer/[16] 汉密尔顿·乌尔默: https://www.linkedin.com/in/hamilton-ulmer-28b97817/[17] SUMMARIZE 关键字: https://duckdb.org/docs/guides/meta/summarize[18] UNPIVOT: https://duckdb.org/docs/sql/statements/unpivot[19] STRUCT: https://duckdb.org/docs/sql/data_types/struct[20] 示例数据集: https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset[21] Hugging Face: https://huggingface.co/[22] DuckDB 可访问的 Parquet 文件: https://huggingface.co/blog/hub-duckdb[23] STRUCT.* 语法: https://duckdb.org/docs/sql/data_types/struct#struct