前言:
现在你们对“oracle range between”大概比较关注,各位老铁们都想要分析一些“oracle range between”的相关资讯。那么小编也在网摘上收集了一些关于“oracle range between””的相关知识,希望兄弟们能喜欢,兄弟们快快来学习一下吧!/***Sql Server*/
/*
OVER ( [ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::= PARTITION BY value_expression , ... [ n ]
--PARTITION BY将查询结果集分为多个分区,开窗函数分别应用于每个分区,并为每个分区重新启动计算;
-- 如果未指定 PARTITION BY,则此函数将查询结果集的所有行视为单个组。
-- value_expressio:指定行集按其分区的列。value_expression 只能引用可供 FROM 子句使用的列。
-- value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量
<ORDER BY clause> ::= ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ]
--ORDER BY定义结果集的每个分区中行的逻辑顺序。也就是说,它指定按其执行开窗函数计算的逻辑顺序。
-- 如果未指定 ROWS/RANGE,但指定了 ORDER BY,则将 RANGE UNBOUNDED PRECEDING AND CURRENT ROW 用作窗口的默认值。
-- order_by_expression指定用于进行排序的列或表达式,只能引用可供 FROM 子句使用的列,不能将整数指定为表示列名或别名。
<ROW or RANGE clause> ::= { ROWS | RANGE } <window frame extent>
<window frame extent> ::= { <window frame preceding> | <window frame between> }
<window frame between> ::= BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::= { <window frame preceding> | <window frame following> }
<window frame preceding> ::= { UNBOUNDED PRECEDING | <unsigned_value_specification> PRECEDING | CURRENT ROW }
<window frame following> ::= { UNBOUNDED FOLLOWING | <unsigned_value_specification> FOLLOWING | CURRENT ROW }
<unsigned value specification> ::= { <unsigned integer literal> }
--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_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会认为结束位置是当前行,而开始位置是表达式指定的行;
<window frame bound1> ::= { UNBOUNDED PRECEDING | CURRENT ROW | value_expr {PRECEDING|FOLLOWING} }
<window frame bound2> ::= { UNBOUNDED FOLLOWING | CURRENT ROW | <value_expr> {PRECEDING|FOLLOWING} }
<window frame preceding> ::= { UNBOUNDED PRECEDING | CURRENT ROW | <value_expr> PRECEDING }
<value_expr> ::= {columns|nonanalytic functions|function expressions|expressions involving any of these}
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;
标签: #oracle range between