Oracle分析函数2(rank,lag等)
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

执行结果如下:

         5765	77418080.18	1
	5761	54225413.04	2
	5763	54225413.04	3
	5762	52039619.6	4
	5764	45814632.6	5
   

   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

  执行结果如下：
1	200405	5765	25057737.47	1
2	200405	5761	13060433.89	2
3	200405	5763	13060433.89	2
4	200405	5762	12643792.11	4
5	200405	5764	12487791.94	5
6	200406	5765	26058461.31	1
7	200406	5761	13318931.01	2
8	200406	5763	13318931.01	2
9	200406	5764	13295187.67	4
10	200406	5762	12795060.65	5
11	200407	5765	26301881.4	1
12	200407	5763	13710265.93	2
13	200407	5761	13710265.93	2
14	200407	5764	13444093.76	4
15	200407	5762	13224298.12	5
16	200408	5761	14135782.21	1
17	200408	5763	14135782.21	1
18	200408	5762	13376468.72	3
19	200408	5764	6587559.23	4
  

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          1