我最近了解了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 );
SurroundedBy.. 25
首先,对于尚未阅读过该主题的人:
将GROUP BY与ROLLUP,CUBE和GROUPING SETS一起使用
话虽如此,不要将这些分组选项视为获得结果集的方法.这些是性能工具.
我们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
提供完全相同结果的示例进行比较,但使用效率较低的执行计划(两次表扫描而不是一次).
想象一个极端的例子,你正在处理一个如此大的数据集,每次扫描数据需要一整个小时.您必须每天为每个可能的维度(切片方式)提供总计数据.啊哈!我敢打赌,其中一个分组选项正是您所需要的.如果将一次扫描的结果保存到特殊模式布局中,则可以在保存结果的当天剩余时间内运行报告.
所以我基本上说你正在开发一个数据仓库项目.对于我们其他人来说,它主要属于"整洁的事物"类别.
首先,对于尚未阅读过该主题的人:
将GROUP BY与ROLLUP,CUBE和GROUPING SETS一起使用
话虽如此,不要将这些分组选项视为获得结果集的方法.这些是性能工具.
我们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
提供完全相同结果的示例进行比较,但使用效率较低的执行计划(两次表扫描而不是一次).
想象一个极端的例子,你正在处理一个如此大的数据集,每次扫描数据需要一整个小时.您必须每天为每个可能的维度(切片方式)提供总计数据.啊哈!我敢打赌,其中一个分组选项正是您所需要的.如果将一次扫描的结果保存到特殊模式布局中,则可以在保存结果的当天剩余时间内运行报告.
所以我基本上说你正在开发一个数据仓库项目.对于我们其他人来说,它主要属于"整洁的事物"类别.
在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维度或层次结构的等效项.
看这里看分组集等价物
UPDATE
我想一个例子在这里会有所帮助.假设您有按国家和性别分列的不明飞行物数量表,如下图所示:
????????????????????????????? ? 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小提琴
要获得相同的结果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小提琴
但是不可能用CUBE获得相同的结果,因为它将返回所有可能性.
现在,如果你想知道所有可能的组合,那么你应该使用 CUBE
我发现当你制作报告时它们很好,结果不是可以在客户端内汇总的东西.
例如,如果您正在执行某些操作,COUNT(DISTINCT...)
则较大组中的结果不一定与各部分的总和相同.例如,在两个单独的日子里,您可能有1500个访问者和2000个访问者,但总数可能在2000到3500之间,具体取决于重叠.在客户端中执行此操作很好,但由于客户端无法分辨重叠是什么,您可以使用它GROUPING SETS
来提供答案(然后处理报告中出现的额外行).