何时使用GROUPING SETS、CUBE和ROLLUP

Question

我最近学习了GROUPING SETS、CUBE和ROLLUP在SQL Server中定义多个分组集的功能。

我的问题是，在什么情况下我们会使用这些功能？使用它们有哪些好处和优势？

SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY GROUPING SETS ( ( shipperid, YEAR(shippeddate) ), ( shipperid ), ( YEAR(shippeddate) ), ( ) );


SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY CUBE( shipperid, YEAR(shippeddate) );


SELECT shipcountry, shipregion, shipcity, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY ROLLUP( shipcountry, shipregion, shipcity );

- June

Answer 1

首先，对于那些尚未了解此主题的人：

• 使用 ROLLUP、CUBE 和 GROUPING SETS 的 GROUP BY

话虽如此，请不要认为这些分组选项是获取结果集的方式。这些是性能工具。

以ROLLUP为简单例子。

我可以使用以下查询来获取每个 GrpCol 值的记录计数。

SELECT   GrpCol, count(*) AS cnt
FROM     dbo.MyTable
GROUP BY GrpCol

我可以使用以下查询来汇总“卷起”所有记录的计数。

SELECT   NULL, count(*) AS cnt
FROM     dbo.MyTable

我可以使用UNION ALL将上述两个查询组合起来，以获得与使用ROLLUP子句编写第一个查询时可能获得的完全相同的结果（这就是我在其中放置NULL的原因）。

实际上，将此作为两个不同的查询执行可能更方便，因为这样我就可以将分组结果与总计分开。为什么要将最终总数混合到那些结果中呢？答案是使用ROLLUP子句同时进行两者更有效率。SQL Server将使用一次计算所有汇总的执行计划。相比之下，UNION ALL示例提供完全相同的结果，但使用不太高效的执行计划（而不是一个）进行了两次表扫描。

想象一个极端的例子，你正在处理一个数据集，每次扫描数据都需要一个小时。你必须每天对基本上每个可能的维度（切片方式）进行汇总。啊哈！我敢打赌其中一种分组选项正是你所需的。如果你将那一个扫描的结果保存到特殊的模式布局中，然后你就能从已保存的结果运行报告，直到一整天的结束。

所以，我基本上是在说你正在从事数据仓库项目。对于我们大多数人来说，它主要属于“有趣的知识”类别。

- SurroundedByFish

---

并不总是可以简单地进行汇总以获得相同的结果，例如在执行COUNT DISTINCT时。 - mattinbits

Answer 2

CUBE是使用所有可能的组合，与GROUPING SETS相同。

因此，这个（使用CUBE）

GROUP BY CUBE (C1, C2, C3, ..., Cn-2, Cn-1, Cn)

使用 GROUPING SETS，与此相同。

GROUP BY GROUPING SETS (
     (C1, C2, C3, ..., Cn-2, Cn-1, Cn) -- All dimensions are included.
    ,( , C2, C3, ..., Cn-2, Cn-1, Cn) -- n-1 dimensions are included.
    ,(C1, C3, ..., Cn-2, Cn-1, Cn)
    …
    ,(C1, C2, C3, ..., Cn-2, Cn-1,)
    ,(C3, ..., Cn-2, Cn-1, Cn) -- n-2 dimensions included
    ,(C1  ..., Cn-2, Cn-1, Cn)
    …
    ,(C1, C2) -- 2 dimensions are included.
    ,…
    ,(C1, Cn)
    ,…
    ,(Cn-1, Cn)
    ,…
    ,(C1) -- 1 dimension included
    ,(C2)
    ,…
    ,(Cn-1)
    ,(Cn)
    ,() ) -- Grand total, 0 dimension is included.

如果您不需要所有组合，应该使用GROUPING SETS而不是CUBE。

ROLLUP和CUBE运算符生成一些相同的结果集，并执行一些与OLAP应用程序相同的计算。 CUBE运算符生成可用于交叉制表报告的结果集。 ROLLUP操作可以计算等效于OLAP维度或层次结构。

单击此处查看Grouping Sets的等效项

---

更新

我认为这里举个例子会有所帮助。假设您有一个按国家和性别分类的UFO目击次数表格，如下所示:

╔═════════╦═══════╦═════════╗
║ COUNTRY ║ GENDER║ #SIGHTS ║
╠═════════╬═══════╬═════════╣
║ USA     ║ F     ║     450 ║
║ USA     ║ M     ║    1500 ║
║ ITALY   ║ F     ║     704 ║
║ ITALY   ║ M     ║     720 ║
║ SWEDEN  ║ F     ║     317 ║
║ SWEDEN  ║ M     ║     310 ║
║ BRAZIL  ║ F     ║     144 ║
║ BRAZIL  ║ M     ║     159 ║
╚═════════╩═══════╩═════════╝

如果您想了解每个国家的男女总数和总计，那么您应该使用 GROUPING SETS

 select Country, Gender, sum(Number_Of_Sights)
 from Table1
 group by GROUPING SETS((Country), (Gender), ())
 order by Country, Gender

SQL Fiddle

要使用GROUP BY获得相同的结果，您可以使用UNION ALL：

select Country, NULL Gender, sum(Number_Of_Sights)
from Table1
GROUP BY Country
UNION ALL
select NULL Country, Gender, sum(Number_Of_Sights)
from Table1
GROUP BY GENDER
UNION ALL
SELECT NULL Country, NULL Gender, sum(Number_Of_Sights)
FROM TABLE1
ORDER BY COUNTRY, GENDER

SQL Fiddle

然而，使用CUBE无法获得相同的结果，因为它将返回所有可能性。

现在，如果你想知道所有可能的组合，那么你应该使用CUBE。

- Nizam

---

优秀！谢谢。 - June

您可能想使用“性别”一词，而不是“流派”。 - Paul Gorbas

这是一个详细的回答和帖子。我认为它应该是最佳答案，但有些人不想读太多的文字。但是谢谢。 - The Original Android

Answer 3

我发现在生成报告时，如果结果无法在客户端内部汇总，则它们非常有用。

例如，如果您正在使用 COUNT(DISTINCT...) 进行某些操作，则在更大的组中的结果不一定与部分之和相同。例如，在两个单独的日期内，您可能有1500个访问者和2000个访问者，但总计可能介于2000和3500之间，具体取决于重叠情况。在客户端中执行此操作很好，但由于客户端无法确定重叠情况，因此可以使用 GROUPING SETS 提供答案（然后处理报告中传递的额外行）。

- Rob Farley