About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文（169）
分类： Oracle
2020-06-02 16:02:34
前面已经说了两种多维数据统计的方法：ROLLUP和CUBE，它们的输出结果是由对应分组的行伴随着小计行产生的，它们会产生标准分组，各种小计以及合计，但是有时候我们只关心对某个单列分组，从而得到其他维度小计信息，这样就需要使用到GROUPING SETS扩展分组，它是Oracle9i提供的。
比如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个。
GROUPING SETS的语法很简单，和ROLLUP、CUBE类似：
SELECT … GROUP BY GROUPING SETS(grouping_column_reference_list)
现在把1.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
执行计划
---------------------------------------------------------------------------------------------------
Plan hash value: 18386332
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 384 | 15 (20)| 0
0:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | |
|
| 2 | LOAD AS SELECT | | | | |
|
| 3 | NESTED LOOPS | | 12 | 432 | 4 (0)| 0
0:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 12 | 276 | 3 (0)| 0
0:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 0
0:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 0
0:00:01 |
| 7 | LOAD AS SELECT | | | | |
|
| 8 | HASH GROUP BY | | 1 | 19 | 3 (34)| 0
0:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_43CAA9C | 1 | 19 | 2 (0)| 0
0:00:01 |
| 10 | LOAD AS SELECT | | | | |
|
| 11 | HASH GROUP BY | | 1 | 22 | 3 (34)| 0
0:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_43CAA9C | 1 | 22 | 2 (0)| 0
0:00:01 |
| 13 | LOAD AS SELECT | | | | |
|
| 14 | HASH GROUP BY | | 1 | 17 | 3 (34)| 0
0:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_43CAA9C | 1 | 17 | 2 (0)| 0
0:00:01 |
| 16 | VIEW | | 1 | 32 | 2 (0)| 0
0:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_43CAA9C | 1 | 32 | 2 (0)| 0
0:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")
从结果上很容易看出这个统计的规律，也就是分别按单列分组之后UNION
ALL的结果。比如前5行就是对于每个JOB值，
计算横跨所有入职时间(年)和部门的小计。
注意：GROUPING
SETS的结果和列的顺序没有关系，而且结果的顺序也是无序的，从计划看出，上面的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
将部门名从GROUPING SETS中移到GROUP BY中，则语句的含义发生了变化，现在就是统计对于每个部门每个入职时间(年)，
对所有职位进行小计以及对于每个部门每个职位，对入职时间(年)进行小计。
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中无合计，
Oracle允许ROLLUP,CUBE作为GROUPING SETS的参数，增强了GROUPING SETS分组功能。
现在3个扩展GROUP BY：ROLLUP、CUBE、GROUPING SETS已经基本讲完。实际上里面还有很多值得研究的内容，
比如3个扩展GROUP BY之间是否可以转换、如何对结果中的NULL进行判断是否是小计或合计列、如何制作可读性强的报表、
如何对结果显式排序、如何实现更加复杂的需求，比如部分ROLLUP中需要保留合计、
如何简单地剔除某些不必要的行(强大的GROUPING_ID函数使用)等等，在后续的内容中，会逐一介绍。
