oracle over分析函数用法介绍 oracle分析函数partition by

/* 语法：

over(

[query_partition_clause]

[order by clause]

[windowing clause]

)

<query_partition_clause> ::= parition by expr,...[n]

<order by clause> ::= order[sliblings] by {expr|position|c_alias} [asc|desc] [nulls first|nulls last] [,...n]

<windowing_clause> ::= {rows|range} <window frame extent>

<window frame extent> ::= { <window frame between> | <window frame preceding> }

<window frame between> ::= BETWEEN <window frame bound1> AND <window frame bound2>

--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;