高级分组ROLLUP,CUBE,GROUPING,GROUPING SETS操作
来源：互联网 发布：活塞坏孩子军团知乎 编辑：程序博客网 时间：2024/05/01 12:23
目的是掌握高级分组的语法.理解高级分组的工作原理.
组函数中的集合操作
Rollup分组(ORACLE数据库中的ROLLUP配合GROUP BY命令使用，可以提供信息汇总功能（与"小计"相似）)
按部门分组SQL> select department_id,sum(salary) from emp group by department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
100 51600
30 27390
7000
20 20900
70 11000
90 58000
110 20300
50 172040
40 7150
80 304500
10 4840
DEPARTMENT_ID SUM(SALARY)
------------- -----------
60 31680
12 rows selected.
按部门分组,并求总计
SQL> select department_id,sum(salary) from emp group by rollup(department_id);
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4840
20 20900
30 27390
40 7150
50 172040
60 31680
70 11000
80 304500
90 58000
100 51600
110 20300
DEPARTMENT_ID SUM(SALARY)
------------- -----------
7000
716400
13 rows selected.
Rollup分组，一次全表扫描
select department_id,sum(salary) from emp group by rollup(department_id);
-----------------------------------------------------------
分解为下列语句
select department_id,sum(salary) from emp group by department_id
union all
select null,sum(salary) from emp
order by 1;
两次扫描表，效率低
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 department_id,sum(salary) from emp group by rollup(department_id,job_id);
DEPARTMENT_ID SUM(SALARY)
------------- -----------
7000
7000
10 4840
10 4840
20 14300
20 6600
20 20900
30 12100
30 15290
30 27390
40 7150
DEPARTMENT_ID SUM(SALARY)
------------- -----------
40 7150
50 40040
50 70730
50 61270
50 172040
60 31680
60 31680
70 11000
70 11000
80 61000
80 243500
DEPARTMENT_ID SUM(SALARY)
------------- -----------
80 304500
90 34000
90 24000
90 58000
100 12000
100 39600
100 51600
110 12000
110 8300
110 20300
716400
33 rows selected.
结果为
select DEPARTMENT_ID,job_id,sum(salary) from emp group by DEPARTMENT_ID,job_id
union all
select DEPARTMENT_ID,null,sum(salary) from emp group by DEPARTMENT_ID
union all
select null,null,sum(salary) from emp;
Grouping(列名称）的使用，为了表达该列是否参加了分组活动：
0为该列参加了分组，1为该列未参加分组操作
SQL> select department_id,sum(salary),grouping(department_id),grouping(job_id) from emp group by rollup(department_id,job_id);
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
7000 0 0
7000 0 1
10 4840 0 0
10 4840 0 1
20 14300 0 0
20 6600 0 0
20 20900 0 1
30 12100 0 0
30 15290 0 0
30 27390 0 1
40 7150 0 0
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
40 7150 0 1
50 40040 0 0
50 70730 0 0
50 61270 0 0
50 172040 0 1
60 31680 0 0
60 31680 0 1
70 11000 0 0
70 11000 0 1
80 61000 0 0
80 243500 0 0
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
80 304500 0 1
90 34000 0 0
90 24000 0 0
90 58000 0 1
100 12000 0 0
100 39600 0 0
100 51600 0 1
110 12000 0 0
110 8300 0 0
110 20300 0 1
716400 1 1
33 rows selected.
Cube分组(CUBE，也是GROUP BY子句的一种扩展 可以返回每一个列组合的小计记录（从左向右+从右向左），同时在末尾加上总计记录)
SQL> select department_id,sum(salary),grouping(department_id),grouping(job_id) from emp group by cube(department_id,job_id);
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
7000 0 1
716400 1 1
34000 1 0
12000 1 0
12000 1 0
7150 1 0
14300 1 0
6600 1 0
11000 1 0
12100 1 0
61000 1 0
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
7000 0 0
250500 1 0
40040 1 0
4840 1 0
24000 1 0
31680 1 0
15290 1 0
70730 1 0
61270 1 0
8300 1 0
39600 1 0
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
10 4840 0 1
10 4840 0 0
20 20900 0 1
20 14300 0 0
20 6600 0 0
30 27390 0 1
30 12100 0 0
30 15290 0 0
40 7150 0 1
40 7150 0 0
50 172040 0 1
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
50 40040 0 0
50 70730 0 0
50 61270 0 0
60 31680 0 1
60 31680 0 0
70 11000 0 1
70 11000 0 0
80 304500 0 1
80 61000 0 0
80 243500 0 0
90 58000 0 1
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
90 34000 0 0
90 24000 0 0
100 51600 0 1
100 12000 0 0
100 39600 0 0
110 20300 0 1
110 12000 0 0
110 8300 0 0
52 rows selected.
结果集为，2**n(2的N次方)个结果集
select DEPARTMENT_ID,job_id,sum(salary) from emp group by DEPARTMENT_ID,job_id
union all
select DEPARTMENT_ID,null,sum(salary) from emp group by DEPARTMENT_ID
union all
select null,job_id,sum(salary) from emp group by JOB_ID
union all
select null,null,sum(salary) from emp;
5.GROUPING SETS与GROUPING的作用是不同的
Oracle服务器计算GROUPING SETS子句中所有的组并将结果通过UNION ALL组合成一个结果集.GROUPING SETS的效果:1.只需要访问一次基表.2.不需要写很复杂的UNION语句.
SELECT department_id, job_id, null manager_id,avg(salary)
FROM employees
GROUP BY (department_id,job_id)
UNION ALL
SELECT null department_id, job_id, manager_id,avg(salary)
FROM employees
GROUP BY (job_id,manager_id)
等同于
SQL> set autotrace on
SQL> SELECT department_id, job_id,
2 manager_id,avg(salary)
3 FROM employees
4 GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));
上面得到的是通过job_id,manager_id分组的avg(salary)
下面的是通过department_id,job_id分组的avg(salary)
- 高级分组ROLLUP,CUBE,GROUPING,GROUPING SETS操作
- 高级分组实例【rollup,cube,grouping sets】
- oracle 11g:高级分组：rollup,cube,grouping sets
- Jarno详解Oracle高级分组函数(ROLLUP, CUBE, GROUPING SETS)
- GROUPING SETS、ROLLUP、CUBE
- GROUPING SETS、ROLLUP、CUBE
- GROUPING SETS、ROLLUP、CUBE
- Oracle分组函数 rollup、cube、grouping sets、grouping、grouping_id
- Oracle数据分组：group by，having，rollup，cube，grouping sets
- Hive高级聚合之GROUPING SETS/ROLLUP/CUBE
- Oracle-->事关CUBE ROLLUP GROUPING SETS(zz)
- 事关CUBE ROLLUP GROUPING SETS（1）
- oracle CUBE ROLLUP GROUPING SETS实例讲解
- Oracle的rollup、cube、grouping sets函数
- Oracle的rollup、cube、grouping sets函数
- DB2 9.0 GROUPING SETS & ROLLUP & CUBE 比较
- GROUPING SETS,ROLLUP,CUBE用法介绍
- SQL SERVER中GROUPING SETS，CUBE，ROLLUP
- cocos2d-x 中添加显示文字的三种方式 LabelTTF 、LabelBMFont 和 LabelAtlas
- AJAX技术
- VLC使用的问题
- newLISP序列化
- final 和 finally你知道多少?
- 高级分组ROLLUP,CUBE,GROUPING,GROUPING SETS操作
- 介绍两个Python web框架：Django & Tornado
- 通用的adapter
- QT快速使用ntohs
- Ubuntu下用docker安装redis镜像和使用redis容器分享
- win7(64)位下WinDbg64调试VMware10下的win7(32位)
- ObReferenceObjectByName函数调用WIN7下的解决
- #define DELAY_ONE_MICROSECOND (-10) 时间是负数的原因
- 维基百科api的简单调用以及wiki 标记的转义(pear的安装以及使用)
