[原創] 小議rank(),dense_rank(),row_number()使用與區別

SQL> desc t * from t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 TS                                                 VARCHAR2(10)
 
SQL> select * from t;

        ID TS
---------- ----------
         1 20061105
         1 20061101
         1 20061102
         1 20061103
         1 20061104
         1 20061105
         1 20061106
         1 20061107
         2 20061101
         2 20061103
         2 20061106
         2 20061107
         2 20061109
         2 20061110
         3 20061101
         3 20061103
         3 20061106
         3 20061107
         3 20061108
         3 20061109
         3 20061110
         3 20061111

22 rows selected.

SQL>

要取出大於五次,並且第五次的時間的 ID,TS

結果應該為
        ID TS
---------- ----------
         1 20061105
         2 20061109
         3 20061108
        
SQL> select a.id,a.ts from
  2  (select id,ts,row_number() over (partition by id order by ts ) as rn from t ) a
  3* where a.rn = 5

        ID TS
---------- ----------
         1 20061105
         2 20061109
         3 20061108

SQL> select a.id,a.ts from
  2  (select id,ts,rank() over (partition by id order by ts ) as rn from t ) a
  3  where a.rn = 5;

        ID TS
---------- ----------
         1 20061105
         1 20061105
         2 20061109
         3 20061108
        
SQL> select id,ts,rank() over (partition by id order by ts ) as rn from t ;

        ID TS                 RN
---------- ---------- ----------
         1 20061101            1
         1 20061102            2
         1 20061103            3
         1 20061104            4
         1 20061105            5
         1 20061105            5
         1 20061106            7
         1 20061107            8
         2 20061101            1
         2 20061103            2
         2 20061106            3
         2 20061107            4
         2 20061109            5
         2 20061110            6
         3 20061101            1
         3 20061103            2
         3 20061106            3
         3 20061107            4
         3 20061108            5
         3 20061109            6
         3 20061110            7
         3 20061111            8

 

SQL> select a.id,a.ts from
  2  (select id,ts,dense_rank() over (partition by id order by ts ) as rn from t ) a
  3  where a.rn = 5;

        ID TS
---------- ----------
         1 20061105
         1 20061105
         2 20061109
         3 20061108

SQL> select id,ts,dense_rank() over (partition by id order by ts ) as rn from t;

        ID TS                 RN
---------- ---------- ----------
         1 20061101            1
         1 20061102            2
         1 20061103            3
         1 20061104            4
         1 20061105            5
         1 20061105            5
         1 20061106            6
         1 20061107            7
         2 20061101            1
         2 20061103            2
         2 20061106            3
         2 20061107            4
         2 20061109            5
         2 20061110            6
         3 20061101            1
         3 20061103            2
         3 20061106            3
         3 20061107            4
         3 20061108            5
         3 20061109            6
         3 20061110            7
         3 20061111            8
        
SQL> select * from (
  2  select * from (
  3  select id,ts from (
  4  select id,ts,dense_rank() over(partition by id order by ts) as tsrank from t
  5  )
  6  where tsrank=5
  7  order by ts
  8  )
  9  union
 10  select * from (
 11  select id,ts from (
 12  select id,ts,dense_rank() over(partition by id order by ts) as tsrank from t
 13  )
 14  where tsrank=5
 15  order by ts
 16  )
 17  );

        ID TS
---------- ----------
         1 20061105
         2 20061109
         3 20061108

要取出大於五次,並且第五次的時間的 ID,TS ,同時選出前2個ID

SQL>select * from (
  2  select id,ts from (
  3  select id,ts,row_number() over(partition by id order by ts) as tsrank from t
  4  )
  5  where tsrank=5
  6  order by ts
  7* ) where rownum<=2

        ID TS
---------- ----------
         1 20061105
         3 20061108

SQL> select * from (
  2  select id,ts from (
  3  select id,ts from (
  4  select id,ts,dense_rank() over(partition by id order by ts) as tsrank from t
  5  )
  6  where tsrank=5
  7  )
  8  union
  9  select * from (
 10  select id,ts from (
 11  select id,ts,dense_rank() over(partition by id order by ts) as tsrank from t
 12  )
 13  where tsrank=5
 14  )
 15  order by ts
 16  )
 17  where rownum<=2;

        ID TS
---------- ----------
         1 20061105
         3 20061108