【實驗】分析函式之魅力展示
眾所周知，Oracle的分析函式在BI類的分析資料庫中起到了非常大的作用，不僅僅是因為它能做的事情非常的豐富，同時，分析函式作為Oracle的內部實現，較之其他的實現形式（例如使用group by實現）效率也是出奇的高。
透過這個例子展示一下分析函式的一點點魅力。
1.樣本資料如下
sec@ora10g> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ---- ------------------- ----- ---- ------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
2.實驗目標
從上面的員工資訊表emp中得到以下六列資訊：
1）部門號
2）員工名字
3）工資
4）部門工資總數
5）員工工資佔所在部門總工資數的百分比
6）員工工資佔所總工資數的百分比
3.使用Oracle的分析函式實現如下
sec@ora10g> select deptno, ename, sal,
2 sum(sal)
3 over(partition by deptno order by sal, ename ) cum_sal,
4 round(100*ratio_to_report(sal)
5 over(partition by deptno),1) pct_dept,
6 round(100*ratio_to_report(sal)
7 over(),1) pct_overall
8 from emp
9 order by deptno,sal
10 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
10 CLARK 2450 3750 28 8.4
10 KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
20 ADAMS 1100 1900 10.1 3.8
20 JONES 2975 4875 27.4 10.2
20 FORD 3000 7875 27.6 10.3
20 SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
30 MARTIN 1250 2200 13.3 4.3
30 WARD 1250 3450 13.3 4.3
30 TURNER 1500 4950 16 5.2
30 ALLEN 1600 6550 17 5.5
30 BLAKE 2850 9400 30.3 9.8
14 rows selected.
如果上面的顯示效果不是很漂亮，可以使用break on在SQL*Plus中美化一下
sec@ora10g> break on deptno skip 1
sec@ora10g> select deptno, ename, sal,
2 sum(sal)
3 over(partition by deptno order by sal, ename ) cum_sal,
4 round(100*ratio_to_report(sal)
5 over(partition by deptno),1) pct_dept,
6 round(100*ratio_to_report(sal)
7 over(),1) pct_overall
8 from emp
9 order by deptno,sal
10 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
CLARK 2450 3750 28 8.4
KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
ADAMS 1100 1900 10.1 3.8
JONES 2975 4875 27.4 10.2
FORD 3000 7875 27.6 10.3
SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
MARTIN 1250 2200 13.3 4.3
WARD 1250 3450 13.3 4.3
TURNER 1500 4950 16 5.2
ALLEN 1600 6550 17 5.5
BLAKE 2850 9400 30.3 9.8
14 rows selected.
OK，這時的顯示效果是不是很直觀，類似於報表統計之後的效果，這就是SQL的魅力。
4.解釋一下上面用到的分析函式
1）DEPTNO，ENAME和SAL三列就不用多說了，僅僅是欄位的羅列而已。
2）CUM_SAL列使用的分析函式是：sum(sal) over(partition by deptno order by sal, ename )
功能：按照部門號將每一位員工的工資進行累加，最終得到這個部門的部門工資總數。
3）PCT_DEPT列使用到的分析函式是：round(100*ratio_to_report(sal) over (partition by deptno),1)
功能：按照部門號deptno對工資sal計算得到員工工資佔所在部門總工資數的百分比
4）PCT_OVERALL列使用的分析函式是：round(100*ratio_to_report(sal) over(),1) pct_overall
功能：over引數為空，表示計算單位是所有的資料，最終得到員工工資佔所總工資數的百分比
5.如果不用分析函式如何改寫上面的SQL語句呢？可以做如下的改寫
sec@ora10g> break on deptno skip 1
sec@ora10g> select emp.deptno, emp.ename, emp.sal,
2 sum(emp4.sal) cum_sal,
3 round(100*emp.sal/emp2.sal_by_dept,1) pct_dept,
4 round(100*emp.sal/emp3.sal_overall,1) pct_overall
5 from emp,
6 (select deptno, sum(sal) sal_by_dept
7 from emp
8 group by deptno ) emp2,
9 (select sum(sal) sal_overall
10 from emp ) emp3,
11 emp emp4
12 where emp.deptno = emp2.deptno
13 and emp.deptno = emp4.deptno
14 and (emp.sal > emp4.sal or
15 (emp.sal = emp4.sal and emp.ename >= emp4.ename))
16 group by emp.deptno, emp.ename, emp.sal,
17 round(100*emp.sal/emp2.sal_by_dept,1),
18 round(100*emp.sal/emp3.sal_overall,1)
19 order by deptno, sal
20 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
CLARK 2450 3750 28 8.4
KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
ADAMS 1100 1900 10.1 3.8
JONES 2975 4875 27.4 10.2
FORD 3000 7875 27.6 10.3
SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
MARTIN 1250 2200 13.3 4.3
WARD 1250 3450 13.3 4.3
TURNER 1500 4950 16 5.2
ALLEN 1600 6550 17 5.5
BLAKE 2850 9400 30.3 9.8
14 rows selected.
6.小結
經測試表明，使用分析函式在資料量增加的情況下，執行效率是呈線性降低的，但是使用group by的實現方式，執行效率是呈指數下降！（您可以參考一下【附】中的測試結果）
因此，分析函式的實現方式可以成百上千的提高SQL的效率。
雖然對分析函式的學習和接觸有一個過程，不過在對海量資料進行分析時，漫長的執行等待是誰都無法容忍的，這也是技術的魅力所在。
【附】分析函式與普通查詢方法的效能測試結果
第一次測試：測試記錄數為14條
分析函式耗時：Elapsed: 00:00:00.02
普通查詢耗時：Elapsed: 00:00:00.09
第二次測試：測試記錄數為1792條
分析函式耗時：Elapsed: 00:00:00.06
普通查詢耗時：Elapsed: 00:00:01.37
第三次測試：測試記錄數為14336條
分析函式耗時：Elapsed: 00:00:00.24
普通查詢耗時：Elapsed: 00:01:17.43
第四次試驗：測試記錄數為1835008條
分析函式耗時：Elapsed: 00:00:26.63
普通查詢耗時：當emp表的資料量達到近2百萬的數量級的時候，普通查詢方法已經無法返回結果，時間超過50031秒，而且時間還在累計，我無法等到這個結果的返回，手工cancel了。
-- The End --
透過這個例子展示一下分析函式的一點點魅力。
1.樣本資料如下
sec@ora10g> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ---- ------------------- ----- ---- ------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
2.實驗目標
從上面的員工資訊表emp中得到以下六列資訊：
1）部門號
2）員工名字
3）工資
4）部門工資總數
5）員工工資佔所在部門總工資數的百分比
6）員工工資佔所總工資數的百分比
3.使用Oracle的分析函式實現如下
sec@ora10g> select deptno, ename, sal,
2 sum(sal)
3 over(partition by deptno order by sal, ename ) cum_sal,
4 round(100*ratio_to_report(sal)
5 over(partition by deptno),1) pct_dept,
6 round(100*ratio_to_report(sal)
7 over(),1) pct_overall
8 from emp
9 order by deptno,sal
10 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
10 CLARK 2450 3750 28 8.4
10 KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
20 ADAMS 1100 1900 10.1 3.8
20 JONES 2975 4875 27.4 10.2
20 FORD 3000 7875 27.6 10.3
20 SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
30 MARTIN 1250 2200 13.3 4.3
30 WARD 1250 3450 13.3 4.3
30 TURNER 1500 4950 16 5.2
30 ALLEN 1600 6550 17 5.5
30 BLAKE 2850 9400 30.3 9.8
14 rows selected.
如果上面的顯示效果不是很漂亮，可以使用break on在SQL*Plus中美化一下
sec@ora10g> break on deptno skip 1
sec@ora10g> select deptno, ename, sal,
2 sum(sal)
3 over(partition by deptno order by sal, ename ) cum_sal,
4 round(100*ratio_to_report(sal)
5 over(partition by deptno),1) pct_dept,
6 round(100*ratio_to_report(sal)
7 over(),1) pct_overall
8 from emp
9 order by deptno,sal
10 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
CLARK 2450 3750 28 8.4
KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
ADAMS 1100 1900 10.1 3.8
JONES 2975 4875 27.4 10.2
FORD 3000 7875 27.6 10.3
SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
MARTIN 1250 2200 13.3 4.3
WARD 1250 3450 13.3 4.3
TURNER 1500 4950 16 5.2
ALLEN 1600 6550 17 5.5
BLAKE 2850 9400 30.3 9.8
14 rows selected.
OK，這時的顯示效果是不是很直觀，類似於報表統計之後的效果，這就是SQL的魅力。
4.解釋一下上面用到的分析函式
1）DEPTNO，ENAME和SAL三列就不用多說了，僅僅是欄位的羅列而已。
2）CUM_SAL列使用的分析函式是：sum(sal) over(partition by deptno order by sal, ename )
功能：按照部門號將每一位員工的工資進行累加，最終得到這個部門的部門工資總數。
3）PCT_DEPT列使用到的分析函式是：round(100*ratio_to_report(sal) over (partition by deptno),1)
功能：按照部門號deptno對工資sal計算得到員工工資佔所在部門總工資數的百分比
4）PCT_OVERALL列使用的分析函式是：round(100*ratio_to_report(sal) over(),1) pct_overall
功能：over引數為空，表示計算單位是所有的資料，最終得到員工工資佔所總工資數的百分比
5.如果不用分析函式如何改寫上面的SQL語句呢？可以做如下的改寫
sec@ora10g> break on deptno skip 1
sec@ora10g> select emp.deptno, emp.ename, emp.sal,
2 sum(emp4.sal) cum_sal,
3 round(100*emp.sal/emp2.sal_by_dept,1) pct_dept,
4 round(100*emp.sal/emp3.sal_overall,1) pct_overall
5 from emp,
6 (select deptno, sum(sal) sal_by_dept
7 from emp
8 group by deptno ) emp2,
9 (select sum(sal) sal_overall
10 from emp ) emp3,
11 emp emp4
12 where emp.deptno = emp2.deptno
13 and emp.deptno = emp4.deptno
14 and (emp.sal > emp4.sal or
15 (emp.sal = emp4.sal and emp.ename >= emp4.ename))
16 group by emp.deptno, emp.ename, emp.sal,
17 round(100*emp.sal/emp2.sal_by_dept,1),
18 round(100*emp.sal/emp3.sal_overall,1)
19 order by deptno, sal
20 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
CLARK 2450 3750 28 8.4
KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
ADAMS 1100 1900 10.1 3.8
JONES 2975 4875 27.4 10.2
FORD 3000 7875 27.6 10.3
SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
MARTIN 1250 2200 13.3 4.3
WARD 1250 3450 13.3 4.3
TURNER 1500 4950 16 5.2
ALLEN 1600 6550 17 5.5
BLAKE 2850 9400 30.3 9.8
14 rows selected.
6.小結
經測試表明，使用分析函式在資料量增加的情況下，執行效率是呈線性降低的，但是使用group by的實現方式，執行效率是呈指數下降！（您可以參考一下【附】中的測試結果）
因此，分析函式的實現方式可以成百上千的提高SQL的效率。
雖然對分析函式的學習和接觸有一個過程，不過在對海量資料進行分析時，漫長的執行等待是誰都無法容忍的，這也是技術的魅力所在。
【附】分析函式與普通查詢方法的效能測試結果
第一次測試：測試記錄數為14條
分析函式耗時：Elapsed: 00:00:00.02
普通查詢耗時：Elapsed: 00:00:00.09
第二次測試：測試記錄數為1792條
分析函式耗時：Elapsed: 00:00:00.06
普通查詢耗時：Elapsed: 00:00:01.37
第三次測試：測試記錄數為14336條
分析函式耗時：Elapsed: 00:00:00.24
普通查詢耗時：Elapsed: 00:01:17.43
第四次試驗：測試記錄數為1835008條
分析函式耗時：Elapsed: 00:00:26.63
普通查詢耗時：當emp表的資料量達到近2百萬的數量級的時候，普通查詢方法已經無法返回結果，時間超過50031秒，而且時間還在累計，我無法等到這個結果的返回，手工cancel了。
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-615990/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【函式】Oracle的常用字元函式實驗展示（一）函式Oracle字元
- 【函式】Oracle的常用字元函式實驗展示（二）函式Oracle字元
- 【ROLLUP】Oracle分組函式之ROLLUP魅力Oracle函式
- 【CUBE】Oracle分組函式之CUBE魅力Oracle函式
- JavaScript中高階函式的魅力JavaScript函式
- 【Analytic】分析函式之MIN函式函式
- 【Analytic】分析函式之MAX函式函式
- 【Analytic】分析函式之AVG函式函式
- 【Analytic】分析函式之RANK函式函式
- 【Analytic】分析函式之COUNT函式函式
- 第二章 函式的魅力函式
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- 【Analytic】分析函式之ROW_NUMBER函式函式
- 【Analytic】分析函式之DENSE_RANK函式函式
- 【Analytic】分析函式之FIRST_VALUE函式函式
- 【Analytic】分析函式之LAST_VALUE函式函式AST
- 分析函式之視窗子句函式
- 分析函式之排名統計函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- MySQL分析函式實現MySql函式
- 案例展示自定義C函式的實現過程函式
- 函式節流、函式防抖實現原理分析函式
- Oracle分析函式之LEAD和LAG實際應用Oracle函式
- oracle實驗記錄 （函式index）Oracle函式Index
- Oracle分析函式七——分析函式案例Oracle函式
- oracle分析函式之ratio_to_reportOracle函式
- Oracle聚合函式/分析函式Oracle函式
- 【實驗】【總結】基礎日期函式函式
- 分析函式函式
- 字串函式之Strtok()函式字串函式
- redux原始碼分析之四：compose函式Redux原始碼函式
- Golang之不可重入函式實現Golang函式
- mysql日期函式小結及個人實驗MySql函式
- 分析函式概述函式
- 分析函式 over函式
- Oracle 分析函式Oracle函式
- 分析函式 - LAG函式
- Oracle分析函式Oracle函式