高级分组rollup,cube操作
该实验的目的是掌握高级分组的语法，理解高级分组的工作原理
·组函数中的集合操作
·ROLLUP分组
按部门分组
SQL> select deptno,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
------ ----------
30 10450
20 10876
10 8750
按部门分组，并求总计
SQL> select deptno,sum(sal) from emp group by rollup(deptno);
DEPTNO SUM(SAL)
------ ----------
10 8750
20 10876
30 10450
30076
Rollup分组，一次全表扫描。
select deptno,sum(sal) from emp group by rollup(deptno);
分解为下列语句
SQL> select deptno,sum(sal) from emp group by deptno
2 union all
3 select null,sum(sal) from emp
4 order by 1;
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10876
30 10450
30076
两次扫描表，效率递。
Group by rollup(a,b,c,d)的结果集为n+1个集
Group by(a,b,c,d)
Union all
Group by(a,b,c)
Union all
Group by(a,b)
Union all
Group by(a)
Union all
Group by null
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1901
20 ANALYST 6000
20 MANAGER 2975
20 10876
30 CLERK 2000
30 MANAGER 2850
30 SALESMAN 5600
30 10450
30076
结果为：
SQL> select deptno,job,sum(sal) from emp group by deptno,job
union all
select deptno,null, sum(sal) from emp group by deptno
union all
select null,null,sum(sal) from emp
grouping(列名称)的使用，为了表达该列是否参加了分组活动。
0为该列参加了分组，1为该列未参加分组操作。
SQL> select deptno,job,grouping(deptno),grouping(job),sum(sal)
from emp group by rollup(deptno,job);
DEPTNO JOB GROUPING(DEPTNO) GROUPING(JOB) SUM(SAL)
------ --------- ---------------- ------------- ----------
10 CLERK 0 0 1300
10 MANAGER 0 0 2450
10 PRESIDENT 0 0 5000
10 0 1 8750
20 CLERK 0 0 1901
20 ANALYST 0 0 6000
20 MANAGER 0 0 2975
20 0 1 10876
30 CLERK 0 0 2000
30 MANAGER 0 0 2850
30 SALESMAN 0 0 5600
30 0 1 10450
1 1 30076
cube分组
SQL> select deptno,job,grouping(deptno),grouping(job),sum(sal)
from emp group by cube(deptno,job);
结果集为2的N次方个结果集。
DEPTNO JOB GROUPING(DEPTNO) GROUPING(JOB) SUM(SAL)
------ --------- ---------------- ------------- ----------
1 1 30076
CLERK 1 0 5201
ANALYST 1 0 6000
MANAGER 1 0 8275
SALESMAN 1 0 5600
PRESIDENT 1 0 5000
10 0 1 8750
10 CLERK 0 0 1300
10 MANAGER 0 0 2450
10 PRESIDENT 0 0 5000
20 0 1 10876
20 CLERK 0 0 1901
20 ANALYST 0 0 6000
20 MANAGER 0 0 2975
30 0 1 10450
30 CLERK 0 0 2000
30 MANAGER 0 0 2850
30 SALESMAN 0 0 5600
SQL> select deptno,job,sum(sal) from emp group by deptno,job
union all
select deptno,null,sum(sal) from emp group by deptno
union all
select null,job,sum(sal) from emp group by job
union all
select null,null,sum(sal) from emp ;
oracle 高级分组rollup和cube
create table input (area varchar2(10),chanel number(3),pinpai varchar2(20),input number(5,2));
insert into input values ('CAOYANG',11,'SHENZHOUXING',1000);
insert into input values ('CAOYANG',11,'DONGGANDIDAI',2000);
insert into input values ('CAOYANG',11,'QUANQIUTONG',3000);
insert into input values ('CAOYANG',12,'SHENZHOUXING',4000);
insert into input values ('CAOYANG',12,'DONGGANDIDAI',5000);
insert into input values ('CAOYANG',12,'QUANQIUTONG',6000);
insert into input values ('CAOYANG',13,'SHENZHOUXING',7000);
insert into input values ('CAOYANG',13,'DONGGANDIDAI',8000);
insert into input values ('CAOYANG',13,'QUANQIUTONG',9000);
insert into input values ('HAIDIAN',21,'SHENZHOUXING',1010);
insert into input values ('HAIDIAN',21,'DONGGANDIDAI',2010);
insert into input values ('HAIDIAN',21,'QUANQIUTONG',3010);
insert into input values ('HAIDIAN',12,'SHENZHOUXING',4010);
insert into input values ('HAIDIAN',22,'DONGGANDIDAI',5010);
insert into input values ('HAIDIAN',22,'QUANQIUTONG',6010);
insert into input values ('HAIDIAN',23,'SHENZHOUXING',7010);
insert into input values ('HAIDIAN',23,'DONGGANDIDAI',8010);
insert into input values ('HAIDIAN',23,'QUANQIUTONG',9010);
按地区，chanel（营业厅），品牌进行汇总排序。用rollup进行高级分组
SELECT AREA,CHANEL,PINP,SUM(SAL) FROM INPUT GROUP BY ROLLUP(AREA,CHANEL,PINP);
AREA CHANEL PINP SUM(SAL)
-------------------- ---------- -------------------- ----------
CAOYANG 11 QUANQIUTONG 3000
CAOYANG 11 DONGGANDIDAI 2000
CAOYANG 11 SHENZHOUXING 1000
CAOYANG 11 6000
CAOYANG 12 QUANQIUTONG 6000
CAOYANG 12 DONGGANDIDAI 5000
CAOYANG 12 SHENZHOUXING 4000
CAOYANG 12 15000
CAOYANG 13 QUANQIUTONG 9000
CAOYANG 13 DONGGANDIDAI 8000
CAOYANG 13 SHENZHOUXING 7000
CAOYANG 13 24000
CAOYANG 45000
HAIDIAN 12 SHENZHOUXING 4010
HAIDIAN 12 4010
HAIDIAN 21 QUANQIUTONG 3010
HAIDIAN 21 DONGGANDIDAI 2010
HAIDIAN 21 SHENZHOUXING 1010
HAIDIAN 21 6030
HAIDIAN 22 QUANQIUTONG 6010
HAIDIAN 22 DONGGANDIDAI 5010
HAIDIAN 22 11020
HAIDIAN 23 QUANQIUTONG 9010
HAIDIAN 23 DONGGANDIDAI 8010
HAIDIAN 23 SHENZHOUXING 7010
HAIDIAN 23 24030
HAIDIAN 45090
90090
28 rows selected.
select area,chanel,pinp,sum(sal)total_input from input group by cube(area,chanel,pinp);
AREA CHANEL PINP TOTAL_INPUT
-------------------- ---------- -------------------- -----------
90090
QUANQIUTONG 36030
DONGGANDIDAI 30030
SHENZHOUXING 24030
11 6000
11 QUANQIUTONG 3000
11 DONGGANDIDAI 2000
11 SHENZHOUXING 1000
12 19010
12 QUANQIUTONG 6000
12 DONGGANDIDAI 5000
12 SHENZHOUXING 8010
13 24000
13 QUANQIUTONG 9000
13 DONGGANDIDAI 8000
13 SHENZHOUXING 7000
21 6030
21 QUANQIUTONG 3010
21 DONGGANDIDAI 2010
21 SHENZHOUXING 1010
22 11020
22 QUANQIUTONG 6010
22 DONGGANDIDAI 5010
23 24030
23 QUANQIUTONG 9010
23 DONGGANDIDAI 8010
23 SHENZHOUXING 7010
CAOYANG 45000
CAOYANG QUANQIUTONG 18000
CAOYANG DONGGANDIDAI 15000
CAOYANG SHENZHOUXING 12000
CAOYANG 11 6000
CAOYANG 11 QUANQIUTONG 3000
CAOYANG 11 DONGGANDIDAI 2000
CAOYANG 11 SHENZHOUXING 1000
CAOYANG 12 15000
CAOYANG 12 QUANQIUTONG 6000
CAOYANG 12 DONGGANDIDAI 5000
CAOYANG 12 SHENZHOUXING 4000
CAOYANG 13 24000
CAOYANG 13 QUANQIUTONG 9000
CAOYANG 13 DONGGANDIDAI 8000
CAOYANG 13 SHENZHOUXING 7000
HAIDIAN 45090
HAIDIAN QUANQIUTONG 18030
HAIDIAN DONGGANDIDAI 15030
HAIDIAN SHENZHOUXING 12030
HAIDIAN 12 4010
HAIDIAN 12 SHENZHOUXING 4010
HAIDIAN 21 6030
HAIDIAN 21 QUANQIUTONG 3010
HAIDIAN 21 DONGGANDIDAI 2010
HAIDIAN 21 SHENZHOUXING 1010
HAIDIAN 22 11020
HAIDIAN 22 QUANQIUTONG 6010
HAIDIAN 22 DONGGANDIDAI 5010
HAIDIAN 23 24030
HAIDIAN 23 QUANQIUTONG 9010
HAIDIAN 23 DONGGANDIDAI 8010
HAIDIAN 23 SHENZHOUXING 7010
60 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27668565/viewspace-750863/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
undo表空间
下一篇：
PL/SQL优化工具profiler
请登录后发表评论
登录
全部评论
