05.11 oracle over分析函數用法介紹

/* 語法：

over(

[query_partition_clause]

[order by clause]

[windowing clause]

)

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

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

<windowing> ::= {rows|range} <window>/<windowing>

<window> ::= { <window> | <window> }/<window>/<window>/<window>

<window> ::= BETWEEN <window> AND <window> /<window>/<window>/<window>

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