高级分组rollup,cube操作
高级分组rollup,cube操作
目的是掌握高级分组的语法.理解高级分组的工作原理.
组函数中的集合操作
Rollup分组
按部门分组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
