即由以下三部分组成:
分析函数名 如SUM()、MAX()、MIN()、COUNT()、AVG()等聚集函数以及LEAD()、LAG()行比较函数等
OVER 关键字,表示前面的函数是分析函数,不是普通的集合函数
分析子句 OVER关键字后面括号内的内容
分析子句又由下面三部分组成:
PARTITION BY 分组子句,表示分析函数的计算范围,不同的组互不相干
ORDER BY 排序子句,表示分组后,组内的排序方式
ROWS/RANGE 窗口子句,是在分组(PARTITION BY)后,组内的子分组(也称窗口),
是分析函数的计算范围窗口,而不是PARTITION;
窗口有两种,ROWS 和 RANGE
下面分析 ROWS 与 RANGE 窗口子句的用法,先看下面例子:
- SQL> edit
- 已写入 file afiedt.buf
-
- WITH t AS
- (
- SELECT (CASE
- WHEN LEVEL IN (1, 2) THEN
- 1
- WHEN LEVEL IN (4, 5) THEN
- 6
- ELSE
- LEVEL
- END) ID
- FROM dual
- CONNECT BY LEVEL < 10)
- SELECT id,
- SUM(ID)OVER(ORDER BY ID) AS default_sum,
- SUM(ID)OVER(ORDER BY IDRANGE BETWEEN unbounded preceding AND CURRENT ROW)
- AS range_unbound_sum,
- SUM(ID)OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW)
- AS rows_unbound_sum,
- SUM(ID)OVER(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following)
- AS range_sum,
- SUM(ID)OVER(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following)
- AS rows_sum
- FROM t
- SQL> /
-
- ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_SUM ROWS_SUM
- ———- ———– —————– —————- ———- ———-
-
- 1 2 2 1 5 5
- 1 2 2 2 5 11
- 3 5 5 5 3 16
- 6 23 23 11 33 21
- 6 23 23 17 33 25
- 6 23 23 23 33 27
- 7 30 30 30 42 30
- 8 38 38 38 24 24
- 9 4
从上面的例子可知:
1. 窗口子句必须和 ORDER BY 子句同时使用,且如果指定了 ORDER BY 子句未指定窗口子句,则默认为
RANGE BETWEEN unbounded preceding AND CURRENT ROW,
如上例结果集中的defult_sum等于range_unbound_sum
2.如果分析函数没有指定 ORDER BY子句,也就不存在 ROWS/RANGE 窗口的计算3.RANGE 是逻辑窗口,是指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内,
如上例中range_sum(即range 1 preceing and 2 following)例的分析结果:
当id=1时,是sum为1-1<=id<=1+2 的和,即sum=1+1+3=5(取id为1,1,3)
当id=3时,是sum为3-1<=id<=3+2 的和,即sum=3(取id为3)
当id=6时,是sum为6-1<=id<=6+2 的和,即sum=6+6+6+7+8=33(取id为6,6,6,7,8)
4.ROWS 是物理窗口,即根据 ORDER BY 子句排序后,取的前 N行及后N行的数据计算(与当前行的值无关,只与排序后的
行号相关),如上例中rows_sum例结果,是取前1行和后2行数据的求和,分析上例rows_sum的结果:
当id=1(第一个1时)时,前一行没数,后二行分别是1和3,sum=1+1+3=5
当id=3时,前一行id=1,后二行id都为6,则sum=1+3+6+6=16
以此类推下去,结果如上例所示注:行比较分析函数 LEAD 和 LAG 无window(窗口)子句。
—————————————————————————-
一、 ROWS 的具体用法
SET LINESIZE 200;
SET PAGESIZE 1000;
SELECT
dept_id,hire_date,salary,
–窗口范围为该分区的第一行和该分区的最后一行
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS varchar2(6)) SUM_1,
–窗口范围为该分区的第一行和当前行
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS varchar2(6)) SUM_2,
–窗口范围为该分区的第一行和当前行的前一行,统计的是第一行和当前行的前一行的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING
AND 1/*value_expr*/ PRECEDING) AS varchar2(6)) SUM_3,
–窗口范围为该分区的第一行和当前行的后一行,统计的是第一行和当前行的后一行的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING
AND 1/*value_expr*/ FOLLOWING) AS varchar2(6)) SUM_4,
–窗口范围为当前行和该分区的最后一行,统计的是大于等于当前记录hire_date之后的所有薪资
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING)AS varchar2(6)) SUM_5,
–窗口范围只是当前行,所以与当前行薪资一样
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW
AND CURRENT ROW) AS varchar2(6)) SUM_6,
–窗口范围为当前行和当前行的后一行,统计的是当前行和后一行的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN CURRENT ROW
AND 1/*value_expr*/ FOLLOWING) AS varchar2(6)) SUM_7,
–窗口范围为当前行的前一行和该分区的最后一行,统计的是当前行的前一行和该分区最后一行的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ PRECEDING
AND UNBOUNDED FOLLOWING) AS varchar2(6))SUM_8,
–窗口范围为当前行的前一行和当前行,统计的是当前行的前一行和当前行的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ PRECEDING
AND CURRENT ROW)AS varchar2(6)) SUM_9,
–窗口范围为该分区的当前行前value_expr1到当前行前value_expr2的累计,本例为本行前2行和前1行的累计
–强调value_expr1 >= value_expr2
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 2/*value_expr1*/ PRECEDING
AND 1/*value_expr2*/ PRECEDING) AS varchar2(6)) SUM_10,
–窗口范围为该分区的本行前value_expr1到本行后value_expr2的累计,本例为本行前1行和后2行的累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr1*/ PRECEDING
AND 2/*value_expr2*/ FOLLOWING) AS varchar2(6)) SUM_11,
–窗口范围为本行后一行和本分区最后一行,统计的是本行后一行和本区最后一行的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr*/ FOLLOWING
AND UNBOUNDED FOLLOWING) AS varchar2(6)) SUM_12,
–窗口范围为该分区的本行后value_expr1行到本行后value_expr2的累计,本例为本行后1行到后2行的累计
–强调value_expr1 <= value_expr2
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN 1/*value_expr1*/ FOLLOWING
AND 2/*value_expr2*/ FOLLOWING) as varchar2(6))SUM_13,
–窗口范围为该分区的第一行,结束行默认为本行,同sum_2
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS UNBOUNDED PRECEDING)
AS varchar2(6)) SUM_14,
–窗口范围仅为当前行,所以与本行薪资一样,同sum_6
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS CURRENT ROW)
AS varchar2(6)) SUM_15,
–窗口范围为该分区的第一行和本行前一行,统计的是本行前一行和本行的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date ROWS 1/*value_expr*/ PRECEDING)
AS varchar2(6)) SUM_16
FROM employeeinfo;
—————————————————————————-
二、 RANGE 的具体用法
SELECT
dept_id,hire_date,salary,
–窗口范围为该分区的第一行和该分区的最后一行,在非条件表达式中等同于ROWS
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)AS varchar2(6)) SUM_1,
–窗口范围为该分区的第一行和本行,在非条件表达式中等同于ROWS
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS varchar2(6)) SUM_2,
–窗口范围为该分区内小于本记录hire_date-365天的所有的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING
AND 365/*value_expr*/ PRECEDING) AS varchar2(6)) SUM_3,
–窗口范围为该分区内小于本记录hire_date+365天的所有的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING
AND 365/*value_expr*/ FOLLOWING) AS varchar2(6)) SUM_4,
–窗口范围为本行和该分区的最后一行,统计的是大于等于本记录hire_date之后的所有薪资,在非条件表达式中等同于ROWS
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS varchar2(6)) SUM_5,
–窗口范围只是本行,所以与本行薪资一样
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN CURRENT ROW
AND CURRENT ROW) AS varchar2(6)) SUM_6,
–窗口范围为该分区内本记录起和小于本记录hire_date-365天的所有的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN CURRENT ROW
AND 365/*value_expr*/ FOLLOWING) AS varchar2(6)) SUM_7,
–窗口范围为该分区内本记录起和小于本记录hire_date+365天的所有的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr*/ PRECEDING
AND CURRENT ROW) AS varchar2(6)) SUM_8,
–窗口范围为该分区内大于本记录hire_date-365天,并且截止到当前记录的所有的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr*/ PRECEDING
AND CURRENT ROW) AS varchar2(6)) SUM_9,
–窗口范围为该分区的本行current_value-value_expr1到本行current_value-value_expr2之间的累计
–强调value_expr1>=value_expr2
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr1*/ PRECEDING
AND 30/*value_expr2*/ PRECEDING) AS varchar2(6)) SUM_10,
–窗口范围为该分区的本行current_value-value_expr1到本行current_value+value_expr2之间的累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr1*/ PRECEDING
AND 30/*value_expr2*/ FOLLOWING) AS varchar2(6)) SUM_11,
–窗口范围为该分区内大于本记录hire_date+365天的所有的薪资累计
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 365/*value_expr*/ FOLLOWING
AND UNBOUNDED FOLLOWING) AS varchar2(6)) SUM_12,
–窗口范围为该分区的本行current_value+value_expr1到本行current_value+value_expr2的累计
–强调value_expr1<=value_expr2
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN 30/*value_expr1*/ FOLLOWING
AND 365/*value_expr2*/ FOLLOWING)AS varchar2(6)) SUM_13,
–窗口范围为该分区的第一行,结束行默认为本行,与sum_salary_part_order,sum_2等同,在非条件表达式中等同于ROWS
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE UNBOUNDED PRECEDING)
AS varchar2(6)) SUM_14,
–窗口范围为仅为当前行,所有与本行薪资一样,与sum_6一样,在非条件表达式的中等同于ROWS
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE CURRENT ROW)
AS varchar2(6)) SUM_15,
–窗口范围为该分区内大于本记录hire_date-365天,并且截止到当前记录的所有的薪资累计,与sum_9等价
CAST(SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE 365/*value_expr*/ PRECEDING)
AS varchar2(6)) SUM_16
FROM employeeinfo;
附:另一种写法
–统计当天及前30天的薪资累计
CAST(SUM(salary) OVER(PARTTION BY dept_id ORDER BY hire_date RANGE INTERVAL ‘30’ day PRECEDING)
AS varchar2(6)) SUM_17,
–统计当天及后30天的薪资累计
CAST(SUM(salary) OVER(PARTTION BY dept_id ORDER BY hire_date RANGE INTERVAL ‘30’ day FOLLOWING)
AS varchar2(6)) SUM_18
本文使用的脚本在 http://pan.baidu.com/s/1GpFH0