關鍵字: 涵數rank, over partition使用
排列（rank()）函數。這些排列函數提供了定義一個集合（使用 PARTITION 子句），然後根據某種排序方式對這個集合內的元素進行排列的能力，下面以scott使用者的emp表為例來說明rank over partition如何使用
1）查詢員工薪水並連續求和
select deptno,ename,sal,
sum(sal)over(order by ename) sum1, /*表示連續求和*/
sum(sal)over() sum2, /*相當於求和sum(sal)*/
100* round(sal/sum(sal)over(),4) "bal%"
from emp
結果如下：
DEPTNO ENAME SAL SUM1 SUM2 bal%
---------- ---------- ---------- ---------- ---------- ----------
20 ADAMS 1100 1100 29025 3.79
30 ALLEN 1600 2700 29025 5.51
30 BLAKE 2850 5550 29025 9.82
10 CLARK 2450 8000 29025 8.44
20 FORD 3000 11000 29025 10.34
30 JAMES 950 11950 29025 3.27
20 JONES 2975 14925 29025 10.25
10 KING 5000 19925 29025 17.23
30 MARTIN 1250 21175 29025 4.31
10 MILLER 1300 22475 29025 4.48
20 SCOTT 3000 25475 29025 10.34
DEPTNO ENAME SAL SUM1 SUM2 bal%
---------- ---------- ---------- ---------- ---------- ----------
20 SMITH 800 26275 29025 2.76
30 TURNER 1500 27775 29025 5.17
30 WARD 1250 29025 29025 4.31
2）如下：
select deptno,ename,sal,
sum(sal)over(partition by deptno order by ename) sum1,/*表示按部門號分氏，按姓名排序並連續求和*/
sum(sal)over(partition by deptno) sum2,/*表示部門分區，求和*/
sum(sal)over(partition by deptno order by sal) sum3,/*按部門分區，按薪水排序並連續求和*/
100* round(sal/sum(sal)over(),4) "bal%"
from emp
結果如下：
DEPTNO ENAME SAL SUM1 SUM2 SUM3 bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 CLARK 2450 2450 8750 3750 8.44
10 KING 5000 7450 8750 8750 17.23
10 MILLER 1300 8750 8750 1300 4.48
20 ADAMS 1100 1100 10875 1900 3.79
20 FORD 3000 4100 10875 10875 10.34
20 JONES 2975 7075 10875 4875 10.25
20 SCOTT 3000 10075 10875 10875 10.34
20 SMITH 800 10875 10875 800 2.76
30 ALLEN 1600 1600 9400 6550 5.51
30 BLAKE 2850 4450 9400 9400 9.82
30 JAMES 950 5400 9400 950 3.27
DEPTNO ENAME SAL SUM1 SUM2 SUM3 bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
30 MARTIN 1250 6650 9400 3450 4.31
30 TURNER 1500 8150 9400 4950 5.17
30 WARD 1250 9400 9400 3450 4.31
3）如下：
select empno,deptno,sal,
sum(sal)over(partition by deptno) "deptSum",/*按部門分區，並求和*/
rank()over(partition by deptno order by sal desc nulls last) rank, /*按部門分區，按薪水排序並計算序號*/
dense_rank()over(partition by deptno order by sal desc nulls last) d_rank,
row_number()over(partition by deptno order by sal desc nulls last) row_rank
from emp
註：
rang()涵數主要用於排序，並給出序號
dense_rank()：功能同rank()一樣，區別在於，rank()對於排序並的資料給予相同序號，接下來的資料序號直接跳中躍，dense_rank()則不是，比如資料：1，2，2，4，5，6.。。。。這是rank()的形式
1，2，2，3，4，5，。。。。這是dense_rank()的形式
1，2，3，4，5，6.。。。。。這是row_number()涵數形式
row_number()涵數則是按照順序依次使用，相當於我們普通查詢裡的rownum值
其實從上面三個例子當中，不難看出over(partition by ... order by ...)的整體概念，我理解是
partition by :按照指字的欄位分區，如果沒有則針對全體資料
order by :按照指定欄位進行連續操作（如求和(sum),排序(rank()等)，如果沒有指定，就相當於對指定分區集合內的資料進行整體sum操作
oracle彙總函式rank()的用法
SQL> select * from test_a;
ID PLAYNAME SCORE
-------------------- -------------------- ----------
01 aa 100
02 aa 101
02 bb 99
03 bb 98
04 aa 101
02 aa 101
需求是，將score降序排序，列印所有欄位，並且如果是同一個playname的score只取出最高分，如果這個playname獲得過多個相同的最高分，則只取出其中一個（比如：aa獲得過3次101，則只取其中一個），最終要的結果就是：
RK ID PALYNAME SCORE
---------- -------------------- -------------------- ----------
1 02 aa 101
1 02 bb 99
本來我想用max函數，結果直接就出來了：
SQL> select max(score),palyname from test_a group by palyname;
MAX(SCORE) PALYNAME
---------- --------------------
101 aa
99 bb
但是要列印所有欄位…OTL
即使用了嵌套，還是無法解決重複重現最高分的現象：
SQL> select distinct * from test_a t where score in (select max(score) from test_a group by palyname) order by score desc;
ID PALYNAME SCORE
-------------------- -------------------- ----------
02 aa 101
04 aa 101
02 bb 99
由於相同的playname對應的id不同，所以用distinct也無法過濾掉相同playname的並列最高分。
於是只好用rank（）了
Rank的基本文法為：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
例子1：
TABLE：A （科目，分數）
數學，80
語文，70
數學，90
數學，60
數學，100
語文，88
語文，65
語文，77
現在我想要的結果是：（即想要每門科目的前3名的分數）
數學，100
數學，90
數學，80
語文，88
語文，77
語文，70
那麼語句就這麼寫：
select * from (select rank() over(partition by 科目 order by 分數 desc) rk,a.* from a) t
where t.rk<=3;
以科目來分組，然後以分數來排序，給排序的結果分配rank，取前三名的rank
例子2：
有表Table內容如下
COL1 COL2
1 1
2 1
3 2
3 1
4 1
4 2
5 2
5 2
6 2
分析功能：列出Col2分組後根據Col1排序,並產生數字列。比較實用於在成績表中查出各科前幾名的資訊。
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
結果如下：
COL1 COL2 Rank
1 1 1
2 1 2
3 1 3
4 1 4
3 2 1
4 2 2
5 2 3
5 2 3
6 2 5
這個例子更直觀一點，根據col2分組，根據clo1排序，我們可以發現：
5 2 3
5 2 3
6 2 5
即，如果兩行記錄完全相同，他們會被給予相同的rank，而排在它們之後的那行記錄，由於前面的並列第3，使得之後的那條記錄變成了第5，而如果我們在這裡用的是dense_rank，那麼之後的那條會變成第4
例子3：
合計功能：計算出數值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以後的位置
SELECT RANK(4,1) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
結果如下：
Rank
4
通過以上方法，得出col1為4，col2為1的那行資料的rank排名為多少
Dense_rank的例子：
dense_rank與rank()用法相當，但是有一個區別：dence_rank在並列關係是，相關等級不會跳過。rank則跳過
例如：表
A B C
a liu wang
a jin shu
a cai kai
b yang du
b lin ying
b yao cai
b yang 99
例如：當rank時為：
select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 4
而如果用dense_rank時為：
select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 3
那麼再回到之前的那個需求，
SQL> select distinct * from (select rank() over(partition by playname order by score desc,id) rk,t.* from test_a t) where rk=1;
RK ID PLAYNAME SCORE
---------- -------------------- -------------------- ----------
1 02 aa 101
1 02 bb 99
這裡order by score desc,id 以score降序和id這兩個欄位排序，也就是說，正因為相同的playname對應的id不同，這樣相同的playname，相同的score，但是不同的id，這樣的2行資料就獲得了不同的rank，而rk=1，即是只取rank=1，也就是最高分。這樣就完成了需求。
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
