Oracle分析函数2(rank,lag等)

o1bhnf0620

rank函数介绍：
   准备工作：对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.
  update latty.test_t t1 set local_fare = (
select local_fare from latty.test_t t2
where t1.bill_month = t2.bill_month
and t1.net_type = t2.net_type
and t2.area_code = '5761'
) where area_code = '5763'

  我们先使用rank函数来计算各个地区的话费排名，SQL代码如下：
   select area_code,sum(local_fare) local_fare,
rank() over (order by sum(local_fare) desc) fare_rank
from latty.test_t
group by area_code
   执行结果显示为:
   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

注意查看结果发现这里没有出现排名3。
下面我们看下dense_rank排名结果，SQL如下：
   select area_code,sum(local_fare) local_fare,
dense_rank()  over (order by sum(local_fare)     
desc) fare_rank
from latty.test_t
group by area_code
  执行结果如下：
  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

再来看下row_number,查询SQL语句为:
select area_code,sum(local_fare) local_fare,
row_number()  over (order by sum(local_fare) desc) fare_rank
from latty.test_t
group by area_code

执行结果如下:
         576577418080.181
576154225413.042
576354225413.043
576252039619.64
576445814632.65

   rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处。
   看看这个查询：取出各地区的话费收入在各个月份排名.SQL语句如下：
    select bill_month,area_code,sum(local_fare) local_fare,
rank() over (partition by bill_month order by sum   (local_fare)        desc) area_rank
from latty.test_t
group by bill_month,area_code
执行结果如下：
1200405576525057737.471
2200405576113060433.892
3200405576313060433.892
4200405576212643792.114
5200405576412487791.945
6200406576526058461.311
7200406576113318931.012
8200406576313318931.012
9200406576413295187.674
10200406576212795060.655
11200407576526301881.41
12200407576313710265.932
13200407576113710265.932
14200407576413444093.764
15200407576213224298.125
16200408576114135782.211
17200408576314135782.211
18200408576213376468.723
1920040857646587559.234


lag和lead函数介绍:取出每个月的上个月和下个月的话费总额
查询SQL如下：

select area_code,bill_month, local_fare cur_local_fare,
lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
from (
select area_code,bill_month,sum(local_fare) local_fare
from latty.test_t
group by area_code,bill_month
)

执行结果为：
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

sum,avg,max,min移动计算数据介绍：
查询为：计算出各个连续3个月的通话费用的平均数
SQL代码为：
select area_code,bill_month, local_fare cur_local_fare,
lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
from (
select area_code,bill_month,sum(local_fare) local_fare
from latty.test_t
group by area_code,bill_month
)

执行结果为:
AREA_CODE BILL_MONTH       LOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min

1576120040513060433.890013318931.0113710265.93
2576120040613318931.01013060433.8913710265.9314135782.21
3576120040713710265.9313060433.8913318931.0114135782.210
4576120040814135782.2113318931.0113710265.9300
5576220040512643792.110012795060.6513224298.12
6576220040612795060.65012643792.1113224298.1213376468.72
7576220040713224298.1212643792.1112795060.6513376468.720
8576220040813376468.7212795060.6513224298.1200
9576320040513060433.890013318931.0113710265.93
10576320040613318931.01013060433.8913710265.9314135782.21
11576320040713710265.9313060433.8913318931.0114135782.210
12576320040814135782.2113318931.0113710265.9300
13576420040512487791.940013295187.6713444093.76
14576420040613295187.67012487791.9413444093.766587559.23
15576420040713444093.7612487791.9413295187.676587559.230
1657642004086587559.2313295187.6713444093.7600
17576520040525057737.470026058461.3126301881.4
18576520040626058461.31025057737.4726301881.40
19576520040726301881.425057737.4726058461.3100

first,last函数使用介绍：
查询为:取出每月通话费最高和最低的两个用户.
查询SQL为:

select bill_month,area_code,sum(local_fare) local_fare,
first_value(area_code)
over (order by sum(local_fare) desc
rows unbounded preceding) firstval,
first_value(area_code)
over (order by sum(local_fare) asc
rows unbounded preceding) lastval
from latty.test_t
group by bill_month,area_code
order by bill_month

执行结果显示为:
   BILL_MONTH AREA_CODE       LOCAL_FARE FIRSTVAL        LASTVAL
---------- --------- ---------------- --------------- ---------------
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