如何在DuckDB中通过宏实现按列列表进行GROUP BY分组？

数据结构与样本数据 #

测试用表结构及初始化数据：

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完全等效的查询结果：

SELECT YEAR, BRAND, PRODUCT, SUM(SALES) 
FROM SAMPLE_TABLE 
GROUP BY YEAR, GROUPING SETS(CUBE(BRAND, PRODUCT));

错误尝试与问题分析 #

用户最初编写的宏代码：

CREATE OR REPLACE MACRO MSUM(
    GRPCOLS
) AS TABLE (
    FROM TBL
    SELECT 
        COLUMNS(C -> (LIST_CONTAINS(GRPCOLS, C))),
        SUM(SALES)
    GROUP BY YEAR, GROUPING SETS(CUBE(COLUMNS(C -> LIST_CONTAINS(GRPCOLS, C))))
);

WITH TBL AS (SELECT * FROM SAMPLE_TABLE)
FROM MSUM([BRAND, PRODUCT]);

执行后触发报错：

Binder Error: STAR expression is not supported here

问题根源：COLUMNS()属于星型表达式，Snowflake不允许在GROUP BY子句中使用该类表达式。

可行解决方案 #

核心思路 #

通过动态SQL生成合法的GROUP BY子句：将传入的列名列表展开并拼接成标准列名序列，嵌入到CUBE()中，规避星型表达式的限制。

正确宏定义 #

CREATE OR REPLACE MACRO MSUM(grp_cols) AS TABLE (
    EXECUTE IMMEDIATE $$
        SELECT
            YEAR,
            $$ || LISTAGG(VALUE, ', ') FROM TABLE(FLATTEN(INPUT => ?)) || $$,
            SUM(SALES) AS total_sales
        FROM sample_table
        GROUP BY YEAR, GROUPING SETS(CUBE( $$ || LISTAGG(VALUE, ', ') FROM TABLE(FLATTEN(INPUT => ?)) || $$ ))
    $$ USING grp_cols, grp_cols
);

调用示例 #

直接传入列名列表即可执行查询：

SELECT * FROM MSUM(['BRAND', 'PRODUCT']);

结果说明 #

执行后会生成与目标SQL完全一致的聚合结果，包含YEAR、指定分组列的所有CUBE组合，以及对应的销售总额。

内容的提问来源于stack exchange，提问作者Rooh