--=============================================
-- 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 子句返回的重复记录
八、更多
VmWare6.5.2下安装RHEL 5.4（配置Oracle安装环境）
SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
