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 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
1 5761 200405 13060433.89 0 0 13318931.01 13710265.93
2 5761 200406 13318931.01 0 13060433.89 13710265.93 14135782.21
3 5761 200407 13710265.93 13060433.89 13318931.01 14135782.21 0
4 5761 200408 14135782.21 13318931.01 13710265.93 0 0
5 5762 200405 12643792.11 0 0 12795060.65 13224298.12
6 5762 200406 12795060.65 0 12643792.11 13224298.12 13376468.72
7 5762 200407 13224298.12 12643792.11 12795060.65 13376468.72 0
8 5762 200408 13376468.72 12795060.65 13224298.12 0 0
9 5763 200405 13060433.89 0 0 13318931.01 13710265.93
10 5763 200406 13318931.01 0 13060433.89 13710265.93 14135782.21
11 5763 200407 13710265.93 13060433.89 13318931.01 14135782.21 0
12 5763 200408 14135782.21 13318931.01 13710265.93 0 0
13 5764 200405 12487791.94 0 0 13295187.67 13444093.76
14 5764 200406 13295187.67 0 12487791.94 13444093.76 6587559.23
15 5764 200407 13444093.76 12487791.94 13295187.67 6587559.23 0
16 5764 200408 6587559.23 13295187.67 13444093.76 0 0
17 5765 200405 25057737.47 0 0 26058461.31 26301881.4
18 5765 200406 26058461.31 0 25057737.47 26301881.4 0
19 5765 200407 26301881.4 25057737.47 26058461.31 0 0
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
分享到：
相关推荐
Oracle 分析函数详解 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数
Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法 Oracle分析函数参考手册 开窗函数(over)详解
1、oracle分析函数 中文： 主要含： rank() 和 dense_rank() first_value()和last_value() row_number() LAG() range开窗函数 2、oracle分析函数 英文： 比较详细
Oracle分析函数——函数列表 SUM ：该函数计算组中表达式的累积和 MIN ：在一个组中的数据窗口中查找表达式的最小值 MAX ：在一个组中的数据窗口中查找表达式的最大值 AVG ：用于计算一个组和数据窗口内表达式的...
Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法,需要的朋友可以参考下。
从论坛搜到的，感觉还不错，整理成了word文档，希望对大家也有用，同时也感谢原作者。 主要讲了以下函数： 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number ...6. first,last取基数的分析函数
1，数值型函数(abs()、sign()、ceil()、floor()、power()、exp()、round()...)；...6，分析函数(sum(...) over(...)、dense_rank、row_number()、lag()...)； 7，其它函数(decode()、nvl()、nvl2()、least()...)；
本文是对Oracle中分析函数做的总结，主要总结了前几篇关于分析函数、窗口函数、报表函数文章的内容，需要的朋友可以参考下。
作者通过总结各自多年的软件开发和教学培训经验，与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在，内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧：...