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-04 17:11:04
1.6节主要讲解3个扩展分组函数的使用：GROUPING、GROUPING_ID和GROUP_ID函数，这3个函数在生成有意义的报表、结果行过滤、排序中有很重要的作用。复杂的报表查询，经常会使用到这3个函数。主要内容有：
1）使用GROUPING函数制作有意义的报表以及对结果进行过滤。
2）使用GROUPING_ID函数对结果行过滤以及排序。
3）使用GROUP_ID函数剔除重复行。
对扩展GROUP BY子句来说，比如ROLLUP,CUBE会生成标准分组，一系列小计以及合计，这样查询结果中，有的行的列值就会存在NULL，NULL在扩展GROUP BY中有特殊意义，结果行中的列值为NULL，一般就意味着是对此列的小计或合计，但是NULL也有可能是原始数据存在的NULL，所以引入了GROUPING函数专门处理扩展GROUP BY分组结果中NULL的问题：
1) 它只接受一个参数，且此参数来自ROLLUP、CUBE、GROUPING SETS中的列。当然参数也可以来自于GROUP BY中但不在上述3个子句中的列(包括简单GROUP BY也可以使用此函数，但是结果肯定是0)，那就没有什么特别的意义了。
2) GROUPING函数对于是小计或合计的列返回1，否则返回0，而小计或合计列的值是NULL，所以常使用GROUPING函数来区分最终的结果行中的NULL是原始数据中存在NULL还是小计列或合计列，常和DECODE函数配合使用，当然也可以使用GROUPING函数来区别分组级别，从而过滤某些行，但是过滤的分组种类比较多的话，语句会很麻烦，所以常使用GROUPING_ID函数代替。
下面讲解实例，这样就能清楚地知道GROUPING函数的使用了。
1. 用于格式化报表，生成有意义的报表
如果要制作有意义的扩展GROUP BY报表，那么首先必须区分哪些列是小计、合计，哪些不是，请看如下语句：
SELECT a.dname, b.mgr, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname, b.mgr);
显示结果如下：
DNAME MGR SUM_SAL
---------------------------- ---------- ----------
SALES 7698 6550
SALES 7839 2850
SALES 9400
RESEARCH 7566 3000
RESEARCH 7839 2975
RESEARCH 7902 800
RESEARCH 6775
ACCOUNTING 5000
ACCOUNTING 7782 1300
ACCOUNTING 7839 2450
ACCOUNTING 8750
24925
已选择12行。
第8行和第11行，mgr列都为NULL,那么到底哪个列是小计呢?因为这里不可能出现两个小计行，如何区分?当然是使用GROUPING函数了，参数为mgr。如下：
SELECT a.dname,b.mgr, SUM(sal) sum_sal,GROUPING(b.mgr)
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.mgr);
结果如下：
DNAME MGR SUM_SAL GROUPING(B.MGR)
---------------------------- ---------- ---------- --------------------------------------------------------------
SALES 7698 6550 0
SALES 7839 2850 0
SALES 9400 1
RESEARCH 7566 3000 0
RESEARCH 7839 2975 0
RESEARCH 7902 800 0
RESEARCH 6775 1
ACCOUNTING 5000 0
ACCOUNTING 7782 1300 0
ACCOUNTING 7839 2450 0
ACCOUNTING 8750 1
24925 1
已选择12行。
第8行的GROUPING(mgr)的值为0，第11行为1，所以第8行的mgr列不是小计列，第11行才是。有了GROUPING函数就好办了，下面使用DECODE+GROUPING来制作有意义的报表：
SELECT DECODE(GROUPING(a.dname),1,'全部部门',a.dname) dname,
DECODE(GROUPING(b.mgr),1,'全部老板',NVL(TO_CHAR(b.mgr),'总老板')) mgr,
SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.mgr);
结果如下：
DNAME MGR SUM_SAL
---------------------------- ---------- ----------
SALES 7698 6550
SALES 7839 2850
SALES 全部老板 9400
RESEARCH 7566 3000
RESEARCH 7839 2975
RESEARCH 7902 800
RESEARCH 全部老板 6775
ACCOUNTING 总老板 5000
ACCOUNTING 7782 1300
ACCOUNTING 7839 2450
ACCOUNTING 全部老板 8750
全部部门 全部老板 24925
已选择12行。
现在的结果比以前一堆NULL要好多了，但是还是不够完美，如果要将所有的mgr列小计一起放在后面显示，位置在合计之前，这种需求如何解决?见GROUPING_ID函数详解。
2. 过滤某些分组结果
这个一般使用GROUPING函数来做的不多，因为如果过滤的分组级别比较多，那么会写很多GROUPING函数，比较麻烦，一般使用GROUPING_ID代替。比如对GROUP BY ROLLUP(a.dname,b.mgr ,b.job)的结果保留合计和标准分组：
SELECT a.dname,b.mgr,b.job,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.mgr,b.job)
HAVING GROUPING(a.dname) = 1 OR
GROUPING(b.job) =0;
结果为：
DNAME MGR JOB SUM_SAL
---------------------------- -------------- ------------------ ----------
SALES 7698 CLERK 950
SALES 7698 SALESMAN 5600
SALES 7839 MANAGER 2850
RESEARCH 7566 ANALYST 3000
RESEARCH 7839 MANAGER 2975
RESEARCH 7902 CLERK 800
ACCOUNTING PRESIDENT 5000
ACCOUNTING 7782 CLERK 1300
ACCOUNTING 7839 MANAGER 2450
24925
已选择10行。
现在剔除了小计，保留了常规分组和合计。这里的列比较少，上面使用了两个GROUPING函数，如果比较多，使用很多GROUPING函数，SQL就复杂了，对于这种过滤需求，常不使用GROUPING函数，而使用GROUPING_ID函数。
前面已经说了使用GROUPING函数来生成有意义的报表以及过滤某些分组级别。那么GROUPING_ID函数主要就是用来过滤分组级别和排序结果的：因为GROUPING函数过滤分组级别有点麻烦，因此，常使用GROUPING_ID代替。
GROUPING_ID另外一个很重要的功能就是排序结果，只有结果有一定顺序才能使报表的可读性更强，从而更加能返回多维数据分析的作用，不管ROLLUP、CUBE、GROUPING SETS的结果是否有默认顺序，都是不可靠的，可靠的只有显式排序。
GROUPING_ID函数可以接受多个参数，这些参数来自于ROLLUP、CUBE、GROUPING SETS中的列(参数来源和GROUPING函数一致)，按列从左到右顺序计算，如果此列是分组列则为0，如果是对此列的小计或合计则为1，然后按列顺序将计算结果组成二进制序列(位向量)，最后将位向量转为10进制数。如CUBE(a,b)，那么GROUPING_ID(a,b)的结果如下：
|
分组级别 |
位向量 |
GROUPING_ID结果 |
|
a,b |
0 0 |
0 |
|
a (对于每个a列值，计算横跨b的小计) |
0 1 |
1 |
|
b (对于每个b列值，计算横跨a的小计) |
1 0 |
2 |
|
汇总 |
1 1 |
3 |
GROUPING_ID的好处就是可以对多列进行计算，从而得到此列的分组级别。如上表所示，如果使用GROUPING函数来确定分组级别，则要写两次，如果列数增多，则使用GROUPING函数的次数就增多，使用GROUPING_ID函数因为它可以接受多列，而且转为二进制位向量计算为10进制值，所以每个分组级别上的值都不同，而且可确定，从而方便区分各种不同的分组级别，为后续操作，比如指定规则的排序、HAVING过滤分组级别等提供强大的支持。
从上述分析可以看出，GROUPING_ID(column_list),这里的column_list和扩展分组保持一致，那么GROUPING_ID的值种类必与对应扩展分组数目一致，比如CUBE(a,b,c)的GROUPING_ID(a,b,c)值有8种，ROLLUP(a,b,c)对应的GROUPING_ID(a,b,c)有4种。GROUPING_ID的值范围都一样，和列的数目有关，比如有n列，则GROUPING_ID的取值范围在[0,2n -1]。
1. GROUPING_ID函数过滤某些分组结果
改写1.6.1GROUPING过滤结果的例子，用GROUPING_ID实现同等功能。先来分析下对于ROLLUP(a.dname,b.mgr ,b.job)使用GROUPING_ID函数的结果(简化说明)，当然一般使用GROUPING_ID函数，列的顺序要与ROLLUP、CUBE、GROUPING SETS中的顺序保持一致，这样便于分析：
|
分组级别 |
位向量 |
GROUPING_ID结果 |
|
dname,mgr,job |
0 0 0 |
0 |
|
dname,mgr |
0 0 1 |
1 |
|
dname |
0 1 1 |
3 |
|
汇总 |
1 1 1 |
7 |
从表中就可以很清楚地看出，实现这个需求只要GROUPING_ID(a.dname,b.mgr,b.job)取0和7即可。语句如下：
SELECT a.dname,b.mgr,b.job,SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.mgr,b.job)
HAVING GROUPING_ID(a.dname,b.mgr,b.job) IN (0,7);
2.使用GROUPING_ID对结果进行排序
对扩展分组，需要手动进行有意义的排序，很多时候一个报表重点关心的是汇总信息：小计和合计。比如ROLLUP来说，一般它的小计是交错分布在明细之间，如果一个报表很长，看报表的人如何快速找到所有小计呢?另一方面，从Oracle来说，如果想要结果有序，最好ORDER BY ，不管这个操作是否有默认排序，如果这个操作有默认排序，如果ORDER
BY的排序和默认顺序是一样的，那么CBO优化器不会做额外的工作，因此，增加ORDER BY对要求结果有序的操作是有备无患的。
下面就解决1.6.1最后遗留下来的排序问题：将所有的mgr列小计一起放在后面显示，位置在合计之前。先来分一下ROLLUP(a.dname,b.mgr)操作对应的GROUPING_ID：
|
分组级别 |
位向量 |
GROUPING_ID结果 |
|
dname,mgr |
0 0 |
0 |
|
dname |
1 0 |
2 |
|
汇总 |
1 1 |
3 |
从上面的结果分析，这种排序很简单，直接按GROUPING_ID排序即可。SQL如下：
SELECT DECODE(GROUPING(a.dname),1,'全部部门',a.dname) dname,
DECODE(GROUPING(b.mgr),1,'全部老板',NVL(TO_CHAR(b.mgr),'总老板')) mgr,
SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.mgr)
ORDER BY GROUPING_ID(a.dname,b.mgr);
结果为：
DNAME MGR SUM_SAL
------------------------------ -------------------- ---------------------------------------------------
SALES 7698 6550
SALES 7839 2850
RESEARCH 7902 800
ACCOUNTING 7839 2450
ACCOUNTING 7782 1300
ACCOUNTING 总老板 5000
RESEARCH 7566 3000
RESEARCH 7839 2975
ACCOUNTING 全部老板 8750
RESEARCH 全部老板 6775
SALES 全部老板 9400
全部部门 全部老板 24925
已选择12行。
当然了，也可以使用DECODE函数对此结果进一步操作，比如最后一行，只保留dname单元格的值，并显示为合计，这很简单，在1.7的例子中会讲解。
排序还有很多灵活性，比如改变GROUPING_ID中列的顺序，就可以达到不同的排序效果。比如
SELECT a.dname,b.mgr,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY CUBE(a.dname,b.mgr,b.job)
ORDER BY GROUPING_ID(a.dname,b.mgr,b.job);
SELECT a.dname,b.mgr,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY CUBE(a.dname,b.mgr,b.job)
ORDER BY GROUPING_ID(a.dname,b.job,b.mgr);
上面两条语句的结果一样，但是排序却是不同的。GROUPING函数其实也能用来排序，甚至可以将GROUPING函数与GROUPING_ID函数排序结合起来，比如
SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal,
GROUPING(a.dname)+GROUPING(to_char(b.hiredate,'yyyy'))+GROUPING(b.job) ord1,
GROUPING_ID(a.dname,to_char(b.hiredate,'yyyy'),b.job) ord2
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY CUBE(a.dname,to_char(b.hiredate,'yyyy'),b.job)
ORDER BY GROUPING(a.dname)+GROUPING(to_char(b.hiredate,'yyyy'))+GROUPING(b.job) DESC,
GROUPING_ID(a.dname,to_char(b.hiredate,'yyyy'),b.job);
结果为：
DNAME HIRE_YEA JOB SUM_SAL ORD1 ORD2
------------------------------ -------- ------------------ ---------- ---------- ----------
24925 3 7
SALES 9400 2 3
RESEARCH 6775 2 3
ACCOUNTING 8750 2 3
1981 22825 2 5
1982 1300 2 5
1980 800 2 5
SALESMAN 5600 2 6
MANAGER 8275 2 6
ANALYST 3000 2 6
CLERK 3050 2 6
PRESIDENT 5000 2 6
SALES 1981 9400 1 1
ACCOUNTING 1982 1300 1 1
ACCOUNTING 1981 7450 1 1
RESEARCH 1981 5975 1 1
RESEARCH 1980 800 1 1
RESEARCH ANALYST 3000 1 2
SALES MANAGER 2850 1 2
SALES SALESMAN 5600 1 2
ACCOUNTING PRESIDENT 5000 1 2
RESEARCH CLERK 800 1 2
ACCOUNTING CLERK 1300 1 2
RESEARCH MANAGER 2975 1 2
SALES CLERK 950 1 2
ACCOUNTING MANAGER 2450 1 2
1981 CLERK 950 1 4
1981 PRESIDENT 5000 1 4
1981 SALESMAN 5600 1 4
1981 MANAGER 8275 1 4
1982 CLERK 1300 1 4
1981 ANALYST 3000 1 4
1980 CLERK 800 1 4
ACCOUNTING 1982 CLERK 1300 0 0
SALES 1981 MANAGER 2850 0 0
SALES 1981 CLERK 950 0 0
RESEARCH 1981 MANAGER 2975 0 0
SALES 1981 SALESMAN 5600 0 0
ACCOUNTING 1981 MANAGER 2450 0 0
RESEARCH 1980 CLERK 800 0 0
ACCOUNTING 1981 PRESIDENT 5000 0 0
RESEARCH 1981 ANALYST 3000 0 0
已选择42行。
从上面结果看出，这种利用GROUPING和GROUPING_ID函数的排序方法，可以指定排序，灵活运行这两个GROUPING函数就可以获得有意义的排序结果。上面的排序实现：2) 最后是标准分组。
