GROUP BY的扩展

原创: 2016-06-07 15:54:01 1032浏览

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