1. rank函数的介绍

　　介绍完rollup和cube函数的使用,下面我们来看看rank系列函数的使用方法.

　　问题2.我想查出这几个月份中各个地区的总话费的排名.

　　Quote:

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

　　1

　　update t t1 set local_fare = (

　　2

　　select local_fare from t 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'

　　07:19:18 SQL> /

　　8 rows updated.

　　Elapsed: 00:00:00.01

　　我们先使用rank函数来计算各个地区的话费排名.

　　07:34:19 SQL> select area_code,sum(local_fare) local_fare,

　　07:35:25

　　2

　　rank() over (order by sum(local_fare) desc) fare_rank

　　07:35:44

　　3

　　from t

　　07:35:45

　　4

　　group by area_codee

　　07:35:50

　　5

　　07:35:52 SQL> select area_code,sum(local_fare) local_fare,

　　07:36:02

　　2

　　rank() over (order by sum(local_fare) desc) fare_rank

　　07:36:20

　　3

　　from t

　　07:36:21

　　4

　　group by area_code

　　07:36:25

　　5

　　/

　　AREA_CODE

　　LOCAL_FARE

　　FARE_RANK

　　---------- -------------- ----------

　　5765

　　104548.72

　　1

　　5761

　　54225.41

　　2

　　5763

　　54225.41

　　2

　　5764

　　53156.77

　　4

　　5762

　　52039.62

　　5

　　Elapsed: 00:00:00.01

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

　　07:36:26 SQL> select area_code,sum(local_fare) local_fare,

　　07:39:16

　　2

　　dense_rank() over (order by sum(local_fare) desc ) fare_rank

　　07:39:39

　　3

　　from t

　　07:39:42

　　4

　　group by area_code

　　07:39:46

　　5

　　/

　　AREA_CODE

　　LOCAL_FARE

　　FARE_RANK

　　---------- -------------- ----------

　　5765

　　104548.72

　　1

　　5761

　　54225.41

　　2

　　5763

　　54225.41

　　2

　　5764

　　53156.77

　　3

　　这是这里出现了第三名

　　5762

　　52039.62

　　4

　　Elapsed: 00:00:00.00

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

　　1

　　select area_code,sum(local_fare) local_fare,

　　2

　　row_number() over (order by sum(local_fare) desc ) fare_rank

　　3

　　from t

　　4* group by area_code

　　07:44:50 SQL> /

　　AREA_CODE

　　LOCAL_FARE

　　FARE_RANK

　　---------- -------------- ----------

　　5765

　　104548.72

　　1

　　5761

　　54225.41

　　2

　　5763

　　54225.41

　　3

　　5764

　　53156.77

　　4

　　5762

　　52039.62

　　5

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

　　这个帖子中的几个例子是为了说明这三个函数的基本用法的. 下个帖子我们将详细介绍他们的一些用法.

　　2. 三个函数的基本用法

　　a. 取出数据库中最后入网的n个用户

　　select user_id,tele_num,user_name,user_status,create_date

　　from (

　　select user_id,tele_num,user_name,user_status,create_date,

　　rank() over (order by create_date desc) add_rank

　　from user_info

　　)

　　where add_rank <= :n;

　　b.根据object_name删除数据库中的重复记录

　　create table t as select obj#,name from sys.obj$;

　　再insert into t1 select * from t1 数次.

　　delete from t1 where rowid in (

　　select row_id from (

　　select rowid row_id,row_number() over (partition by obj# order by rowid ) rn

　　) where rn <> 1

　　);

　　c. 取出各地区的话费收入在各个月份排名.

　　SQL> select bill_month,area_code,sum(local_fare) local_fare,

　　2

　　rank() over (partition by bill_month order by sum(local_fare) desc) area_rank

　　3

　　from t

　　4

　　group by bill_month,area_code

　　5

　　/

　　BILL_MONTH

　　AREA_CODE

　　LOCAL_FARE

　　AREA_RANK

　　--------------- --------------- -------------- ----------

　　200405

　　5765

　　25057.74

　　1

　　200405

　　5761

　　13060.43

　　2

　　200405

　　5763

　　13060.43

　　2

　　200405

　　5762

　　12643.79

　　4

　　200405

　　5764

　　12487.79

　　5

　　200406

　　5765

　　26058.46

　　1

　　200406

　　5761

　　13318.93

　　2

　　200406

　　5763

　　13318.93

　　2

　　200406

　　5764

　　13295.19

　　4

　　200406

　　5762

　　12795.06

　　5

　　200407

　　5765

　　26301.88

　　1

　　200407

　　5761

　　13710.27

　　2

　　200407

　　5763

　　13710.27

　　2

　　200407

　　5764

　　13444.09

　　4

　　200407

　　5762

　　13224.30

　　5

　　200408

　　5765

　　27130.64

　　1

　　200408

　　5761

　　14135.78

　　2

　　200408

　　5763

　　14135.78

　　2

　　200408

　　5764

　　13929.69

　　4

　　200408

　　5762

　　13376.47

　　5

　　20 rows selected.

　　SQL>

　　3. lag和lead函数介绍

　　取出每个月的上个月和下个月的话费总额

　　1

　　select area_code,bill_month, local_fare cur_local_fare,

　　2

　　lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,

　　3

　　lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,

　　4

　　lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,

　　5

　　lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare

　　6

　　from (

　　7

　　select area_code,bill_month,sum(local_fare) local_fare

　　8

　　from t

　　9

　　group by area_code,bill_month

　　10* )

　　SQL> /

　　AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE

　　--------- ---------- -------------- -------------- --------------- --------------- ---------------

　　5761

　　200405

　　13060.433

　　0

　　0

　　13318.93

　　13710.265

　　5761

　　200406

　　13318.93

　　0

　　13060.433

　　13710.265

　　14135.781

　　5761

　　200407

　　13710.265

　　13060.433

　　13318.93

　　14135.781

　　0

　　5761

　　200408

　　14135.781

　　13318.93

　　13710.265

　　0

　　0

　　5762

　　200405

　　12643.791

　　0

　　0

　　12795.06

　　13224.297

　　5762

　　200406

　　12795.06

　　0

　　12643.791

　　13224.297

　　13376.468

　　5762

　　200407

　　13224.297

　　12643.791

　　12795.06

　　13376.468

　　0

　　5762

　　200408

　　13376.468

　　12795.06

　　13224.297

　　0

　　0

　　5763

　　200405

　　13060.433

　　0

　　0

　　13318.93

　　13710.265

　　5763

　　200406

　　13318.93

　　0

　　13060.433

　　13710.265

　　14135.781

　　5763

　　200407

　　13710.265

　　13060.433

　　13318.93

　　14135.781

　　0

　　5763

　　200408

　　14135.781

　　13318.93

　　13710.265

　　0