分析函数

[@more@]

普通的sql匯總數據:
SQL> SELECT job,sum(sal) from emp group by job
2 union all
3 select '合計' job,sum(sal) from emp;

JOB SUM(SAL)
--------- ----------
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
合計 29025
autot: 6 consistent gets
使用rollup統計函數:
SQL> select nvl(job,'合計') job,sum(sal)
2 from emp
3 group by rollup (job);

JOB SUM(SAL)
--------- ----------
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
合計 29025
autot: 3 consistent gets
从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到3个consistent gets,如果基表很大的话,结果就可想而知了.
为了介绍cube函数我们再来看看另外一个使用rollup的例子:
1 select deptno,nvl(job,'合計') job,sum(sal)
2 from emp
3 group by rollup (deptno,job)
4* order by deptno
SQL> /

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 合計 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 合計 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 合計 9400
合計 29025
系统只是根据rollup的第一个参数DEPTNO对结果集的数据做了汇总处理,而没有对JOB做汇总分析处理,cube函数就是为了这个而设计的.
下面,让我们看看使用cube函数的结果
cube函數的使用:
1 select deptno,nvl(job,'合計') job,sum(sal
2 from emp
3 group by cube (deptno,job)
4* order by deptno
SQL> /

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 合計 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 合計 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 合計 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
合計 29025
可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据JOB做的汇总统计结果

1 rollup 和 cube函数的再深入
从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
我们如何来区分到底是根据那个字段做的汇总呢,
这时候,oracle的grouping函数就粉墨登场了.
如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0
EXAMPLE:
1 select decode(grouping(deptno),1,'部門統計',deptno) deptno,
2 decode(grouping(job),1,'職位統計',job) job,
3 sum(sal)
4 from emp
5 group by cube(deptno,job)
6* order by deptno,job
SQL> /

DEPTNO JOB SUM(SAL)
---------------------------------------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 職位統計 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 職位統計 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 職位統計 9400
部門統計 ANALYST 6000
部門統計 CLERK 4150
部門統計 MANAGER 8275
部門統計 PRESIDENT 5000
部門統計 SALESMAN 5600
部門統計職位統計 29025
可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.

ITPub博客

分析函數的學習

最新文章