报表开发之扩展GROUP BY_组合列分组、连接分组、重复列分组_PART5

接PART4：

1.5  组合列分组、连接分组、重复列分组

 组合列分组、连接分组、重复列分组都是Oracle9i中才有的特性。组合列也就是将多个列用括号括起来，从而将多个列当作整体对待，比如GROUP BY ROLLUP((a,b),c)则相当于GROUP BY ROLLUP(x,c),其中x相当于(a,b)这个组合列，一般在in条件中比较常见，比如：

--where in中使用组合列

SELECT empno,ename,job FROM emp

WHERE (empno,ename) IN((7369,'SMITH'),(7499,'ALLEN'));

 结果为：

     EMPNO ENAME                JOB

---------- -------------------- ------------------

      7369 SMITH                CLERK

      7499 ALLEN                SALESMAN

已选择2行。

上面的查询将empno和ename当作整体，分别匹配两个组合值(7369,'SMITH')和(7499,'ALLEN')，获得两行结果。
  这里说的组合列分组是括号括起来的用在扩展GROUP BY的ROLLUP、CUBE、GROUPING SETS中，不同于GROUP BY (a,b),c这种简单分组，这个不是组合列分组，而且括号也没有什么特别意义。
  在扩展GROUP BY中组合列分组很有用，在ROLLUP、CUBE、GROUPING SETS以及即将要说的连接分组中使用广泛，先看一个普通列ROLLUP和组合列ROLLUP的对比(CUBE、GROUPING SETS类似)，这样就能清楚知道组合列在扩展GROUP BY中的用途了。

分组方式	描述
ROLLUP(a,b,c)	等价于下列组合： GROUP BY a,b,c GROUP BY a,b GROUP BY a GROUP BY NULL
ROLLUP(a,(b,c))	等价于下列组合： GROUP BY a,b,c GROUP BY a GROUP BY NULL

  从上面的表中可以看出，ROLLUP(a,(b,c))与ROLLUP(a,b,c)相比，前者相当于两列，也就只有3种分组级别了，少了GROUP BY a,b操作，组合列分组达到了剔除某些小计的功能，但是保证了最终结果有合计行，前面说了部分分组也能剔除某些小计，但是同时将合计也去掉了，使用组合列分组剔除某些小计更加灵活，而且还能保存合计，在复杂的扩展GROUP BY报表中，组合列分组经常会被使用。

  对于连接分组，功能更是强大：它允许在GROUP BY之后出现多个ROLLUP、CUBE、GROUPING SETS操作，这样分组级别更多，结果更加精细。有时候为了实现很复杂的需求，经常使用连接分组。还是看下连接分组是怎么回事吧：

分组方式	描述
ROLLUP(a,b),ROLLUP(c)	等价于下列组合: GROUP BY a,b,c GROUP BY a,b GROUP BY a,c GROUP BY a GROUP BY c GROUP BY NULL
ROLLUP(a,b),GROUPING SETS(c)	等价于下列组合: GROUP BY a,b,c GROUP BY a,c GROUP BY c

  实际上不管是同类型的连接分组还是不同类型的连接分组之间，最后的分组级别种类都是每个扩展分组级别种类的乘积，分组级别是笛卡尔积。比如同类型连接分组ROLLUP(a,b),ROLLUP(c)最终有3*2=6种分组级别，不同类型连接分组ROLLUP(a,b),GROUPING SETS(c)有3*1=3种分组级别，类似地比如CUBE(a,b),CUBE(c)则相当于CUBE（a,b,c）了，其他组合情况都可以上述方法进行分析。

  有了连接分组以及组合列分组就可以进行更加复杂的分组计算，也可以实现ROLLUP、CUBE、GROUPING SETS之间的相互转换。

 重复列分组也就是GROUP BY中允许重复列，有时候比如在ROLLUP中使用复杂的复合列分组可能会用到，比如GROUP BY ROLLUP(a,(a,b))、GROUP BY a,ROLLUP(a,b)都属于重复列分组。

1.5.1  组合列分组

组合列分组有过滤某些小计或计算一些额外的小计等功能，前面的部分ROLLUP、部分CUBE都没有合计，使用组合列分组可以实现部分ROLLUP、CUBE的功能，还能有合计。其实扩展分组提供了很多强大的功能，而且这些功能之间经常能够相互转换或者使用某几个组合功能实现一个复杂的需求，所以，面对如此多的选择，应该根据需求选择合适的实现才是最重要的。比如实现需求：

1） 对部门、入职时间(年)、职位进行标准分组。

2） 对每个部门计算横跨入职时间(年)和职位的小计。

3） 最后合计。

使用组合列ROLLUP分组就很容易了，语句如下：

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  ROLLUP(a.dname,(to_char(b.hiredate,'yyyy'),b.job));

       结果如下：

DNAME                        HIRE_YEA JOB                   SUM_SAL

---------------------------- -------- ------------------ ----------

SALES                        1981     CLERK                     950

SALES                        1981     MANAGER                  2850

SALES                        1981     SALESMAN                 5600

SALES                                                          9400

RESEARCH                     1980     CLERK                     800

RESEARCH                     1981     ANALYST                  3000

RESEARCH                     1981     MANAGER                  2975

RESEARCH                                                       6775

ACCOUNTING                   1981     MANAGER                  2450

ACCOUNTING                   1981     PRESIDENT                5000

ACCOUNTING                   1982     CLERK                    1300

ACCOUNTING                                                     8750

                                                              24925

已选择13行。

  从上面结果看出可以看出：
  ROLLUP(a.dname,(to_char(b.hiredate,'yyyy'),b.job))与ROLLUP(a.dname,to_char(b.hiredate,'yyyy'),b.job)比较少了GROUP BY a.dname, to_char(b.hiredate,'yyyy')分组，因为后面两列被当作了一个整体，相当于1列，最终类似对2列进行ROLLUP,所以结果有3种。CUBE、GROUPING SETS组合列分组类似，这里就不再分析了。
  组合列分组在Oracle8i中使用会报错，但是也是有变通方法的，比如使用连接符||，将组合的列连接起来即可，最终结果是一样的，只不过显示的列数少了而已。用||改写上面的复合列分组，语句为：

SELECT a.dname,to_char(b.hiredate,'yyyy') || '     ' || b.job hiredate_job, SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY  ROLLUP(a.dname,to_char(b.hiredate,'yyyy') || '     ' || b.job);

       结果为：

DNAME                        HIREDATE_JOB                            SUM_SAL

---------------------------- ------------------------------------ ----------

SALES                        1981     CLERK                              950

SALES                        1981     MANAGER                           2850

SALES                        1981     SALESMAN                          5600

SALES                                                                   9400

RESEARCH                     1980     CLERK                              800

RESEARCH                     1981     ANALYST                           3000

RESEARCH                     1981     MANAGER                           2975

RESEARCH                                                                6775

ACCOUNTING                   1982     CLERK                             1300

ACCOUNTING                   1981     MANAGER                           2450

ACCOUNTING                   1981     PRESIDENT                         5000

ACCOUNTING                                                              8750

                                                                       24925

已选择13行。

  显示的结果是一致的，只不过使用组合列结果是4列，而使用||连接符最终结果只有3列了，在Oracle9i或以上版本就不需要用连接符||了。

  现在使用组合列分组解决前面遗留的问题，如何使用组合列分组达到类似部分ROLLUP、CUBE的效果，而且还会加上合计呢?以前面的GROUP BY to_char(b.hiredate,'yyyy'),a.dname,ROLLUP(b.job)为例，用组合列分组改写保留合计行如下：

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 ROLLUP((to_char(b.hiredate,'yyyy'),a.dname),b.job);

 结果省略。当然有时候使用组合列分组或其它分组实现上述需求可能会很麻烦，更多的时候对ROLLUP、CUBE需要合计，又剔除小计，我会选择使用GROUPING_ID或GROUPING函数来进行剔除工作，后面讲解扩展GROUP BY函数。

CUBE和ROLLUP操作都可以用复合列分组转为对应的GROUPING SETS，比如：

ROLLUP(a,b,c)转为等价的GROUPING SETS是：GROUPING SETS ((a, b, c), (a, b), NULL)。

       CUBE(a,b,c)转为等价的GROUPING SETS是：GROUPING SETS ((a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), NULL)。

       GROUPING SETS是否可以用ROLLUP或CUBE转换呢?当然可以，有了部分分组，连接分组和组合列分组以及后面说到的GROUPING_ID函数，都是可以实现相互转换的，但是GROUPING SETS用ROLLUP或CUBE改写，意义不大，所以不再讨论。

1.5.2  连接分组

连接分组是Oracle9i才有的功能，对于生成各种分组级别的数据很有用，它允许GROUP BY后面有多个ROLLUP、CUBE、GROUPING SETS，连接分组的分组级别是由每个ROLLUP、CUBE、GROUPING SETS分组级别组成的笛卡尔积。比如ROLLUP(a,b),ROLLUP(c,d,e)则共有分组统计级别为3*4=12种。看下面的SQL：

SELECT a.dname,b.job,to_char(b.hiredate,'yyyy') hire_year, SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY  ROLLUP(a.dname,b.job),ROLLUP(to_char(b.hiredate,'yyyy'));

       显示结果为：

DNAME                        JOB                HIRE_YEA    SUM_SAL

---------------------------- ------------------ -------- ----------

SALES                        CLERK                              950

SALES                        MANAGER                           2850

SALES                        SALESMAN                          5600

SALES                                                          9400

RESEARCH                     CLERK                              800

RESEARCH                     ANALYST                           3000

RESEARCH                     MANAGER                           2975

RESEARCH                                                       6775

ACCOUNTING                   CLERK                             1300

ACCOUNTING                   MANAGER                           2450

ACCOUNTING                   PRESIDENT                         5000

ACCOUNTING                                                     8750

                                                              24925

RESEARCH                     CLERK              1980            800

RESEARCH                                        1980            800

                                                1980            800

SALES                        CLERK              1981            950

SALES                        MANAGER            1981           2850

SALES                        SALESMAN           1981           5600

SALES                                           1981           9400

RESEARCH                     ANALYST            1981           3000

RESEARCH                     MANAGER            1981           2975

RESEARCH                                        1981           5975

ACCOUNTING                   MANAGER            1981           2450

ACCOUNTING                   PRESIDENT          1981           5000

ACCOUNTING                                      1981           7450

                                                1981          22825

ACCOUNTING                   CLERK              1982           1300

ACCOUNTING                                      1982           1300

                                                1982           1300

已选择30行。

       GROUP BY  ROLLUP(a.dname,b.job),ROLLUP(to_char(b.hiredate,'yyyy'))实现了6种分组结果，相当于两个ROLLUP的笛卡尔积：

分组级别	描述
部门、职位、入职时间(年)	常规分组
部门、职位	对每个部门每个职位，计算所有入职时间(年)的小计
部门、入职时间(年)	对每个部门每个入职时间(年)，计算所有职位的小计
入职时间(年)	对每个入职时间(年)，计算所有部门和职位的小计
部门	对每个部门，计算所有入职时间(年)和职位的小计
合计	合计

 只要理解了连接分组的分组级别就是多个扩展分组的分组级别笛卡尔积，那么就好办了，CUBE,GROUPING SETS都类似。利用连接分组， CUBE可以用ROLLUP转换：

1）  只有一列的时候，比如ROLLUP(a)与CUBE(a)是一样的，都有2种统计方式。

2）  当有n列的时候，n>1,比如CUBE(a,b,c)则可以转为ROLLUP(a),ROLLUP(b),ROLLUP(c)的连接分组表示，也就是有n列的CUBE转为ROLLUP则需要拆开，转为单列ROLLUP的连接分组即可。使用多个ROLLUP这种情况特别适合不需要CUBE操作，但是要实现多种可能的组合分组，比如下列语句：

SELECT a.dname,b.job,to_char(b.hiredate,'yyyy') hire_year,b.mgr , SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY  ROLLUP((a.dname,b.job)),ROLLUP(to_char(b.hiredate,'yyyy')),ROLLUP(b.mgr);

其中第1个ROLLUP使用了组合列分组，3个连接ROLLUP最后分组级别有2*2*2=8种。

对于ROLLUP和GROUPING SETS转为CUBE一般没有多大用途，有兴趣的可以研究一下。

       连接分组一般是同类型的连接分组，不同类型的连接分组比如GROUP BY ROLLUP…CUBE…等是不常用的，但是分析方法上面已经说了，如果有复杂需求适合这样的情况，也可以考虑使用。

1.5.3  重复列分组

重复列分组也是Oracle9i才有的，在Oracle8i使用会报错。也就是GROUP BY后面允许重复列。举个例子如下：

SELECT a.dname,b.job, SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY a.dname,ROLLUP(a.dname,b.job);

      

       结果为：

DNAME                        JOB                   SUM_SAL

---------------------------- ------------------ ----------

SALES                        CLERK                     950

SALES                        MANAGER                  2850

SALES                        SALESMAN                 5600

RESEARCH                     CLERK                     800

RESEARCH                     ANALYST                  3000

RESEARCH                     MANAGER                  2975

ACCOUNTING                   CLERK                    1300

ACCOUNTING                   MANAGER                  2450

ACCOUNTING                   PRESIDENT                5000

SALES                                                 9400

RESEARCH                                              6775

ACCOUNTING                                            8750

SALES                                                 9400

RESEARCH                                              6775

ACCOUNTING                                            8750

已选择15行。

这个例子没有实际意义，只不过为了说明重复列在允许的，上面因为dname有重复，所以最后多了3行按GROUP BY dname的分组结果，上面SQL的计划与一般ROLLUP计划不同，导致小计全到最后显示，计划很复杂，就不贴出了，有兴趣的可以研究一下，这说明一切默认排序都是可能会变化的，只有显式排序才可靠。

1.5.4  组合列分组、连接分组、重复列分组总结

组合列分组、连接分组在复杂的报表需求中很有用，组合列主要实现剔除某些不必要的小计保留合计，连接分组按每个扩展分组的分组级别的笛卡尔积形式进行操作，分组种类更多更细，比如ROLLUP连接分组就实现了类似CUBE的功能，而且有时候比CUBE更能满足需求，如果有复杂的需求不符合常规扩展分组功能的，可以考虑组合列分组、连接分组，重复列分组。