Oracle SQL包含的几个高级聚合功能可以提供报表功能，此功能通常是与OLAP报表工具相联系的。SQL CUBE, ROLLUP和GROUPING SETS是GROUP BY语句的扩展，它们通常不会被SQL开发人员使用，因为既不想了解也不想知道它们在哪里应用。特别是CUBE操作的应用范围相当小。但是，在此范围内，它所生成的结果是需要好几个单独查询才能完成的。

当用于修改GROUP BY语句时，SQL CUBE和ROLLUP关键词拥有类似的输出。当添加至GROUP BY语句时，ROLLUP关键词会生成subtotal，而SQL CUBE关键词会生成交叉表值。ROLLUP只会为从左至右读取的分组表达式外加一个合计(n+1组)生成小结。当使用CUBE关键词时，所有GROUP BY语句中使用的字段会交叉引用来产生分组的一个超集。任何SELECT列表中的聚合函数会用于超聚合记录以生成汇总数据。CUBE会为所提供表达式外加一个合计的每个可能的组合都生成subtotal。这将会产生2^n个分组(一个n维cube)。

一个由字段A和B分组的CUBE会产生4个分组(2^2=4):

• 由字段A单独分组的记录总数
• 由字段B单独分组的记录总数
• 由字段A和B共同分组的记录总数
• 合计

一个包含有SQL CUBE的语句语法如下：

SELECT [col1, col2, …], aggregate_function(col3)…
FROM   table
[WHERE  condition]
GROUP BY CUBE group_by_expr
[HAVING  having_expr]
[ORDER BY  order_by_expr]

以下是在HR样本模式中查询EMP_DETAILS_VIEW的一个示例。CUBE操作在两个字段(REGION_NAME和DEPARTMENT_NAME)上进行分组并生成雇员薪资的总和。我已经在每条记录的右边添加了一个符号以标示其属于哪个分组集合。一个标准的GROUP BY语句只会使用两个字段(A和B集合)来生成结果。而下面的CUBE示例单独通过A生成两个额外字段，单独通过B生成11个结果，另外还有所有雇员的总和。

SELECT region_name, department_name, SUM(salary)
FROM   hr.emp_details_view
GROUP BY CUBE (region_name, department_name)
ORDER BY region_name, department_name;
REGION_NAME   DEPARTMENT_NAME    SUM(SALARY)
------------- ------------------ -----------
Americas      Accounting               20308   A&B
Americas      Administration            4400   A&B
Americas      Executive                58000   A&B
Americas      Finance                  52600   A&B
Americas      IT                       28800   A&B
Americas      Marketing                19000   A&B
Americas      Purchasing               24900   A&B
Americas      Shipping                156400   A&B
Americas                              364408   A&B
Europe        Human Resources           6500   A
Europe        Public Relations         10000   A&B
Europe        Sales                   304500   A&B
Europe                                321000   A
              Accounting               20308   B
              Administration            4400   B
              Executive                58000   B
              Finance                  52600   B
              Human Resources           6500   B
              IT                       28800   B
              Marketing                19000   B
              Public Relations         10000   B
              Purchasing               24900   B
              Sales                   304500   B
              Shipping                156400   B
                                      685408   Total

要用标准GROUP BY语句生成相同结果需要四个用SET操作符进行组合的单独查询：

SELECT region_name, department_name, SUM(salary)
FROM   hr.emp_details_view
GROUP BY region_name, department_name
UNION ALL
SELECT region_name, NULL AS department_name, SUM(salary)
FROM   hr.emp_details_view
GROUP BY region_name
UNION ALL
SELECT NULL AS region_name, department_name, SUM(salary)
FROM   hr.emp_details_view
GROUP BY department_name
UNION ALL
SELECT NULL AS region_name, NULL AS department_name, SUM(salary)
FROM   hr.emp_details_view
ORDER BY region_name, department_name;

当在获得期望结果要求将相同数据分组到几个不同的集合中时，可以使用一个SQL CUBE操作做为关键标识。执行并组合单独的GROUP BY语句会比同等的CUBE操作使用更多的资源。因为这种类型的分析在记录数量庞大的数据仓库中是最为常见的，所以能够巩固操作可以显著减少查询时间。如果从一个SQL CUBE操作得到的结果明确不是所需要的，则其他诸如ROLLUP 和GROUPING SETS的高级聚合操作可能是更好的选择。当给定n个表达式时，ROLLUP操作总是会产生n+1个分组。而由字段A和B分组的一个ROLLUP会产生三个分组(2+1=3)如下：

• 由字段A单独分组的记录总数
• 由字段A和B分组的记录总数
• 合计

因为你能够明确选择查询返回到哪个集合，所以GROUPING SETS操作比起ROLLUP和CUBE显得更为灵活。它可以用来生成结果，而这些结果与CUBE或ROLLUP生成的结果是相同的，或者它还可以产生另外两个都不能够产生的变异。例如，GROUP BY CUBE( a, b)是与GROUP BY GROUPING SETS ( (a, b), (a), (b), NULL)等价的。以下的SQL语句会产生与之前的CUBE示例相同的结果：

SELECT region_name, department_name, SUM(salary)
FROM   hr.emp_details_view
GROUP BY GROUPING SETS ((region_name, department_name),
                         (region_name), (department_name),
                         NULL)
ORDER BY region_name, department_name;

当GROUP BY中包含的字段数量更大时，GROUPING SETS的灵活性就会显得更为重要。一个四字段分组会生成十六个不同的集合。如果其中五个是需要的，那么比起使用SQL CUBE操作并滤除多余集合，前者则不失为更好的选择。

每种高级聚合操作在合适的情形下都是有用的。理解返回的信息是最先要做的。如果你的工作包括创建报表、分析数据，那么迟早你会发现使用它的好处。

关于作者：

Matthew Morris 是Computer Sciences公司的数据库工程师。他曾作为一名技术支持工程师，数据库管理员，开发及架构师与Oracle数据库打了超过17年的交道。