- 浏览: 133996 次
- 性别:
- 来自: 西安
最新评论
-
zhlfresh163.com:
,请速回,我也正在玩百度地图,就这里自定义图层出现问题,需要 ...
MapXtreme加载瓦片地图 -
zhlfresh163.com:
var path = "TileServer/&qu ...
MapXtreme加载瓦片地图
Oracle 分析函数的使用
1.自动汇总函数rollup,cube,
2.rank 函数, rank,dense_rank,row_number
3. lag,lead函数
4. sum,avg,的移动增加,移动平均数
5. ratio_to_report报表处理函数
6. first,last取基数的分析函数
基础数据
1. 使用rollup函数的介绍
1 rollup 和 cube函数的再深入
2. rank函数的介绍
a. 取出数据库中最后入网的n个用户
select user_id,tele_num,user_name,user_status,create_date
from (
select user_id,tele_num,user_name,user_status,create_date,
rank() over (order by create_date desc) add_rank
from user_info
)
where add_rank <= :n;
b.根据object_name删除数据库中的重复记录
create table t as select obj#,name from sys.obj$;
再insert into t1 select * from t1 数次.
delete from t1 where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn
) where rn <> 1
);
c. 取出各地区的话费收入在各个月份排名.
SQL> select bill_month,area_code,sum(local_fare) local_fare,
2 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank
3from t
4group by bill_month,area_code
5/
BILL_MONTH AREA_CODE LOCAL_FAREAREA_RANK
--------------- --------------- -------------- ----------
200405 5765 25057.74 1
200405 5761 13060.43 2
200405 5763 13060.43 2
200405 5762 12643.79 4
200405 5764 12487.79 5
200406 5765 26058.46 1
200406 5761 13318.93 2
200406 5763 13318.93 2
200406 5764 13295.19 4
200406 5762 12795.06 5
200407 5765 26301.88 1
200407 5761 13710.27 2
200407 5763 13710.27 2
200407 5764 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函数介绍
取出每个月的上个月和下个月的话费总额
1select 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
6from (
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 0
5765 200408 27130.638 26058.46 26301.881 0 0
20 rows selected.
利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.
4. sum,avg,max,min移动计算数据介绍
计算出各个连续3个月的通话费用的平均数
1select area_code,bill_month, local_fare,
2 sum(local_fare)
3 over (partition by area_code
4 order by to_number(bill_month)
5 range between 1 preceding and 1 following ) "3month_sum",
6 avg(local_fare)
7 over (partition by area_code
8 order by to_number(bill_month)
9 range between 1 preceding and 1 following ) "3month_avg",
10 max(local_fare)
11 over (partition by area_code
12 order by to_number(bill_month)
13 range between 1 preceding and 1 following ) "3month_max",
14 min(local_fare)
15 over (partition by area_code
16 order by to_number(bill_month)
17 range between 1 preceding and 1 following ) "3month_min"
18from (
19 select area_code,bill_month,sum(local_fare) local_fare
20 from t
21 group by area_code,bill_month
22* )
SQL> /
AREA_CODE BILL_MONTH LOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min
--------- ---------- ---------------- ---------- ---------- ---------- ----------
5761 200405 13060.43326379.363 13189.6815 13318.9313060.433
5761 200406 13318.93040089.628 13363.209313710.26513060.433
5761 200407 13710.26541164.976 13721.658714135.781 13318.93
40089.628 = 13060.433 + 13318.930 + 13710.265
13363.2093 = (13060.433 + 13318.930 + 13710.265) / 3
13710.265 = max(13060.433 + 13318.930 + 13710.265)
13060.433 = min(13060.433 + 13318.930 + 13710.265)
5761 200408 14135.78127846.04613923.02314135.78113710.265
5762 200405 12643.79125438.851 12719.4255 12795.0612643.791
5762 200406 12795.06038663.14812887.71613224.29712643.791
5762 200407 13224.29739395.825 13131.941713376.468 12795.06
5762 200408 13376.46826600.765 13300.382513376.46813224.297
5763 200405 13060.43326379.363 13189.6815 13318.9313060.433
5763 200406 13318.93040089.628 13363.209313710.26513060.433
5763 200407 13710.26541164.976 13721.658714135.781 13318.93
5763 200408 14135.78127846.04613923.02314135.78113710.265
5764 200405 12487.79125782.97812891.48913295.18712487.791
5764 200406 13295.18739227.071 13075.690313444.09312487.791
5764 200407 13444.09340668.974 13556.324713929.69413295.187
5764 200408 13929.69427373.787 13686.893513929.69413444.093
5765 200405 25057.73651116.19625558.098 26058.4625057.736
5765 200406 26058.46077418.077 25806.025726301.88125057.736
5765 200407 26301.88179490.97926496.99327130.638 26058.46
5765 200408 27130.63853432.519 26716.259527130.63826301.881
20 rows selected.
5. ratio_to_report函数的介绍
6 first,last函数使用介绍
2.rank 函数, rank,dense_rank,row_number
3. lag,lead函数
4. sum,avg,的移动增加,移动平均数
5. ratio_to_report报表处理函数
6. first,last取基数的分析函数
基础数据
Code: | [Copy to clipboard] |
|
1. 使用rollup函数的介绍
Quote: | |
|
1. 使用cube函数的介绍
|
Quote: | |
|
2. rank函数的介绍 介绍完rollup和cube函数的使用,下面我们来看看rank系列函数的使用方法. 问题2.我想查出这几个月份中各个地区的总话费的排名.
|
a. 取出数据库中最后入网的n个用户
select user_id,tele_num,user_name,user_status,create_date
from (
select user_id,tele_num,user_name,user_status,create_date,
rank() over (order by create_date desc) add_rank
from user_info
)
where add_rank <= :n;
b.根据object_name删除数据库中的重复记录
create table t as select obj#,name from sys.obj$;
再insert into t1 select * from t1 数次.
delete from t1 where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn
) where rn <> 1
);
c. 取出各地区的话费收入在各个月份排名.
SQL> select bill_month,area_code,sum(local_fare) local_fare,
2 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank
3from t
4group by bill_month,area_code
5/
BILL_MONTH AREA_CODE LOCAL_FAREAREA_RANK
--------------- --------------- -------------- ----------
200405 5765 25057.74 1
200405 5761 13060.43 2
200405 5763 13060.43 2
200405 5762 12643.79 4
200405 5764 12487.79 5
200406 5765 26058.46 1
200406 5761 13318.93 2
200406 5763 13318.93 2
200406 5764 13295.19 4
200406 5762 12795.06 5
200407 5765 26301.88 1
200407 5761 13710.27 2
200407 5763 13710.27 2
200407 5764 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函数介绍
取出每个月的上个月和下个月的话费总额
1select 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
6from (
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 0
5765 200408 27130.638 26058.46 26301.881 0 0
20 rows selected.
利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.
4. sum,avg,max,min移动计算数据介绍
计算出各个连续3个月的通话费用的平均数
1select area_code,bill_month, local_fare,
2 sum(local_fare)
3 over (partition by area_code
4 order by to_number(bill_month)
5 range between 1 preceding and 1 following ) "3month_sum",
6 avg(local_fare)
7 over (partition by area_code
8 order by to_number(bill_month)
9 range between 1 preceding and 1 following ) "3month_avg",
10 max(local_fare)
11 over (partition by area_code
12 order by to_number(bill_month)
13 range between 1 preceding and 1 following ) "3month_max",
14 min(local_fare)
15 over (partition by area_code
16 order by to_number(bill_month)
17 range between 1 preceding and 1 following ) "3month_min"
18from (
19 select area_code,bill_month,sum(local_fare) local_fare
20 from t
21 group by area_code,bill_month
22* )
SQL> /
AREA_CODE BILL_MONTH LOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min
--------- ---------- ---------------- ---------- ---------- ---------- ----------
5761 200405 13060.43326379.363 13189.6815 13318.9313060.433
5761 200406 13318.93040089.628 13363.209313710.26513060.433
5761 200407 13710.26541164.976 13721.658714135.781 13318.93
40089.628 = 13060.433 + 13318.930 + 13710.265
13363.2093 = (13060.433 + 13318.930 + 13710.265) / 3
13710.265 = max(13060.433 + 13318.930 + 13710.265)
13060.433 = min(13060.433 + 13318.930 + 13710.265)
5761 200408 14135.78127846.04613923.02314135.78113710.265
5762 200405 12643.79125438.851 12719.4255 12795.0612643.791
5762 200406 12795.06038663.14812887.71613224.29712643.791
5762 200407 13224.29739395.825 13131.941713376.468 12795.06
5762 200408 13376.46826600.765 13300.382513376.46813224.297
5763 200405 13060.43326379.363 13189.6815 13318.9313060.433
5763 200406 13318.93040089.628 13363.209313710.26513060.433
5763 200407 13710.26541164.976 13721.658714135.781 13318.93
5763 200408 14135.78127846.04613923.02314135.78113710.265
5764 200405 12487.79125782.97812891.48913295.18712487.791
5764 200406 13295.18739227.071 13075.690313444.09312487.791
5764 200407 13444.09340668.974 13556.324713929.69413295.187
5764 200408 13929.69427373.787 13686.893513929.69413444.093
5765 200405 25057.73651116.19625558.098 26058.4625057.736
5765 200406 26058.46077418.077 25806.025726301.88125057.736
5765 200407 26301.88179490.97926496.99327130.638 26058.46
5765 200408 27130.63853432.519 26716.259527130.63826301.881
20 rows selected.
5. ratio_to_report函数的介绍
Quote: | |
|
6 first,last函数使用介绍
Quote: | |
|
相关推荐
Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结
Oracle分析函数使用的总结.doc
Oracle分析函数使用总结[定义].pdf
介绍 Oracle 中分析函数使用方法。 分析函数可以方便对数据库中的数据进行分析统计
关于ORACLE分析函数的教程,教程描述描述清晰
ORACLE 分析函数大全,包含很多关于ORACLE的分析函数,内置函数
文档详细介绍了oracle的分析函数,包括功能说明、sql示例等。分析函数功能强大,在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂,最好使用的时候,参考文档
ORACLE分析函数.pdf
Oracle分析函数.doc
oracle 分析函数 开发必备 数据库开发工程师
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分析函数(用法+实例),这属于oracle的高级应用。
Oracle分析函数,常用分析函数应有尽有
分析函数是oracle中强大的功能,附件是分析函数学习笔记
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。
ORACLE分析函数大全 包括大部分常用的分析函数
主要包括分析函数(OVER);分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () ...
Oracle分析函数.pdf,这份资料详细介绍了Oracle分析函数的使用,Oracle分析函数.pdf是一份不错的文档