DuckDB: 两种方法实现动态分组查询
在使用关系数据库时，你可能经常遇到需要根据不同的标准生成动态查询的场景。其中一个场景是按多列动态分组数据并计算聚合值，例如sum或average。在本文中，我们将演示如何使用DuckDB实现这一点，重点关注GROUPING SETS和CUBE功能。
需求提出
首先准备数据，创建表及数据准备如下：
CREATE TABLE sample_table (
YEAR INTEGER,
BRAND VARCHAR,
PRODUCT VARCHAR,
SALES INTEGER
);
INSERT INTO sample_table (YEAR, BRAND, PRODUCT, SALES) VALUES
(2023, 'AX', 'A', 10),
(2024, 'AX', 'A', 20),
(2024, 'AX', 'B', 70),
(2022, 'AY', 'C', 20),
(2023, 'AY', 'C', 90);
给定这些数据，我们希望创建宏，使我们能够按列（如BRAND和PRODUCT）动态分组，并计算总销售额。以下是实现该目标的方法。
静态SQL查询方案
可以使用以下SQL查询静态地实现所需的结果：
SELECT YEAR, BRAND, PRODUCT, SUM(SALES)
FROM sample_table
GROUP BY YEAR, GROUPING SETS(CUBE(BRAND, PRODUCT));
这将生成跨不同年份值的BRAND和PRODUCT的各种组合的总和。输出结果如下：
┌───────┬─────────┬─────────┬────────────┐
│ YEAR │ BRAND │ PRODUCT │ sum(SALES) │
│ int32 │ varchar │ varchar │ int128 │
├───────┼─────────┼─────────┼────────────┤
│ 2022 │ │ │ 20 │
│ 2022 │ AY │ │ 20 │
│ 2023 │ │ │ 100 │
│ 2022 │ AY │ C │ 20 │
│ 2023 │ │ A │ 10 │
│ 2024 │ │ A │ 20 │
│ 2022 │ │ C │ 20 │
│ 2024 │ │ B │ 70 │
│ 2024 │ │ │ 90 │
│ 2023 │ AX │ │ 10 │
│ 2024 │ AX │ │ 90 │
│ 2023 │ AX │ A │ 10 │
│ 2024 │ AX │ B │ 70 │
│ 2023 │ AY │ C │ 90 │
│ 2023 │ AY │ │ 90 │
│ 2024 │ AX │ A │ 20 │
│ 2023 │ │ C │ 90 │
├───────┴─────────┴─────────┴────────────┤
│ 17 rows 4 columns │
└────────────────────────────────────────┘
动态SQL查询方案
创建动态解决方案稍微复杂一些。在这里，我们将介绍两种方法：使用宏和使用存储过程。
执行动态SQL
在 DuckDB 中，json_serialize_sql函数的作用是将 DuckDB 的数据结构（如结构体、数组等）序列化为 JSON 格式的字符串。json_execute_serialized_sql函数用于执行包含在 JSON 序列化格式中的 SQL 语句。这是一种以灵活的方式来存储和执行 SQL 语句，特别是在处理动态 SQL 场景或从外部数据源接收 SQL 指令（以 JSON 格式存储）时非常有用。
构造一个 JSON 字符串，其中的 SQL 语句用于查询 employees 表中年龄大于某个指定值的员工信息，同时设置参数，示例如下：
-- 创建宏
CREATE OR REPLACE MACRO filter_emps(min_age) AS TABLE(
FROM json_execute_serialized_sql(
json_serialize_sql(
format('SELECT name, age FROM employees WHERE age > {0}', min_age)
)
)
);
执行宏：
D from filter_emps(30);
┌─────────┬───────┐
│ name │ age │
│ varchar │ int32 │
├─────────┼───────┤
│ Bob │ 35 │
└─────────┴───────┘
宏可以实现类似带参数的视图功能，非常灵活。下面利用宏实现动态分组功能。
方法1：自定义宏
要动态构建查询，可以使用json_execute_serialized_sql函数来运行它。下面是如何在DuckDB中实现这一点的示例：
CREATE OR REPLACE MACRO msum(tbl, grpcols) AS TABLE (
FROM json_execute_serialized_sql(json_serialize_sql(
format('
FROM {0}
SELECT {1}, SUM(SALES)
GROUP BY YEAR, GROUPING SETS(CUBE({1}))',
tbl,
list_reduce(grpcols, (x, y) -> format('{0},{1}', x, y))
)
))
);
通过下面语句调用宏示例：
FROM msum(sample_table, ['BRAND', 'YEAR,PRODUCT']);
返回结果如下：
D FROM msum(sample_table, ['BRAND', 'PRODUCT']);
┌─────────┬─────────┬────────────┐
│ BRAND │ PRODUCT │ sum(SALES) │
│ varchar │ varchar │ int128 │
├─────────┼─────────┼────────────┤
│ │ │ 100 │
│ │ │ 20 │
│ AY │ C │ 20 │
│ │ │ 90 │
│ AY │ │ 20 │
│ AX │ │ 10 │
│ AX │ │ 90 │
│ │ C │ 90 │
│ │ A │ 10 │
│ │ A │ 20 │
│ │ C │ 20 │
│ AX │ A │ 10 │
│ AX │ B │ 70 │
│ AY │ C │ 90 │
│ AY │ │ 90 │
│ AX │ A │ 20 │
│ │ B │ 70 │
├─────────┴─────────┴────────────┤
│ 17 rows 3 columns │
└────────────────────────────────┘
当然，你也可以传递多个参数：
FROM msum(sample_table, ['BRAND', 'YEAR,PRODUCT']);
结果与静态SQL查询示例相同。
方法2：使用存储过程
另一种方法是使用存储过程动态生成和执行SQL查询。这里有一个例子：
CREATE OR REPLACE PROCEDURE dynamic_grouping_sum(
IN GRPCOLS VARCHAR -- Pass column names as a comma-separated string like 'BRAND,PRODUCT'
)
BEGIN
DECLARE sql_query VARCHAR;
-- Construct the SQL query dynamically
SET sql_query = CONCAT(
'SELECT YEAR, ', GRPCOLS, ', SUM(SALES) AS total_sales ',
'FROM sample_table ',
'GROUP BY YEAR, GROUPING SETS(CUBE(', GRPCOLS, '));'
);
-- Execute the dynamically created SQL
EXECUTE IMMEDIATE sql_query;
END;
使用下面语句调用存储过程：
CALL dynamic_grouping_sum('BRAND, PRODUCT');
存储过程实际执行的语句为：
SELECT YEAR, BRAND, PRODUCT, SUM(SALES) AS total_sales
FROM sample_table
GROUP BY YEAR, GROUPING SETS(CUBE(BRAND, PRODUCT));
总结
这两种方法都支持动态生成SQL查询，以便根据指定列对数据进行分组和聚合。宏在DuckDB中提供了一种灵活的脚本选项，而存储过程则为习惯了传统RDBMS系统的用户提供了一种更结构化、更熟悉的方法。最佳选择取决于你的应用场景和编码风格。