SQL存储过程怎么用GROUPING SETS实现动态分组聚合

在业务数据分析场景中，经常需要同时按照不同维度组合统计汇总数据，比如既要按地区统计销售额，也要按地区加年份统计，还要看整体 totals。如果针对每种分组组合单独写查询语句，会产生大量重复代码，维护成本也很高。GROUPING SETS是SQL提供的高级分组聚合功能，可以在一条查询中定义多个分组组合，一次性返回所有聚合结果，结合存储过程可以实现动态适配不同维度需求的灵活查询。

GROUPING SETS基础用法

GROUPING SETS允许在GROUP BY子句中指定多个分组组合，每个组合会独立计算聚合结果，最终结果会合并返回。比如我们需要同时统计按地区、按年份、按地区和年份组合、以及整体的销售额，就可以用如下语句实现：

-- 基础GROUPING SETS示例，统计销售数据
SELECT 
    region,
    sale_year,
    SUM(sale_amount) AS total_sale,
    -- GROUPING函数返回1表示该列在当前分组中未被使用，返回0表示被使用
    GROUPING(region) AS is_region_grouped,
    GROUPING(sale_year) AS is_year_grouped
FROM sales_data
GROUP BY GROUPING SETS (
    (region),          -- 仅按地区分组
    (sale_year),       -- 仅按年份分组
    (region, sale_year), -- 按地区和年份组合分组
    ()                 -- 整体聚合，无分组维度
)
ORDER BY region, sale_year;

上述查询会返回四种分组组合的聚合结果，其中GROUPING()函数可以用来判断当前行的聚合是哪个分组组合产生的，方便后续业务区分不同层级的汇总数据。

存储过程中实现动态分组聚合

如果分组维度是动态的，比如用户可以选择任意1个或多个维度进行组合分组，就需要在存储过程中拼接动态SQL，结合GROUPING SETS实现灵活查询。下面以MySQL存储过程为例，实现动态分组聚合功能。

存储过程实现逻辑

• 接收输入参数：需要分组的维度列名列表，以及表名、聚合字段等基础信息
• 校验输入参数的合法性，避免SQL注入风险
• 动态拼接GROUPING SETS的分组组合，支持单维度、多维度组合以及整体聚合
• 执行动态SQL并返回结果

完整存储过程代码

-- 创建存储过程，实现动态分组聚合
DELIMITER //
CREATE PROCEDURE dynamic_grouping_aggregate(
    IN table_name VARCHAR(100),       -- 要查询的表名
    IN group_columns VARCHAR(500),    -- 分组维度列名，多个列用逗号分隔，如'region,sale_year'
    IN aggregate_column VARCHAR(100), -- 要聚合的字段名
    IN aggregate_func VARCHAR(20)     -- 聚合函数，如'SUM','COUNT','AVG'
)
BEGIN
    DECLARE sql_str TEXT;             -- 拼接的动态SQL字符串
    DECLARE grouping_sets_str TEXT;   -- GROUPING SETS部分的分组组合字符串
    DECLARE col_list TEXT;            -- 处理后的分组列名列表
    DECLARE col_array TEXT;           -- 分组列名拆分后的数组临时变量
    DECLARE i INT DEFAULT 1;
    DECLARE col_count INT;
    DECLARE current_col VARCHAR(100);
    DECLARE temp_set_str TEXT;
    
    -- 基础校验，避免空参数
    IF table_name = '' OR group_columns = '' OR aggregate_column = '' OR aggregate_func = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '所有输入参数不能为空';
    END IF;
    
    -- 处理分组列名，拆分逗号分隔的字符串
    SET col_list = REPLACE(group_columns, ' ', ''); -- 去除空格
    SET col_count = CHAR_LENGTH(col_list) - CHAR_LENGTH(REPLACE(col_list, ',', '')) + 1;
    
    -- 拼接GROUPING SETS的分组组合，包含单维度、多维度组合、整体聚合
    SET grouping_sets_str = '(';
    
    -- 1. 添加单维度分组组合
    SET i = 1;
    WHILE i <= col_count DO
        SET current_col = SUBSTRING_INDEX(SUBSTRING_INDEX(col_list, ',', i), ',', -1);
        SET grouping_sets_str = CONCAT(grouping_sets_str, '(', current_col, ')');
        IF i < col_count THEN
            SET grouping_sets_str = CONCAT(grouping_sets_str, ',');
        END IF;
        SET i = i + 1;
    END WHILE;
    
    -- 2. 添加多维度组合分组（所有维度一起分组）
    SET grouping_sets_str = CONCAT(grouping_sets_str, ', (', col_list, ')');
    
    -- 3. 添加整体聚合（空分组）
    SET grouping_sets_str = CONCAT(grouping_sets_str, ', ()');
    
    SET grouping_sets_str = CONCAT(grouping_sets_str, ')');
    
    -- 拼接完整动态SQL
    SET sql_str = CONCAT(
        'SELECT ',
        col_list, ',',
        aggregate_func, '(', aggregate_column, ') AS aggregate_result,',
        'GROUPING(', REPLACE(col_list, ',', '), GROUPING('), ') AS grouping_flag ',
        'FROM ', table_name, ' ',
        'GROUP BY GROUPING SETS ', grouping_sets_str, ' ',
        'ORDER BY ', REPLACE(col_list, ',', ', ')
    );
    
    -- 执行动态SQL
    PREPARE stmt FROM sql_str;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

存储过程调用示例

假设我们有销售表sales_data，包含region（地区）、sale_year（销售年份）、sale_amount（销售金额）三个字段，需要动态按地区和年份分组聚合：

-- 调用存储过程，按地区和年份动态分组聚合，计算销售金额总和
CALL dynamic_grouping_aggregate('sales_data', 'region,sale_year', 'sale_amount', 'SUM');

执行后会返回按地区、按年份、按地区和年份组合、以及整体销售额的所有聚合结果，和之前基础GROUPING SETS示例的返回结果一致，但是不需要手动写死分组组合，只需要传入维度列名即可。

注意事项

• 动态SQL拼接时需要对输入参数做合法性校验，避免传入非法的列名或表名导致SQL注入风险，生产环境可以增加列名白名单校验逻辑
• 不同数据库的GROUPING SETS语法略有差异，比如SQL Server和Oracle也支持该功能，但是动态SQL的拼接语法需要根据对应数据库调整
• 如果分组维度过多，GROUPING SETS生成的分组组合会呈指数级增长，可能影响查询性能，需要根据实际业务场景限制最大分组维度数量
• GROUPING()函数的返回值可以用来区分不同层级的汇总数据，比如返回1的列表示该维度在当前分组中未被使用，属于更高层级的汇总行

SQL存储过程GROUPING_SETS分组聚合修改时间：2026-07-19 10:21:16