DB2分组函数ROLLUP和CUBE的使用

第一次写博客，不足之处请批评指正。

DB2分组函数ROLLUP和CUBE的使用

1.     初始化实验环境

1)     创建测试表group_test：

建表语句：create table group_test (group_id int not null,job varchar(10),name varchar(10),salaryint)

2)      初始化数据：

insert into group_test values (10,'Coding',    'Bruce',1000)

insert into group_test values (10,'Programmer','Clair',1000)

insert into group_test values (10,'Architect', 'Gideon',1000)

insert into group_test values (10,'Director',  'Hill',1000)

insert into group_test values (20,'Coding',    'Jason',2000)

insert into group_test values (20,'Programmer','Joey',2000)

insert into group_test values (20,'Architect', 'Martin',2000)

insert into group_test values (20,'Director',  'Michael',2000)

insert into group_test values (30,'Coding',    'Rebecca',3000)

insert into group_test values (30,'Programmer','Rex',3000)

insert into group_test values (30,'Architect', 'Richard',3000)

insert into group_test values (30,'Director',  'Sabrina',3000)

insert into group_test values (40,'Coding',    'Samuel',4000)

insert into group_test values (40,'Architect', 'Tina',4000)

insert into group_test values (40,'Director',  'Wendy',4000)

3)      初始化后数据情况如下：

select * from group_test

2.     先看一下普通分组的效果，对group_id进行普通的group by操作—按照小组进行分组

select group_id,sum(salary) from group_test group by group_id

3.     DB2中ROLLUP和CUBE用法

DB2的GROUP BY语句除了最基本的语法之外，还支持ROLLUP和CUBE语句

如果是GROUP BY ROLLUP(A,B,C)的话，首先会对全表进行GROUP BY，然后对（A）进行GROUP BY，然后对（A,B）进行GROUP BY操作，最后对（A,B,C）进行GROUP BY操作。

如果是GROUP BY CUBE (A,B)的话，首先会对（B）进行GROUP BY，然后对全表进行GROUP BY，然后对（A）进行GROUP BY，最后对（A,B）进行GROUP BY操作。

4.      对group_id进行普通的roolup一列的操作(最上面一行是分组统计结果)

select  group_id,sum(salary) from group_test group by rollup(group_id)

5.     对group_id进行普通的cube一列的操作(最上面一行是分组统计结果)

select group_id,sum(salary) from group_test group by cube(group_id)

到目前为止还没看出来，rollup和cube的区别,别着急，下面就有了。

6.     用group by 语句翻译一下3、4的SQL语句如下：

select  group_id,sum(salary) from group_test group by group_id union all select null,sum(salary) from group_test order by 1

7.     看一个rollup两列的情况

select  group_id,job,sum(salary) from group_test group by rollup(group_id, job)

8.     用group by 语句翻译一下3、4的SQL语句如下：

select  group_id,job,sum(salary) from group_test group by group_id, job union all  select group_id,null,sum(salary) fromgroup_test group by group_id union all select null,null,sum(salary) fromgroup_test order by 1,2

9.     看一个cube两列的情况

select  group_id,job,sum(salary) from group_test group by cube (group_id, job)

 

10.  GROUPING（COLUMN）使用

使用GROUPING()可以判断该行是数据库本来的行，还是统计产生的行。GROUPING值为0时说明这个值是数据库中本来的值，为1说明是统计的结果（也可以说该列为空时是1，不空时是0），根据这一特性，可以使显示结果更加人性化。

11.  加上GROUPING() 函数的效果（ROLLUP）

select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test groupby rollup(group_id, job)

假名：selectgroup_id,job,grouping(GROUP_ID)as ID,grouping(JOB)as JB ,sum(salary)as salaryfrom group_test group by rollup(group_id, job)

12.  加上grouping函数的效果（CUBE）

select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test groupby cube(group_id, job)

假名：select group_id,job,grouping(GROUP_ID)as ID,grouping(JOB)as JB,sum(salary)as salary from group_test group by cube(group_id, job)

13.  小结

ROLLUP和CUBE在数据统计和报表生成过程中带来极大的便利，而且效率比起来GROUP+UNION组合方式效率高很多。