GROUP BY的扩展
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提供的方案,至少,在本例中是如此。
推荐阅读
-
Logger.getLogger()与LogFactory.getLog()的区别详解
-
MySQL 的CASE WHEN 语句使用说明
-
基于C#中XmlReader读取Xml的深入分析
-
Python 使用requests模块发送GET和POST请求的实现代码
-
Android编程实现自动检测版本及自动升级的方法
-
解决C#中WebBrowser的DocumentCompleted事件不执行的实现方法
-
Android编程使用ListView实现数据列表显示的方法
-
C#中事件的动态调用实现方法
-
try catch finally的执行顺序深入分析
-
SQL Server并发操作单个表时发生在page页面级的死锁