报表开发之扩展GROUPBY

在实际运用中，比如在数据仓库中，经常需要对数据进行多维分析，不仅需要标准分组的结果（相当于 GROUP BY），还需要不同维度的小计（简单 GROUP BY 中取部分列分组）和合计（不分组），从而 提供多角度的数据分析，对于这种复杂分组需求，简单 GROUP BY 很

在实际运用中，比如在数据仓库中，经常需要对数据进行多维分析，不仅需要标准分组的结果（相当于

GROUP BY），还需要不同维度的小计（简单 GROUP BY 中取部分列分组）和合计（不分组），从而

提供多角度的数据分析，对于这种复杂分组需求，简单 GROUP BY 很难达到这种目的，当然，我们可以

使用 UNION 或 UNION ALL 将不同维度的分组结果联合起来，但性能往往不好，此时，我们可以使用扩

展 GROUP BY 来满足实际运用中出现的大部分多维分组问题。

1. 扩展 GROUP BY 概述

扩展 GROUP BY 进行多维数据统计的工作，主要表现在：

a. ROLLUP、CUBE、GROUPING SETS 扩展 GROUP BY 子句提供了丰富的多维分组统计功能； b. 3个扩展分组函数：GROUPING、GROUPING_ID、GROUP_ID 提供扩展 GROUP BY 的辅助功 能，例如，提供区别结果行属于哪个分组级别、区分 NULL 值、建立有意义的报表、对汇总结果排 序、过滤结果行等功能 c. 对扩展 GROUP BY 允许按重复列分组、组合列分组、部分分组、连接分组等，另外 GROUPING SETS 可以接受 CUBE、ROLLUP 操作作为参数，这些功能使扩展 GROUP BY 更加强大。

2. ROLLUP 2.1 UNION ALL 实现 ROLLUP 功能 假设有这样的需求： a. 统计每个部门每个职位的薪水和 b. 统计每个部门所有职位的薪水小计 c. 统计所有部门所有职位的薪水合计 d. 需要显示部门名、职位名和累加后的薪水值
-- 需求一实现 select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,e.job union all -- 需求二实现 select d.dname,null,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname union all -- 需求三实现 select null,null,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno
上面的代码通过执行计划（set autotrace on）可以发现，需要多次访问EMP、DEPT表的索引，如果 实际运用中表的结构很复杂，将严重影响性能。
2.2 ROLLUP 分组 从 Oracle 8i 开始，Oracle 使用 ROLLUP 对 GROUP BY 进行扩展，它允许计算标准分组及相应维度 的小计、合计。 ROLLUP 的语法结构如下： SELECT ... GROUP BY ROLLUP(grouping_column_reference_liist) ROLLUP 后面指定的列以逗号分隔，ROLLUP 的计算和其后面指定列的顺序有关，因为 ROLLUP 分组 过程具有方向性，先计算标准分组，然后列从右向左递减计算更高一级的小计，一直到列全部被选完， 最后计算合计。 如果 ROLLUP 中指定 n 列，则整个计算过程中的分组方式有n+1种。
-- 使用ROLLUP 实现 2.1 节的需求 select d.dname,e.job,sum(e.sal) from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.job);
ROLLUP 分组具有方向性，从上面的结果可以看出，ROLLUP(d.dname,e.job) 分组的过程是： a. 标准分组：GROUP BY(d.dname,e.job)，对每个部门每个职位进行分组； b. 从右到左递减：GROUP BY(d.dname,null)，其实这个null没有必要使用，这里只是方便分析， 这个过程是对上个级别分组的小计，也就是对每个 dname 值，计算横跨所有 job 的小计； c. 最后合计：相当于 GROUP BY(null,null)。 再例如 ROLLUP(a,b,c)

范例：实现以下需求 a. 计算每个入职时间（年）、部门、职位的标准分组的薪水和 b. 计算每个入职时间（年）、部门的所有职位的薪水小计 c. 计算每个入职时间（年）的所有部门所有职位的薪水小计 d. 最后合计薪水，显示入职时间（年）、部门名、职位名
with t as (
select to_char(e.hiredate,'yyyy') hireyear,d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d

where e.deptno=d.deptno group by rollup(to_char(e.hiredate,'yyyy'),d.dname,e.job))

select rownum,t.* from t;

接下来分析上述代码的结果：

因为 ROLLUP 分组过程具有方向性，所以通过改变 ROLLUP 中列的顺序就可以达到改变报表结果和含义的目的， 如现在需要查询的是 标准分组、计算每个 job 的所有部门的小计、最后合计，则代码为： select e.job,d.dname,sum(e.sal) sum_sal from emp e,dept d where e.deptno=d.deptno group by rollup(e.job,d.dname);
2.3 部分 ROLLUP 分组 通过将部分列从 ROLLUP 中移出来，放在 GROUP BY 中，这样合计肯定没有了，某些小计也没有了。 需求：不需要每个入职时间（年）的所有部门所有职位的薪水小计，合计也不需要 select to_char(hiredate,'yyyy'),d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d where e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname,rollup(e.job); select to_char(hiredate,'yyyy'),d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d where e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname,e.job union all select null,null,null,sum(e.sal) sum_sal from emp e,dept d where e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname; 注：将 hiredate 和 dname 从 ROLLUP 中移出来，就可以将每个入职时间（年）的所有部门所有职位的 薪水小计及合计剔除，最终只查询标准分组和每个入职时间（年）、部门的所有职位的小计。 2.4 ROLLUP 总结 先进行标准分组，在标准分组的基础上通过将列从右向左移动，然后进行更高一级的小计，最后合计。
3. CUBE CUBE 是对不同维度的所有可能分组进行统计，从而生成交叉报表；这种需求比 ROLLUP更加精细， 包含了 ROLLUP 的统计结果，而且还有其他组合分组结果（小计）。 3.1 CUBE 分组 CUBE语法结构： SELECT ... GROUP BY CUBE(grouping_column_reference_list) 如果 CUBE 中指定 n 列，则整个计算过程中的分组方式有 power(2,n) 种。

将 2.2 节使用 ROLLUP(dname,job) 替换为 CUBE select d.dname,e.job,sum(e.sal) from dept d,emp e where d.deptno=e.deptno group by cube(d.dname,e.job); 下图分析 CUBE(dname,job) 对应分组级别：

3.2 部分 CUBE 分组 和 ROLLUP 一样，也有部分 CUBE 操作，可以去掉合计及某些不需要的小计，比如上面的 GROUP BY CUBE(d.dname,e.job) 改为 GROUP BY d.dname CUBE(e.job) 则剔除了合计及GROUP BY job。 select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,cube(e.job); select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,e.job union all select null,null,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname;
3.3 CUBE总结 先进行合计，然后小计，最后再按标准分组

4. GROUPING SETS 实现小计 前面所说的两种多维数据统计的方法，即 ROLLUP 和 CUBE，它们的输出结果是由对应分组的行伴随 着小计行产生的，它们会产生标准分组、各种小计及总计，但是有时候我们只关心某个单列分组，从而 得到其它维度小计的信息，这样就需要使用 GROUPING SETS扩展分组，它是Oracle9i提供的。 比如 GROUP BY GROUPING SETS(a,b,c) 相当于 GROUP BY a、GROUP BY b、GROUP BY c 这三 个单列分组，从而得到其他维度的小计信息。 n列的 GROUPING SETS 的分组总类有 n 个。 4.1 GROUPING SETS 分组 语法结构： SELECT ... GROUP BY GROUPING SETS(grouping_column_reference_list) 将2.2节中的 ROLLUP 改为 GROUPING SETS select to_char(e.hiredate,'yyyy') hireyear,d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d where e.deptno=d.deptno group by grouping sets(to_char(e.hiredate,'yyyy'),d.dname,e.job); 注：GROUPING SETS 的结果是分别按单列分组后 UNION ALL的结果； GROUPING SETS 的结果和列的顺序没有关系，而且结果的顺序也是无序的。
4.2 部分 GROUPING SETS 分组 select d.dname,to_char(e.hiredate,'yyyy') hireyear,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,grouping sets(to_char(e.hiredate,'yyyy'),e.job); select d.dname,to_char(e.hiredate,'yyyy') hiredate,null job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,to_char(e.hiredate,'yyyy') union all select d.dname,null,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,e.job;
上述语句统计的是：对于每个部门每个入职时间（年），对所有职位进行小计及 对于每个部门每个职位，对每个入职时间（年）进行小计。
4.3 CUBE、GROUPING 作为 GROUPING SETS 的参数 GROUPING SETS 操作能够接受 ROLLUP 和 CUBE 作为它的参数，GROUPING SETS 操作只对 单列进行分组，而不提供合计的功能，如果需要 GROUPING SETS 提供合计的功能，那么可以使 用 ROLLUP 或 CUBE 作为 GROUPING SETS 的参数。 改写前面的 GROUPING SETS(d.dname,e.job)，提供合计功能。 select d.dname,e.job,sum(e.sal) from dept d,emp e where d.deptno=e.deptno group by grouping sets(rollup(d.dname),rollup(e.job)); select d.dname,null job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname) union all select null dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(e.job); 注：上述语句会产生两个合计行，因为 ROLLUP 或 CUBE 作为 GROUPING SETES 的参数，相当 于对每个 ROLLUP 或 CUBE 操作的 UNION ALL； 可使用 DISTINCT 剔除重复行； ROLLUP 和 CUBE 不能接受 GROUPING SETS 作为参数，ROLLUP 和 CUBE 之间相互作为 参数也不可以。
4.4 GROUPING SETS 总结 GROUPING SETS 的结果和列的顺序没有关系，而且结果的顺序也是无序的。

5. 组合列分组、连接分组、重复列分组 组合列分组、连接分组、重复列分组都是Oracle 9i 中才有的特性。组合列也就是将多个列用括号括 起来，从而将多个列当做整体对待，比如 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')); 下图是普通列 ROLLUP 和组合列 ROLLUP 的对比（CUBE、GROUPING SETS类似）

上图的组合列分组达到了剔除某些小计的功能，且保证了最终结果又合计行。
连接分组允许在 GROUP BY 之后出现多个 ROLLUP、CUBE、GROUPING SETS 操作，这样分组级别 更多，报表更加精细。

实际上不管是同类型的连接分组还是不同类型的连接分组之间，最后的分组级别种类都是每个扩展 分组级别种类的乘积，分组级别是笛卡尔积。比如同类型连接分组 ROLLUP(a,b)，ROLLUP(c) 最终 结果有 3*2=6 种分组级别，不同类型连接分组 ROLLUP(a,b)，GROUPING SETS(c) 有3*1=3 种分 组级别。
重复列分组就是 GROUP BY 中允许重复列，比如在 ROLLUP 中使用复杂的复合列分组可能会用到， 比如 GROUP BY ROLLUP(a,(a,b))、GROUP BY a,ROLLUP(a,b) 都属于重复列。
5.1 组合列分组 组合列分组有过滤某些小计或计算一些额外的小计等功能。 前面的部分 ROLLUP、部分CUBE 都没有合计，使用组合列可以实现部分 ROLLUP、部分 CUBE的 功能，还能有合计。 需求： a. 对部门、入职时间（年）、职位进行标准分组 b. 对每个部门计算横跨入职时间（年）和职位的小计 c. 最后合计 select d.dname,to_char(e.hiredate,'yyyy') hireyear,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,(to_char(e.hiredate,'yyyy'),e.job)); CUBE 和 ROLLUP 操作都可以用组合列分组转为对应的 GROUPING SETS， 例如， ROLLUP(a,b,c) 转为等价的 GROUPING SETS 是 GROUPING SETS((a,b,c),(a,b),(a),NULL); CUBE(a,b,c) 转为等价的 GROUPING SETS 是 GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),NULL);
5.2 连接分组 连接分组是Oracle 9i 才有的功能，它允许 GROUP BY后面有多个 ROLLUP、CUBE、GROUPING SETS， 连接分组的分组级别是由每个 ROLLUP、CUBE、GROUPING SETS 分组组成的笛卡尔积。 比如 ROLLUP(a,b),ROLLUP(c,d,e) 共有分组统计级别为 3*4=12 种。 select d.dname,e.job,to_char(e.hiredate,'yyyy') hireyear,sum(sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.job),rollup(to_char(e.hiredate,'yyyy')); GROUP BY ROLLUP(d.dname,e.job),ROLLUP(to_char(e.hiredate),'yyyy') 实现了 6 种分组结果， 相当于两个 ROLLUP 的笛卡尔积，如下表：

CUBE、GROUPING SETS 都类似，利用连接分组，CUBE 可以用 ROLLUP转换： a. 当只有一列的时候，比如 ROLLUP(a) 与 CUBE(a) 是一样的，都有两种统计方式； b. 当有 n 列的时候，比如 CUBE(a,b,c) 可以转为 ROLLUP(a),ROLLUP(b),ROLLUP(c) 的连接分组表示， 也就是有 n 列的 CUBE 转为 ROLLUP 则需要拆开，转为单列 ROLLUP的连接分组即可。 select d.dname,e.job,to_char(e.hiredate,'yyyy') hireyear,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname),rollup(e.job),rollup(to_char(e.hiredate,'yyyy')); group by cube(d.dname,e.job,to_char(hiredate,'yyyy')); 注：连接分组一般是同类型的连接分组，不同类型的连接分组比如 GROUP BY ROLLUP...CUBE... 等是不常用的， 除非有复杂需求。
5.3 重复列分组 重复列分组也是Oracle 9i 才有的，也就是 GROUP BY 后面允许重复列。 select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,rollup(d.dname,e.job); select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,e.job union all select null,null,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname union all select null,null,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname;
5.4 组合列分组、连接分组、重复列分组总结 a. 组合列主要实现剔除某些不必要的小计保留合计； b. 连接分组按每个扩展分组的分组级别的笛卡尔积形式进行操作，分组类型更多更细， 比如 ROLLUP 连接分组就实现了类似 CUBE 的功能。

6. 3个扩展分组函数：GROUPING、GROUPING_ID、GROUP_ID 主要内容有： a. 使用 GROUPING 函数制作有意义的报表，以及对结果进行过滤； b. 使用 GROUPING_ID 函数对结果进行过滤及排序； c. 使用 GROUP_ID 函数剔除重复行。
6.1 GROUPING 函数 对扩展 GROUP BY 子句来说，比如 ROLLUP、CUBE 会生成标准分组、一系列小计及合计，这样查询结果中， 有些行的列值就会存在 NULL。NULL 在扩展 GROUP BY 中有特殊的意义，结果行中的列值为 NULL，一般 就意味着是此列的小计或合计，但是 NULL 也有可能是原始数据存在的 NULL(如 emp.mgr=NULL)，所以引入 了 GROUPING 函数专门处理扩展GROUP BY 分组结果中 NULL 的问题： a. 它只接受一个参数，此参数来自 ROLLUP、CUBE、GROUPING SETS 中的列； b. GROUPING 函数对于是小计或合计的列返回 1，否则返回 0。如果小计或合计列的值是 NULL，但是原始 数据可能也存在 NULL，则常使用 GROUPING 函数来区分最终结果行中的 NULL 是原始数据中存在的， 还是小计或合计列的值，常和 DECODE 函数配合使用。 6.1.1 用于格式化报表，生成有意义的报表 select d.dname,e.mgr,sum(e.sal) from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr); 6.2 GROUPING_ID 函数 GROUPING 函数用来生成有意义的报表及过滤一些分组级别； GROUPING_ID 函数主要用来过滤分组级别和排序结果（显示排序）。 不管 ROLLUP、CUBE、GROUPING SETS 的结果是否有默认顺序，都是不可靠的。 GROUPING_ID 函数可以接受对个参数，这些参数来自于 ROLLUP、CUBE、GROUPING SETS中的 列（参数来源和 GROUPING 函数一致），按列从左到右顺序计算，如果此列是分组列则为 0 ，如果是 对此列的小计或合计则为 1，然后按列顺序将计算结果组成二进制序列（位向量），最后将位向量转为 十进制数。如CUBE(a,b)，则GROUPING_ID(a,b) 的结果如下图所示：

GROUPING_ID的好处就是可以对多列进行计算，从而得到此列的分组级别。 从上图可以看出，GROUPING_ID(column_list) 中的 colum_list 和扩展分组保持一致，那么 GROUPING_ID 值得种类必须与对应扩展分组数目保持一致： 比如 CUBE(a,b,c) 的GROUPING_ID(a,b,c)的值有 8 种，ROLL(a,b,c)的GROUPING_ID(a,b,c) 的值有 4 种。
GROUPING_ID 的取值范围都一样，和列的数目有关，比如有 n 列，则 GROUPING_ID的取值范围在[ 0-2^n-1 ] 6.2.1 GROUPING_ID 函数过滤某些分组结果 需求：改写6.1 节 GROUPING 过滤结果的例子，用 GROUPING_ID 实现同等功能。 先分析对于 ROLLUP(d.dname,e.mgr,e.job) 使用 GROUPING_ID 函数的结果，注意的是，一般使用 GROUPING_ID函数，列的顺序要与 ROLLUP、CUBE、GROUPING SETS 中的顺序保持一致。

从表中就可以清楚的看出，实现这个需求只要 GROUPING_ID(d.dname,e.mgr,e.job) 取 0 和 7 即可。 select d.dname,e.mgr,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr,e.job) having grouping_id(d.dname,e.mgr,e.job) in (0,7)