ORACLE的分组统计之CUBE(二)
cube统计包含了rollup的统计结果,而且还有其他组合分组结果(小计),CUBE(n列),那么分组种类有:
cube分组就是先进行合计(一个不取),然后小计(
到
),最后取标准分组
。
与rollup不同,CUBE计算结果和列的顺序无关,如果列顺序不同,默认的结果排序则不同。
SQL> select a.dname,b.job,sum(b.sal) sumsal from scott.dept a,scott.emp b where a.deptno=b.deptno group by cube(a.dname,b.job); DNAME JOB SUMSAL -------------- --------- ---------- 29025 CLERK 4150 ANALYST 6000 MANAGER 8275 SALESMAN 5600 PRESIDENT 5000 SALES 9400 SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 RESEARCH 10875 DNAME JOB SUMSAL -------------- --------- ---------- RESEARCH CLERK 1900 RESEARCH ANALYST 6000 RESEARCH MANAGER 2975 ACCOUNTING 8750 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 已选择18行。 执行计划 ---------------------------------------------------------- Plan hash value: 2432972551 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 392 | 5 (20)| 00:00:01 | | 1 | SORT GROUP BY | | 14 | 392 | 5 (20)| 00:00:01 | | 2 | GENERATE CUBE | | 14 | 392 | 5 (20)| 00:00:01 | | 3 | SORT GROUP BY | | 14 | 392 | 5 (20)| 00:00:01 | | 4 | NESTED LOOPS | | 14 | 392 | 4 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("A"."DEPTNO"="B"."DEPTNO") 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 23 consistent gets 0 physical reads 0 redo size 960 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 18 rows processed