分析函式rank（） row_number

很早之前就聽網友談過oracle的分析函式的好處，這兩天有些需求不然不覺中用到了分析函式。

分析函式和聚合函式根本不同之處在於聚合函式sum average max min count等只能返回一行，而分析函式可以按組返回多行。

開窗函式的理解：也就是over(partition by class order by s),開窗函式指定了分析函式工作的視窗大小。（具體開窗函式中一些幅度值限制可以參考tom的export one-on-one）

create table student(name varchar2(10),class number,s number)

然後插入一些資料，例如此時student表中的資料：

select * from student

name class s

as 1 94

sd 2 78

fg 1 95

rtgh 3 88

df 2 90

vb 3 89

tyu 3 95

此時如果需要按班級分組分別按成績排名，如果不用分析函式個人真還不知道有什麼辦法。

Select name,class,s,rank()over(partition by class order by s desc) mingci from student

Mingci

fg 1 95 1

as 1 94 2

df 2 90 1

sd 2 78 2

tyu 3 95 1

vb 3 89 2

rtgh 3 88 3

當時想了半天的需求一個分析函式就ok了。而且效能上比一般的巢狀sql效能高。

其實剛接觸時對於後面的rank()over(partition by class order by s desc) mingci不是很理解，發其實也就是對查詢的結果按照分析要求賦值mingci，從1開始遞增排名。

Dense_ranke()函式和rank函式基本一樣，一點不同是：Rank是跳躍式的，dense_rank沒有跳躍。

如果只想查處每班成績第二名的學生資訊

Select * from (select name,class,s,rank()over(partition by class order by s desc) mingci from student) where mingci=2

分析函式中可以加partition by column_name具體某列來分組，去掉partition by column_name後會不分組全部排名。

Row_number（）函式返回資料行的資訊，並沒有排名。

其實也不好表達，直接來sql吧直觀。

Select name,class,s from (select name,class,s,row_number()over(partition by class order by s) id from student) where id<=2

fg 1 95

as 1 94

df 2 90

sd 2 78

tyu 3 95

vb 3 89

rank排名顯示，row_number返回資料行資訊，如果上面的name,class,s改為* ，也會顯示巢狀表中的ID。（感覺和rank差不多啊。）

簡單的分享了分析函式用法，當然只是很小的一部分。晚了 睡覺了！

[@more@]