扩展GROUP BY之CUBE与GROUPING SETS

IT那活儿 2022-12-27

1582

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

扩展GROUP BY概述

Oracle扩展GROUP BY允许使用SQL语句对数据汇总结果进行多维展现，从而生成复杂的报表，为决策者提供有效的数据支持。主要表现在：

ROLLUP、CUBE、GROUPING SETS扩展GROUP BY子句提供了不同多维分组统计功能。
3个扩展分组函数：GROUPING、GROUPING_ID、GROUP_ID提供扩展GROUP BY的辅助功能：提供区别结果行属于哪个分组级别，区分NULL值，建立有意义的报表，对汇总结果排序，过滤结果行等功能。
对扩展GROUP BY允许按重复列分组、组合列分组、部分分组、连接分组等复杂功能，另外GROUPING SETS可以接受CUBE、ROLLUP操作作为参数，这些功能使扩展GROUP BY更加强大。

通过SQL语句对上述功能的组合使用，就可以实现制作复杂的多维分析报表的功能。针对不同维度的报表统计，使用扩展GROUP BY的强大功能很容易实现，而且SQL编写更简单，性能也比同等的UNION ALL更好，在后面的内容中，我们会见识到强大的扩展GROUP BY功能。

二

CUBE

还有一种需求就是对不同维度的所有可能分组进行统计，从而生成交叉报表。这种需求比ROLLUP更加精细，包含了ROLLUP的统计结果，而且还有其它的组合分组结果(小计)。交叉报表实现的分组级别更多，从而为决策分析提供更细粒度的统计数据。CUBE就可以实现这样的需求，比如CUBE(n列)，那么分组种类有：

C0n  + C1n + C2n + … + Cnn = 2n

CUBE分组就是先进行合计(一个不取C⁰_n)，然后小计(C¹_n.到C ^n-1_n)，最后全取(标准分组Cⁿ_n)，和ROLLUP不同，CUBE计算结果和列的顺序无关，但是列顺序不同，默认的结果排序则不同，当然，我们应该使用显式排序规则，默认排序可能会变化，显式排序见GROUPING_ID函数讲解。对CUBE操作，就不用UNION ALL对比了，有兴趣的可以测试下，CUBE增加一列，则分组种类呈级数增长，使用UNION ALL改写更麻烦，而且效率比较差。

2.1 CUBE分组

CUBE使用的语法和ROLLUP类似，只不过将ROLLUP换成CUBE而已，如下所示：

SELECT … GROUP BY CUBE(grouping_column_reference_list)

在前面使用ROLLUP(dname,job)来统计标准分组，对job的小计以及合计，下面用CUBE替换ROLLUP，看一下结果的区别：

SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY CUBE(a.dname,b.job);

显示结果如下：

DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
24925
                           CLERK 3050
                            ANALYST 3000
                            MANAGER 8275
                            SALESMAN 5600
                            PRESIDENT 5000
SALES 9400
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
RESEARCH 6775
RESEARCH CLERK 800
RESEARCH ANALYST 3000
RESEARCH MANAGER 2975
ACCOUNTING 8750
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000

已选择18行。

执行计划
----------------------------------------------------------
Plan hash value: 2432972551

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    12 | 336 |     5  (20)| 00:00:01 |
| 1 |  SORT GROUP BY | |    12 | 336 |     5  (20)| 00:00:01 |
| 2 |   GENERATE CUBE | |    12 | 336 |     5  (20)| 00:00:01 |
| 3 |    SORT GROUP BY | |    12 | 336 |     5  (20)| 00:00:01 |
| 4 |     NESTED LOOPS | |    12 | 336 |     4   (0)| 00:00:01 |
| 5 |      TABLE ACCESS FULL | EMP |    12 | 180 |     3   (0)| 00:00:01 |
| 6 |      TABLE ACCESS BY INDEX ROWID| DEPT |     1 | 13 |     1   (0)| 00:00:01 |
|* 7 |       INDEX UNIQUE SCAN | PK_DEPT |     1 | |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("A"."DEPTNO"="B"."DEPTNO")

对比ROLLUP(dname,job)，CUBE(dname,job)多了5行数据。这5行数据就是GROUP BY (NULL,job)。因为ROLLUP是按列的顺序从右到左递减分组统计的，而CUBE是各种可能性的分组，对于ROLLUP中有n列，CUBE中有同样的n列，那么CUBE的分组种类比ROLLUP多2ⁿ-(n+1)种，这里的n=2,因此多1种分组结果，根据两种分组的规则推算CUBE(dname,job)比ROLLUP(dname,job)多了GROUP BY (NULL,job)分组。

从上面的计划看出，不像ROLLUP，有SORT GROUP BY ROLLUP，CUBE操作是先采用GENERATE CUBE，然后对结果SORT GROUP BY，所以上面的CUBE计算结果也是有默认排序的，同样这种默认排序也是受计划影响的，应该用显式排序，在后面内容中会讲解如何对扩展分组排序。

下面用表格分析一下CUBE(dname,job)对应分组级别：

分组级别	描述
dname,job	标准分组
dname	对于每个dname,计算横跨所有job的小计
job	对于每个job,计算横跨所有dname的小计
合计	合计

CUBE对两列操作，对应4个分组级别，最终对各种可能性分组进行统计，获得多维度更加精细的数据统计结果。

2.2 部分CUBE分组

和ROLLUP一样，也有部分CUBE操作，可以去掉合计以及某些不需要的小计。比如上面的GROUP BY CUBE(dname,job)改为GROUP BY dname,CUBE(job)则剔除了合计以及GROUP BY job。代码如下：

SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname,CUBE(b.job);

显示结果如下：

DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
SALES 9400
SALES CLERK                     950
SALES MANAGER 2850
SALES SALESMAN 5600
RESEARCH 6775
RESEARCH CLERK                     800
RESEARCH ANALYST 3000
RESEARCH MANAGER 2975
ACCOUNTING 8750
ACCOUNTING CLERK                    1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000

已选择12行。

结果少了合计以及GROUP BY job。部分CUBE很有用，有时候我们统计的列很多，没有必要将所有的情况都统计，而是关注于某几个维度统计，这样部分CUBE的作用就发挥出来了。

2.3 CUBE总结

CUBE像ROLLUP一样，可以实现多维数组分析统计工作，而且CUBE是对所有可能性的组合情况进行统计，从而生成交叉报表，CUBE分组级别更多，结果更精细，从而为决策者提供强大的数据支撑，为实现灵活的报表提供保障。

三

GROUPING SETS实现小计

前面已经说了两种多维数据统计的方法：ROLLUP和CUBE，它们的输出结果是由对应分组的行伴随着小计行产生的，它们会产生标准分组、各种小计以及合计，但是有时候我们只关心对某个单列分组，从而得到其他维度小计信息，这样就需要使用到GROUPING SETS扩展分组。

比如GROUP BY GROUPING SETS(a,b,c)相当于GROUP BY a、GROUP BY b和GROUP BY c这3个分组的UNION ALL结果，这样结果中只有指定某些维度的小计，没有常规分组结果以及合计结果，对只关注某些维度的小计分析很有用，从GROUPING SETS操作的功能看出，n列的GROUPING SETS的分组种类有n个。

3.1 GROUPING SETS分组

GROUPING SETS的语法很简单，和ROLLUP、CUBE类似：

SELECT … GROUP BY GROUPING SETS(grouping_column_reference_list)

现在把5.2.2中的例子中的ROLLUP改为GROUPING SETS：

SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY GROUPING SETS(to_char(b.hiredate,'yyyy'),a.dname,b.job);

结果如下：

HIRE_YEA DNAME JOB SUM_SAL
-------- ---------------------------- ------------------ ----------
                                     CLERK 3050
                                     SALESMAN 5600
                                     PRESIDENT 5000
                                     MANAGER 8275
                                     ANALYST 3000
ACCOUNTING 8750
RESEARCH 6775
SALES 9400
1980                                                            800
1982                                                           1300
1981                                                          22825

已选择11行。

执行计划
----------------------------------------------------------------------------------------------
Plan hash value: 18386332

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    12 | 384 |    15  (20)| 00:00:01 |
| 1 |  TEMP TABLE TRANSFORMATION | |       | |            | |
| 2 |   LOAD AS SELECT | |       | |            | |
| 3 |    NESTED LOOPS | |    12 | 432 |     4   (0)| 00:00:01 |
| 4 |     TABLE ACCESS FULL | EMP |    12 | 276 |     3   (0)| 00:00:01 |
| 5 |     TABLE ACCESS BY INDEX ROWID| DEPT |     1 | 13 |     1   (0)| 00:00:01 |
|* 6 |      INDEX UNIQUE SCAN | PK_DEPT |     1 | |     0   (0)| 00:00:01 |
| 7 |   LOAD AS SELECT | |       | |            | |
| 8 |    HASH GROUP BY | |     1 | 19 |     3  (34)| 00:00:01 |
| 9 |     TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_4AC9B4F |     1 | 19 |     2   (0)| 00:00:01 |
| 10 |   LOAD AS SELECT | |       | |            | |
| 11 |    HASH GROUP BY | |     1 | 22 |     3  (34)| 00:00:01 |
| 12 |     TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_4AC9B4F |     1 | 22 |     2   (0)| 00:00:01 |
| 13 |   LOAD AS SELECT | |       | |            | |
| 14 |    HASH GROUP BY | |     1 | 17 |     3  (34)| 00:00:01 |
| 15 |     TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_4AC9B4F |     1 | 17 |     2   (0)| 00:00:01 |
| 16 |   VIEW | |     1 | 32 |     2   (0)| 00:00:01 |
| 17 |    TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_4AC9B4F |     1 | 32 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")

从结果上很容易看出这个统计的规律，也就是分别按单列分组之后UNION ALL的结果。比如前5行就是对于每个JOB值，计算横跨所有入职时间(年)和部门的小计。

注意：GROUPING SETS的结果和列的顺序没有关系，而且结果的顺序也是无序的，从计划看出，上面的GROUPING SETS的计划还是很复杂的。

3.2 部分GROUPING SETS分组

每种扩展GROUP BY都有部分分组特性，GROUPING SETS也不例外，改写上面的语句：

SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname, GROUPING SETS(to_char(b.hiredate,'yyyy'),b.job);

显示结果为：

DNAME HIRE_YEA JOB SUM_SAL
---------------------------- -------- ------------------ ----------
SALES MANAGER 2850
SALES CLERK                     950
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING CLERK                    1300
SALES SALESMAN 5600
RESEARCH MANAGER 2975
RESEARCH ANALYST 3000
RESEARCH CLERK                     800
RESEARCH 1981                              5975
SALES 1981                              9400
ACCOUNTING 1981                              7450
ACCOUNTING 1982                              1300
RESEARCH 1980                               800

已选择14行。

将部门名从GROUPING SETS中移到GROUP BY中，则语句的含义发生了变化，现在就是统计对于每个部门每个入职时间(年)，对所有职位进行小计以及对于每个部门每个职位，对入职时间(年)进行小计。

3.3 CUBE、ROLLUP作为GROUPING SETS的参数

GROUPING SETS操作能够接受ROLLUP和CUBE作为它的参数， GROUPING SETS操作只对单列分组，而不提供合计的功能，如果需要GROUPING SETS提供合计的功能，那么可以使用ROLLUP或CUBE作为GROUPING SETS的参数，比如改写前面的GROUPING SETS(a.dname,b.job)，提供合计功能：

SELECT a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job));

结果如下：

DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
CLERK 3050
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 3000
ACCOUNTING 8750
RESEARCH 6775
SALES 9400
24925
24925

这条语句产生了2个合计行，因为ROLLUP或CUBE作为GROUPING SETS的参数，则相当于对每个ROLLUP或CUBE操作的UNION ALL。所以上面的语句等价于：

SELECT a.dname,NULL job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname)
UNION ALL
SELECT NULL dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY  ROLLUP(b.job);

这样，就很好理解ROLLUP或CUBE作为GROUPING SETS所实现的功能了。上面的SQL具有重复合计，可以使用DISTINCT剔除重复行,另外在扩展分组中有特殊的函数可以使用，后面会说到GROUP_ID函数专门用来剔除重复分组（注意DISTINCT和GROUP_ID实现的功能还是不一样的，见GROUP_ID部分讲解）。当然，在GROUPING SETS中，ROLLUP和CUBE也可以混合使用，而且也能使用其他扩展功能，如部分分组、复合列分组、连接分组等。

ROLLUP和CUBE就不能接受GROUPING SETS作为参数了，ROLLUP和CUBE之间互相作为参数也是不可以的。

GROUPING SETS总结

GROUPING SETS很简单，就是分别对单列进行分组，从而统计其他维度的小计，对于GROUPING SETS中无合计，Oracle允许ROLLUP,CUBE作为GROUPING SETS的参数，增强了GROUPING SETS分组功能。

现在3个扩展GROUP BY：ROLLUP、CUBE、GROUPING SETS已经基本讲完。实际上里面还有很多值得研究的内容，比如3个扩展GROUP BY之间是否可以转换、如何对结果中的NULL进行判断是否是小计或合计列、如何制作可读性强的报表、如何对结果显式排序、如何实现更加复杂的需求：比如部分ROLLUP中需要保留合计、如何简单地剔除某些不必要的行(强大的GROUPING_ID函数使用)等等，在后续的内容中，会逐一介绍。

本文作者：丁俊(上海新炬中北团队）

本文来源：“IT那活儿”公众号

cube sql分组

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。