报表开发之扩展GROUP BY_ROLLUP_PART2

接PART1：

1.2.2 ROLLUP分组

       从Oracle8i开始，Oracle使用ROLLUP对GROUP BY进行扩展：它允许计算标准分组以及相应维度的小计、合计功能。ROLLUP的语法结构很简单：

SELECT … GROUP BY ROLLUP(grouping_column_reference_list)

       ROLLUP后面指定的列以逗号分割，ROLLUP的计算结果和其后面指定列的顺序有关，因为ROLLUP分组过程具有方向性：先计算标准分组，然后列从右到左递减计算更高一级的小计，一直到列全部被选完，最后计算合计。这样如果ROLLUP中指定n列，则整个计算过程中的分组方式有n+1种。下面就使用ROLLUP实现1.2.1的需求，如下：

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

显示结果如下：

DNAME                        JOB                   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

已选择13行。

执行计划
----------------------------------------------------------
Plan hash value: 503922295
-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |    12 |   336 |     5  (20)| 00:00:01 |
|   1 |  SORT GROUP BY ROLLUP         |         |    12 |   336 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |         |    12 |   336 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | EMP     |    12 |   180 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

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

从上面的结果可以看出，使用ROLLUP只需要访问EMP表一次，通过DEPT表的主键获得ROWID回表查询相关行，比前面的UNION ALL性能要好，而且从执行计划上看出有一个特别的操作：SORT GROUP BY ROLLUP，这说明：ROLLUP的计算结果是有顺序的，所以展示的结果很直观，但是一般我们会显式排序，因为默认排序很多时候不符合业务需求，另外，默认排序是受执行计划影响的，可能有的ROLLUP就无序了，在重复列分组中就会说到没有排序的情况，对于显式排序，后面说到的GROUPING_ID函数会专门解决扩展分组排序问题。

ROLLUP分组具有方向性，从上面的结果看出，ROLLUP(a.dname,b.job)分组的过程是这样的：

1)      标准分组：GROUP BY(a.dname,b.job)，对每个部门每个职位进行分组(我分析的时候经常对GROUP BY加上括号，其实没有必要，只是为了更直观而已)。

2)      从右到左递减：GROUP BY(a.dname,NULL),其实这个NULL没有必要使用，这里只是为了方便分析。这个过程是对上个级别分组的小计,也就是对每个dname值，计算横跨所有job的小计。

3)      最后合计：相当于GROUP BY (NULL,NULL)。

上面的ROLLUP只用了两个列，如果有n个列，那么结果就是n+1种GROUP BY的组合，从右到左递减的过程中，下一个分组就是对上一个分组的小计，最后合计，比如ROLLUP(a,b,c)，那么有：

分组级别	描述
a,b,c	标准分组
a,b	对于每个a,b列值，计算横跨c列的小计
a	对于每个a列值，计算横跨b,c列的小计
合计汇总	合计

       另外提一下，其实ROLLUP操作，如果使用HINT: expand_gset_to_union，那么则优化器会将ROLLUP转为对应的UNION ALL操作，其它的GROUPING SETS、CUBE也可以，有兴趣的可以试一下。

ROLLUP语法简单，而且具体处理过程也很简单，除了第1个是标准分组，然后就是列从右到左递减的分组，最后合计。下面实现需求：

1） 计算每个入职时间(年)、部门、职位的标准分组的薪水和。

2） 计算每个入职时间(年)、部门的所有职位的薪水小计。

3） 计算每个入职时间(年)的所有部门所有职位的薪水小计。

4） 最后合计薪水，显示入职时间(年)、部门名、职位名。

从上面学到的ROLLUP知识，可以很容易地编写SQL实现上述需求，将hire_date格式化到年，部门名dname,职位名job按顺序放到ROLLUP中即可。

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);

结果如下：

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

       从结果看出，编写的SQL是实现了上述需求的。下面选些结果中的典型记录对此结果进行分析，其它行分析类似：

相关行	描述
第7行	对第4、5、6行的所有JOB进行小计
第14行	对第7行、第13行的所有DNAME、JOB进行小计，当然也相当于对4-6、8-12行所有DNAME、JOB的小计。
最后一行	对所有入职日期(精确到年)、DNAME、JOB进行合计

       因为ROLLUP分组过程具有方向性，所以通过改变ROLLUP中列的顺序就可以达到改变报表结果和含义的目的。比如将前面的ROLLUP(dname,job)改为ROLLUP(job,dname)则含义就发生了变化，现在需要查询的就是标准分组、计算每个job的所有部门的小计、最后合计，这里就两个列，也就是小计的含义发生了变化，请看：

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

显示结果如下：

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


已选择15行。

       从结果看出，与ROLLUP(dname,job)的确发生了变化，小计是对每个职位的所有部门进行小计，一定要牢记ROLLUP列的顺序对结果的影响，才能灵活地使用ROLLUP实现多维分析统计。

1.2.3部分ROLLUP分组

       1.2.2中的ROLLUP是普通的ROLLUP，有标准分组、多种小计、合计。如果现在的报表不需要某些小计、合计，那么怎么办呢?有办法，Oracle提供了部分ROLLUP分组功能，也就说将部分列从ROLLUP中移出来，放在GROUP BY中，这样合计肯定没有了，某些小计也没有了。如ROLLUP(to_char(b.hiredate,'yyyy'),a.dname,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 to_char(b.hiredate,'yyyy'),a.dname,ROLLUP(b.job);

      将hiredate和dname从ROLLUP中移出来，就可以将每个入职时间(年)的所有部门所有职位的薪水小计以及合计剔除。最终只查询标准分组和每个入职时间(年)、部门的所有职位的小计。部分ROLLUP提供了报表的额外选择，当然，很多情况下合计还是需要的，能不能既剔除部分小计，又能保留合计呢?有很多方法，有时候可以很容易用组合列分组、连接分组等解决，有时候使用扩展分组函数剔除比较简单，根据具体情况使用合适方法，部分CUBE类似。

1.2.4 ROLLUP总结

在数据仓库系统中，经常需要对相关表进行多维汇总统计，如果分组有规律，比如先进行常规分组，在常规分组基础上通过将列从右到左移动，然后进行更高一级的小计，最后合计，这样一般就需要使用ROLLUP，ROLLUP结果和列的顺序有关，顺序不同结果则不同，这为报表实现提供了很大的灵活性。

ROLLUP中指定n列，则根据ROLLUP的分组过程可以知道，分组方式有n+1种。另外部分ROLLUP可以剔除某些不需要的小计和合计，只要根据需求将相关列从ROLLUP中移出，放到GROUP BY中即可。