GBase 8a OLAP函数（一）GROUP BY ROLLUP

环境信息：

数据库版本9.5.2.44.10

使用emp和dept表测试：

该函数常用于需要小计和总计的场景，例如GROUP BY ROLLUP(A,B,C) 

1. 首先会对(A、 B、 C)进行 GROUP BY;

2. 然后对(A、B)进行 GROUP BY;

3. 然后是(A)进行 GROUP BY;

4. 最后对全表进行 GROUP BY 操作;

举例：按照部门、职位统计工资，需要部门小计+总计

select deptno, job, sum(sal)

from emp

group by rollup(deptno, job);

可以进一步把NULL值改为小计和总计，修改如下：

select nvl(deptno, '总计') as deptno, 

case when deptno is null then '' else nvl(job, '小计') end as job, 

sum(sal)

from emp

group by rollup(deptno, job)

order by deptno, job;

不使用rollup，能否实现相同的效果？答案是可以的，首先通过各层次group by，然后union all起来，实现roll up的效果：

select deptno, job, sum(sal) as sum_sal

from emp

group by deptno, job

union all

select deptno, null, sum(sal)

from emp

group by deptno

union all

select null, null, sum(sal)

from emp;

然后再通过子查询处理“小计”、“总计”

select nvl(deptno, '总计') as deptno, 

  case when deptno is null then '' else nvl(job, '小计') end as job, 

  sum_sal

from (

  select deptno, job, sum(sal) as sum_sal

  from emp

  group by deptno, job

  union all

  select deptno, null, sum(sal)

  from emp

  group by deptno

  union all

  select null, null, sum(sal)

  from emp

) t

order by deptno, job;