linux 多个条件查找函数,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也都支持同样的功能。