oracle sql 高级编程学习笔记（十八）

短命女 2022-05-13 11:10 212阅读 0赞

# 一、group by 子句优点 #

1、使sql语句更具可读性  
2、书写起来比使用很多相关子查询更简单  
3、减少了重复访问同一个数据块的次数（从而也得到了更好的性能）

# 二、group by的局限性 #

1、LOB列，嵌套或数组不能用作group by表达式的一部分  
2、标量子查询表达式是不允许的  
3、如果group by 子句引用任何对象类型的列，则查询不能并行化

## 实例演示 ##

with tmp1 as
     (select to_clob(d.dname) dname from emp e left join dept d on e.deptno = d.deptno) select tmp1.dname from tmp1 group by tmp1.dname;

![这里写图片描述][70]

select d.dname,count(e.empno) emp_count from emp e left join dept d on e.deptno = d.deptno group by (select dname from dept d2 where d2.dname=d.dname);

![这里写图片描述][70 1]

# 三、cube函数 #

## 实例演示： ##

创建测试表  
字段有 大类，小类，明细，销售额

create table test_cube( main_type varchar2(10), small_type varchar2(10), detail_type varchar(10), sales_mount number(10,2) );

插入数据

insert into test_cube (MAIN_TYPE, SMALL_TYPE, DETAIL_TYPE, SALES_MOUNT) values ('生活用品', '成人', '洗发露', 1600.00);
    
    insert into test_cube (MAIN_TYPE, SMALL_TYPE, DETAIL_TYPE, SALES_MOUNT) values ('生活用品', '婴儿', '尿不湿', 2000.00);
    
    insert into test_cube (MAIN_TYPE, SMALL_TYPE, DETAIL_TYPE, SALES_MOUNT) values ('电器类', '个人', '电脑', 10000.00);
    
    insert into test_cube (MAIN_TYPE, SMALL_TYPE, DETAIL_TYPE, SALES_MOUNT) values ('电器类', '公司', '投影仪', 2000.00);

–用cube函数按照大类和小类分组 求销售额  
我们先求每个大类的销售额

select t.main_type,sum(t.sales_mount)sales_mount from test_cube t group by t.main_type;

![这里写图片描述][70 2]

–每个小类的销售额

select t.small_type,sum(t.sales_mount)sales_mount from test_cube t group by t.small_type;

![这里写图片描述][70 3]  
– 大类,以及小类的销售额

select t.main_type, t.small_type,sum(t.sales_mount)sales_mount from test_cube t group by t.main_type, t.small_type;

![这里写图片描述][70 4]

使用cube函数

select t.main_type,t.small_type,sum(t.sales_mount) from test_cube t group by cube( t.main_type,t.small_type)

结果如下:

![这里写图片描述][70 5]  
可知cube结果不仅包含了上面所有的结果，还多了一个总和，  
cube(a,b) 包含(a,b),(a),(b),()的分组情况()表示;  
而cube(a,b,c)  
包含的结果(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),();

select  t.main_type,t.small_type,t.detail_type,sum(t.sales_mount) from  test_cube t
    group by cube( t.main_type,t.small_type,t.detail_type);

![这里写图片描述][70 6]

由上面可见cube函数可以帮助我们省去很多个union all，让代码看起来更加简洁

## 执行计划 ##

同样我们再来看看使用union all 的执行计划；

select t.main_type,null,sum(t.sales_mount)sales_mount from test_cube t group by t.main_type union all select null,t.small_type,sum(t.sales_mount)sales_mount from test_cube t group by t.small_type union all select t.main_type, t.small_type,sum(t.sales_mount)sales_mount from test_cube t group by t.main_type, t.small_type;

![这里写图片描述][70 7]

可知执行计划有三次全表扫描 逻辑读是45个

我们再来看cube函数，同样返回11条数据，只有一次全表扫描，逻辑读也只有15个  
![这里写图片描述][70 8]

所以cube函数也大大的提升了sql的性能，特别是当大数据时，三次全表扫描与一次相比的差异就更加明显了。

## 总结: ##

扩展 rollup(a,b) 其左右与cube相似，但结果集却大不相同  
group by rollup(a,b) 包含的结果集 (a,b) ,(a),();  
group by rollup(a,b,c) 包含的结果集 (a,b,c) ,(a,b),(a),();

ROLLUP，是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。  
CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。

[70]: /images/20220513/43163b812ca44433b18cc779156d5a44.png
[70 1]: /images/20220513/5c4d9560359940dfa49943e4801cc910.png
[70 2]: /images/20220513/f1c872b468ad4232af86e7407b5b7b58.png
[70 3]: /images/20220513/cb3d849c3ae345cfb5b7488974c5eb91.png
[70 4]: /images/20220513/b9a907731c774278bed97790e4d04b41.png
[70 5]: /images/20220513/7c8d965ccd0e4a519cd84e3e62ae1ace.png
[70 6]: /images/20220513/8c022fb7c30144fca3b93c9ade3555aa.png
[70 7]: /images/20220513/e17a1df535514db58345567e2fe4092f.png
[70 8]: /images/20220513/060ccefd07da4905b75973f50db8af23.png