OVER(PARTITION BY)函數介紹

• 問題場景　　

　　最近在項目中遇到了對每一個類型進行求和並且求該類型所占的比例，當時考慮求出每種類型的和，併在java中分別對每一種類型的和與總和相除求出所占比例。後來，想到這樣有點麻煩，並且項目中持久層使用的是iBatis框架，所有考慮從SQL方面進行入手來簡化這個問題。

　　後來SQL的解決方法就為：

1 SELECT T.CHANNEL AS PATTERN,
2        COUNT(T.TRANSACTIONKEY) AS T_COUNT,
3        SUM(T.AMT) AS T_AMT,
4        ROUND(100 * SUM(T.AMT) / SUM(SUM(T.AMT)) OVER(PARTITION BY 1), 2) AS AMT_PERCENT,
5        ROUND(100 * COUNT(T.TRANSACTIONKEY) / SUM(COUNT(T.TRANSACTIONKEY)) OVER(PARTITION BY 1),2) AS COUNT_PERCENT
6   FROM XX(表名) T
7  WHERE T.PARTY_ID = '100579050'
8  GROUP BY T.CHANNEL

　　看到這裡自己很佩服SQL的強大，於是刨根問底，深入研究了一番Oracel的OVER(PARTITION BY)函數。

• 簡介

　　開窗函數，Oracle從8.1.6開始提供分析函數，分析函數用於計算基於組的某種聚合值，它和聚合函數的不同之處是：對於每個組返回多行，而聚合函數對於每個組只返回一行。

　　開窗函數指定了分析函數工作的數據視窗大小，這個數據視窗大小可能會隨著行的變化而變化。

　　下麵的測試用例數據語句如下：　

 1 create table T2_TEMP(
 2     NAME varchar2(10) primary key,
 3     CLASS varchar2(10),
 4     SROCE NUMBER 
 5 )
 6 
 7 insert into T2_TEMP (NAME, CLASS, SROCE)
 8 values ('cfe', '2', 74);
 9 
10 insert into T2_TEMP (NAME, CLASS, SROCE)
11 values ('dss', '1', 95);
12 
13 insert into T2_TEMP (NAME, CLASS, SROCE)
14 values ('ffd', '1', 95);
15 
16 insert into T2_TEMP (NAME, CLASS, SROCE)
17 values ('fda', '1', 80);
18 
19 insert into T2_TEMP (NAME, CLASS, SROCE)
20 values ('gds', '2', 92);
21 
22 insert into T2_TEMP (NAME, CLASS, SROCE)
23 values ('gf', '3', 99);
24 
25 insert into T2_TEMP (NAME, CLASS, SROCE)
26 values ('ddd', '3', 99);
27 
28 insert into T2_TEMP (NAME, CLASS, SROCE)
29 values ('adf', '3', 45);
30 
31 insert into T2_TEMP (NAME, CLASS, SROCE)
32 values ('asdf', '3', 55);
33 
34 insert into T2_TEMP (NAME, CLASS, SROCE)
35 values ('3dd', '3', 78);

　　1、over函數的寫法：

　　over（partition by class order by sroce） 按照sroce排序進行累計，order by是個預設的開窗函數，按照class分區。

　　2、開窗的視窗範圍：

　　over（order by sroce range between 5 preceding and 5 following）：視窗範圍為當前行數據幅度減5加5後的範圍內的。

　　over（order by sroce rows between 5 preceding and 5 following）：視窗範圍為當前行前後各移動5行。

　　3、與over()函數結合的函數的介紹

　　（1）、查詢每個班的第一名的成績：如下　

1 SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

　　結果為：

1 得到的結果是:
2 dss        1        95        1
3 ffd        1        95        1
4 gds        2        92        1
5 gf         3        99        1
6 ddd        3        99        1

　　註意：在求第一名成績的時候，不能用row_number()，因為如果同班有兩個併列第一，row_number()只返回一個結果。

1 SELECT * FROM (select t.name,t.class,t.sroce,row_number() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

　　結果為：

dss      1        95        1  
gfs      2        92        1
ddd      3        99        1 

　　可以看出，本來第一名是兩個人的併列，結果只顯示了一個。

　　（2）、rank()和dense_rank()可以將所有的都查找出來，rank可以將併列第一名的都查找出來；rank()和dense_rank()區別：rank()是跳躍排序，有兩個第二名時接下來就是第四名。

　　求班級成績排名：

1 select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

　　查詢結果：

dss        1        95        1
ffd        1        95        1
fda        1        80        3
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        3
asdf       3        55        4
adf        3        45        5

　　dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名

1 select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

　　查詢結果：

dss        1        95        1
ffd        1        95        1
fda        1        80        2 
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        2
asdf       3        55        3
adf        3        45        4

　　3、sum()over（）的使用

　　根據班級進行分數求和

1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

dss        1        95        190  --由於兩個95都是第一名，所以累加時是兩個第一名的相加
ffd        1        95        190 
fda        1        80        270  --第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376

　　4、first_value() over()和last_value() over()的使用 

1 select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
2 select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

　　分別求出第一個和最後一個成績。

　　5、sum() over()的使用

1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

　　求出班級的總分。

　　下麵還有很多用法，就不一一列舉了，簡單介紹一下，和上面用法類似：

　　count() over(partition by ... order by ...)：求分組後的總數。
　　max() over(partition by ... order by ...)：求分組後的最大值。
　　min() over(partition by ... order by ...)：求分組後的最小值。
　　avg() over(partition by ... order by ...)：求分組後的平均值。
　　lag() over(partition by ... order by ...)：取出前n行數據。　　

　　lead() over(partition by ... order by ...)：取出後n行數據。

　　ratio_to_report() over(partition by ... order by ...)：Ratio_to_report() 括弧中就是分子，over() 括弧中就是分母。

　　percent_rank() over(partition by ... order by ...)：

　　6、over partition by與group by的區別：

　　group by是對檢索結果的保留行進行單純分組，一般和聚合函數一起使用例如max、min、sum、avg、count等一塊用。partition by雖然也具有分組功能，但同時也具有其他的高級功能。