oracle分析函数（二）

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 t23;;;;;where t1.bill_month = t2.bill_month4;;;;;and t1.net_type = t2.net_type5;;;;;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;;4 5762;;;;;52039.62;;5Elapsed: 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;;1 5761;;;;;54225.41;;25763;;;;;54225.41;;25764;;;;;53156.77;;3;;这是这里出现了第三名5762;;;;;52039.62;;4Elapsed: 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_rank3;;from t4* 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_date from (select user_id,tele_num,user_name,user_status,create_date,rank() over (order by create_date desc) add_rankfrom 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 t4;;group by bill_month,area_code5;;/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;;4200406;;5762;;12795.06;;5200407;;5765;;26301.88;;1 200407;;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;;2200408;;5764;;13929.69;;4200408;;5762;;13376.47;;520 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_fare6;;from (7;;;;;select area_code,bill_month,sum(local_fare) local_fare ;;8;;;;;from t9;;;;;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;;;;;;;013318.93;;;;;;;13710.2655761;;;;;;200406;;;13318.93;;;;;;0;;;;;;;13060.433;;;;;;;13710.265;;;;;;;14135.7815761;;;;;;200407;;13710.265;;;;;;13060.43313318.93;;;;;;;14135.781;;;;;;;05761;;;;;;200408;;14135.781;;;;;;;13318.93;;;;;;;13710.265;;;;;;;0;;;;;;;05762;;;;;;200405;;12643.791;;;;;;0;;;;;;;012795.06;;;;;;;13224.2975762;;;;;;200406;;;12795.06;;;;;;0;;;;;;;12643.791;;;;;;;13224.297;;;;;;;13376.4685762;;;;;;200407;;13224.297;;;;;;12643.79112795.06;;;;;;;13376.468;;;;;;;0 5762;;;;;;200408;;13376.468;;;;;;;12795.06;;;;;;;13224.297;;;;;;;0;;;;;;;05763;;;;;;200405;;13060.433;;;;;;0;;;;;;;013318.93;;;;;;;13710.2655763;;;;;;200406;;;13318.93;;;;;;0;;;;;;;13060.433;;;;;;;13710.265;;;;;;;14135.7815763;;;;;;200407;;13710.265;;;;;;13060.43313318.93;;;;;;;14135.781;;;;;;;05763;;;;;;200408;;14135.781;;;;;;;13318.93;;;;;;;13710.265;;;;;;;0;;;;;;;05764;;;;;;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;;;;;;;0 5764;;;;;;200408;;13929.694;;;;;;13295.187;;;;;;;13444.093;;;;;;;0;;;;;;;05765;;;;;;200405;;25057.736;;;;;;0;;;;;;;026058.46;;;;;;;26301.8815765;;;;;;200406;;;26058.46;;;;;;0;;;;;;;25057.736;;;;;;;26301.881;;;;;;;27130.6385765;;;;;;200407;;26301.881;;;;;;25057.73626058.46;;;;;;;27130.638;;;;;;;05765;;;;;;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_code4;;;;;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_code8;;;;;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.791 5762;;;;;;200407;;;;13224.297;;39395.825 13131.9417;;13376.468;;;12795.065762;;;;;;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.7365765;;;;;;200407;;;;26301.881;;79490.979;;26496.993;;27130.638;;;26058.465765;;;;;;200408;;;;27130.638;;53432.519 26716.2595;;27130.638;;26301.88120 rows selected.5. ratio_to_report函数的介绍 ;;Quote: 1;;select bill_month,area_code,sum(local_fare) local_fare,2;;;;;ratio_to_report(sum(local_fare)) over3;;;;;;;( partition by bill_month ) area_pct4;;from t5* 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 .1711492795762;;;;;12643.791 .1656894315763;;;;;13060.433 .1711492795764;;;;;12487.791 .1636451435765;;;;;25057.736 .328366866**********;;;---------------- ----------sum;76310.184;;1200406;;;;;5761;;;;;13318.930 .1690507725762;;;;;12795.060 .1624015425763;;;;;13318.930 .1690507725764;;;;;13295.187 .1687494145765;;;;;26058.460 .330747499**********;;;---------------- ----------sum;78786.567;;1 200407;;;;;5761;;;;;13710.265 .1705451975762;;;;;13224.297 .1645001275763;;;;;13710.265 .1705451975764;;;;;13444.093 .1672342215765;;;;;26301.881 .327175257**********;;;---------------- ----------sum;80390.801;;1200408;;;;;5761;;;;;14135.781 .1709111475762;;;;;13376.468 .1617305395763;;;;;14135.781 .1709111475764;;;;;13929.694 .1684194165765;;;;;27130.638 .328027751**********;;;---------------- ----------sum;82708.362;;120 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) desc4;;;;;rows unbounded preceding) firstval,5;;;;;first_value(area_code)6;;;;;over (order by sum(local_fare) asc ;;7;;;;;rows unbounded preceding) lastval8;;from t9;;group by bill_month,area_code10* order by bill_monthSQL> /BILL_MONTH AREA_CODE;;;;;;;LOCAL_FARE FIRSTVALLASTVAL---------- --------- ---------------- --------------- ---------------200405;;;;;5764;;;;;12487.791 5765;;;;5764200405;;;;;5762;;;;;12643.791 5765;;;;5764200405;;;;;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;;;;576420 rows selected.