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 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               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               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               0        26058.46       26301.881

5765      200406           26058.46              0       25057.736       26301.881       27130.638

5765      200407          26301.881      25057.736        26058.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.

 

[ 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_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

           <