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'
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 15761 54225.41 25763 54225.41 2 5764 53156.77 4 5762 52039.62 5
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 15761 54225.41 25763 54225.41 25764 53156.77 3 这是这里出现了第三名5762 52039.62 4
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.181576154225413.042576354225413.043576252039619.64576445814632.65
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.4712200405576113060433.8923200405576313060433.8924200405576212643792.1145200405576412487791.9456200406576526058461.3117200406576113318931.0128200406576313318931.0129200406576413295187.67410200406576212795060.65511200407576526301881.4112200407576313710265.93213200407576113710265.93214200407576413444093.76415200407576213224298.12516200408576114135782.21117200408576314135782.21118200408576213376468.7231920040857646587559.234
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.2655761 200406 13318.93 0 13060.433 13710.265 14135.7815761 200407 13710.265 13060.433 13318.93 14135.781 05761 200408 14135.781 13318.93 13710.265 0 05762 200405 12643.791 0 0 12795.06 13224.2975762 200406 12795.06 0 12643.791 13224.297 13376.4685762 200407 13224.297 12643.791 12795.06 13376.468 05762 200408 13376.468 12795.06 13224.297 0 05763 200405 13060.433 0 0 13318.93 13710.2655763 200406 13318.93 0 13060.433 13710.265 14135.7815763 200407 13710.265 13060.433 13318.93 14135.781 05763 200408 14135.781 13318.93 13710.265 0 05764 200405 12487.791 0 0 13295.187 13444.0935764 200406 13295.187 0 12487.791 13444.093 13929.6945764 200407 13444.093 12487.791 13295.187 13929.694 05764 200408 13929.694 13295.187 13444.093 0 05765 200405 25057.736 0 0 26058.46 26301.8815765 200406 26058.46 0 25057.736 26301.881 27130.6385765 200407 26301.881 25057.736 26058.46 27130.638 05765 200408 27130.638 26058.46 26301.881 0 0
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_min1576120040513060433.890013318931.0113710265.932576120040613318931.01013060433.8913710265.9314135782.213576120040713710265.9313060433.8913318931.0114135782.2104576120040814135782.2113318931.0113710265.93005576220040512643792.110012795060.6513224298.126576220040612795060.65012643792.1113224298.1213376468.727576220040713224298.1212643792.1112795060.6513376468.7208576220040813376468.7212795060.6513224298.12009576320040513060433.890013318931.0113710265.9310576320040613318931.01013060433.8913710265.9314135782.2111576320040713710265.9313060433.8913318931.0114135782.21012576320040814135782.2113318931.0113710265.930013576420040512487791.940013295187.6713444093.7614576420040613295187.67012487791.9413444093.766587559.2315576420040713444093.7612487791.9413295187.676587559.2301657642004086587559.2313295187.6713444093.760017576520040525057737.470026058461.3126301881.418576520040626058461.31025057737.4726301881.4019576520040726301881.425057737.4726058461.3100
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 5764200405 5762 12643.791 5765 5764200405 5761 13060.433 5765 5764200405 5765 25057.736 5765 5764200405 5763 13060.433 5765 5764200406 5762 12795.060 5765 5764200406 5763 13318.930 5765 5764200406 5764 13295.187 5765 5764200406 5765 26058.460 5765 5764200406 5761 13318.930 5765 5764200407 5762 13224.297 5765 5764200407 5765 26301.881 5765 5764200407 5761 13710.265 5765 5764200407 5763 13710.265 5765 5764200407 5764 13444.093 5765 5764200408 5762 13376.468 5765 5764200408 5764 13929.694 5765 5764200408 5761 14135.781 5765 5764200408 5765 27130.638 5765 5764200408 5763 14135.781 5765 5764