【開發篇sql】 分析函式（一） 評級相關的函式
三，分析函式
分析函式計算基於一組行的聚合值，與聚合函式不同的是它為每個分組返回多行值。行的分組被叫做一個視窗，在analytic_clause子句中定義。
具體的句法如下：
Analytic_function (arguments) over (analytic_clause)
其中：
Analytic_function函式名，9i/10g中有26個分析函式，接下來會逐一分析。
analytic_clause:{Query_partition_clause } {order by clause} {windowing_clause}
使用over告知查詢分析器函式為分析函式而不是聚合函式，且在隨後的子句指出要在那些欄位上做分析計算。
Query_partition_clause: partition by {expr}/{(expr)}
使用partition表示對資料進行分組，如果沒有指定，則將全部的結果作為一個分組來對待。
Order by clause:order by expr {asc/desc} {null/{first/last}}
Order by新增一個預設的開窗子句，告知當前分組內的計算順序。後面新增nulls last或者nulls first是正對null值的處理。
Window_clause:{rows/range}
定義分組內用於計算或操作的具體行的集合。
Range:
產生一個滑動視窗，在組中擁有指定的range的行，使用range時對order by限制為一列，使得其滑動視窗的範圍為一維。
下面分幾組來講解分析函式的具體使用：
1, 評級函式row_number,rank dense_rank,percent_rank，cume_dist,ntile
先來看看row_number，現在要對scott使用者下面的工人表emp按照薪水從小到大排序，看看哪些是困難戶：
SQL> select a.empno, a.ename, a.sal, row_number() over(order by a.sal) num
2 from scott.emp a;
EMPNO ENAME SAL NUM
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 2
7876 ADAMS 1100 3
7521 WARD 1250 4
7654 MARTIN 1250 5
7934 update 1300 6
7844 TURNER 1500 7
7499 ALLEN 1600 8
7566 JONES 2000 9
7782 CLARK 2450 10
7698 BLAKE 2850 11
EMPNO ENAME SAL NUM
---------- ---------- ---------- ----------
7788 SCOTT 3000 12
7902 FORD 3000 13
7839 KING 5000 14
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 196 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 196 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
使用row_number()很容易為一個分組裡面的行排序並加上序號（注意上面執行計劃id為2的window sort。）
上述排序中12和13的工錢一樣多，這樣排序只是按照行的rowid來排的，預設是從小到大，可以具體看看：
SQL> select row_number() over(order by a.deptno) num, rowid, a.empno,a.deptno
2 from scott.emp a;
NUM ROWID EMPNO DEPTNO
---------- ------------------ ---------- ----------
1 AAANMFAAEAAAABEAAG 7782 10
2 AAANMFAAEAAAABEAAI 7839 10
3 AAANMFAAEAAAABEAAN 7934 10
4 AAANMFAAEAAAABEAAD 7566 20
5 AAANMFAAEAAAABEAAM 7902 20
6 AAANMFAAEAAAABEAAK 7876 20
7 AAANMFAAEAAAABEAAA 7369 20
8 AAANMFAAEAAAABEAAH 7788 20
9 AAANMFAAEAAAABEAAC 7521 30
10 AAANMFAAEAAAABEAAJ 7844 30
11 AAANMFAAEAAAABEAAB 7499 30
NUM ROWID EMPNO DEPTNO
---------- ------------------ ---------- ----------
12 AAANMFAAEAAAABEAAL 7900 30
13 AAANMFAAEAAAABEAAF 7698 30
14 AAANMFAAEAAAABEAAE 7654 30
14 rows selected.
通過看相同deptno的rowid就可以看出來這個規律。
現在需要對分不同部門來看部門內的工錢排名，且從大到小排列：
SQL> select a.empno,
2 a.ename,
3 a.deptno,
4 a.sal,
5 row_number() over(partition by a.deptno order by a.sal desc) num
6 from scott.emp a;
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7782 CLARK 10 2450 2
7934 update 10 1300 3
7788 SCOTT 20 3000 1
7902 FORD 20 3000 2
7566 JONES 20 2000 3
7876 ADAMS 20 1100 4
7369 SMITH 20 800 5
7698 BLAKE 30 2850 1
7499 ALLEN 30 1600 2
7844 TURNER 30 1500 3
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7654 MARTIN 30 1250 4
7521 WARD 30 1250 5
7900 JAMES 30 950 6
14 rows selected.
如果每個部門只要取前三名的話，則可以稍稍修改查詢：
SQL> select * from (
2 select a.empno,
3 a.ename,
4 a.deptno,
5 a.sal,
6 row_number() over(partition by a.deptno order by a.sal desc) num
7 from scott.emp a) where num <=3;
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7782 CLARK 10 2450 2
7934 update 10 1300 3
7788 SCOTT 20 3000 1
7902 FORD 20 3000 2
7566 JONES 20 2000 3
7698 BLAKE 30 2850 1
7499 ALLEN 30 1600 2
7844 TURNER 30 1500 3
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 14 | 826 | 4 (25)| 00:00:01
|
|* 1 | VIEW | | 14 | 826 | 4 (25)| 00:00:01
|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 238 | 4 (25)| 00:00:01
|
| 3 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01
|
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM"<=3)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."DEPTNO" ORDER BY
INTERNAL_FUNCTION("A"."SAL") DESC )<=3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
845 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
很容易想到用巢狀查詢，在外層限定序號num的值不超過3，注意看看執行計劃id為2的行，使用的是WINDOW SORT PUSHED RANK
下面就來看看rank是如何實現上面的這個求前三甲的需求的：
SQL> select * from (
2 select a.empno,
3 a.ename,
4 a.deptno,
5 a.sal,
6 rank() over(partition by a.deptno order by a.sal desc) num
7 from scott.emp a) where num <=3;
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7782 CLARK 10 2450 2
7934 update 10 1300 3
7788 SCOTT 20 3000 1
7902 FORD 20 3000 1
7566 JONES 20 2000 3
7698 BLAKE 30 2850 1
7499 ALLEN 30 1600 2
7844 TURNER 30 1500 3
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 14 | 826 | 4 (25)| 00:00:01
|
|* 1 | VIEW | | 14 | 826 | 4 (25)| 00:00:01
|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 238 | 4 (25)| 00:00:01
|
| 3 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM"<=3)
2 - filter(RANK() OVER ( PARTITION BY "A"."DEPTNO" ORDER BY
INTERNAL_FUNCTION("A"."SAL") DESC )<=3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
842 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
好像寫法是一樣的，但是結果稍稍有所不同，rank會把相同的sal當作並列處理。但看執行機會和統計資訊則發現基本一樣(這裡猜測oracle內部實現row_number和rank的主要演算法是一樣的)
Dense_rank和rank稍有不同，不過是序號不會像rank一樣跳躍。再看一個例子：
SQL> select a.deptno,
2 a.job,
3 sum(a.sal),
4 dense_rank() over(partition by a.deptno order by sum(a.sal) desc) num
5 from scott.emp a
6 group by grouping sets((a.deptno),(a.job),(a.deptno, a.job),());
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
10 8750 1
10 PRESIDENT 5000 2
10 MANAGER 2450 3
10 CLERK 1300 4
20 9900 1
20 ANALYST 6000 2
20 MANAGER 2000 3
20 CLERK 1900 4
30 9400 1
30 SALESMAN 5600 2
30 MANAGER 2850 3
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
30 CLERK 950 4
28050 1
MANAGER 7300 2
ANALYST 6000 3
SALESMAN 5600 4
PRESIDENT 5000 5
CLERK 4150 6
18 rows selected.
以上是分部門和工種來求工錢總和，並且使用了grouping sets根據需要獲得分組的排名。
Percent_rank也很容易理解：
當前行佔分組內行的百分比，比如要知道當前的員工的工錢水平佔整個部門內的什麼水平，可以這樣來看：
SQL> select a.empno,
2 a.ename,
3 a.deptno,
4 a.sal,
5 percent_rank() over(partition by a.deptno order by a.sal desc) num
6 from scott.emp a
7 ;
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 0
7782 CLARK 10 2450 .5
7934 update 10 1300 1
7788 SCOTT 20 3000 0
7902 FORD 20 3000 0
7566 JONES 20 2000 .5
7876 ADAMS 20 1100 .75
7369 SMITH 20 800 1
7698 BLAKE 30 2850 0
7499 ALLEN 30 1600 .2
7844 TURNER 30 1500 .4
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7654 MARTIN 30 1250 .6
7521 WARD 30 1250 .6
7900 JAMES 30 950 1
14 rows selected.
顯然KING和CLARK都是部門內50%的富人，但是他們相差卻非常之大，貧富差距往往是資料看不出來的，前面還沒有用avg來求平均值，求了則更是粉飾了真正的現實。想到最近10年或者20年（從記事開始算起）以來的變遷，我們的“涉會注意郭家”是相當優越的，對內喜歡講人均，對外喜歡將總和，就是轉移一下貧富差距的視線，現在的情況是大寡頭佔有了涉會絕大多數財富資源，很少數的人搶佔了大多數人的幸福生活。不要覺得意外，其基本原理本來就是這樣：
讓一部分人先富起來，帶動全民富裕。
這句看著很搞笑了。
下面繼續來看看cume_dist,和percent_rank差不多，看一個例子就可以知道了：
SQL> select a.deptno,
2 a.job,
3 sum(a.sal),
4 cume_dist() over(partition by a.deptno order by sum(a.sal) desc) num
5 from scott.emp a
6 group by rollup (a.deptno, a.job);
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
10 8750 .25
10 PRESIDENT 5000 .5
10 MANAGER 2450 .75
10 CLERK 1300 1
20 9900 .25
20 ANALYST 6000 .5
20 MANAGER 2000 .75
20 CLERK 1900 1
30 9400 .25
30 SALESMAN 5600 .5
30 MANAGER 2850 .75
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
30 CLERK 950 1
28050 1
13 rows selected.
而ntile是將行再分組，修改上述查詢也很容易理解，ntile(2)將行分為2組，下面例子中4行的即為2，2，如果是3行，則2，1，其他雷同：
SQL> select a.deptno,
2 a.job,
3 sum(a.sal),
4 ntile(2) over(partition by a.deptno order by sum(a.sal) desc) num
5 from scott.emp a
6 group by rollup (a.deptno, a.job);
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
10 8750 1
10 PRESIDENT 5000 1
10 MANAGER 2450 2
10 CLERK 1300 2
20 9900 1
20 ANALYST 6000 1
20 MANAGER 2000 2
20 CLERK 1900 2
30 9400 1
30 SALESMAN 5600 1
30 MANAGER 2850 2
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
30 CLERK 950 2
28050 1
13 rows selected.
有關評級函式，再說一個表結構設計時候的問題，現在有一個log表，記錄某些操作的具體資訊（操作事件，操作人，操作資訊，開始時間，結束時間，操作物件id，操作狀態等等），現在要對操作人考核，看看這個人總共操作了多少次，在操作中花了多少時間，每個狀態值停了多少時間，從第一次開始操作，到最終完成操作又話了多少時間。好像沒有什麼是sql完成不了的，rank,row_number,或者lag,lead可能都可以用的上，不過個人覺得，像經常性查詢的一些關鍵欄位，應該在基表內有一定的冗餘欄位，關聯或者計算在大資料量和高響應要求時給系統帶來了負面影響，嚴格來講分析函式應該儘可能多用於資料倉儲或者分析報表庫，而不是oltp的生產系統，但是往往情況不是這樣。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-663141/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【開發篇sql】 分析函式（二） 行篩選相關的函式SQL函式
- SQL 日期格式相關函式SQL函式
- 字串的相關函式字串函式
- sql優化用group by 函式代替分析函式SQL優化函式
- 正規表示式☞相關函式函式
- 物件及函式相關物件函式
- SAP PP相關函式函式
- Python 函式相關Python函式
- ORACLE函式介紹第六篇 著名函式之分析函式Oracle函式
- oracle的分析函式over 及開窗函式Oracle函式
- Sql Server函式全解(一)字串函式SQLServer函式字串
- 字串相關函式的實現字串函式
- ES6 函式相關函式
- 3.3 數學相關函式函式
- 函式組：CSAP（BOM 相關）函式
- SQL函式之日期函式SQL函式
- Python資料分析--Numpy常用函式介紹(5)--Numpy中的相關性函式Python函式
- ORACLE函式介紹第七篇 非著名函式之分析函式Oracle函式
- MySQL函式大全(字串函式，數學函式，日期函式，系統級函式，聚合函式)MySql函式字串
- Oracle聚合函式/分析函式Oracle函式
- oracle的分析函式over(Partition by...) 及開窗函式Oracle函式
- Oracle分析函式七——分析函式案例Oracle函式
- oracle分析函式（一）Oracle函式
- 【C語言】常用的字串函式及相關函式的自我實現C語言字串函式
- 函式篇函式
- Sql 中的 left 函式、right 函式SQL函式
- Spark SQL 開窗函式SparkSQL函式
- ORACLE函式介紹第五篇 分析函式簡述Oracle函式
- SQL函式SQL函式
- 關於一篇文章引發的匿名函式的思考函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- ORACLE函式介紹第一篇 著名函式之單值函式Oracle函式
- 測開之函式進階· 第2篇《純函式》函式
- 測開之函式進階· 第4篇《匿名函式》函式
- 測開之函式進階· 第5篇《偏函式》函式
- T-SQL——函式——字串操作函式SQL函式字串
- Presto 函式開發REST函式
- python開發第四篇–函式Python函式