OVER(PARTITION BY)函數介紹

簡介

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

開窗函數指定了分析函數工作的資料視窗大小，這個資料視窗大小可能會随着行的變化而變化。

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 得到的結果是:
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()隻傳回一個結果。

結果為：

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

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

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

求班級成績排名：

查詢結果：

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是連續排序，有兩個第二名時仍然跟着第三名

查詢結果：

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
           

4、sum()over（）的使用

根據班級進行分數求和

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
           

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

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

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

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雖然也具有分組功能，但同時也具有其他的進階功能。