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_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. 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_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;;;;;;;013318.93;;;;;;;13710.265
5761;;;;;;200406;;;13318.93;;;;;;0;;;;;;;13060.433;;;;;;;13710.265;;;;;;;14135.781
5761;;;;;;200407;;13710.265;;;;;;13060.43313318.93;;;;;;;14135.781;;;;;;;0
5761;;;;;;200408;;14135.781;;;;;;;13318.93;;;;;;;13710.265;;;;;;;0;;;;;;;0
5762;;;;;;200405;;12643.791;;;;;;0;;;;;;;012795.06;;;;;;;13224.297
5762;;;;;;200406;;;12795.06;;;;;;0;;;;;;;12643.791;;;;;;;13224.297;;;;;;;13376.468
5762;;;;;;200407;;13224.297;;;;;;12643.79112795.06;;;;;;;13376.468;;;;;;;0
5762;;;;;;200408;;13376.468;;;;;;;12795.06;;;;;;;13224.297;;;;;;;0;;;;;;;0
5763;;;;;;200405;;13060.433;;;;;;0;;;;;;;013318.93;;;;;;;13710.265
5763;;;;;;200406;;;13318.93;;;;;;0;;;;;;;13060.433;;;;;;;13710.265;;;;;;;14135.781
5763;;;;;;200407;;13710.265;;;;;;13060.43313318.93;;;;;;;14135.781;;;;;;;0
5763;;;;;;200408;;14135.781;;;;;;;13318.93;;;;;;;13710.265;;;;;;;0;;;;;;;0
5764;;;;;;200405;;12487.791;;;;;;0;;;;;;;0;;;;;;;13295.187;;;;;;;13444.093
5764;;;;;;200406;;13295.187;;;;;;0;;;;;;;12487.791;;;;;;;13444.093;;;;;;;13929.694
5764;;;;;;200407;;13444.093;;;;;;12487.791;;;;;;;13295.187;;;;;;;13929.694;;;;;;;0
5764;;;;;;200408;;13929.694;;;;;;13295.187;;;;;;;13444.093;;;;;;;0;;;;;;;0
5765;;;;;;200405;;25057.736;;;;;;0;;;;;;;026058.46;;;;;;;26301.881
5765;;;;;;200406;;;26058.46;;;;;;0;;;;;;;25057.736;;;;;;;26301.881;;;;;;;27130.638
5765;;;;;;200407;;26301.881;;;;;;25057.73626058.46;;;;;;;27130.638;;;;;;;0
5765;;;;;;200408;;27130.638;;;;;;;26058.46;;;;;;;26301.881;;;;;;;0;;;;;;;0
20 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_code
12;;;;;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_code
16;;;;;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_fare
20;;;;;from t
21;;;;;group by area_code,bill_month
22* )
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.433
5761;;;;;;200406;;;;13318.930;;40089.628 13363.2093;;13710.265;;13060.433
5761;;;;;;200407;;;;13710.265;;41164.976 13721.6587;;14135.781;;;13318.93
40089.628 = 13060.433 + 13318.930 + 13710.265
13363.2093 = (13060.433 + 13318.930 + 13710.265) / 3
13710.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.265
5762;;;;;;200405;;;;12643.791;;25438.851 12719.4255;;;12795.06;;12643.791
5762;;;;;;200406;;;;12795.060;;38663.148;;12887.716;;13224.297;;12643.791
5762;;;;;;200407;;;;13224.297;;39395.825 13131.9417;;13376.468;;;12795.06
5762;;;;;;200408;;;;13376.468;;26600.765 13300.3825;;13376.468;;13224.297
5763;;;;;;200405;;;;13060.433;;26379.363 13189.6815;;;13318.93;;13060.433
5763;;;;;;200406;;;;13318.930;;40089.628 13363.2093;;13710.265;;13060.433
5763;;;;;;200407;;;;13710.265;;41164.976 13721.6587;;14135.781;;;13318.93
5763;;;;;;200408;;;;14135.781;;27846.046;;13923.023;;14135.781;;13710.265
5764;;;;;;200405;;;;12487.791;;25782.978;;12891.489;;13295.187;;12487.791
5764;;;;;;200406;;;;13295.187;;39227.071 13075.6903;;13444.093;;12487.791
5764;;;;;;200407;;;;13444.093;;40668.974 13556.3247;;13929.694;;13295.187
5764;;;;;;200408;;;;13929.694;;27373.787 13686.8935;;13929.694;;13444.093
5765;;;;;;200405;;;;25057.736;;51116.196;;25558.098;;;26058.46;;25057.736
5765;;;;;;200406;;;;26058.460;;77418.077 25806.0257;;26301.881;;25057.736
5765;;;;;;200407;;;;26301.881;;79490.979;;26496.993;;27130.638;;;26058.46
5765;;;;;;200408;;;;27130.638;;53432.519 26716.2595;;27130.638;;26301.881
20 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)) over
3;;;;;;;( partition by bill_month ) area_pct
4;;from t
5* group by bill_month,area_code
SQL> break on bill_month skip 1
SQL> compute sum of local_fare on bill_month
SQL> compute sum of area_pct on bill_month
SQL> /
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_code
10* order by bill_month
SQL> /
BILL_MONTH AREA_CODE;;;;;;;LOCAL_FARE FIRSTVALLASTVAL
---------- --------- ---------------- --------------- ---------------
200405;;;;;5764;;;;;12487.791 5765;;;;5764
200405;;;;;5762;;;;;12643.791 5765;;;;5764
200405;;;;;5761;;;;;13060.433 5765;;;;5764
200405;;;;;5765;;;;;25057.736 5765;;;;5764
200405;;;;;5763;;;;;13060.433 5765;;;;5764
200406;;;;;5762;;;;;12795.060 5765;;;;5764
200406;;;;;5763;;;;;13318.930 5765;;;;5764
200406;;;;;5764;;;;;13295.187 5765;;;;5764
200406;;;;;5765;;;;;26058.460 5765;;;;5764
200406;;;;;5761;;;;;13318.930 5765;;;;5764
200407;;;;;5762;;;;;13224.297 5765;;;;5764
200407;;;;;5765;;;;;26301.881 5765;;;;5764
200407;;;;;5761;;;;;13710.265 5765;;;;5764
200407;;;;;5763;;;;;13710.265 5765;;;;5764
200407;;;;;5764;;;;;13444.093 5765;;;;5764
200408;;;;;5762;;;;;13376.468 5765;;;;5764
200408;;;;;5764;;;;;13929.694 5765;;;;5764
200408;;;;;5761;;;;;14135.781 5765;;;;5764
200408;;;;;5765;;;;;27130.638 5765;;;;5764
200408;;;;;5763;;;;;14135.781 5765;;;;5764
20 rows selected.
