oracle系列__開窗函數____靜態函數

一個學習性任務：每個人有不同次數的成績，統計出每個人的最高成績。

這個問題應該還是相對簡單，其實就用彙總函式就好了。

select id,name,max(score) from Student group by id,name order by name

上邊這種情況只適用id 和name是一一對應的，否則查詢出來的資料是不正確的。

例如 ： 1 張三 100

           2 張三 90

          查詢出來的結果

          兩條資訊都會輸出。

避免這種情況，可以使用開窗函數。

個人理解就是，開窗函數和彙總函式功能是相反的。

彙總函式，將多行資料合併成一行資料；而開窗函數則是將一行資料拆分成多行。

開窗函數可以滿足上述問題，同事也可以滿足其他問題。例如：求每個班最高成績學生的資訊。

分析：每個人學號一定是不同的，名字可能有重名，最大複雜的情況是，每個班最高成績可能不止一個。

        如果繼續使用開始的方式，那麼是不能滿足要求的。

        使用開窗函數就能很好的解決這個問題。

--每個班級的成績第一的學生
--學生表中資訊如下
a 1 80 
b 1 78 
c 1 95 
d 2 74 
e 2 92 
f 3 99 
g 3 99 
h 3 45 
i 3 55 
j 3 78

查詢結果如下：
c 1 95 1 
e 2 92 1 
f 3 99 1 
g 3 99 1

SQL查詢語句如下： 
select * 
from 
( 
select name,class,s,rank()over(partition by class order by s desc) mm
from t2

) as t 
where t.mm=1

 

心得：
rank（）跳躍排序，有兩個第二名時後邊跟著的是第四名
dense_rank() 連續排序，有兩個第二名時仍然跟著第三名

over（）開窗函數： 在使用彙總函式後，會將多行變成一行，
而開窗函數是將一行變成多行；
並且在使用彙總函式後，如果要顯示其他的列必須將列加入到group by中，
而使用開窗函數後，可以不使用group by，直接將所有資訊顯示出來。

開窗函數適用於在每一行的最後一列添加彙總函式的結果。

常用開窗函數：
1.為每條資料顯示彙總資訊.(彙總函式() over())
2.為每條資料提供分組的彙總函式結果(彙總函式() over(partition by 欄位) as 別名) --按照欄位分組，分組後進行計算
3.與次序函數一起使用(row number() over(order by 欄位) as 別名)

常用分析函數：（最常用的應該是1.2.3 的排序）
1、row_number() over(partition by ... order by ...)
2、rank() over(partition by ... order by ...)
3、dense_rank() over(partition by ... order by ...)
4、count() over(partition by ... order by ...)
5、max() over(partition by ... order by ...)
6、min() over(partition by ... order by ...)
7、sum() over(partition by ... order by ...)
8、avg() over(partition by ... order by ...)
9、first_value() over(partition by ... order by ...)
10、last_value() over(partition by ... order by ...)
11、lag() over(partition by ... order by ...)
12、lead() over(partition by ... order by ...)
lag 和lead 可以 擷取結果集中，按一定排序所排列的當前行的上下相鄰若干offset 的某個行的某個列(不用結果集的自關聯）；
lag ，lead 分別是向前，向後；
lag 和lead 有三個參數，第一個參數是列名，第二個參數是位移的offset，第三個參數是 超出記錄視窗時的預設值）