报表开发之扩展GROUP BY_概述_PART1

简单的GROUP BY，用过SQL的应该很熟悉了，通过GROUP BY子句指定相关列，就可以得到对应分组的聚合结果，对于每个分组只返回一行结果，可以计算SUM，MIN，MAX，AVG等。然而在实际应用中，比如在数据仓库系统中，经常需要对数据进行多维分析，不仅需要标准分组的结果(相当于简单GROUP BY)，还需要不同维度的小计(简单GROUP BY中取部分列分组)和合计(不分组)，从而提供多角度的数据分析支持，对于这种复杂分组需求，简单GROUP BY就很难达到这种目的，当然，我们可以使用UNION或UNION ALL将不同维度的分组结果联合起来，但是性能往往不好，而且SQL编写相对来说也复杂多了。

鉴于以上复杂分组的需求，从Oracle8i开始，Oracle提供了一系列扩展GROUP BY特性。当然，8i的扩展GROUP BY特性还是不够丰富，9i得到进一步的增强，比如提供了GROUPING SETS、重复列分组、组合列分组、连接分组、另外增加了两个扩展分组函数（GROUPING_ID和GROUP_ID)等一系列增强特性，从而使扩展GROUP BY足以解决实际应用中出现的大部分多维分组问题。

1.1    扩展GROUP BY概述

在本章的开头已经简单描述了扩展GROUP BY的应用场景，Oracle扩展GROUP BY允许使用SQL语句对数据汇总结果进行多维展现，从而生成复杂的报表，为决策者提供有效的数据支持。最重要的是，Oracle9i之后，扩展GROUP BY的功能已经趋于完善，能够满足大部分多维数据分析统计的工作。主要表现在：

1）  ROLLUP、CUBE、GROUPING SETS扩展GROUP BY子句提供了不同多维分组统计功能。

2）  3个扩展分组函数：GROUPING、GROUPING_ID、GROUP_ID提供扩展GROUP BY的辅助功能：提供区别结果行属于哪个分组级别，区分NULL值，建立有意义的报表，对汇总结果排序，过滤结果行等功能。

3）  对扩展GROUP BY允许按重复列分组、组合列分组、部分分组、连接分组等复杂功能，另外GROUPING SETS可以接受CUBE、ROLLUP操作作为参数，这些功能使扩展GROUP BY更加强大。

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

还需要强调一点，扩展GROUP BY还是需要符合GROUP BY的相关语法语义规则的。比如SELECT中引用的列必须是分组列。

       本章的表DEPT和EMP都来自于SCOTT用户下，虽然表比较简单，但是足以能说明扩展GROUP BY的功能。下面开始进入主要内容，探讨强大的扩展GROUP BY功能。

1.2    ROLLUP

在数据仓库系统中，一般多维分析报表，光有标准分组还不行，小计和合计往往是报表的核心内容，这种需求使用ROLLUP很常见，如果使用同等的UNION ALL实现，ROLLUP中的列越多，则需要写的SQL语句就越复杂，所以与UNION ALL相比，ROLLUP写法简单，而且性能一般更好。本章主要探讨普通ROLLUP和部分ROLLUP提供的强大功能。

1.2.1 UNION ALL实现ROLLUP功能

假设有这样的需求：

1} 统计每个部门每个职位的薪水和

2) 统计每个部门所有职位的薪水小计

3）统计所有部门所有职位的薪水合计

4）需要显示部门名、职位名和累加后的薪水值

       如果不知道Oracle中有扩展GROUP BY功能，那么很容易想起使用UNION ALL解决，的确在Oracle8i之前，没有扩展GROUP BY功能，就要使用UNION ALL解决了，写3条SQL分别完成3个分组功能，然后用UNION ALL连接：

--需求1实现
SELECT  a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname,b.job
UNION ALL
--需求2实现
SELECT  a.dname,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname
UNION ALL
--需求3实现
SELECT  NULL,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno;

显示结果如下：

DNAME                        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
ACCOUNTING                                            8750
RESEARCH                                              6775
SALES                                                 9400
                                                     24925


已选择13行。


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


------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |    17 |   423 |    13  (70)| 00:00:01 |
|   1 |  UNION-ALL                     |         |       |       |            |          |
|   2 |   HASH GROUP BY                |         |    12 |   336 |     5  (20)| 00:00:01 |
|   3 |    NESTED LOOPS                |         |    12 |   336 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | EMP     |    12 |   180 |     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 |   HASH GROUP BY                |         |     4 |    80 |     5  (20)| 00:00:01 |
|   8 |    NESTED LOOPS                |         |    12 |   240 |     4   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL          | EMP     |    12 |    84 |     3   (0)| 00:00:01 |
|  10 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|  12 |   SORT AGGREGATE               |         |     1 |     7 |            |          |
|* 13 |    TABLE ACCESS FULL           | EMP     |    12 |    84 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("A"."DEPTNO"="B"."DEPTNO")
  11 - access("A"."DEPTNO"="B"."DEPTNO")
13 - filter("B"."DEPTNO" IS NOT NULL)

从执行计划可以看出，为了实现这样的需求，需要多次访问EMP、DEPT表以及DEPT表的索引，这里的测试数据很少而且表结构简单，实际应用中表结构可能很复杂，经常是多表关联，数据量可能达到百万级，千万级，甚至上亿，那么使用UNION ALL，明显性能低下，如果使用WITH子句将常规分组的结果固定下来，然后在此基础上再聚合，效率可能比单纯UNION ALL好，但是还是不够完美，现在的需求是对2列进行多维分析，如果是很多列呢?必然增加语句复杂度，类似地，CUBE,GROUPING SETS用UNION ALL改写也有此类问题，何况扩展GROUP BY还提供了很多复杂功能，用UNION ALL改写就更加麻烦了。试想对于上面的需求，如果Oracle能提供一个这样的分组功能就好了：

1)      直接把分组的列按顺序写在一起，提供一个简单的语法结构。

2)      此语法结构先进行全分组：标准分组。

3)      然后这个语法结构从右到左递减列，做对应维度的分组，实现小计和合计。

如果有实现这3个要求的语法结构，那么上述需求就能很容易地实现，的确，Oracle提供了这样的功能，那就是ROLLUP分组。