SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

--=============================================

-- SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

--=============================================

在使用ROLLUP与CUBE运算符实现数据的汇总是，Oracle与SQL Server使用了不同的写法，但其实质是一样的，都遵循了SQL规范。

ROLLUP与CUBE运算符都是对GROUP BY 子句的扩展

SQL Server中的用法

GROUP BY col1,col2

WITH ROLLUP | CUBE ;

Oracle 中的用法

GROUP BY

ROLLUP | CUBE (col1,col2);

ROLLUP：为每个分组返回小计记录以及为所有分组返回总计记录

CUBE：返回列中所有组合的小计记录以及为所有分组返回总计记录

关于在SQL Sever中ROLLUP与CUBE运算符，请参照：ROLLUP 与CUBE 运算符的使用

一、演示ROLLUP 及CUBE 的使用

--使用常规的GROUP BY 实现数据的汇总

SQL> SELECT deptno,SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY deptno;

DEPTNO SUM(SAL)

---------- ----------

30 9400

20 10875

10 8750

--增加ROLLUP运算符实现对数据的汇总，增加了对DEPTNO列的总计

SQL> SELECT deptno,SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP (deptno);

DEPTNO SUM(SAL)

---------- ----------

10 8750

20 10875

30 9400

29025

--使用ROLLUP实现对多列数据进行汇总

--以下示例，先按deptno,job分组，并实现sum函数的聚合，接下来按从右向左的方向实现更高层次的聚合，如对同一个

--部门的作汇总，最后对所有部门作汇总。汇总层数为n+1,其中n为rollup中的列数

--因表emp存在deptno为空的记录，故以下所有演示中使用了where 子句过滤空值

SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP (deptno,job)

3 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

29025

--交换deptno,job列查看不同的列的顺序导致了不同的结果，原因是不同的分组产生了不同的结果

SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP (job,deptno)

3 ORDER BY job,deptno;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

20 ANALYST 6000

ANALYST 6000

10 CLERK 1300

20 CLERK 1900

30 CLERK 950

CLERK 4150

10 MANAGER 2450

20 MANAGER 2975

30 MANAGER 2850

MANAGER 8275

10 PRESIDENT 5000

DEPTNO JOB SUM(SAL)

---------- --------- ----------

PRESIDENT 5000

30 SALESMAN 5600

SALESMAN 5600

29025

--ROLLUP ,CUBE可以配合不同的聚合函数来使用

SQL> SELECT deptno,job, SUM(sal),ROUND(AVG(sal),2) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP (deptno,job)

3 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL) ROUND(AVG(SAL),2)

---------- --------- ---------- -----------------

10 CLERK 1300 1300

10 MANAGER 2450 2450

10 PRESIDENT 5000 5000

10 8750 2916.67

20 ANALYST 6000 3000

20 CLERK 1900 950

20 MANAGER 2975 2975

20 10875 2175

30 CLERK 950 950

30 MANAGER 2850 2850

30 SALESMAN 5600 1400

DEPTNO JOB SUM(SAL) ROUND(AVG(SAL),2)

---------- --------- ---------- -----------------

30 9400 1566.67

29025 2073.21

13 rows selected.

--使用CUBE子句实现对数据的汇总

--从结果集中可以看出CUBE对不同的维度也实现了数据汇总，本例中多出的列即为不同的JOB也产生了汇总数据

SQL> SELECT deptno,job, SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY CUBE (deptno,job)

3 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

ANALYST 6000

CLERK 4150

MANAGER 8275

PRESIDENT 5000

SALESMAN 5600

29025

18 rows selected.

二、使用GROUPING函数处理汇总结果中的空值

GROUPING函数仅在使用ROLLUP和CUBE查询中使用，可以接受一列，其结果返回为或者，如果列值为空，则返回，否则返回

--单列使用GROUPING函数

SQL> SELECT GROUPING(deptno),deptno, SUM(sal) FROM emp WHERE deptno IS NOT NULL

2 GROUP BY ROLLUP(deptno)

3 ORDER BY deptno;

GROUPING(DEPTNO) DEPTNO SUM(SAL)

---------------- ---------- ----------

0 10 8750

0 20 10875

0 30 9400

1 29025

--使用CASE子句转换GROUPING函数的返回值

SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS deptnumber, SUM(sal)

2 FROM emp WHERE deptno IS NOT NULL

3 GROUP BY ROLLUP(deptno)

4 ORDER BY deptno;

DEPTNUMBER SUM(SAL)

---------------------------------------- ----------

10 8750

20 10875

30 9400

All Deptno 29025

--使用CASE子句转换多列GROUPING函数的返回值

SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS "DeptNo",

2 CASE GROUPING(job) WHEN 1 THEN 'All Job' ELSE job END AS "Job",

3 SUM(sal)

4 FROM emp WHERE deptno IS NOT NULL

5 GROUP BY ROLLUP(deptno,job);

DeptNo Job SUM(SAL)

---------------------------------------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 All Job 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

20 All Job 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DeptNo Job SUM(SAL)

---------------------------------------- --------- ----------

30 All Job 9400

All Deptno All Job 29025

--CUBE与GROUPING函数的结合使用

SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS "DeptNo",

2 CASE GROUPING(job) WHEN 1 THEN 'All Job' ELSE job END AS "Job",

3 SUM(sal)

4 FROM emp WHERE deptno IS NOT NULL

5 GROUP BY CUBE(deptno,job)

6 ORDER BY deptno,job;

DeptNo Job SUM(SAL)

---------------------------------------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 All Job 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 All Job 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DeptNo Job SUM(SAL)

---------------------------------------- --------- ----------

30 All Job 9400

All Deptno ANALYST 6000

All Deptno CLERK 4150

All Deptno MANAGER 8275

All Deptno PRESIDENT 5000

All Deptno SALESMAN 5600

All Deptno All Job 29025

三、使用GROUPING SETS 子句列出小计

--GROUPING SETS 子句仅返回小计的记录

--注意GROUPING SETS的用法是替换掉ROLLUP 或CUBE

SQL> SELECT deptno,job,SUM(sal)

2 FROM emp

3 WHERE deptno IS NOT NULL

4 GROUP BY GROUPING SETS(deptno,job)

5 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 8750

20 10875

30 9400

ANALYST 6000

CLERK 4150

MANAGER 8275

PRESIDENT 5000

SALESMAN 5600

四、使用GROUPING_ID函数和HAVING子句过滤结果集

--GROUPING_ID函数接受一列或多列输入，返回一个十进制的值

--GROUPING_ID返回值为调用GROUPING函数的组合结果

--如0 0 ==> 00 ,0 1 ==> 1 , 1 0 ==> 2 , 1 1 ==> 3(左边为GROUPING函数得到的列值，右边为转换结果)

SQL> SELECT deptno,job,GROUPING(deptno) dept_grp,GROUPING(job) job_grp,

2 GROUPING_ID(deptno,job),SUM(sal)

3 FROM emp

4 WHERE deptno IS NOT NULL

5 GROUP BY CUBE(deptno,job)

6 ORDER BY deptno,job;

DEPTNO JOB DEPT_GRP JOB_GRP GROUPING_ID(DEPTNO,JOB) SUM(SAL)

---------- --------- ---------- ---------- ----------------------- ----------

10 CLERK 0 0 0 1300

10 MANAGER 0 0 0 2450

10 PRESIDENT 0 0 0 5000

10 0 1 1 8750

20 ANALYST 0 0 0 6000

20 CLERK 0 0 0 1900

20 MANAGER 0 0 0 2975

20 0 1 1 10875

30 CLERK 0 0 0 950

30 MANAGER 0 0 0 2850

30 SALESMAN 0 0 0 5600

DEPTNO JOB DEPT_GRP JOB_GRP GROUPING_ID(DEPTNO,JOB) SUM(SAL)

---------- --------- ---------- ---------- ----------------------- ----------

30 0 1 1 9400

ANALYST 1 0 2 6000

CLERK 1 0 2 4150

MANAGER 1 0 2 8275

PRESIDENT 1 0 2 5000

SALESMAN 1 0 2 5600

1 1 3 29025

--使用HAVING子句和GROUPING_ID过滤非小计和总计记录

SQL> SELECT deptno,job,

2 GROUPING_ID(deptno,job),SUM(sal)

3 FROM emp

4 WHERE deptno IS NOT NULL

5 GROUP BY CUBE(deptno,job)

6 HAVING GROUPING_ID(deptno,job) > 0

7 ORDER BY deptno,job;

DEPTNO JOB GROUPING_ID(DEPTNO,JOB) SUM(SAL)

---------- --------- ----------------------- ----------

10 1 8750

20 1 10875

30 1 9400

ANALYST 2 6000

CLERK 2 4150

MANAGER 2 8275

PRESIDENT 2 5000

SALESMAN 2 5600

3 29025

五、GROUP BY ,CUBE 或ROLLUP 中同时使用一列的处理

--即某些列同时在GROUP BY 子句和CUBE(ROLLUP)中存在

--结果中可以看出多出了一些重复的列

SQL> SELECT deptno,job,SUM(sal)

2 FROM emp

3 WHERE deptno IS NOT NULL

4 GROUP BY deptno,ROLLUP(deptno,job)

5 ORDER BY deptno,job;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

20 10875

30 CLERK 950

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 MANAGER 2850

30 SALESMAN 5600

30 9400

30 9400

六、使用GROUP_ID函数过滤结果集

--GROUP_ID函可以消除GROUP BY 子句返回的重复记录

--GROUP_ID函数不接受任何参数

--对于特定分组结果中重复出现n次，GROUP_ID()函数返回到n-1 之间的一个整数

SQL> SELECT deptno,job,GROUP_ID(),SUM(sal)

2 FROM emp

3 WHERE deptno IS NOT NULL

4 GROUP BY deptno,ROLLUP(deptno,job)

5 ORDER BY deptno,job;

DEPTNO JOB GROUP_ID() SUM(SAL)

---------- --------- ---------- ----------

10 CLERK 0 1300

10 MANAGER 0 2450

10 PRESIDENT 0 5000

10 1 8750

10 0 8750

20 ANALYST 0 6000

20 CLERK 0 1900

20 MANAGER 0 2975

20 1 10875

20 0 10875

30 CLERK 0 950

DEPTNO JOB GROUP_ID() SUM(SAL)

---------- --------- ---------- ----------

30 MANAGER 0 2850

30 SALESMAN 0 5600

30 1 9400

30 0 9400

--使用HAVING子句和GROUP_ID函数过滤结果集

SQL> SELECT deptno,job,GROUP_ID(),SUM(sal)

2 FROM emp

3 WHERE deptno IS NOT NULL

4 GROUP BY deptno,ROLLUP(deptno,job)

5 HAVING GROUP_ID() = 0

6 ORDER BY deptno,job;

DEPTNO JOB GROUP_ID() SUM(SAL)

---------- --------- ---------- ----------

10 CLERK 0 1300

10 MANAGER 0 2450

10 PRESIDENT 0 5000

10 0 8750

20 ANALYST 0 6000

20 CLERK 0 1900

20 MANAGER 0 2975

20 0 10875

30 CLERK 0 950

30 MANAGER 0 2850

30 SALESMAN 0 5600

DEPTNO JOB GROUP_ID() SUM(SAL)

---------- --------- ---------- ----------

30 0 9400

七、总结：

ROLLUP 、CUBE 是对GROUP BY 子句的扩展

ROLLUP：根据grouping list 的rollup条款创建小计，然后从左向右再实现聚合。

1.首先按group by (col1,col2)标准分组聚合

2.按照rollup(col1,col2)中的列从右向左的顺序进行更高层次的聚合

3.创建n+1层的总计，n = rollup(col1,col2...)中的列数

CUBE: 创建所有可能的小计

1.结果集将会包括相应rollup的所有值并加上额外的合并

2.如果有n类在cube中规定，将会有的n次方的小计返回

GROUPING()函数：

仅仅接受CUBE或ROLLUP中的单列，不能使用复合列，如果列值为空，将返回，否则返回

通常配合CASE WHEN 用于替换空值

GROUPING SETS子句：

用于只返回小计记录

GROUPING_ID() 函数：

接受ROLLUP或CUBE中的多列，返回一个十进制值，实际上是GROUPING()函数值的组合

可以配合HAVING子句来过滤掉不需要的统计信息

GRUOUP_ID() 函数:

不接受任何参数

对于特定出现n次的分组， GRUOUP_ID()返回从到n-1之间的一个整数

可以配合HAVING子句消除group by 子句返回的重复记录

八、更多

ROLLUP 与 CUBE 运算符的使用

VmWare6.5.2下安装RHEL 5.4（配置Oracle安装环境）

Oracle 10g SGA 的自动化管理

Oracle 表空间与数据文件

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

Oracle 常用目录结构(10g)