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