ITPub博客

首页 > Linux操作系统 > Linux操作系统 > RANK函数基于条件的查询

RANK函数基于条件的查询

原创 Linux操作系统 作者:yangtingkun 时间:2009-10-24 23:59:28 0 删除 编辑

最近看了数据仓库中分析函数的部分,发现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。对比上面的执行结果不难发现,ID100的记录,排名就是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,如果插入一条ID99的记录,那么这条记录的排名就会取代ID100的记录,变成第31名。而如果插入ID101的记录,则返回排名是32名。

除了RANK函数,DENSE_RANKPERCENT_RANK也都支持同样的功能。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-617362/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10406920