sql - 何时使用 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 );

Answer 1

首先，对于那些还没有阅读过该主题的人:

• Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

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

让我们以ROLLUP作为一个简单的例子。

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

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

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

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

如果我使用 ROLLUP 子句编写第一个查询，我可以将上述两个查询合并起来，以获得完全相同的结果(这就是为什么我将 NULL 放在那里)。

对于我来说，将其作为两个不同的查询执行实际上可能更方便，因为这样我就可以将分组结果与总计分开。为什么我希望将最终总数与其余结果混合在一起？答案是使用 ROLLUP 子句同时执行这两项操作会更有效。 SQL Server 将使用一个执行计划一次性计算所有聚合。将其与 UNION ALL 示例进行比较，该示例将提供完全相同的结果，但使用效率较低的执行计划(两次表扫描而不是一次)。

想象一个极端的例子，您正在处理一个非常大的数据集，以至于每次扫描数据都需要整整一个小时。您必须每天提供该数据基本上每个可能维度(切片方式)的总计。啊哈!我敢打赌，这些分组选项之一正是您所需要的。如果您将一次扫描的结果保存到特殊的架构布局中，那么您将能够根据保存的结果运行当天剩余时间的报告。

所以我基本上是说您正在开发一个数据仓库项目。对于我们其他人来说，它主要属于“需要知道的好事情”类别。