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.
