About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文（169）
分类： Oracle
2020-06-01 23:44:28
还有一种需求就是对不同维度的所有可能分组进行统计，从而生成交叉报表。这种需求比ROLLUP更加精细，包含了ROLLUP的统计结果，而且还有其它的组合分组结果(小计)。交叉报表实现的分组级别更多，从而为决策分析提供更细粒度的统计报表。CUBE就可以实现这样的需求，比如CUBE(n列)，那么分组种类有：
C0n + C1n + C2n + … + Cnn = 2n
CUBE分组就是先进行合计(一个不取C0n)，然后小计(C1n.到C n-1n)，最后全取(标准分组Cnn)，和ROLLUP不同，CUBE计算结果和列的顺序无关，但是列顺序不同，默认的结果排序则不同，当然，我们应该使用显式排序规则，默认排序可能会变化，显式排序见GROUPING_ID函数讲解。对CUBE操作，就不用UNION ALL对比了，有兴趣的可以测试下，CUBE增加一列，则分组种类呈级数增长，使用UNION ALL改写更麻烦，而且效率一般比较差。
CUBE使用的语法和ROLLUP类似，只不过将ROLLUP换成CUBE而已，如下所示：
SELECT … GROUP BY CUBE(grouping_column_reference_list)
在1.2.2中使用ROLLUP(dname,job)来统计标准分组，对job的小计以及合计，下面用CUBE替换ROLLUP，看一下结果的区别：
SELECT a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY CUBE(a.dname,b.job);
显示结果如下：
|
DNAME JOB SUM_SAL ---------------------------- ------------------ ---------- 24925 CLERK 3050 ANALYST 3000 MANAGER 8275 SALESMAN 5600 PRESIDENT 5000 SALES 9400 SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 RESEARCH 6775 RESEARCH CLERK 800 RESEARCH ANALYST 3000 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 | | 12 | 336 | 5 (20)| 00:00:01 | | 1 | SORT GROUP BY | | 12 | 336 | 5 (20)| 00:00:01 | | 2 | GENERATE CUBE | | 12 | 336 | 5 (20)| 00:00:01 | | 3 | SORT GROUP BY | | 12 | 336 | 5 (20)| 00:00:01 | | 4 | NESTED LOOPS | | 12 | 336 | 4 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 12 | 180 | 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") |
对比ROLLUP(dname,job)，CUBE(dname,job)多了5行数据。这5行数据就是GROUP BY (NULL,job)。因为ROLLUP是按列的顺序从右到左递减分组统计的，而CUBE是各种可能性的分组，对于ROLLUP中有n列，CUBE中有同样的n列，那么CUBE的分组种类比ROLLUP多2n -(n+1)种，这里的n=2,因此多1种分组结果，根据两种分组的规则推算CUBE(dname,job)比ROLLUP(dname,job)多了GROUP BY (NULL,job)分组。
从上面的计划看出，不像ROLLUP，有SORT GROUP BY ROLLUP，CUBE操作是先采用GENERATE CUBE，然后对结果SORT GROUP BY，所以上面的CUBE计算结果也是有默认排序的，同样这种默认排序也是受计划影响的，应该用显式排序，见GROUPING_ID。
下面用表格分析一下CUBE(dname,job)对应分组级别：
|
分组级别 |
描述 |
|
dname,job |
标准分组 |
|
dname |
对于每个dname,计算横跨所有job的小计 |
|
job |
对于每个job,计算横跨所有dname的小计 |
|
合计 |
合计 |
CUBE对两列操作，对应4个分组级别，最终对各种可能性分组进行统计，获得多维度更加精细的数据统计结果。
和ROLLUP一样，也有部分CUBE操作，可以去掉合计以及某些不需要的小计。比如上面的GROUP BY CUBE(dname,job)改为GROUP BY dname,CUBE(job)则剔除了合计以及GROUP BY job。代码如下：
SELECT a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname,CUBE(b.job);
显示结果如下：
|
DNAME JOB SUM_SAL ---------------------------- ------------------ ------------------------------------------------- SALES 9400 SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 RESEARCH 6775 RESEARCH CLERK 800 RESEARCH ANALYST 3000 RESEARCH MANAGER 2975 ACCOUNTING 8750 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 已选择12行。 |
CUBE像ROLLUP一样，可以实现多维数组分析统计工作，而且CUBE是对所有可能性的组合情况进行统计，从而生成交叉报表，CUBE分组级别更多，结果更精细，从而为决策者提供强大的数据支撑
部分CUBE允许剔除某些小计和合计行，为实现灵活的报表提供保障。
