你的位置:首页 > 数据库

[数据库]Grouping Sets:CUBE和ROLLUP从句


在上一篇文章里我讨论了SQL Server里Grouping Sets的功能。从文中的例子可以看到,通过简单定义需要的分组集是很容易进行各自分组。但如果像从所给的列集里想要有所有可能的分布——即所谓的幂集(Power Set),要怎么做呢?

当然,你可以用grouping set的语法功能来手动生成幂集,但那需要写一大堆的代码。因此今天我向你展示下grouping set功能支持的2个从句:CUBEROLLUP从句。

CUBE从句

使用CUBE从句,对于提供的列集,你可以生成所有可能的分组集。这就是所谓的幂集。当你有3列:a,b,和c。CUBE(a,b,c会为你生成下列分组:

  • (a,b,c)
  • (a,b)
  • (b,c)
  • (a)
  • (b)
  • (c)
  • ()

下列查询对CustomerID, SalesPersonID和YEAR(OrderDate) 列通过上周介绍的grouping set功能手工生成幂集。 

 1 -- Calculates the power set of CustomerID, SalesPersonID, YEAR(OrderDate) 2 SELECT 3   CustomerID,  4   SalesPersonID,  5   YEAR(OrderDate) AS 'OrderYear',  6   SUM(TotalDue) AS 'TotalDue' 7 FROM Sales.SalesOrderHeader 8 WHERE SalesPersonID IS NOT NULL 9 GROUP BY GROUPING SETS10 (11   (CustomerID, SalesPersonID, YEAR(OrderDate)),12   (CustomerID, SalesPersonID),13   (CustomerID, YEAR(OrderDate)),14   (SalesPersonID, YEAR(OrderDate)),15   (CustomerID),16   (SalesPersonID),17   (YEAR(OrderDate)),18   ()19 )20 GO

从代码里可以看到,你必须指定每个可能的组合。因此用简单的需求写出这样的查询是个很困难的,笨重的工作。如果你使用CUBE从句而不是指定各个分组集的话,事情就变得简单多了。我们来看下面的代码。

 1 -- Calculates the power set of CustomerID, SalesPersonID, YEAR(OrderDate) with the CUBE subclause 2 SELECT 3   CustomerID,  4   SalesPersonID,  5   YEAR(OrderDate) AS 'OrderYear',  6   SUM(TotalDue) AS 'TotalDue' 7 FROM Sales.SalesOrderHeader 8 WHERE SalesPersonID IS NOT NULL 9 GROUP BY CUBE(CustomerID, SalesPersonID, YEAR(OrderDate))10 GO

从代码里可以看到,你只要指定列,SQL Server本身就会生成它的幂集。于第一次列出的代码,这个代码简单,精炼很多。 

ROLLUP从句

CUBE从句外,自SQL Server 2008起,SQL Server也支持ROLLUP从句。使用ROLLUP从句你可以定义幂集的子集。ROLLUP从句也假设各个列间的层级。当你有3列:a,b,和c。当你使用ROLLUP(a,b,c),它会生成下列分组集:

  • (a,b,c)
  • (a,b)
  • (a)
  • ()

从这些独立的分组集,你很容易看到在这些列之间有个层级。我们换用CustomerID, SalesPersonID和YEAR(OrderDate) 列,这里你就可以获得这类分析查询的实现思路。这是SSAS(SQL Server分析服务)的穷人做法。我们来看下列使用ROLLUP从句的查询: 

-- Calculates the following grouping sets:-- => (OrderYear, OrderMonth, OrderDay)-- => (OrderYear, OrderMonth)-- => (OrderYear)-- => ()SELECT  YEAR(OrderDate) AS 'OrderYear',   MONTH(OrderDate) AS 'OrderMonth',  DAY(OrderDate) AS 'OrderDay',  SUM(TotalDue) AS 'TotalDue'FROM Sales.SalesOrderHeaderWHERE SalesPersonID IS NOT NULLGROUP BY ROLLUP(YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate))GO

这个查询的输出给你下列各自分组集: 

  • (OrderYear, OrderMonth, OrderDay)
  • (OrderYear, OrderMonth)
  • (OrderYear)
  • ()

ROLLUP从句有非常简单的语法,但对于数据分析来说你的返回结果是非常强大的。 

小结

我希望你对今天文章里,自SQL Server 2008引入的grouping sets功能里的CUBE和ROLLUP子句的介绍有所收获。有空的话,不要吝啬你的留言,告诉我你是否已经在你自己的数据库里使用这些从句,或者你是否认为在你的环境里它们是有用的。

感谢关注!