Grouping Sets:CUBE和ROLLUP从句

时间：2021-12-22 12:55:57

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

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

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功能手工生成幂集。

 -- Calculates the power set of CustomerID, SalesPersonID, YEAR(OrderDate)

 SELECT

     CustomerID,

     SalesPersonID,

     YEAR(OrderDate) AS 'OrderYear',

     SUM(TotalDue) AS 'TotalDue'

 FROM Sales.SalesOrderHeader

 WHERE SalesPersonID IS NOT NULL

 GROUP BY GROUPING SETS

 (

     (CustomerID, SalesPersonID, YEAR(OrderDate)),

     (CustomerID, SalesPersonID),

     (CustomerID, YEAR(OrderDate)),

     (SalesPersonID, YEAR(OrderDate)),

     (CustomerID),

     (SalesPersonID),

     (YEAR(OrderDate)),

     ()

 )

 GO

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

 -- Calculates the power set of CustomerID, SalesPersonID, YEAR(OrderDate) with the CUBE subclause

 SELECT

     CustomerID,

     SalesPersonID,

     YEAR(OrderDate) AS 'OrderYear',

     SUM(TotalDue) AS 'TotalDue'

 FROM Sales.SalesOrderHeader

 WHERE SalesPersonID IS NOT NULL

 GROUP BY CUBE(CustomerID, SalesPersonID, YEAR(OrderDate))

 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.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY ROLLUP(YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate))

GO

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

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

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

小结

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

感谢关注！

参考文章：

https://www.sqlpassion.at/archive/2014/09/22/grouping-sets-the-cube-and-rollup-subclauses/

相关文章