; 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 &nb上一页 [1] [2] [3] [4] 下一页 |
