2. 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_rank07:35:44 3 from t07:35:45 4 group by area_codee07:35:50 507:35:52 SQL> select area_code,sum(local_fare) local_fare,07:36:02 2 rank() over (order by sum(local_fare) desc) fare_rank07:36:20 3 from t07:36:21 4 group by area_code07:36:25 5 / AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 2
5764 53156.77 45762 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_rank07:39:39 3 from t07:39:42 4 group by area_code07:39:46 5 / AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 2
5763 54225.41 25764 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_code07:44:50 SQL> / AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 35764 53156.77 45762 52039.62 5 在row_nubmer函数中,我们发现,哪怕sum(local_fare)完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录. 这个帖子中的几个例子是为了说明这三个函数的基本用法的. 下个帖子我们将具体介绍他们的一些用法. 2. rank函数的介绍 a. 取出数据库中最后入网的n个用户select user_id,tele_num,user_name,user_status,create_datefrom ( 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 1200405 5761 13060.43 2200405 5763 13060.43 2200405 5762 12643.79 4200405 5764 12487.79 5200406 5765 26058.46 1200406 5761 13318.93 2200406 5763 13318.93 2200406 5764 13295.19 4
200406 5762 12795.06 5200407 5765 26301.88 1200407 5761 13710.27 2200407 5763 13710.27 2200407 5764 13444.09 4200407 5762 13224.30 5200408 5765 27130.64 1200408 5761 14135.78 2200408 5763 14135.78 2
200408 5764 13929.69 4200408 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_month10* )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.2655761 200406 13318.93 0 13060.433 13710.265 14135.7815761 200407 13710.265 13060.433 13318.93 14135.781 05761 200408 14135.781 13318.93 13710.265 0 05762 200405 12643.791 0 0 12795.06 13224.297
5762 200406 12795.06 0 12643.791 13224.297 13376.4685762 200407 13224.297 12643.791 12795.06 13376.468 05762 200408 13376.468 12795.06 13224.297 0 05763 200405 13060.433 0 0 13318.93 13710.2655763 200406 13318.93 0 13060.433 13710.265 14135.7815763 200407 13710.265 13060.433 13318.93 14135.781 05763 200408 14135.781 13318.93 13710.265 0 0
5764 200405 12487.791 0 0 13295.187 13444.0935764 200406 13295.187 0 12487.791 13444.093 13929.6945764 200407 13444.093 12487.791 13295.187 13929.694 05764 200408 13929.694 13295.187 13444.093 0 05765 200405 25057.736 0 0 26058.46 26301.8815765 200406 26058.46 0 25057.736 26301.881 27130.6385765 200407 26301.881 25057.736 26058.46 27130.638 0
5765 200408 27130.638 26058.46 26301.881 0 020 rows selected. 利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据. 4. sum,avg,max,min移动计算数据介绍 计算出各个连续3个月的通话费用的平均数 1 select area_code,bill_month, local_fare, 2 sum(local_fare) 3 over ( partition by area_code 4 order by to_number(bill_month) 5 range between 1 preceding and 1 following ) "3month_sum", 6 avg(local_fare) 7 over ( partition by area_code 8 order by to_number(bill_month)
9 range between 1 preceding and 1 following ) "3month_avg",10 max(local_fare)11 over ( partition by area_code12 order by to_number(bill_month)13 range between 1 preceding and 1 following ) "3month_max",14 min(local_fare)15 over ( partition by area_code16 order by to_number(bill_month)17 range between 1 preceding and 1 following ) "3month_min"18 from (19 select area_code,bill_month,sum(local_fare) local_fare20 from t21 group by area_code,bill_month22* )SQL> /
AREA_CODE BILL_MONTH LOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min--------- ---------- ---------------- ---------- ---------- ---------- ----------5761 200405 13060.433 26379.363 13189.6815 13318.93 13060.4335761 200406 13318.930 40089.628 13363.2093 13710.265 13060.4335761 200407 13710.265 41164.976 13721.6587 14135.781 13318.9340089.628 = 13060.433 + 13318.930 + 13710.26513363.2093 = (13060.433 + 13318.930 + 13710.265) / 313710.265 = max(13060.433 + 13318.930 + 13710.265)13060.433 = min(13060.433 + 13318.930 + 13710.265)5761 200408 14135.781 27846.046 13923.023 14135.781 13710.2655762 200405 12643.791 25438.851 12719.4255 12795.06 12643.7915762 200406 12795.060 38663.148 12887.716 13224.297 12643.7915762 200407 13224.297 39395.825 13131.9417 13376.468 12795.06
5762 200408 13376.468 26600.765 13300.3825 13376.468 13224.2975763 200405 13060.433 26379.363 13189.6815 13318.93 13060.4335763 200406 13318.930 40089.628 13363.2093 13710.265 13060.4335763 200407 13710.265 41164.976 13721.6587 14135.781 13318.935763 200408 14135.781 27846.046 13923.023 14135.781 13710.2655764 200405 12487.791 25782.978 12891.489 13295.187 12487.7915764 200406 13295.187 39227.071 13075.6903 13444.093 12487.7915764 200407 13444.093 40668.974 13556.3247 13929.694 13295.1875764 200408 13929.694 27373.787 13686.8935 13929.694 13444.0935765 200405 25057.736 51116.196 25558.098 26058.46 25057.7365765 200406 26058.460 77418.077 25806.0257 26301.881 25057.736
5765 200407 26301.881 79490.979 26496.993 27130.638 26058.465765 200408 27130.638 53432.519 26716.2595 27130.638 26301.881 20 rows selected. [ Last edited by jametong on 2004-9-19 at 19:40 ] 5. ratio_to_report函数的介绍 Quote: 1 select bill_month,area_code,sum(local_fare) local_fare, 2 ratio_to_report(sum(local_fare)) over 3 ( partition by bill_month ) area_pct 4 from t 5* group by bill_month,area_codeSQL> break on bill_month skip 1SQL> compute sum of local_fare on bill_monthSQL> compute sum of area_pct on bill_monthSQL> /
BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT---------- --------- ---------------- ----------200405 5761 13060.433 .171149279 5762 12643.791 .165689431 5763 13060.433 .171149279 5764 12487.791 .163645143 5765 25057.736 .328366866********** ---------------- ----------sum 76310.184 1 200406 5761 13318.930 .169050772 5762 12795.060 .162401542 5763 13318.930 .169050772
5764 13295.187 .168749414 5765 26058.460 .330747499********** ---------------- ----------sum 78786.567 1 200407 5761 13710.265 .170545197 5762 13224.297 .164500127 5763 13710.265 .170545197 5764 13444.093 .167234221 5765 26301.881 .327175257********** ---------------- ----------sum 80390.801 1
200408 5761 14135.781 .170911147 5762 13376.468 .161730539 5763 14135.781 .170911147 5764 13929.694 .168419416 5765 27130.638 .328027751********** ---------------- ----------sum 82708.362 1 20 rows selected. 6 first,last函数使用介绍 Quote: 取出每月通话费最高和最低的两个用户.
1 select bill_month,area_code,sum(local_fare) local_fare, 2 first_value(area_code) 3 over (order by sum(local_fare) desc 4 rows unbounded preceding) firstval, 5 first_value(area_code) 6 over (order by sum(local_fare) asc 7 rows unbounded preceding) lastval 8 from t 9 group by bill_month,area_code10* order by bill_monthSQL> / BILL_MONTH AREA_CODE LOCAL_FARE FIRSTVAL LASTVAL---------- --------- ---------------- --------------- ---------------200405 5764 12487.791 5765 5764200405 5762 12643.791 5765 5764
200405 5761 13060.433 5765 5764200405 5765 25057.736 5765 5764200405 5763 13060.433 5765 5764200406 5762 12795.060 5765 5764200406 5763 13318.930 5765 5764200406 5764 13295.187 5765 5764200406 5765 26058.460 5765 5764200406 5761 13318.930 5765 5764200407 5762 13224.297 5765 5764200407 5765 26301.881 5765 5764
200407 5761 13710.265 5765 5764200407 5763 13710.265 5765 5764200407 5764 13444.093 5765 5764200408 5762 13376.468 5765 5764200408 5764 13929.694 5765 5764200408 5761 14135.781 5765 5764200408 5765 27130.638 5765 5764200408 5763 14135.781 5765 5764 20 rows selected.
