oracle 分析函数 rank,dense_rank,row_number(转)

分析函数，可以说是oracle的一大利器，尤其是在做统计分析的时候，可以极大的提高语句的性能。于是我想有必要对各类分析函数，根据网上G到的资料和自己以前用过的经历，进行总结。

先创建一个测试表：

create table TEMP
(
BILL_MONTH VARCHAR2(8),
AREA_CODE VARCHAR2(5),
NET_TYPE VARCHAR2(2),
LOCAL_FARE NUMBER

)
tablespace TABS_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);


BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE
200405 5761 G 1
200405 5761 J 2
200405 5762 G 3
200405 5762 J 4
200405 5763 G 5
200405 5763 J 6
200405 5764 G 7
200405 5764 J 8
200405 5765 G 9
200405 5765 J 10
200406 5761 G 1
200406 5761 J 2
200406 5762 G 3
200406 5762 J 4
200406 5763 G 5
200406 5763 J 6
200406 5764 G 7
200406 5764 J 8
200406 5765 G 9
200406 5765 J 10
200407 5761 G 1
200407 5761 J 2
200407 5762 G 3
200407 5762 J 4
200407 5763 G 5
200407 5763 J 6
200407 5764 G 7
200407 5764 J 8
200407 5765 G 9
200407 5765 J 10
200408 5761 G 1
200408 5761 J 2
200408 5762 G 3
200408 5762 J 4
200408 5763 G 5
200408 5763 J 6
200408 5764 G 7
200408 5764 J 8
200408 5765 G 9
200408 5765 J 10



为了将rank,dense_rank,row_number函数的差别显示出来,我们对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.

SQL> update temp t1 set local_fare = (
2 select local_fare from temp t2
3 where t1.bill_month = t2.bill_month
4 and t1.net_type = t2.net_type
5 and t2.area_code = '5761'
6 ) where area_code = '5763'
7 /

已更新8行。

使用rank函数来计算各个地区的费用排名

SQL> select area_code,sum(local_fare)local_fare,
2 rank() over (order by sum(local_fare) desc)fare_rank
3 from temp
4 group by area_code
5 /

AREA_ LOCAL_FARE FARE_RANK
----- ---------- ----------
5765 40 1
5763 24 2
5764 24 2
5762 16 4
5761 8 5

我们可以看到红色标注的地方出现了,跳位,排名3没有出现
下面我们再看看dense_rank查询的结果

SQL> select area_code,sum(local_fare)local_fare,
2 dense_rank() over (order by sum(local_fare)desc)fare_rank
3 from temp
4 group by area_code
5 /

AREA_ LOCAL_FARE FARE_RANK
----- ---------- ----------
5765 40 1
5763 24 2
5764 24 2
5762 16 3
5761 8 4

在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,
rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,
差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处

SQL> select area_code,sum(local_fare) local_fare,
2 row_number() over (order by sum(local_fare)desc)fare_rank
3 from temp
4 group by area_code
5 /

AREA_ LOCAL_FARE FARE_RANK
----- ---------- ----------
5765 40 1
5763 24 2
5764 24 3
5762 16 4
5761 8 5

在row_nubmer函数中,我们发现,哪怕sum(local_fare)完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录.