OVER(PARTITION BY)函数介绍
来源:互联网 发布:如何下载spss13软件 编辑:程序博客网 时间:2024/06/09 18:51
不错,学习了
over(partition by deptno orderby salary) select name,class,s, sum(s)over(order by s rangebetween 2 preceding and 2 following) mm from t2
adf 3 45 45 --45加2减2即43到47,但是s在这个范围内只有45
asdf 3 55 55
cfe 2 74 74
3dd 3 78 158 --78在76到80范围内有78,80,求和得158
fda 1 80 158
gds 2 92 92
ffd 1 95 190
dss 1 95 190
ddd 3 99 198
cfe 2 74
dss 1 95
ffd 1 95
fda 1 80
gds 2 92
gf 3 99
ddd 3 99
adf 3 45
asdf 3 55
3dd 3 78
select *from
(
原文地址:BY)函数介绍">OVER(PARTITION BY)函数介绍 作者:
开窗函数
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。
1:over后的写法:
2:开窗的窗口范围:
over(order by salary
举例:
--sum(s)over(orderby s range between 2 preceding and 2following)
adf
asdf
cfe
3dd
fda
gds
ffd
dss
ddd
gf
over(order by salary rows between5 preceding and 5 following):窗口范围为当前行前后各移动5行。
举例:
--sum(s)over(order by s rows between 2 preceding and 2following)表示在上下两行之间的范围内
select name,class,s, sum(s)over(order by s rows between 2 precedingand 2 following) mm from t2
adf 3 45 174 (45+55+74=174)
asdf 3 55 252 (45+55+74+78=252)
cfe 2 74 332 (74+55+45+78+80=332)
3dd 3 78 379 (78+74+55+80+92=379)
fda 1 80 419
gds 2 92 440
ffd 1 95 461
dss 1 95 480
ddd 3 99 388
gf 3 99 293
select name,class,s, sum(s)over(order by s rows between 2 precedingand 2 following) mm from t2
adf
asdf
cfe
3dd
fda
gds
ffd
dss
ddd
gf
over(order by salary range betweenunbounded preceding and unbounded following)或者
over(order by salary rows betweenunbounded preceding and unbounded following):窗口不做限制
3、与over函数结合的几个函数介绍
row_number()over()、rank()over()和dense_rank()over()函数的使用
下面以班级成绩表t2来说明其应用
t2表信息如下:cfe
dss
ffd
fda
gds
gf
ddd
adf
asdf
3dd
select *from