Oracle 中 Over() 函式學習總結
Oracle從8.1.6開始提供分析函式，分析函式用於計算基於組的某種聚合值，它和聚合函式的不同之處是對於每個組返回多行，而聚合函式對於每個組只返回一行。
開窗函式指定了分析函式工作的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化，舉例如下：
over(order by salary) 按照salary排序進行累計，order by是個預設的開窗函式；
over(partition by deptid)按照部門分割槽；
over(partition by deptid)按照部門分割槽；
over(order by salary range between 50 preceding and 150 following) 每行對應的資料視窗是之前行幅度值不超過50，之後行幅度值不超過150；
over(order by salary rows between 50 preceding and 150 following) 每行對應的資料視窗是之前50行，之後150行；
over(order by salary rows between unbounded preceding and unbounded following) 每行對應的資料視窗是從第一行到最後一行，等效：
over(order by salary range between unbounded preceding and unbounded following）
over(order by salary rows between 50 preceding and 150 following) 每行對應的資料視窗是之前50行，之後150行；
over(order by salary rows between unbounded preceding and unbounded following) 每行對應的資料視窗是從第一行到最後一行，等效：
over(order by salary range between unbounded preceding and unbounded following）
1、首先看看測試表的測試資料，順便演示一下分組功能
SQL> select test.*,row_number() over(partition by deptid order by deptid) rw from test;
DEPTID NAME SALARY RW
---------- ------ ---------- ----------
10 david 1000 1
10 john 1100 2
10 jerry 1200 3
20 smith 1300 1
20 scott 1400 2
30 tony 1500 1
30 amanda 1600 2
---------- ------ ---------- ----------
10 david 1000 1
10 john 1100 2
10 jerry 1200 3
20 smith 1300 1
20 scott 1400 2
30 tony 1500 1
30 amanda 1600 2
7 rows selected
SQL>
測試表test中共做了7條測試資料，包括三列（機構ID，員工姓名，工資）。
2、檢視每個部門的平均工資
SQL> select a.*, avg(salary) OVER (PARTITION BY deptid) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 1100
10 john 1100 1100
10 jerry 1200 1100
20 smith 1300 1350
20 scott 1400 1350
30 tony 1500 1550
30 amanda 1600 1550
---------- ------ ---------- ----------
10 david 1000 1100
10 john 1100 1100
10 jerry 1200 1100
20 smith 1300 1350
20 scott 1400 1350
30 tony 1500 1550
30 amanda 1600 1550
7 rows selected
以及檢視每個部門的最大、最小工資
SQL> select a.*, min(salary) OVER (PARTITION BY deptid) min_c, max(salary) OVER (PARTITION BY deptid) max_c from test a;
DEPTID NAME SALARY MIN_C MAX_C
---------- ------ ---------- ---------- ----------
10 david 1210 1100 1210
10 john 1100 1100 1210
10 jerry 1200 1100 1210
20 smith 1100 1100 1300
20 scott 1300 1100 1300
30 tony 1200 1200 1250
30 amanda 1250 1200 1250
---------- ------ ---------- ---------- ----------
10 david 1210 1100 1210
10 john 1100 1100 1210
10 jerry 1200 1100 1210
20 smith 1100 1100 1300
20 scott 1300 1100 1300
30 tony 1200 1200 1250
30 amanda 1250 1200 1250
7 rows selected
3、檢視每個部門的人數
SQL> select a.*, count(*) OVER (PARTITION BY deptid) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 3
10 john 1100 3
10 jerry 1200 3
20 smith 1300 2
20 scott 1400 2
30 tony 1500 2
30 amanda 1600 2
---------- ------ ---------- ----------
10 david 1000 3
10 john 1100 3
10 jerry 1200 3
20 smith 1300 2
20 scott 1400 2
30 tony 1500 2
30 amanda 1600 2
7 rows selected
4、檢視每個部門的工資總數
SQL> select a.*, sum(salary) OVER (PARTITION BY deptid) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 3300
10 john 1100 3300
10 jerry 1200 3300
20 smith 1300 2700
20 scott 1400 2700
30 tony 1500 3100
30 amanda 1600 3100
---------- ------ ---------- ----------
10 david 1000 3300
10 john 1100 3300
10 jerry 1200 3300
20 smith 1300 2700
20 scott 1400 2700
30 tony 1500 3100
30 amanda 1600 3100
7 rows selected
按照每個部門工資排序進行統計
SQL> select a.*, sum(salary) OVER (PARTITION BY deptid order by salary) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 1000
10 john 1100 2100
10 jerry 1200 3300
20 smith 1300 1300
20 scott 1400 2700
30 tony 1500 1500
30 amanda 1600 3100
---------- ------ ---------- ----------
10 david 1000 1000
10 john 1100 2100
10 jerry 1200 3300
20 smith 1300 1300
20 scott 1400 2700
30 tony 1500 1500
30 amanda 1600 3100
7 rows selected
按照所有員工工資排序進行統計
SQL> select a.*, sum(salary) OVER (order by salary) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 1000
10 john 1100 2100
10 jerry 1200 3300
20 smith 1300 4600
20 scott 1400 6000
30 tony 1500 7500
30 amanda 1600 9100
---------- ------ ---------- ----------
10 david 1000 1000
10 john 1100 2100
10 jerry 1200 3300
20 smith 1300 4600
20 scott 1400 6000
30 tony 1500 7500
30 amanda 1600 9100
7 rows selected
如果不需要已某個欄位的值分割，那就要用 null，等同於OVER ()
SQL> select a.*, sum(salary) OVER (order by null) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 9100
10 john 1100 9100
10 jerry 1200 9100
30 amanda 1600 9100
20 scott 1400 9100
30 tony 1500 9100
20 smith 1300 9100
---------- ------ ---------- ----------
10 david 1000 9100
10 john 1100 9100
10 jerry 1200 9100
30 amanda 1600 9100
20 scott 1400 9100
30 tony 1500 9100
20 smith 1300 9100
7 rows selected
SQL> select a.*, sum(salary) OVER () sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 9100
10 john 1100 9100
10 jerry 1200 9100
20 smith 1300 9100
20 scott 1400 9100
30 tony 1500 9100
30 amanda 1600 9100
---------- ------ ---------- ----------
10 david 1000 9100
10 john 1100 9100
10 jerry 1200 9100
20 smith 1300 9100
20 scott 1400 9100
30 tony 1500 9100
30 amanda 1600 9100
7 rows selected
5、檢視每人工資佔部門總工資的百分比
SQL> select a.*, round(salary*100/sum(salary) OVER (PARTITION BY deptid) ,2) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 30.3
10 john 1100 33.33
10 jerry 1200 36.36
20 smith 1300 48.15
20 scott 1400 51.85
30 tony 1500 48.39
30 amanda 1600 51.61
---------- ------ ---------- ----------
10 david 1000 30.3
10 john 1100 33.33
10 jerry 1200 36.36
20 smith 1300 48.15
20 scott 1400 51.85
30 tony 1500 48.39
30 amanda 1600 51.61
7 rows selected
個人工資佔整個公司工資的百分比
SQL> select a.*, round(salary*100/sum(salary) OVER (PARTITION BY null) ,2) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 10.99
10 john 1100 12.09
10 jerry 1200 13.19
30 amanda 1600 17.58
20 scott 1400 15.38
30 tony 1500 16.48
20 smith 1300 14.29
---------- ------ ---------- ----------
10 david 1000 10.99
10 john 1100 12.09
10 jerry 1200 13.19
30 amanda 1600 17.58
20 scott 1400 15.38
30 tony 1500 16.48
20 smith 1300 14.29
7 rows selected
Oracle提供了另一個函式RATIO_TO_REPORT計算百分比，比上面的演算法更簡單。該函式計算expression/(sum(expression))的值，它給出相對於總數的百分比，即當前行對sum(expression)的貢獻
SQL> select a.*, round(ratio_to_report(salary) OVER (PARTITION BY deptid),4) sum_b,round(salary*100/sum(salary) OVER (PARTITION BY deptid) ,2) sum_c from test a;
DEPTID NAME SALARY SUM_B SUM_C
---------- ------ ---------- ---------- ----------
10 david 1210 0.3447 34.47
10 john 1100 0.3134 31.34
10 jerry 1200 0.3419 34.19
20 smith 1100 0.4583 45.83
20 scott 1300 0.5417 54.17
30 tony 1200 0.4898 48.98
30 amanda 1250 0.5102 51.02
---------- ------ ---------- ---------- ----------
10 david 1210 0.3447 34.47
10 john 1100 0.3134 31.34
10 jerry 1200 0.3419 34.19
20 smith 1100 0.4583 45.83
20 scott 1300 0.5417 54.17
30 tony 1200 0.4898 48.98
30 amanda 1250 0.5102 51.02
7 rows selected
6、分部門按姓名排序後，統計對應的資料視窗之前1行，之後1行
SQL> select a.*, sum(salary) OVER (PARTITION BY deptid order by name rows between 1 preceding and 1 following) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 2200
10 jerry 1200 3300
10 john 1100 2300
20 scott 1400 2700
20 smith 1300 2700
30 amanda 1600 3100
30 tony 1500 3100
---------- ------ ---------- ----------
10 david 1000 2200
10 jerry 1200 3300
10 john 1100 2300
20 scott 1400 2700
20 smith 1300 2700
30 amanda 1600 3100
30 tony 1500 3100
7 rows selected
結果可以看出，按照部門分組後，按姓名排序並sum每個部門每條記錄的前一行，本行和後一行。
SQL> select a.*, sum(salary) OVER (order by salary rows between 1 preceding and 1 following) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1000 2100
10 john 1100 3300
10 jerry 1200 3600
20 smith 1300 3900
20 scott 1400 4200
30 tony 1500 4500
30 amanda 1600 3100
---------- ------ ---------- ----------
10 david 1000 2100
10 john 1100 3300
10 jerry 1200 3600
20 smith 1300 3900
20 scott 1400 4200
30 tony 1500 4500
30 amanda 1600 3100
7 rows selected
按工資排序後sum每條記錄的前一行，本行和後一行。
7、排序函式rank()和dense_rank()的區別
rank() 是跳躍排序，有兩個第二名時接下來就是第四名，有三個第二名時接下來就是第五名；
dense_rank() 是連續排序，有兩個第二名時仍然跟著第三名；
下面調整一下測試資料的salary，使存在相同salary的情況，然後把整個員工按salary排序，看看效果。
SQL> select a.*, rank() OVER (/*PARTITION BY deptid*/ order by salary) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
20 smith 1100 1
10 john 1100 1
10 jerry 1200 3
30 tony 1200 3
10 david 1210 5
30 amanda 1250 6
20 scott 1300 7
---------- ------ ---------- ----------
20 smith 1100 1
10 john 1100 1
10 jerry 1200 3
30 tony 1200 3
10 david 1210 5
30 amanda 1250 6
20 scott 1300 7
7 rows selected
SQL> select a.*, dense_rank() OVER (/*PARTITION BY deptid*/ order by salary) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
20 smith 1100 1
10 john 1100 1
10 jerry 1200 2
30 tony 1200 2
10 david 1210 3
30 amanda 1250 4
20 scott 1300 5
---------- ------ ---------- ----------
20 smith 1100 1
10 john 1100 1
10 jerry 1200 2
30 tony 1200 2
10 david 1210 3
30 amanda 1250 4
20 scott 1300 5
7 rows selected
SQL>
可以看出，使用rank()排序的為跳躍數字，而使用dense_rank() 排序的為連續數字。
8、LAG 和 LEAD 函式的區別
LAG 可以訪問結果集中的其它行而不用進行自連線。它允許去處理遊標，就好像遊標是一個陣列一樣。在給定組中可參考當前行之前的行，這樣就可以從組中與當前行一起選擇以前的行。Offset是一個正整數，其預設值為1，若索引超出視窗的範圍，就返回預設值（預設返回的是組中第一行），其相反的函式是LEAD
SQL> select a.*, lag(salary) OVER (PARTITION BY deptid order by salary desc) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 david 1210
10 jerry 1200 1210
10 john 1100 1200
20 scott 1300
20 smith 1100 1300
30 amanda 1250
30 tony 1200 1250
---------- ------ ---------- ----------
10 david 1210
10 jerry 1200 1210
10 john 1100 1200
20 scott 1300
20 smith 1100 1300
30 amanda 1250
30 tony 1200 1250
7 rows selected
按部門分組，按salary倒序排序，透過lag()選擇部門內前一行的salary；
SQL> select a.*, lead(salary) OVER (PARTITION BY deptid order by salary) sum_c from test a;
DEPTID NAME SALARY SUM_C
---------- ------ ---------- ----------
10 john 1100 1200
10 jerry 1200 1210
10 david 1210
20 smith 1100 1300
20 scott 1300
30 tony 1200 1250
30 amanda 1250
---------- ------ ---------- ----------
10 john 1100 1200
10 jerry 1200 1210
10 david 1210
20 smith 1100 1300
20 scott 1300
30 tony 1200 1250
30 amanda 1250
7 rows selected
SQL>
按部門分組，按salary排序，透過lead()選擇部門內後一行的salary；
利用lag和lead函式，我們可以在同一行中顯示前n行的資料，也可以顯示後n行的資料。
9、first_value() 與 last_value() 函式比較
FIRST_VALUE，返回組中資料視窗的第一個值；
LAST_VALUE，返回組中資料視窗的最後一個值。
SQL> select a.*, first_value(salary) OVER (PARTITION BY deptid) first_c, last_value(salary) OVER (PARTITION BY deptid) last_c from test a;
DEPTID NAME SALARY FIRST_C LAST_C
---------- ------ ---------- ---------- ----------
10 david 1210 1210 1200
10 john 1100 1210 1200
10 jerry 1200 1210 1200
20 smith 1100 1100 1300
20 scott 1300 1100 1300
30 tony 1200 1200 1250
30 amanda 1250 1200 1250
---------- ------ ---------- ---------- ----------
10 david 1210 1210 1200
10 john 1100 1210 1200
10 jerry 1200 1210 1200
20 smith 1100 1100 1300
20 scott 1300 1100 1300
30 tony 1200 1200 1250
30 amanda 1250 1200 1250
7 rows selected
over()很神奇，很強大，還有更多功能需要去學習和掌握。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9399028/viewspace-712967/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle over()函式使用Oracle函式
- PHP 學習總結之函式PHP函式
- c++函式學習總結C++函式
- Oracle 函式總結Oracle函式
- 【函式】oracle視窗函式over()的理解函式Oracle
- python中list方法與函式的學習總結Python函式
- oracle 與 mysql 中的函式總結OracleMySql函式
- oracle over函式 詳解（轉）Oracle函式
- sql(oracle)資料處理實用總結開窗函式(over partition)使用SQLOracle函式
- oracle的分析函式over 及開窗函式Oracle函式
- tensorflow相關函式學習總結函式
- C++虛擬函式學習總結C++函式
- JavaScript學習總結（四）function函式部分JavaScriptFunction函式
- 廖雪峰JS學習總結-函式篇JS函式
- Oracle常用函式總結Oracle函式
- Oracle 中的 ROW_NUMBER() OVER() 分析函式的用法Oracle函式
- ORACLE函式學習Oracle函式
- oracle分析函式,keep and over解說Oracle函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- 前端學習之PHP基礎函式總結前端PHP函式
- Python---python函式學習總結Python函式
- 分析函式 over函式
- ORACLE 實用函式總結Oracle函式
- Oracle分析函式使用總結Oracle函式
- oracle的分析函式over(Partition by...) 及開窗函式Oracle函式
- Oracle學習總結Oracle
- sum()over()和count()over()分析函式函式
- python學習總結之 函式定義defPython函式
- OVER(PARTITION BY)函式用法函式
- spark中的聚合函式總結Spark函式
- JS 中的函式 this 指向總結JS函式
- wcf學習總結《中》
- row_number() over函式函式
- Oracle GoldenGate學習總結OracleGo
- oracle flashback特性學習總結Oracle
- MySQL數學函式簡明總結MySql函式
- Oracle中B-Tree、Bitmap和函式索引使用案例總結Oracle函式索引
- Oracle中的時間函式用法(to_date、to_char) (總結）Oracle函式