报表开发之扩展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 总结

先进行标准分组，在标准分组的基础上通过将列从右向左移动，然后进行更高一级的小计，最后合计。