分析函数
来源:互联网 发布:js 模拟a标签触发href 编辑:程序博客网 时间:2024/04/29 08:36
OVER(PARTITION BY)函数介绍
开窗函数
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。
1:over后的写法:
2:开窗的窗口范围:
over(order by salary
举例:
--sum(s)over(order by s range between 2 preceding and 2 following)
adf
asdf
cfe
3dd
fda
gds
ffd
dss
ddd
gf
over(order by salary rows between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。
举例:
--sum(s)over(order by s rows between 2 preceding and 2 following)表示在上下两行之间的范围内
select name,class,s, sum(s)over(order by s rows between 2 preceding and 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 preceding and 2 following) mm from t2
adf
asdf
cfe
3dd
fda
gds
ffd
dss
ddd
gf
over(order by salary range between unbounded preceding and unbounded following)或者
over(order by salary rows between unbounded 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