分析函式

[@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函式,我們做資料統計的時候就可以輕鬆很多了.

分析函式的學習

相關文章