Oracle Group by+rollup+cube 的应用
首先我们创建一个示例表：
Create table test_group
(v_name varchar2(4)
,v_size varchar2(4)
,v_color varchar2(4)
,n_num number(4)
);
--插入数据
insert into test_group
select '桌子','大','红',10 from dual union all
select '桌子','大','绿',10 from dual union all
select '桌子','小','红',20 from dual union all
select '桌子','小','绿',40 from dual union all
select '椅子','大','红',20 from dual union all
select '椅子','大','绿',30 from dual union all
select '椅子','小','红',50 from dual union all
select '椅子','小','绿',100 from dual;
commit;
数据如下：
|
V_NAME |
V_SIZE |
V_COLOR |
N_NUM |
|
桌子 |
大 |
红 |
10 |
|
桌子 |
大 |
绿 |
10 |
|
桌子 |
小 |
红 |
20 |
|
桌子 |
小 |
绿 |
40 |
|
椅子 |
大 |
红 |
20 |
|
椅子 |
大 |
绿 |
30 |
|
椅子 |
小 |
红 |
50 |
|
椅子 |
小 |
绿 |
100 |
最简单的分组(为了初学者)
Select v_name,v_size,sum(n_num) as total_num
From test_group
group by v_name,v_size
|
V_NAME |
V_SIZE |
TOTAL_NUM |
|
椅子 |
大 |
50 |
|
桌子 |
小 |
60 |
|
椅子 |
小 |
150 |
|
桌子 |
大 |
20 |
使用rollup
rollup() 是group by()子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录
Select v_name,v_size,sum(n_num) as total_num
From test_group
group by rollup(v_name,v_size)
| V_NAME | V_SIZE | TOTAL_NUM |
| 椅子 | 大 | 50 |
| 椅子 | 小 | 150 |
| 椅子 | 200 | |
| 桌子 | 大 | 20 |
| 桌子 | 小 | 60 |
| 桌子 | 80 | |
| 280 |
注意结果中
对各个型号,各个物品(不分颜色)进行统计，;
对各个物品（不分大小和颜色）进行统计（蓝色底纹的数据）
对所有物品（不分类别，大小和颜色）进行总数量统计(绿色底纹的数据)
如果我想要所有大的物品（不区分椅子还是桌子，也不区分颜色）统计数据怎么办？
可以将group by rollup(v_name,v_size)变为：group by rollup(v_size ，V_name)
那如果我即想要对所有物品类别进行统计还想要对大小进行统计怎么办？这时就需要用到cube
使用cube
cube() 也是group by()子句的一种扩展，可以返回每一个列组合的小计记录(就是所有的维度都会进行统计)，同时在末尾加上总计记录.
Select v_name,v_size,sum(n_num) as total_num
From test_group
group by cube(v_name,v_size)
|
V_NAME |
V_SIZE |
TOTAL_NUM |
|
|
|
280 |
|
|
大 |
70 |
|
|
小 |
210 |
|
椅子 |
|
200 |
|
椅子 |
大 |
50 |
|
椅子 |
小 |
150 |
|
桌子 |
|
80 |
|
桌子 |
大 |
20 |
|
桌子 |
小 |
60 |
注意结果中
对各个型号，各个物品(不分颜色)进行分别统计;
对各个物品（不分大小和颜色）进行分别统计（蓝色底纹的数据）
对各个型号（不分物品和颜色）进行分别统计（黄色底纹的数据）
对所有物品（不分类别，大小和颜色）进行统计(绿色底纹的数据)
那么，如何可以方便的区分结果中那个是小计，哪个是合计呢？这时需要用到grouping_id()
使用grouping_id()
grouping_id()函数可以接受一列或多列，返回grouping()位向量的十进制值
grouping()位向量的计算方法是将按照顺序对每一列调用grouping()函数的结果组合起来
以grouping_id(v_name,v_size)为例：
v_name ,v_size 位向量 grouping_id(v_name,v_size)
非空 非空 00 0
非空 空 01 1
空 非空 10 2
空 空 11 3
Select v_name,v_size,v_color,sum(n_num) as total_num,grouping_id(v_name,v_size,v_color) as grping_Id
From test_group
group by cube(v_name,v_size,v_color)
order by 1,2,3
|
V_NAME |
V_SIZE |
TOTAL_NUM |
GRPING_ID |
|
椅子 |
大 |
50 |
0 |
|
椅子 |
小 |
150 |
0 |
|
椅子 |
|
200 |
1 |
|
桌子 |
大 |
20 |
0 |
|
桌子 |
小 |
60 |
0 |
|
桌子 |
|
80 |
1 |
|
|
大 |
70 |
2 |
|
|
小 |
210 |
2 |
|
|
|
280 |
3 |
7.使用group_id()函数
使用group_Id()之前,我们先分析一下下面的查询结果:
Select v_name,v_size,sum(n_num) as total_num
From test_group
group by v_name,rollup(v_name,v_size)
|
V_NAME |
V_SIZE |
TOTAL_NUM |
|
椅子 |
大 |
50 |
|
椅子 |
小 |
150 |
|
桌子 |
大 |
20 |
|
桌子 |
小 |
60 |
|
椅子 |
|
200 |
|
桌子 |
|
80 |
|
椅子 |
|
200 |
|
桌子 |
|
80 |
和group by rollup(v_name,v_size) 相比结果集中多了两组重复的数据,少了总计.
多出来的数据是怎么来的呢?期实是由总计演化而来的,演化的过程如下:
首先可以将语句
Select v_name,v_size,sum(n_num) as total_num
From test_group
group by v_name,rollup(v_name,v_size)
进行分解:
Select v_name,v_size,sum(n_num) as total_num
From test_group
where v_name='椅子'
group by rollup(v_name,v_size)
Select v_name,v_size,sum(n_num) as total_num
From test_group
where v_name='桌子'
group by rollup(v_name,v_size)
因为这里只有’椅子’,’桌子’两种物品.
结果分别如下:
|
V_NAME |
V_SIZE |
TOTAL_NUM |
|
椅子 |
大 |
50 |
|
椅子 |
小 |
150 |
|
椅子 |
|
200 |
|
椅子 |
|
200 |
|
V_NAME |
V_SIZE |
TOTAL_NUM |
|
桌子 |
大 |
20 |
|
桌子 |
小 |
60 |
|
桌子 |
|
80 |
|
桌子 |
|
80 |
这是两个使用rollup的查询结果,里面的总计部分期初和小计部分的数据是相同的,因为只计也只是包含椅子(或桌子)的数据. 所以这里可以用椅子(或桌子)来填充总计的空白处(表格中的黄色字体部分).然后将这两个表格的数据合并在一起,就可以得到
group by v_name,rollup(v_name,v_size) 的结果了.
那我们如何来区分这两组相同的数据呢?这时就可以用到group_id()函数了.
如下:
Select v_name,v_size,sum(n_num) as total_num
,grouping_id(v_name,v_size) as gping_id
,group_Id() as gp_id
From test_group
group by v_name,rollup(v_name,v_size)
|
V_NAME |
V_SIZE |
TOTAL_NUM |
GPING_ID |
GP_ID |
|
椅子 |
大 |
50 |
0 |
0 |
|
椅子 |
小 |
150 |
0 |
0 |
|
桌子 |
大 |
20 |
0 |
0 |
|
桌子 |
小 |
60 |
0 |
0 |
|
椅子 |
|
200 |
1 |
0 |
|
桌子 |
|
80 |
1 |
0 |
|
椅子 |
|
200 |
1 |
1 |
|
桌子 |
|
80 |
1 |
1 |
为了进行区分这里一并加上了grouping_id,注意观察grouping_id()和group_Id()的区别.
