搜索
您的当前位置:首页GROUPBY的扩展

GROUPBY的扩展

时间:2020-11-09 来源:乌哈旅游

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提供的方案,至少,在本例中是如此。

本文永久更新链接地址:

Top