RANK函式基於條件的查詢

最近看了資料倉儲中分析函式的部分，發現RANK等函式還有基於假設條件進行查詢的功能。

 

 

對於RANK分析函式的功能，大多數人都不陌生，這個函式可以返回排名，看一個簡單的例子：

SQL> CREATE TABLE T AS
  2  SELECT ROWNUM ID, OBJECT_NAME, OBJECT_TYPE
  3  FROM USER_OBJECTS;

Table created.

SQL> SELECT ID, OBJECT_NAME, RANK() OVER(ORDER BY ID) RN
  2  FROM T
  3  WHERE OBJECT_TYPE = 'TABLE';

        ID OBJECT_NAME                            RN
---------- ------------------------------ ----------
         1 REG                                     1
         2 TT                                      2
         4 T2                                      3
         7 CP_TEST_T                               4
         9 T3                                      5
        11 CELL                                    6
        12 ORD_ORDER_MV                            7
        14 COMPANY                                 8
        24 MESMS                                   9
        30 JSS_T2                                 10
        36 N1                                     11
        38 N2                                     12
        44 DMN_DATE                               13
        49 T_STAT                                 14
        56 T_PART                                 15
        57 REC                                    16
        59 HFFX                                   17
        61 SYS_TEMP_FBT                           18
        62 FARM                                   19
        66 AQ$_MY_QUEUE_TBL1_S                    20
        68 AQ$_MY_QUEUE_TBL1_T                    21
        71 AQ$_MY_QUEUE_TBL1_H                    22
        73 SYS_IOT_OVER_120360                    23
        74 AQ$_MY_QUEUE_TBL1_G                    24
        76 AQ$_MY_QUEUE_TBL1_I                    25
        86 MY_QUEUE_TBL1                          26
        87 TEST                                   27
        88 DEPT                                   28
        93 AQ$_EVENT_QUEUE_TAB_S                  29
        95 AQ$_EVENT_QUEUE_TAB_T                  30
       100 AQ$_EVENT_QUEUE_TAB_H                  31
       102 SYS_IOT_OVER_120446                    32
       103 AQ$_EVENT_QUEUE_TAB_G                  33
       105 AQ$_EVENT_QUEUE_TAB_I                  34
       117 EVENT_QUEUE_TAB                        35
       136 T                                      36
       137 TMP1                                   37
       144 JSSTBL                                 38
       145 JSS_1                                  39
       147 TT1                                    40
       148 TBL                                    41
       150 A                                      42
       151 B                                      43
       153 DR$IND_A_OWNER$I                       44
       155 DR$IND_A_OWNER$K                       45
       157 DR$IND_A_OWNER$R                       46
       159 DR$IND_A_OWNER$N                       47
       163 DR$IND_B_OWNER$I                       48
       165 DR$IND_B_OWNER$K                       49
       167 DR$IND_B_OWNER$R                       50
       169 DR$IND_B_OWNER$N                       51
       173 DR$IND_ORD_ORDER_RE$I                  52
       175 DR$IND_ORD_ORDER_RE$K                  53
       177 DR$IND_ORD_ORDER_RE$R                  54
       179 DR$IND_ORD_ORDER_RE$N                  55
       184 MV_T                                   56
       185 MV_T1_AGG                              57
       188 MV_T2_AGG                              58
       191 MLOG$_MV_T1_AGG                        59
       192 MLOG$_MV_T2_AGG                        60
       193 MV_T_UNIONALL                          61

61 rows selected.

除了常用的獲得排名功能之外，RANK還可以回答給定一個常量在當前結果的排名是多少：

SQL> SELECT RANK(100) WITHIN GROUP (ORDER BY ID)
  2  FROM T
  3  WHERE OBJECT_TYPE = 'TABLE';

RANK(100)WITHINGROUP(ORDERBYID)
-------------------------------
                             31

這個查詢返回的結果說明，如果插入一條記錄，ID的值是100，那麼查詢返回結果時，這條記錄的排名是31。對比上面的執行結果不難發現，ID為100的記錄，排名就是31。

下面將RANK的輸入常量改為表中不存在的值：

SQL> SELECT RANK(99) WITHIN GROUP (ORDER BY ID)
  2  FROM T
  3  WHERE OBJECT_TYPE = 'TABLE';

RANK(99)WITHINGROUP(ORDERBYID)
------------------------------
                            31

SQL> SELECT RANK(101) WITHIN GROUP (ORDER BY ID)
  2  FROM T
  3  WHERE OBJECT_TYPE = 'TABLE';

RANK(101)WITHINGROUP(ORDERBYID)
-------------------------------
                             32

由於99小於100，如果插入一條ID為99的記錄，那麼這條記錄的排名就會取代ID為100的記錄，變成第31名。而如果插入ID為101的記錄，則返回排名是32名。

除了RANK函式，DENSE_RANK和PERCENT_RANK也都支援同樣的功能。