rollup 和 cube
group by 生成数据结果时,只会生成相关列的数据统计信息,而不是生成小计和总计
group by统计结果如下（不是select直接返回格式）:
SQL> select deptno,job,avg(sal) from emp group by deptno,job; -------------------------------------------------------------- |deptnojob| clerk | analyst | manager | president | salesman |
-------------------------------------------------------------- | 10 | 1300 | | 2450 | 5000 | | -------------------------------------------------------------- | 20 | 950 | 3000 | 2975 | | | -------------------------------------------------------------- | 30 | 950 | | 2850 | | 1400 | --------------------------------------------------------------
rollup操作在保留group by统计结果的基础上,还会生成横向小计(部门平均工资)和总计(所有员工的平均工资)
select deptno,job,avg(sal) from emp group by rollup(deptno,job); ----------------------------------------------------------------------------- |deptnojob | clerk | analyst | manager | president | salesman | 小计 |
----------------------------------------------------------------------------- | 10 | 1300 | | 2450 | 5000 | | 2916.66667 | ----------------------------------------------------------------------------- | 20 | 950 | 3000 | 2975 | | | 2175 | ----------------------------------------------------------------------------- | 30 | 950 | | 2850 | | 1400 | 1566.66667 | ----------------------------------------------------------------------------- | 合计 | | | | | | 2073.21429 | -----------------------------------------------------------------------------
cube 是在group by rollup 的基础上,再做出纵向小计:
SQL> select deptno,job,avg(sal) from emp group by cube(deptno,job); ----------------------------------------------------------------------------- |deptnojob | clark | analyst | manager | president | salesman | 小计 |
----------------------------------------------------------------------------- | 10 | 1300 || 2450 | 5000 | | 2916.66667 | ----------------------------------------------------------------------------- | 20 | 950 | 3000 | 2975 | | | 2175 | ----------------------------------------------------------------------------- | 30 | 950 | | 2850 | | 1400 | 1566.66667 | ----------------------------------------------------------------------------- | 职位小计 |1037.5 | 3000 |2758.333 | 5000 | 1400 | | ----------------------------------------------------------------------------- | 合计 | | | | | | 2073.21429 | -----------------------------------------------------------------------------
ROLLUP sql写法:
SQL> select deptno,job,avg(sal) from emp group by rollup (deptno,job); DEPTNO JOB AVG(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 2916.66667 20 CLERK 950 20 ANALYST 3000 20 MANAGER 2975 20 2175 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400 30 1566.66667 2073.21429 13 rows selected.
CUBE sql写法：
SQL> select deptno,job,avg(sal) from emp group by cube (deptno,job) order by 1; DEPTNO JOB AVG(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 2916.66667 20 ANALYST 3000 20 CLERK 950 20 MANAGER 2975 20 2175 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400 30 1566.66667 ANALYST 3000 CLERK 1037.5 MANAGER 2758.33333 PRESIDENT 5000 SALESMAN 1400 2073.21429 18 rows selected.
解析rollup语句
rollup(a,b,c)
等同于
group by a,b,c union all group by a,b union all group by a union all group by null
为维持输出列的数量一直 select子句中用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 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 29025 13 rows selected. -----分解为: SQL> select deptno,job,sum(sal) from emp group by deptno,job 2 union all 3 select deptno,null,sum(sal) from emp group by deptno 4 union all 5 select null,null,sum(sal) from emp 6 order by 1; DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 8750 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 MANAGER 2975 20 CLERK 1900 20 10875 30 SALESMAN 5600 30 CLERK 950 30 MANAGER 2850 30 9400 29025 13 rows selected.
解析cube语句
cube(a,b,c) 分解成2的N次方个union all组合
等同于:
group by a,b,c union all group by a,b union all group by a union all group by a,c union all group by b,c union all group by b union all group by c union all group by null
举例：
SQL> select deptno,job,sum(sal) from emp group by cube(deptno,job); --------解析为: 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 order by 1
grouping
在使用rollup和cube时统计结果可能用到一列也可能用到多列,为了确定统计结果是否使用了特定列才使用grouping
返回为0 代表使用了该列
返回为1 代表没使用该列
SQL> select deptno,job,avg(sal),grouping(deptno),grouping(job) from emp group by cube (deptno,job) order by 1; DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB) ---------- --------- ---------- ---------------- ------------- 10 CLERK 1300 0 0 10 MANAGER 2450 0 0 10 PRESIDENT 5000 0 0 10 2916.66667 0 1 20 ANALYST 3000 0 0 20 CLERK 950 0 0 20 MANAGER 2975 0 0 20 2175 0 1 30 CLERK 950 0 0 30 MANAGER 2850 0 0 30 SALESMAN 1400 0 0 30 1566.66667 0 1 ANALYST 3000 1 0 CLERK 1037.5 1 0 MANAGER 2758.33333 1 0 PRESIDENT 5000 1 0 SALESMAN 1400 1 0 2073.21429 1 1 18 rows selected.
grouping sets
GROUPING SETS是一个GROUP BY的增强语句，能让你随心所欲的指定多个分组列小计或合计，ORACLE会用UNION ALL将GROUPING SETS中制定的每组合计组合在一起。
GROUPING SETS相对与多个GROUP BY的优势：
- 只要一次扫描基表，能得到更好的性能
- 不需要写复杂的UNION语句
select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);
--------实际就是两个group by的union all--------
select deptno,null,avg(sal) from emp group by deptno union all select null,job,avg(sal) from emp group by job;