欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/49847027
ROLLUP,是GROUP BY子句的一种扩展,可以为每个分组返回小计记录以及所有分组返回小计记录以及为所有分组返回总计记录。
CUBE,也是GROUP子句的一种扩展,可以返回每一个列组合的小计记录,同时在末尾加上总计记录。
执行如下:
store@PDB1> select * from divisions;
DIV NAME
--- ---------------
SAL Sales
OPE Operations
SUP Support
BUS Business
store@PDB1> SELECT * from jobs;
JOB NAME
--- --------------------
WOR Worker
MGR Manager
ENG Engineer
TEC Technologist
PRE President
执行如下:
store@PDB1> select * from employees2 where rownum<= 5;
EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME SALARY
----------- --- --- ---------- --------------------
1 BUS PRE James Smith 800000
2 SAL MGR Ron Johnson 350000
3 SAL WOR Fred Hobbs 140000
4 SUP MGR Susan Jones 200000
5 SAL WOR Rob Green 350000
1. 使用ROLLUP子句
将employee2表中的数据按division_id进行分组,并使用SUM()得到每一个division_id的工资总计:
store@PDB1> select division_id,sum(salary) fromemployees2 group by division_id order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
1.1 向ROLLUP传递一列
store@PDB1> select division_id,sum(salary) fromemployees2 group by rollup(division_id) order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
最后多了一条记录,包含所有分组的工资总计。
如果需要以特定的顺序排列行,应该使用ORDER BY 子句。
1.2 向ROLLUP传递多列
store@PDB1> select division_id,job_id,sum(salary)from employees2 group by rollup(division_id,job_id) order bydivision_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE 1320000
SAL MGR 4446000
SAL WOR 490000
SAL 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP 1015000
8881000
16 rows selected.
1.3 修改传递给ROLLUP的列的位置
store@PDB1> select job_id,division_id,sum(salary)from employees2 group by rollup(job_id,division_id) order byjob_id,division_id;
JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE 245000
ENG 245000
MGR BUS 530000
MGR OPE 805000
MGR SAL 4446000
MGR SUP 465000
MGR 6246000
PRE BUS 800000
PRE 800000
TEC SUP 115000
TEC 115000
WOR BUS 280000
WOR OPE 270000
WOR SAL 490000
WOR SUP 435000
WOR 1475000
8881000
17 rows selected.
1.4 ROLLUP与其他的聚合函数一起使用
任何聚合函数都可以和ROLLUP一起使用。
store@PDB1> select division_id,job_id,avg(salary)from employees2 group by rollup(division_id,job_id) order bydivision_id,job_id;
DIV JOB AVG(SALARY)
--- --- -----------
BUS MGR 176666.667
BUS PRE 800000
BUS WOR 280000
BUS 322000
OPE ENG 245000
OPE MGR 201250
OPE WOR 135000
OPE 188571.429
SAL MGR 261529.412
SAL WOR 245000
SAL 259789.474
SUP MGR 232500
SUP TEC 115000
SUP WOR 145000
SUP 169166.667
240027.027
2. 使用CUBE子句
使用CUBE子句对GROUP BY进行扩展,返回CUBE中所有列组合的小计信息,同时在最后显示总计信息。
store@PDB1> select division_id,job_id,sum(salary)from employees2 group by cube(division_id,job_id) order by division_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE 1320000
SAL MGR 4446000
SAL WOR 490000
SAL 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP 1015000
ENG 245000
MGR 6246000
PRE 800000
TEC 115000
WOR 1475000
8881000
21 rows selected.
改变division_id 和 job_id的顺序
store@PDB1> select job_id,division_id,sum(salary)from employees2 group by cube(job_id,division_id) order by job_id,division_id;
JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE 245000
ENG 245000
MGR BUS 530000
MGR OPE 805000
MGR SAL 4446000
MGR SUP 465000
MGR 6246000
PRE BUS 800000
PRE 800000
TEC SUP 115000
TEC 115000
WOR BUS 280000
WOR OPE 270000
WOR SAL 490000
WOR SUP 435000
WOR 1475000
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
21 rows selected.
3. 使用GROUPING 函数
GROUPING函数可以接受一列,返回0或者1. 列值为空,那么GROUPING返回1.
GROUPING只能在使用ROLLUP或CUBE的查询中使用。
3.1 在ROLLUP中对单列使用GROUPING
store@PDB1> select division_id,sum(salary) fromemployees2 group by rollup(division_id) order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
最后一条记录的division_id列为空。可以用GROUPING函数来确定这一列是否为空。
store@PDB1> selectgrouping(division_id),division_id,sum(salary) from employees2 group byrollup(division_id) order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
3.2 使用CASE转换GROUPING的返回值
store@PDB1> select case grouping(division_id) when1 then 'all divisions'
elsedivision_id
end as div,
sum(salary)
fromemployees2 group by rollup(division_id) order by division_id;
DIV SUM(SALARY)
------------- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
all divisions 8881000
3.3 使用CASE和GROUPING转换多个列的值
store@PDB1> select casegrouping(division_id)
when 1 then 'all divisions'
else division_id
end as div,
case grouping(job_id)
when 1 then 'all jobs'
else job_id
end as job,
sum(salary)
from employees2 group by rollup(division_id,job_id)
order by division_id,job_id;
DIV JOB SUM(SALARY)
------------- -------- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS all jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE all jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL all jobs 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP all jobs 1015000
all divisions all jobs 8881000
16 rows selected.
3.4 使用CUBE与GROUPING结合使用
store@PDB1> select case grouping(division_id) when1 then 'all divisions'
elsedivision_id end as div,
casegrouping(job_id)
when 1 then'all jobs'
else job_id
end as job,
sum(salary)from employees2
group bycube(division_id,job_id)
order bydivision_id,job_id;
DIV JOB SUM(SALARY)
------------- -------- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS all jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE all jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL all jobs 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP all jobs 1015000
all divisions ENG 245000
all divisions MGR 6246000
all divisions PRE 800000
all divisions TEC 115000
all divisions WOR 1475000
all divisions all jobs 8881000
21 rows selected.
3.5 使用GROUPINGS SETS子句
使用GROUPING SETS子句可以只返回小计记录。
例如:
store@PDB1> select division_id,job_id,sum(salary)from employees2 group by grouping sets( division_id,job_id)
order bydivision_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
ENG 245000
MGR 6246000
PRE 800000
TEC 115000
WOR 1475000
9 rows selected.
返回结果中只有division_id和job_id列的小计,而所有记录的工资总计记录并没有返回。
GROUPING SETS子句性能一般比CUBE好,尽可能使用GROUPINGSETS 少使用CUBE。
3.6 使用GROUPING_ID函数
使用GROUPING_ID函数借助HAVING子句对记录进行过滤。可以接受一列或多列。
当列值为空时,GROUPING返回1,当列值非空时返回0.
如下图1:
store@PDB1> select division_id,job_id,
grouping(division_id)as div_grp,
grouping(job_id) as job_grp,
grouping_id(division_id,job_id) as grp_id,
sum(salary)
fromemployees2
group bycube(division_id,job_id)
order bydivision_id,job_id;
DIV JOB DIV_GRP JOB_GRP GRP_ID SUM(SALARY)
--- --- ---------- ---------- ---------------------
BUS MGR 0 0 0 530000
BUS PRE 0 0 0 800000
BUS WOR 0 0 0 280000
BUS 0 1 1 1610000
OPE ENG 0 0 0 245000
OPE MGR 0 0 0 805000
OPE WOR 0 0 0 270000
OPE 0 1 1 1320000
SAL MGR 0 0 0 4446000
SAL WOR 0 0 0 490000
SAL 0 1 1 4936000
SUP MGR 0 0 0 465000
SUP TEC 0 0 0 115000
SUP WOR 0 0 0 435000
SUP 0 1 1 1015000
ENG 1 0 2 245000
MGR 1 0 2 6246000
PRE 1 0 2 800000
TEC 1 0 2 115000
WOR 1 0 2 1475000
1 1 3 8881000
21 rows selected.
GROUPING_ID的一个用武之地在于使用HAVING子句过滤记录。HAVING子句可以将不包含小计或总计的记录出去。
store@PDB1> select division_id,job_id,grouping_id(division_id,job_id)as grp_id,
sum(salary)from employees2 group by cube(division_id,job_id)
havinggrouping_id(division_id,job_id) > 0 order by division_id,job_id;
DIV JOB GRP_ID SUM(SALARY)
--- --- ---------- -----------
BUS 1 1610000
OPE 1 1320000
SAL 1 4936000
SUP 1 1015000
ENG 2 245000
MGR 2 6246000
PRE 2 800000
TEC 2 115000
WOR 2 1475000
3 8881000
10 rows selected.
3.7 在GROUP BY子句中多次使用一个列
在GROUP BY子句中可以多次使用某个列,这样可以实现对数据的重新组织,或是按照不同的数据分组进行统计。
store@PDB1> select division_id,job_id,sum(salary)from employees2 group by division_id,rollup(division_id,job_id);
DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
SAL MGR 4446000
SAL WOR 490000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
19 rows selected.
最后四行记录出现重复,可以使用GROUP_ID来进行消除。
3.8 使用GROUP_ID函数
GROUP_ID函数用于消除GROUPBY子句返回的重复记录。GROUP_ID不接受任何参数。
store@PDB1> selectdivision_id,job_id,group_id(),sum(salary) from employees2 group bydivision_id,rollup(division_id,job_id);
DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS MGR 0 530000
BUS PRE 0 800000
BUS WOR 0 280000
OPE ENG 0 245000
OPE MGR 0 805000
OPE WOR 0 270000
SAL MGR 0 4446000
SAL WOR 0 490000
SUP MGR 0 465000
SUP TEC 0 115000
SUP WOR 0 435000
BUS 0 1610000
OPE 0 1320000
SAL 0 4936000
SUP 0 1015000
BUS 1 1610000
OPE 1 1320000
SAL 1 4936000
SUP 1 1015000
19 rows selected.
使用HAVING子句消除重复记录,只返回GROUP_ID等于0的记录,如下:
store@PDB1> select division_id,job_id,group_id(),sum(salary)from employees2 group by division_id,rollup(division_id,job_id) havinggroup_id()=0;
DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS MGR 0 530000
BUS PRE 0 800000
BUS WOR 0 280000
OPE ENG 0 245000
OPE MGR 0 805000
OPE WOR 0 270000
SAL MGR 0 4446000
SAL WOR 0 490000
SUP MGR 0 465000
SUP TEC 0 115000
SUP WOR 0 435000
BUS 0 1610000
OPE 0 1320000
SAL 0 4936000
SUP 0 1015000
15 rows selected.