`
javahigh1
  • 浏览: 1215862 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

orcale分析函数(二)

 
阅读更多
分析函数over 及开窗函数
一:分析函数over
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是
对于每个组返回多行,而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。
1:统计某商店的营业额。
date sale
1 20
2 15
3 14
4 18
5 30
规则:按天统计:每天都统计前面几天的总额
得到的结果:
DATE SALE SUM
----- -------- ------
1 20 20 --1天
2 15 35 --1天+2天
3 14 49 --1天+2天+3天
4 18 67 .
5 30 97 .

2:统计各班成绩第一名的同学信息
NAME CLASS S
----- ----- ----------------------
fda 1 80
ffd 1 78
dss 1 95
cfe 2 74
gds 2 92
gf 3 99
ddd 3 99
adf 3 45
asdf 3 55
3dd 3 78

通过:
--
select * from
(
select name,class,s,rank()over(partition by class order by s desc) mm from t2
)
where mm=1
--
得到结果:
NAME CLASS S MM
----- ----- ---------------------- ----------------------
dss 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1

注意:
1.在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果
2.rank()和dense_rank()的区别是:
--rank()是跳跃排序,有两个第二名时接下来就是第四名
--dense_rank()l是连续排序,有两个第二名时仍然跟着第三名


3.分类统计 (并显示信息)
A B C
-- -- ----------------------
m a 2
n a 3
m a 2
n b 2
n b 1
x b 3
x b 2
x b 4
h b 3
select a,c,sum(c)over(partition by a) from t2
得到结果:
A B C SUM(C)OVER(PARTITIONBYA)
-- -- ------- ------------------------
h b 3 3
m a 2 4
m a 2 4
n a 3 6
n b 2 6
n b 1 6
x b 3 9
x b 2 9
x b 4 9

如果用sum,group by 则只能得到
A SUM(C)
-- ----------------------
h 3
m 4
n 6
x 9
无法得到B列值

=====
二:开窗函数
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
1:
over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数
over(partition by deptno)按照部门分区
2:
over(order by salary range between 5 preceding and 5 following)
每行对应的数据窗口是之前行幅度值不超过5,之后行幅度值不超过5
例如:对于以下列
aa
1
2
2
2
3
4
5
6
7
9

sum(aa)over(order by aa range between 2 preceding and 2 following)
得出的结果是
AA SUM
---------------------- -------------------------------------------------------
1 10
2 14
2 14
2 14
3 18
4 18
5 22
6 18
7 22
9 9

就是说,对于aa=5的一行 ,sum为 5-1<=aa<=5+2 的和
对于aa=2来说 ,sum=1+2+2+2+3+4=14 ;
又如 对于aa=9 ,9-1<=aa<=9+2 只有9一个数,所以sum=9 ;

3:其它:
over(order by salary rows between 2 preceding and 4 following)
每行对应的数据窗口是之前2行,之后4行
4:下面三条语句等效:
over(order by salary rows between unbounded preceding and unbounded following)
每行对应的数据窗口是从第一行到最后一行,等效:
over(order by salary range between unbounded preceding and unbounded following)
等效
over(partition by null)
分享到:
评论

相关推荐

    ORACLE分析函数教程

    关于ORACLE分析函数的教程,教程描述描述清晰

    ORACLE 分析函数大全

    ORACLE 分析函数大全,包含很多关于ORACLE的分析函数,内置函数

    ORACLE分析函数大全

    文档详细介绍了oracle的分析函数,包括功能说明、sql示例等。分析函数功能强大,在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂,最好使用的时候,参考文档

    ORACLE分析函数.pdf

    ORACLE分析函数.pdf

    Oracle分析函数.doc

    Oracle分析函数.doc

    oracle 分析函数

    oracle 分析函数 开发必备 数据库开发工程师

    Oracle 分析函数.doc

    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分析函数使用总结

    oracle分析函数(用法+实例)

    oracle分析函数(用法+实例),这属于oracle的高级应用。

    Oracle分析函数

    Oracle分析函数,常用分析函数应有尽有

    oracle 分析函数学习笔记

    分析函数是oracle中强大的功能,附件是分析函数学习笔记

    oracle分析函数

    ·2Oracle分析函数二——函数用法 ·3Oracle分析函数三——SUM,AVG,MIN,MAX,COUNT ·4Oracle分析函数四——函数RANK,DENSE_RANK,FIRST,LAST… ·5Oracle分析函数五——统计分析函数 ·6Oracle分析函数六——数据...

    oracle分析函数大全

    Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。

    ORACLE_分析函数大全

    ORACLE分析函数大全 包括大部分常用的分析函数

    ORACLE 常用分析函数

    主要包括分析函数(OVER);分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述  ROW_NUMBER () ...

    oracle分析函数,窗口函数,报表函数

    oracle分析函数,窗口函数,报表函数 分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile)

    Oracle分析函数.pdf

    Oracle分析函数.pdf,这份资料详细介绍了Oracle分析函数的使用,Oracle分析函数.pdf是一份不错的文档

    Oracle中的分析函数详解

    NULL 博文链接:https://yangkai.iteye.com/blog/754447

Global site tag (gtag.js) - Google Analytics