/* 语法:
over(
[query_partition_clause]
[order by clause]
[windowing clause]
)
--BETWEEN...AND子句为窗口指定开始和结束位置,第一个表达式定义开始位置,第二个表达式定义结束位置,
-- 如果省略了bewteen,oracle会认为结束位置是当前行,而开始位置是表达式指定的行;*/
下面是用法举例:
create table hptestover
(
v varchar(10),
v1 int,
rn int
);
insert into hptestover(v,v1,rn)
select 'a',level,level from dual connect by level < 7;
insert into hptestover(v,v1,rn)
select 'b',level*level,level from dual connect by level < 4;
insert into hptestover(v,v1,rn)values('c',1,5);
insert into hptestover(v,v1,rn)values('c',3,6);
insert into hptestover(v,v1,rn)values('c',9,10);
insert into hptestover(v,v1,rn)values('d',1,2);
insert into hptestover(v,v1,rn)values('d',2,4);
insert into hptestover(v,v1,rn)values('d',4,7);
/*RANGE只指定了前后两个值(order by列的值)之间相差值的范围,而ROWS则指定了前后多少行的范围。*/
/*进行查询分析*/
select v,v1,rn,
sum(rn) over(partition by v order by v1 rows between 1 preceding and current row ) row_p1, /*将本行和上一行的rn值相加*/
sum(rn) over(partition by v order by v1 range between 1 preceding and current row ) range_p1, /*将本行之前的rn差额不超过1,本行之后的的rn等于当前行的所有rn值相加*/
sum(rn) over(partition by v order by v1 range between 2 preceding and current row ) range_p2, /*将本行之前的rn差额不超过2,本行之后的的rn等于当前行的所有rn值相加*/
sum(rn) over(partition by v order by v1 rows 1 preceding) row_p1_1 , /*将本行和上一行的rn值相加,省略了between,因此,oracle认为结束行时当前行,而开始行为1 precding*/
sum(rn) over(partition by v order by v1 range 1 preceding) range_p1_1 , /*将本行之前的rn差额不超过1,本行之后的的rn等于当前行的所有rn值相加,省略了between,因此,oracle认为结束行时当前行,而开始行为1 precding*/
sum(rn) over(partition by v order by v1 range 2 preceding) range_p2_1 , /*将本行之前的rn差额不超过2,本行之后的的rn等于当前行的所有rn值相加*/
sum(rn) over(partition by v order by v1 range 4 preceding) range_p4_1 , /*将本行之前的rn差额不超过2,本行之后的的rn等于当前行的所有rn值相加*/
sum(rn) over(partition by v order by v1 rows between current row and 1 following) row_f1, /*将本行和下一行的rn值相加*/
sum(rn) over(partition by v order by v1 range between current row and 1 following) range_f1, /*将本行和下一行的rn值相加*/
sum(rn) over(partition by v order by v1 range between current row and 4 following) range_f4, /*将本行和下一行的rn值相加*/
sum(rn) over(partition by v order by v1 rows between unbounded preceding and current row) row_pn, /*将第一行到当前行的所有rn值相加*/
sum(rn) over(partition by v order by v1 range between unbounded preceding and current row) range_pn, /*将第一行到当前行的所有rn值相加*/
sum(rn) over(partition by v order by v1 rows unbounded preceding ) row_pn_1, /*将第一行到当前行的所有rn值相加,省略between*/
sum(rn) over(partition by v order by v1 range unbounded preceding ) range_pn_1, /*将第一行到当前行的所有rn值相加,省略between*/
sum(rn) over(partition by v order by v1 rows between current row and unbounded following) row_fn, /*将当前行到最后一行的所有rn值相加*/
sum(rn) over(partition by v order by v1 range between current row and unbounded following) range_fn, /*将当前行到最后一行的所有rn值相加*/
sum(rn) over(partition by v order by v1 rows between 1 preceding and 1 following) row_p1_f1, /*将当前行、前一行、最后一行的所有rn值相加*/
sum(rn) over(partition by v order by v1 range between 1 preceding and 1 following) range_p1_f1, /*将当前行、前一行、最后一行的所有rn值相加*/
sum(rn) over(partition by v order by v1 rows between unbounded preceding and unbounded following) row_all, /*将所有v值相同的行相加*/
sum(rn) over(partition by v order by v1 range between unbounded preceding and unbounded following) range_all, /*将所有v值相同的行相加*/
sum(rn) over(partition by v ) s_all /*将所有v值相同的行相加*/
from hptestover
order by v,v1;
--清理测试表
drop table hptestover;
閱讀更多 老孔說編程 的文章