欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

GROUP BY的扩展

程序员文章站 2022-06-05 21:37:50
...

GROUP BY的扩展主要包括ROLLUP,CUBE,GROUPING SETS三种形式。

GROUP BY的扩展主要包括ROLLUP,,CUBE,GROUPING SETS三种形式。

ROLLUP

rollup相对于简单的分组合计增加了小计和合计,解释起来会比较抽象,下面我们来看看具体事例。

例1,统计不同部门工资的总和和所有部门工资的总和。

SQL rollup(deptno); DEPTNO SUM(SAL)

例2,该例中先对deptno进行分组,再对job进行分组

SQL rollup(deptno,job); DEPTNO JOB SUM(SAL) CLERK PRESIDENT CLERK MANAGER CLERK SALESMAN rows selected.

如果要用普通的分组函数实现,可用UNION ALL语句:

--实现单个部门,单个工种的工资的总和
select
deptno,job,sum(sal) from emp group by deptno,job deptno,null,sum(sal) from emp group by deptno ,null,sum(sal) from emp ,2

下面我们分别来看看两者的执行计划及统计信息,

ROLLUP语句:

Execution Id STATEMENT (SORT (ACCESS ( recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 895 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client client 1 sorts (memory) 0 sorts (disk) 13 rows processed

UNION ALL语句:

Execution Id STATEMENT (SORT (HASH (ACCESS (HASH (ACCESS (SORT AGGREGATE ACCESS (
recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 895 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client client 1 sorts (memory) 0 sorts (disk) 13 rows processed

不难看出,相同的功能实现,ROLLUP相对于UNION ALL效率有了极大的提升。

CUBE

cube相对于rollup,结果输出更加详细。

例1,在本例中还不是很明显。

SQL cube(deptno); DEPTNO SUM(SAL)

例2,相对于rollup,cube还对工种这一列进行了专门的汇总。

SQL cube(deptno,job); DEPTNO JOB SUM(SAL) CLERK 4150 ANALYST 6000 MANAGER 8275 SALESMAN 5600 PRESIDENT CLERK PRESIDENT CLERK MANAGER CLERK SALESMAN 5600 18 rows selected.

GROUPING SETS

GROUPING SETS相对于ROLLUP和CUBE,结果是分类统计的,可读性更好一些。

例1:

SQL)hireyear,sets(deptno,job,to_char(hiredate,)); DEPTNO JOB HIRE SUM(SAL) ---------- --------- ---- ---------- CLERK 4150 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST

例2:

SQL sets(deptno,job); DEPTNO JOB SUM(SAL) ---------- --------- ---------- CLERK 4150 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST rows selected.

对于该例,如何用UNION ALL实现呢?

job deptno,null,sum(sal) from emp group by deptno;

两者的执行计划及统计信息分别如下:

GROUPING SETS:

Execution Id STATEMENT (TRANSFORMATION SYS_TEMP_0FD9D6795_E71F79 ACCESS (SYS_TEMP_0FD9D6796_E71F79 HASH (ACCESS (SYS_TEMP_0FD9D6796_E71F79 HASH (ACCESS ((ACCESS ( recursive calls 24 db block gets 17 consistent gets 3 physical reads 1596 redo size 819 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client client 0 sorts (memory) 0 sorts (disk) 8 rows processed

UNION ALL:

Id STATEMENT (HASH (ACCESS (HASH (ACCESS ( recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 819 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client client 0 sorts (memory) 0 sorts (disk) 8 rows processed

和rollup不同的是,grouping sets的效率竟然比同等功能的union all语句低,这实现有点出乎意料。看来,也不可盲目应用Oracle提供的方案,至少,在本例中是如此。