05.13 sql語法之over用法

/***Sql Server*/

/*

OVER ( [ <partition> ]/<partition>

[ <order> ] /<order>

[ ]

)

<partition> ::= PARTITION BY value_expression , ... [ n ] /<partition>

--PARTITION BY將查詢結果集分為多個分區，開窗函數分別應用於每個分區，併為每個分區重新啟動計算；

-- 如果未指定 PARTITION BY，則此函數將查詢結果集的所有行視為單個組。

-- value_expressio：指定行集按其分區的列。value_expression 只能引用可供 FROM 子句使用的列。

-- value_expression 可以是列表達式、標量子查詢、標量函數或用戶定義的變量

<order> ::= ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ]/<order>

--ORDER BY定義結果集的每個分區中行的邏輯順序。也就是說，它指定按其執行開窗函數計算的邏輯順序。

-- 如果未指定 ROWS/RANGE，但指定了 ORDER BY，則將 RANGE UNBOUNDED PRECEDING AND CURRENT ROW 用作窗口的默認值。

-- order_by_expression指定用於進行排序的列或表達式，只能引用可供 FROM 子句使用的列，不能將整數指定為表示列名或別名。

::= { ROWS | RANGE } <window>

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

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

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

<window> ::= { UNBOUNDED PRECEDING | <unsigned> PRECEDING | CURRENT ROW }/<unsigned>/<window>

<window> ::= { UNBOUNDED FOLLOWING | <unsigned> FOLLOWING | CURRENT ROW }/<unsigned>/<window>

<unsigned> ::= { <unsigned> }/<unsigned>/<unsigned>

--Range用於指定order by列表中的值與指定的行一致的記錄行，這個指定的行只能是current row(當前行)或unbounded preceding(分區內的第一行) 。

--Rows是用於指定某一行到另外一行之間的所有行，其中的“某一行”和"另外一行"均可以是"n preceding(當前行之前的第n行)"、

-- "n following(當前行之後的第n行)"、"current row(當前行)"、"unbounded preceding(分區內的第一行)"、

-- "unbounded following(分區內的最後一行)"；

-- 但是要求在當前窗口中“某一行”必須排在"另外一行"之前。

-- Rows一般與between...and...同時出現，即“Rows between 某一行 and另外一行”；

-- 但是between..and也可以省略，表示從指定行到當前行之間的行，其中指定行必須為當前行之前的行，

-- 即"unbounded preceding(分區內的第一行)"或"n preceding(當前行之前的第n行)"，比如 Rows unbounded preceding 。

*/

/*創建測試表*/

create table hptestover

(

v varchar(10),

rn int

);

insert into hptestover(v,rn)values('a',1),('a',2),('a',3);

insert into hptestover(v,rn)values('b',1),('b',2),('b',3);

insert into hptestover(v,rn)values('a',5);

insert into hptestover(v,rn)values('a',4);

/*進行查詢分析：RANGE 只支持使用 UNBOUNDED 和 CURRENT ROW 窗口框架分隔符*/

select v,rn,

sum(rn) over(partition by v order by rn rows between 1 preceding and current row ) row_p1, /*將本行和上一行的rn值相加*/

--sum(rn) over(partition by v order by rn range between 1 preceding and current row ) range_p1, /*將本行和上一行的rn值相加*/

sum(rn) over(partition by v order by rn rows 1 preceding) row_p1_1 , /*將本行和上一行的rn值相加，省略了between，因此，oracle認為結束行時當前行，而開始行為1 precding*/

--sum(rn) over(partition by v order by rn range 1 preceding) range_p1_1 , --將本行和上一行的rn值相加，省略了between，因此，oracle認為結束行時當前行，而開始行為1 precding*/

sum(rn) over(partition by v order by rn rows between current row and 1 following) row_f1, /*將本行和下一行的rn值相加*/

--sum(rn) over(partition by v order by rn range between current row and 1 following) range_f1, /*將本行和下一行的rn值相加*/

sum(rn) over(partition by v order by rn rows between unbounded preceding and current row) row_pn, /*將第一行到當前行的所有rn值相加*/

sum(rn) over(partition by v order by rn range between unbounded preceding and current row) range_pn, /*將第一行到當前行的所有rn值相加*/

sum(rn) over(partition by v order by rn rows unbounded preceding ) row_pn_1, /*將第一行到當前行的所有rn值相加，省略between*/

sum(rn) over(partition by v order by rn range unbounded preceding ) range_pn_1,/*將第一行到當前行的所有rn值相加，省略between*/

sum(rn) over(partition by v order by rn rows between current row and unbounded following) row_fn,/*將當前行到最後一行的所有rn值相加*/

sum(rn) over(partition by v order by rn range between current row and unbounded following) range_fn,/*將當前行到最後一行的所有rn值相加*/

sum(rn) over(partition by v order by rn rows between 1 preceding and 1 following) row_p1_f1, /*將當前行、前一行、最後一行的所有rn值相加*/

--sum(rn) over(partition by v order by rn range between 1 preceding and 1 following) range_p1_f1, /*將當前行、前一行、最後一行的所有rn值相加*/

sum(rn) over(partition by v order by rn rows between unbounded preceding and unbounded following) row_all, /*將所有v值相同的行相加*/

sum(rn) over(partition by v order by rn range between unbounded preceding and unbounded following) range_all, /*將所有v值相同的行相加*/

sum(rn) over(partition by v ) s_all /*將所有v值相同的行相加*/

from hptestover

order by v,rn;

/*清理測試表*/

drop table hptestover;

/***Oracle*/

/* 語法：

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會認為結束位置是當前行，而開始位置是表達式指定的行；

<window> ::= { UNBOUNDED PRECEDING | CURRENT ROW | value_expr {PRECEDING|FOLLOWING} }/<window>

<window> ::= { UNBOUNDED FOLLOWING | CURRENT ROW | <value> {PRECEDING|FOLLOWING} }/<value>/<window>

<window> ::= { UNBOUNDED PRECEDING | CURRENT ROW | <value> PRECEDING }/<value>/<window>

<value> ::= {columns|nonanalytic functions|function expressions|expressions involving any of these}/<value>

ROWS和RANGE關鍵字為每行定義一個窗口(物理或邏輯的行的集合)，用於計算函數的結果，分析函數可以應用到在窗口中的所有行，按自上而下的順序計算函數值。

ROWS 標識出窗口的物理單元(rows）。 ROWS specifies the window in physical units (rows)

RANGE 通過邏輯偏移量標識出窗口。RANGE specifies the window as a logical offset. 指的是order by的值的差額的範圍

-- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 可簡寫為 RANGE UNBOUNDED PRECEDING.

-- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 可簡寫為 RANGE UNBOUNDED PRECEDING.

-- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

*/

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;