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

一、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;

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);

三、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;

–每个小类的销售额

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

– 大类,以及小类的销售额

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;

使用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)

结果如下:

可知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);

由上面可见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;

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

我们再来看cube函数，同样返回11条数据，只有一次全表扫描，逻辑读也只有15个

所以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子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。