oracle之通过group by产生统计报告
通过group by产生统计报告
Oracle数据库中的ROLLUP配合group by命令使用，可以提供信息汇总功能（与"小计"相似）
CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。
group by有关知识点：
1）group by rollup
2）group by cube
3）grouping和grouping_id函数
4）grouping set
示例如下：
SQL> select job,deptno,sum(sal) total_sal from emp group by (job,deptno);
JOB DEPTNO TOTAL_SAL
--------- ---------- ----------
MANAGER 20 2975
PRESIDENT 10 5000
CLERK 10 1300
SALESMAN 30 5600
ANALYST 20 6000
MANAGER 30 2850
MANAGER 10 2450
CLERK 30 950
CLERK 20 1900
已选择9行。
18.1 rollup的用法
SQL> select job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno);
JOB DEPTNO TOTAL_SAL
--------- ---------- ----------
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
CLERK 4150
ANALYST 20 6000
ANALYST 6000
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 8275
SALESMAN 30 5600
SALESMAN 5600
PRESIDENT 10 5000
PRESIDENT 5000
29025
15 rows selected.
18.2 cube的用法
SQL> select job,deptno,sum(sal) total_sal from emp group by cube(job,deptno);
JOB DEPTNO TOTAL_SAL
--------- ---------- ----------
29025
10 8750
20 10875
30 9400
CLERK 4150
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
ANALYST 6000
ANALYST 20 6000
MANAGER 8275
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
SALESMAN 5600
SALESMAN 30 5600
PRESIDENT 5000
PRESIDENT 10 5000
18 rows selected.
可以看出,用了rollup的group by子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，同时也会对聚合列生成一个合计列。
这里的group by后面我们仅仅接了2列，实际上我们可以使用更多列的，这样的话oracle就会以从右向左的方式来进行逐个小结。
这里需要注意的是使用了group by和rollup后，其后面的列要用括号括起来，否则将会出现ORA-00933: SQL 命令未正确结束的错误。
看看grouping、grouping_id函数是什么？
18.3 grouping函数和grouping_id函数
GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
GROUPING函数可以返回0,1,2,3...可以分别表示小计，合计等信息。
SQL>
SQL> select job,deptno,sum(sal) total_sal,grouping(job) job_grp, grouping(deptno) deptno_grp,grouping_id(job,deptno) total_grp from emp group by cube(job,deptno);
JOB DEPTNO TOTAL_SAL JOB_GRP DEPTNO_GRP TOTAL_GRP
--------- ---------- ---------- ---------- ---------- ----------
29025 1 1 3
10 8750 1 0 2
20 10875 1 0 2
30 9400 1 0 2
CLERK 4150 0 1 1
CLERK 10 1300 0 0 0
CLERK 20 1900 0 0 0
CLERK 30 950 0 0 0
ANALYST 6000 0 1 1
ANALYST 20 6000 0 0 0
MANAGER 8275 0 1 1
MANAGER 10 2450 0 0 0
MANAGER 20 2975 0 0 0
MANAGER 30 2850 0 0 0
SALESMAN 5600 0 1 1
SALESMAN 30 5600 0 0 0
PRESIDENT 5000 0 1 1
PRESIDENT 10 5000 0 0 0
已选择18行。
但是我们大多数情况下需要在查询的结果集的汇总列加上“合计”，怎么办呢？用grouping和grouping_id函数，然后再用decode函数判断一下是否为空就可以了
SQL>select grouping_id(job,deptno) as group_col,sum(sal) total_sal from emp group by rollup(job,deptno);
GROUP_COL TOTAL_SAL
---------- ----------
0 1300
0 1900
0 950
1 4150
0 6000
1 6000
0 2450
0 2975
0 2850
1 8275
0 5600
1 5600
0 5000
1 5000
3 29025
已选择15行。
SQL>select decode(grouping_id(job,deptno),1,'合计',3,'总计',job||deptno) as group_col,sum(sal) total_sal
from emp group by rollup(job,deptno);
GROUP_COL TOTAL_SAL
------------------------------------------------- ----------
CLERK10 1300
CLERK20 1900
CLERK30 950
合计 4150
ANALYST20 6000
合计 6000
MANAGER10 2450
MANAGER20 2975
MANAGER30 2850
合计 8275
SALESMAN30 5600
合计 5600
PRESIDENT10 5000
合计 5000
总计 29025
18.4 grouping sets
grouping sets用于在一个select语句中定义多个grouping, 相当于将grouping set中的多个grouping组合后再UNION ALL。
例1
SELECT null,job,mgr, AVG(sal)
FROM emp
GROUP BY(job,mgr)
union all
SELECT deptno, job, null,AVG(sal)
FROM emp
GROUP by (deptno,job);
例2=例1, 但使用了grouping sets
SELECT deptno,job,mgr, AVG(sal)
FROM emp
GROUP BY grouping sets ((deptno,job),(job,mgr));
DEPTNO JOB MGR AVG(SAL)
---------- --------- ---------- ----------
CLERK 7902 800
PRESIDENT 5000
CLERK 7698 950
CLERK 7788 1100
CLERK 7782 1300
SALESMAN 7698 1400
MANAGER 7839 2758.33333
ANALYST 7566 3000
20 CLERK 950
30 SALESMAN 1400
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 3000
已选择17行。
Oracle给出了一个小表，可以表明grouping sets和 union all的关系：
GROUPING SETS Statements Equivalent GROUP BY Statements
-------------------------------------------------------------------------------
GROUP BY GROUPING SETS(a, b, c) GROUP BY a UNION ALL
GROUP BY b UNION ALL
GROUP BY c
GROUP BY GROUPING SETS(a, b,(b, c)) GROUP BY a UNION ALL
GROUP BY b UNION ALL
GROUP BY b, c
GROUP BY GROUPING SETS((a, b, c)) GROUP BY a, b, c
GROUP BY GROUPING SETS(a, (b), ()) GROUP BY a UNION ALL
GROUP BY b UNION ALL
GROUP BY ()
GROUP BY GROUPING SETS(a,ROLLUP(b, c)) GROUP BY a UNION ALL
GROUP BY ROLLUP(b, c)
