分析函式——排序排列（rank、dense_rank、row_number）

Oracle從8i開始就提供了3個分析函式：rank，dense_rank，row_number


(1)Rank函式返回一個唯一的值，除非遇到相同的資料時，此時所有相同資料的排名是一樣的，
   同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名。
   
(2)Dense_rank函式返回一個唯一的值，除非當碰到相同資料時，此時所有相同資料的排名都是一樣的。


(3)Row_number函式返回一個唯一的值，當碰到相同資料時，排名按照記錄集中記錄的順序依次遞增。


例:
create table s_score
( s_id number(6)
 ,score number(4,2)
);
insert into s_score values(001,98);
insert into s_score values(002,66.5);
insert into s_score values(003,99);
insert into s_score values(004,98);
insert into s_score values(005,98);
insert into s_score values(006,80);

select
    s_id 
   ,score
   ,rank() over(order by score desc) rank
   ,dense_rank() over(order by score desc) dense_rank
   ,row_number() over(order by score desc) row_number
from s_score;

   S_ID  SCORE       RANK DENSE_RANK ROW_NUMBER
------- ------ ---------- ---------- ----------
      3  99.00          1          1          1
      1  98.00          2          2          2
      4  98.00          2          2          3
      5  98.00          2          2          4
      6  80.00          5          3          5
      2  66.50          6          4          6
        
排名/排序的時候，有時候，我們會想到利用偽列rownum，利用rownum確實可以解決某些場景下的問題(但是相對也比較複雜)，而且有些
場景下的問題卻很難解決。

例:取成績前三名，並且前三名含有並列的情況    
透過上面例子，我們可以直觀的看到，結果應該有5條記錄。
select
    s_id 
   ,score
   ,dense_rank
from (
select
    s_id 
   ,score
   ,rank() over(order by score desc) rank
   ,dense_rank() over(order by score desc) dense_rank
   ,row_number() over(order by score desc) row_number
from s_score
) t
where dense_rank <= 3;

   S_ID  SCORE DENSE_RANK
------- ------ ----------
      3  99.00          1
      1  98.00          2
      5  98.00          2
      4  98.00          2
      6  80.00          3
      
如果只是簡單的想到去用rownum <= 3 得到的結果顯然不可能是正確的。

組內的排名或者排序是經常遇到的一種場景。
例如，取每個銷售部門內，銷售業績最好的前三名。
      取每個班級內成績排名資訊等等..
      
取每個班級內每門課成績排名第一的同學資訊     
drop table S_SCORE;
create table S_SCORE
(
  S_ID  NUMBER(6),
  CLASS_ID VARCHAR2(2),
  COURSE VARCHAR2(20),
  SCORE NUMBER(5,2)
);

INSERT INTO S_SCORE VALUES(1001,'A','MATH','67');
INSERT INTO S_SCORE VALUES(1004,'B','MATH','88');
INSERT INTO S_SCORE VALUES(1002,'A','MATH','99');
INSERT INTO S_SCORE VALUES(1003,'A','MATH','55');
INSERT INTO S_SCORE VALUES(1001,'B','MATH','88');
INSERT INTO S_SCORE VALUES(1001,'B','MATH','70');
INSERT INTO S_SCORE VALUES(1001,'A','ORACLE','97');
INSERT INTO S_SCORE VALUES(1004,'B','ORACLE','48');
INSERT INTO S_SCORE VALUES(1002,'A','ORACLE','79');
INSERT INTO S_SCORE VALUES(1003,'A','ORACLE','65');
INSERT INTO S_SCORE VALUES(1001,'B','ORACLE','82');
INSERT INTO S_SCORE VALUES(1001,'B','ORACLE','78');

select
   s_id
  ,class_id
  ,course
  ,score
  ,dense_rank() over (partition by class_id,course order by score desc) drk
from S_SCORE
;

   S_ID CLASS_ID COURSE                 SCORE        DRK
------- -------- -------------------- ------- ----------
   1002 A        MATH                   99.00          1
   1001 A        MATH                   67.00          2
   1003 A        MATH                   55.00          3
   1001 A        ORACLE                 97.00          1
   1002 A        ORACLE                 79.00          2
   1003 A        ORACLE                 65.00          3
   1004 B        MATH                   88.00          1
   1001 B        MATH                   88.00          1
   1001 B        MATH                   70.00          2
   1001 B        ORACLE                 82.00          1
   1001 B        ORACLE                 78.00          2
   1004 B        ORACLE                 48.00          3
   
select
   s_id
  ,class_id
  ,course
  ,score
from (
select
   s_id
  ,class_id
  ,course
  ,score
  ,dense_rank() over (partition by class_id,course order by score desc) drk
from S_SCORE
) t
where drk = 1
;


   S_ID CLASS_ID COURSE                 SCORE
------- -------- -------------------- -------
   1002 A        MATH                   99.00
   1001 A        ORACLE                 97.00
   1004 B        MATH                   88.00
   1001 B        MATH                   88.00
   1001 B        ORACLE                 82.00   
   
rank()和dense_rank()用法相似，這裡就不在舉例說明了。可以將上面的例子中dense_rank()替換成rank()實現。


接下來,看一個使用row_number()的場景
例:檢視每個部門最近一筆銷售記錄
select * from criss_sales order by dept_id,sale_date desc;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D01     2014/5/4    G02                 80
D01     2014/4/30   G03                800
D01     2014/4/8    G01                200
D01     2014/3/4    G00                700
D02     2014/5/2    G03                900
D02     2014/4/27   G01                300
D02     2014/4/8    G02                100
D02     2014/3/6    G00                500

即,我們希望得到
D01     2014/5/4    G02                 80
D02     2014/5/2    G03                900
這兩條記錄

select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
 ,row_number() over (partition by dept_id order by sale_date desc)
from criss_sales;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT ROW_NUMBER()OVER(PARTITIONBYDE
------- ----------- ---------- ----------- ------------------------------
D01     2014/5/4    G02                 80                              1
D01     2014/4/30   G03                800                              2
D01     2014/4/8    G01                200                              3
D01     2014/3/4    G00                700                              4
D02     2014/5/2    G03                900                              1
D02     2014/4/27   G01                300                              2
D02     2014/4/8    G02                100                              3
D02     2014/3/6    G00                500                              4

select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
from (
select
  dept_id
 ,sale_date
 ,goods_type
 ,sale_cnt
 ,row_number() over (partition by dept_id order by sale_date desc) rn
from criss_sales
) t
where rn = 1
;

DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D01     2014/5/4    G02                 80
D02     2014/5/2    G03                900