SQLGROUPINGSETS怎么使用_SQLGROUPINGSETS灵活分组方法

絕刀狂花

发布时间：2025-09-16 18:22:01

296人浏览过

来源于php中文网

原创

GROUPING SETS允许在一个查询中生成多维度聚合结果，简化复杂报表。通过一次数据扫描实现总销售额、按地区、按年份及组合分组的汇总，相比UNION ALL减少多次表扫描，提升性能。其核心是GROUP BY后指定多个分组组合，如(GROUPING SETS ((Year, Region), (Year), (Region), ()))，并可用GROUPING函数标识聚合层级。相比ROLLUP（生成层次汇总）和CUBE（生成所有组合），GROUPING SETS更灵活，适用于定制化聚合需求，广泛用于多维报表、财务分析、ETL预聚合及BI数据准备场景，显著提高查询效率与代码可维护性。

sqlgroupingsets怎么使用_sqlgroupingsets灵活分组方法

SQL GROUPING SETS

是一种非常灵活且强大的SQL聚合功能，它允许你在一个单独的查询中，生成多个不同维度或粒度的分组聚合结果，而无需编写多个

GROUP BY

语句再用

UNION ALL

连接起来。简单来说，它能让你用一次数据扫描，就得到多种你想要的汇总数据视图，比如总销售额、按地区销售额、按产品销售额，甚至是按地区和产品组合的销售额，极大地简化了复杂报表的生成过程。

解决方案

在使用

GROUPING SETS

时，我们不再需要为每一种聚合需求单独写一个

SELECT ... GROUP BY

语句，然后用

UNION ALL

把它们拼起来。这在处理多维报表或者需要同时查看不同聚合层级数据时，效率和可维护性都会大打折扣。

GROUPING SETS

的核心思想是，你在

GROUP BY

子句后面，明确指定你希望生成哪些不同的分组组合。

我们来看一个具体的例子。假设我们有一个

Sales

表，包含

Year

（年份）、

Region

（地区）、

Product

（产品）和

Amount

（销售额）字段。现在，我们想同时看到以下几种销售额：

总销售额（不按任何维度分组）
按年份的销售额
按地区的销售额
按年份和地区组合的销售额

如果用传统方法，你可能需要写四个

SELECT ... GROUP BY

语句，然后

UNION ALL

。但有了

GROUPING SETS

，一个查询就能搞定：

SELECT
    Year,
    Region,
    SUM(Amount) AS TotalAmount
FROM
    Sales
GROUP BY
    GROUPING SETS (
        (Year, Region), -- 按年份和地区分组
        (Year),         -- 仅按年份分组
        (Region),       -- 仅按地区分组
        ()              -- 不分组，即总计
    )
ORDER BY
    Year, Region;

在这个查询中，

GROUPING SETS

后面的括号里，每一个子括号都代表一个独立的

GROUP BY

组合。

```
(Year, Region)
```
：这会生成按年份和地区分组的销售额。
```
(Year)
```
：这会生成仅按年份分组的销售额，此时
```
Region
```
列会显示
```
NULL
```
，表示这个聚合结果不区分地区。
```
(Region)
```
：这会生成仅按地区分组的销售额，此时
```
Year
```
列会显示
```
NULL
```
。
```
()
```
：这表示不按任何列分组，生成的是所有数据的总销售额，此时
```
Year
```
和
```
Region
```
都会显示
```
NULL
```
。

通过观察结果中

Year

和

Region

列的

NULL

值，我们就能区分出不同的聚合层级。为了让结果更清晰，SQL还提供了

GROUPING(column_name)

函数，它会返回0（如果该列参与了当前分组）或1（如果该列没有参与当前分组，即为聚合的“超行”）。

SELECT
    Year,
    Region,
    SUM(Amount) AS TotalAmount,
    GROUPING(Year) AS IsYearAggregated,
    GROUPING(Region) AS IsRegionAggregated
FROM
    Sales
GROUP BY
    GROUPING SETS (
        (Year, Region),
        (Year),
        (Region),
        ()
    )
ORDER BY
    Year, Region;

这样，

IsYearAggregated

和

IsRegionAggregated

就能更明确地指示每一行数据代表的聚合级别。

为什么GROUPING SETS比UNION ALL更高效？

我记得有一次，面对一个需要几十种组合聚合的报表需求，如果用

UNION ALL

，那查询语句简直是噩梦，维护起来更是灾难。

GROUPING SETS

简直是救星，代码量直接砍掉一大半，而且跑得飞快。这背后是有原因的：

首先，性能上的巨大优势是显而易见的。当使用

UNION ALL

时，数据库通常需要对基表进行多次扫描（每个

SELECT

语句至少扫描一次）。这意味着如果你的表很大，数据会被读取和处理多次，I/O和CPU开销都会成倍增加。而

GROUPING SETS

则不同，它通常只需要对基表进行一次扫描。数据库的查询优化器能够识别出

GROUPING SETS

的意图，在一次数据读取和处理的过程中，并行或顺序地计算出所有指定的分组聚合结果。这种单次扫描的机制，在处理大数据量时，能带来非常显著的性能提升。

其次，从数据库优化器的角度来看，

GROUPING SETS

提供了一个更清晰的优化路径。优化器可以更好地规划执行策略，比如利用共享的排序操作或哈希聚合，从而减少重复计算。而

UNION ALL

拼接的多个独立查询，优化器可能无法在它们之间找到这种共享优化的机会。

再者，代码的简洁性和可维护性也是一个重要考量。一个复杂的

UNION ALL

查询可能会有几十甚至上百行，任何一个聚合逻辑的微小改动，都可能导致你需要修改多个

SELECT

子句，容易出错。

GROUPING SETS

则将所有聚合逻辑集中在一个

GROUP BY

子句中，代码量大大减少，也更容易阅读和维护。对我来说，这种清晰的表达方式本身就是一种效率。

当然，对于非常简单的，只有一两个聚合组合的场景，

UNION ALL

和

GROUPING SETS

之间的性能差异可能不那么明显。但只要聚合组合的数量增加，或者数据量变大，

GROUPING SETS

的优势就会立刻凸显出来。

GROUPING SETS、ROLLUP和CUBE有什么区别？

在SQL的聚合功能里，

GROUPING SETS

、

ROLLUP

和

CUBE

是三个密切相关但又各有侧重的概念。我喜欢把它们想象成不同级别的“聚合套餐”：

AI Sofiya

一款AI驱动的多功能工具

下载

```
GROUPING SETS
```
：定制套餐（最灵活）
```
GROUPING SETS
```
是最通用、最灵活的选项。它就像一个菜单，你明确告诉数据库你想要哪些具体的聚合组合。比如，
```
GROUPING SETS ((A, B), (A), (C))
```
，你就指定了这三种组合。它不会自动生成你没明确指出的组合，完全按你的需求来。如果你只需要某些特定的、非连续的聚合层级，
```
GROUPING SETS
```
就是最佳选择。
```
ROLLUP
```
：分层套餐（有层次感）
```
ROLLUP
```
是
```
GROUPING SETS
```
的一个语法糖，专门用于生成层次性的聚合结果。它会从最详细的维度开始，逐步向上汇总，直到生成一个总计。它的顺序很重要。例如，
```
ROLLUP(A, B, C)
```
会生成以下
```
GROUPING SETS
```
组合：
- ```
(A, B, C)
```
  ：最详细的组合
- ```
(A, B)
```
  ：按A和B汇总
- ```
(A)
```
  ：仅按A汇总
- ```
()
```
  ：总计你会发现，它总是沿着你指定的列的顺序，生成所有前缀组合以及一个总计。这在需要生成总计、小计和明细的报表时非常方便，比如按年-月-日逐级汇总销售额。
```
CUBE
```
：豪华自助餐（所有组合）
```
CUBE
```
也是
```
GROUPING SETS
```
的语法糖，但它更“大方”。它会生成所有可能的分组组合，包括你指定列的所有排列组合以及一个总计。如果你有N个列，
```
CUBE
```
会生成
```
2^N
```
种组合。例如，
```
CUBE(A, B)
```
会生成以下
```
GROUPING SETS
```
组合：
- ```
(A, B)
```
- ```
(A)
```
- ```
(B)
```
- ```
()
```
  ：总计
```
CUBE
```
  在进行多维度分析时非常有用，因为它能一次性提供所有维度的聚合视图。但缺点是，如果维度过多，生成的组合数量会呈指数级增长，可能导致结果集非常庞大，计算量也很大，甚至包含一些你根本不需要的组合。

总结一下，

GROUPING SETS

是基石，它提供了最大的灵活性。

ROLLUP

和

CUBE

是基于

GROUPING SETS

的快捷方式，分别用于处理特定模式的聚合需求：

ROLLUP

适用于层次性汇总，

CUBE

适用于全维度交叉汇总。选择哪一个，取决于你具体需要哪些聚合组合。

在实际业务中，GROUPING SETS有哪些典型应用场景？

在我的职业生涯中，

GROUPING SETS

解决了不少让我头疼的业务问题，它的应用场景远比我们想象的要广泛，尤其是在数据分析和报表生成领域：

多维度报表生成： 这是最常见的应用。比如，销售部门需要一张报表，既要看全国总销售额，又要看各省份的销售额，还要看每个省份下不同城市的销售额，甚至细化到每个城市不同产品的销售额。如果用传统的
```
GROUP BY
```
加
```
UNION ALL
```
，那SQL语句会变得非常冗长，而且每次执行都要扫描好几次数据。
```
GROUPING SETS
```
在这里就显得非常强大，一个查询就把所有层级的数据都算出来了，大大简化了开发和维护。
财务分析与成本核算： 财务部门经常需要从不同维度来分析成本或利润，比如按部门、按项目、按成本中心、按产品线，或者这些维度的各种组合。
```
GROUPING SETS
```
能够在一个查询中快速生成这些多维度的汇总数据，帮助财务人员更全面地洞察公司的运营状况。
数据仓库ETL过程中的预聚合： 在数据仓库的ETL（抽取、转换、加载）过程中，为了提高后续BI报表查询的效率，我们经常会创建一些汇总表（Summary Tables）或聚合事实表。
```
GROUPING SETS
```
是生成这些预聚合数据的利器。它能高效地在加载数据时就计算出多种粒度的聚合结果，存储到汇总表中，这样终端用户查询时就无需实时计算，大大加快了报表响应速度。
业务智能（BI）工具的数据准备： 许多BI工具在连接数据源时，需要获取不同粒度的聚合数据。使用
```
GROUPING SETS
```
可以为BI工具提供一个包含多种聚合层级的视图，使得分析师在BI工具中进行钻取（drill-down）和切片（slice-and-dice）操作时，能够更流畅地获取数据，而不需要BI工具在后台频繁地向数据库发送复杂的聚合查询。
数据探索与临时分析： 当数据分析师在探索一个新数据集，或者需要快速验证某个假设时，往往需要从不同角度查看数据的总计和分项。
```
GROUPING SETS
```
提供了一种非常快捷的方式，在不编写大量SQL的情况下，就能一次性获取多种聚合视图，加速数据洞察的过程。