.Net程序员学用Oracle系列(22)：分析函数(OVER)

发布时间：2017-06-20 16:25:50.087作者：IT网络文摘

1、函数语法

2、函数用法

3、补充案例
4、总结

分析函数用于计算一组行的聚合值，由分析函数语法定义的行集叫做窗口，窗口的大小由物理行数或逻辑间隔决定。每一行上的滑动窗口都是已定义的，窗口决定了对某一行而言进行计算的行的范围。从查询结果和查询语法来看，分析函数与聚合函数主要有 3 点不同：

1、聚合函数每组只返回一条记录，而分析函数每组可返回多条记录。
2、在单个查询中，多个聚合函数只能作用于同一个分组，而不同分析函数可作用于不同分组。
3、在分组查询中，查询列表只能出现聚合函数或 GROUP BY 子句中出现过的字段或常量或表达式，而分析函数没有这些限制。

1、函数语法

我理解的分析函数无非也就是由一或多个普通的函数或子句按照一定的规则构成的复杂数据分析函数。换言之，分析函数内部有些函数或子句的语法规则之前已经讲述过了，这里将不再赘述，本节将着重讲述分析函数所特有的一些函数或子句的语法。

1.1、语法概述

有很多网站都把分析函数称之为窗口函数，又称 OVER 为开窗函数，还有些似是而非的概念我本人也不甚了解。我特地查阅过《Oracle Database SQL Reference 10g Release 2》Analytic Functions，语法如下：

analytic_function([arguments]) OVER([analytic_clause])

参数说明：

analytic_function：分析函数的名称。Oracle 10g R2 中内置了 30 个分析函数。
arguments：分析函数的参数。内置的分析函数一般带 0~3 个参数，参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。
OVER：用来标识函数是个分析函数。对于即可作为聚合函数又可作为分析函数的函数，Oracle 无法识别，必须用 OVER 来标识此函数为分析函数。但并不是说只可作为分析函数的函数就无需标识，是分析函数就必须用 OVER 关键字来标识。另外 OVER 后面的一对小括号也是必须的，即便括号中什么都不包含。
analytic_clause：用来确定分析规则。语法是：[query_partition_clause][order_by_clause[windowing_clause]]，语法中的 3 个子句都是可选的，但 windowing_clause 必须依赖于 order_by_clause 而存在。

query_partition_clause：分组子句，用于确定窗口，与 GROUP BY 语句的语法类似。
order_by_clause：排序子句，用于确定窗口规则，与 ORDER BY 语句的语法类似。
windowing_clause：窗口范围子句，用于确定分组中当前的计算范围。

1.2、窗口详解

在《Oracle Database SQL Reference 10g Release 2》中给出 windowing_clause 语法示意图如下：

ROWS：用于指定窗口由物理行构成，即符合指定的数据行的范围。
RANGE：用于指定窗口由逻辑偏移量构成，即符合指定的逻辑条件的范围。
BETWEEN...AND：用于指定窗口的起始点和终结点。
UNBOUNDED PRECEDING：用于指明窗口开始于分组的第一行。
CURRENT ROW：作为起始点，指明窗口开始于当前行或当前行的值；作为终结点，指明窗口结束于当前行或当前行的值。
UNBOUNDED FOLLOWING：用于指明窗口结束于分组的最后一行。
value_expr：物理或逻辑偏移量的表达式。

无论是窗口大小是由物理行数(ROWS)确定，还是由逻辑间隔(RANGE)确定，在分组中窗口总是从上往下滑动。窗口范围可以由 BETWEEN...AND 限定，也可以不用 BETWEEN...AND，不用则表示窗口到当前行或值结束。

1.2.1、ROWS 窗口

ROWS 窗口是由分组排序后分组中若干连续的行所构成的窗口。在 ROWS 窗口中 value_expr 是物理偏移量，它必须是常量或值为非负数的表达式。合法的 ROWS 窗口范围定义共有 16 种，列举如下：

1、窗口开始于分组第一行，结束于分组最后一行。

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

2、窗口开始于分组第一行，结束于当前行。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS UNBOUNDED PRECEDING

3、窗口开始于分组第一行，结束于当前行的前 value_expr 行。

ROWS BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDING

4、窗口开始于分组第一行，结束于当前行的后 value_expr 行。

ROWS BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWING

5、窗口开始于当前行，结束于分组最后一行。

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

6、窗口开始于当前行，结束于当前行。

ROWS BETWEEN CURRENT ROW AND CURRENT ROW
ROWS CURRENT ROW

7、窗口开始于当前行，结束于当前行的前 value_expr 行。

ROWS BETWEEN CURRENT ROW AND value_expr PRECEDING

8、窗口开始于当前行，结束于当前行的后 value_expr 行。

ROWS BETWEEN CURRENT ROW AND value_expr FOLLOWING

9、窗口开始于当前行的前 value_expr 行，结束于当前行。

ROWS BETWEEN value_expr PRECEDING AND CURRENT ROW
ROWS value_expr PRECEDING

10、窗口开始于当前行的前 value_expr1 行，结束于当前行的前 value_expr2 行。前提是要满足 value_expr1 >= value_expr2。

ROWS BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDING

11、窗口开始于当前行的前 value_expr1 行，结束于当前行的后 value_expr2 行。

ROWS BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWING

12、窗口开始于当前行的后 value_expr 行，结束于分组最后一行。

ROWS BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWING

13、窗口开始于当前行的后 value_expr1 行，结束于当前行的后 value_expr2 行。前提是要满足 value_expr1 <= value_expr2。

ROWS BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWING

1.2.2、RANGE 窗口

RANGE 窗口是由分组排序后分组中满足指定逻辑条件的行所构成的窗口。在 RANGE 窗口中 value_expr 为逻辑偏移量，它必须是常量或值为非负数的表达式或间隔值。当 value_expr 值是一个数字时，排序字段必须是数字或日期类型；当 value_expr 值是一个间隔值时，排序字段必须是一个日期类型。合法的 RANGE 窗口范围定义也有 16 种，列举如下：

1、升序排序时，表达式介于第一行的值和最后一行的值之间；降序排序时，表达式介于最后一行的值和第一行的值之间。

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

2、升序排序时，表达式介于第一行的值和当前行的值之间；降序排序时，表达式介于当前行的值和第一行的值之间。若不指定窗口，则默认为该窗口。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWRANGE UNBOUNDED PRECEDING

3、升序排序时，表达式介于第一行的值和当前行的值 -value_expr 之间；降序排序时，表达式介于当前行的值 -value_expr 和第一行的值之间。

RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDING

4、升序排序时，表达式介于第一行的值和当前行的值 +value_expr 之间；降序排序时，表达式介于当前行的值 +value_expr 和第一行的值之间。

RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWING

5、升序排序时，表达式介于当前行的值和最后一行的值之间；降序排序时，表达式介于最后一行的值和当前行的值之间。

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

6、表达式等于当前行的值。

RANGE BETWEEN CURRENT ROW AND CURRENT ROWRANGE CURRENT ROW

7、表达式介于当前行的值和当前行的值 +value_expr 之间。

RANGE BETWEEN CURRENT ROW AND value_expr FOLLOWING

8、表达式介于当前行的值 -value_expr 和最后一行的值之间。

RANGE BETWEEN value_expr PRECEDING AND UNBOUNDED FOLLOWING

9、表达式介于当前行的值 -value_expr 和当前行的值之间。

RANGE BETWEEN value_expr PRECEDING AND CURRENT ROWRANGE value_expr PRECEDING

10、表达式介于当前行的值 -value_expr1 和当前行的值 -value_expr2 之间。前提是要满足 value_expr1 >= value_expr2。

RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDING

11、表达式介于当前行的值 -value_expr1 和当前行的值 +value_expr2 之间。

RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWING

12、升序排序时，表达式介于当前行的值 +value_expr 和最后一行的值之间；降序排序时，表达式介于最后一行的值和当前行的值 +value_expr 之间。

RANGE BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWING

13、表达式介于当前行的值 +value_expr1 和当前行的值 +value_expr2 之间。前提是要满足 value_expr1 <= value_expr2。

RANGE BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWING

2、函数用法

Oracle 10g R2 中内置的分析函数有 38 个，本节按函数用途将其分为 4 类，分别是：普通统计函数、数据排序函数、数据分布函数及统计分析函数，并逐一给出函数释义和用法示例。

2.1、普通统计类函数

MAX：对组内的数据窗口中的字段或表达式求最大值。
MIN：对组内的数据窗口中的字段或表达式求最小值。
AVG：对组内的数据窗口中的字段或表达式求平均值。
SUM：对组内的数据窗口中的字段或表达式求合计值。
COUNT：对组内的数据窗口中的数据行进行累计计数。

示例 1：

SELECT t.staff_name,t.dept_code,t.base_salary,t.post_salary,  MAX(t.post_salary) OVER(PARTITION BY t.dept_code) max_salary,  -- 部门最高岗位工资
  MIN(t.post_salary) OVER(PARTITION BY t.dept_code) min_salary,  -- 部门最低岗位工资
  AVG(t.post_salary) OVER(PARTITION BY t.dept_code) avg_salary,  -- 部门平均岗位工资
  SUM(t.post_salary) OVER(PARTITION BY t.dept_code) sum_salary,  -- 部门岗位工资之和
  COUNT(t.post_salary) OVER(PARTITION BY t.dept_code) cnt_salary -- 部门工资份数FROM demo.t_staff t ORDER BY t.dept_code,t.post_salary;

示例 2：

SELECT t.staff_name,t.dept_code,t.base_salary,t.post_salary,  -- 部门内截至当前行的最高岗位工资
  MAX(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS UNBOUNDED PRECEDING) max_salary,  -- 部门内当前行至最后一行的最低岗位工资
  MIN(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS CURRENT ROW) min_salary,  -- 部门内第一行至前一行的平均岗位工资
  AVG(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) avg_salary,  -- 部门内第一行至后一行的岗位工资之和
  SUM(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) sum_salary,  -- 部门内截至当前行的工资份数
  COUNT(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS UNBOUNDED PRECEDING) cnt_salaryFROM demo.t_staff t ORDER BY t.dept_code,t.post_salary;

2.2、数据排序类函数

RANK()：根据 ORDER BY 的排序结果，计算组间相对位置，可能跳号。组内数据按 ORDER BY 子句排序，每一行都会得到一个序号，从而形成一个序列，该序列从 1 开始，往后 ORDER BY 表达式的值每发生一次变化，该序列也随之加 1。值相同的行将得到相同的序号（NULL 被认为是相等的），后面行的序号将发生跳跃。如前两行的序号为 1，则没有序号 2，第 3 行将得到序号 3。
DENSE_RANK()：根据 ORDER BY 的排序结果，计算组间相对位置，不会跳号。DENSE_RANK 与 RANK 类似，也会得到一个首项为 1、公差为 1 的等差序列，值相同的行序号也相同（也认为 NULL 是相等的），但序号不会跳跃。如前两行的序号为 1，第 3 行将得到序号 2。
FIRST_VALUE(expression)：返回组内数据窗口的第一个值。
LAST_VALUE(expression)：返回组内数据窗口的最后一个值。
LAG(expression [, offset [, default] ])：可以访问结果集中当前行之前的行而不用进行自连接，这样就可以从组中与当前行一起选取当前行之前的行。offset 是一个正整数，默认值为 1，若不指定 offset，或 offset 的值超出窗口范围，就启用默认值。
LEAD(expression [, offset [, default] ])：与 LAG 相反，LEAD 可以访问结果集中当前行之后的行而不用进行自连接，这样就可以从组中与当前行一起选取当前行之后的行。offset 是一个正整数，默认值为 1，若不指定 offset，或 offset 的值超出窗口范围，就启用默认值。
ROW_NUMBER：返回有序组中一行的偏移量，按特定的排序分配行号。
FIRST：从紧凑排序后的结果集中筛选出排在最前面的一个值的行（可能是多行，因为值可能相等）。
LAST：从紧凑排序后的结果集中筛选出排在最后面的一个值的行（可能是多行，因为值可能相等）。

示例 1：

SELECT t.dept_code,t.staff_name,t.post_salary,  RANK() OVER(ORDER BY t.dept_code,t.post_salary) rank,  DENSE_RANK() OVER(ORDER BY t.dept_code,t.post_salary) dense_rank,  FIRST_VALUE(t.post_salary) OVER(ORDER BY t.dept_code,t.post_salary) fist_value,  LAST_VALUE(t.post_salary) OVER(ORDER BY t.dept_code,t.post_salary) last_value,
  LAG(t.post_salary,1,0) OVER(ORDER BY t.dept_code,t.post_salary) lag_value,  LEAD(t.post_salary,1,0) OVER(ORDER BY t.dept_code,t.post_salary) lead_value,
  ROW_NUMBER() OVER(ORDER BY t.dept_code,t.post_salary) row_numberFROM demo.t_staff t ORDER BY t.dept_code,t.post_salary;

示例 2：

SELECT t.dept_code,t.staff_name,t.birthday,t.post_salary,  RANK() OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) rank,  DENSE_RANK() OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) dense_rank,  FIRST_VALUE(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) fist_value,  LAST_VALUE(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) last_value,
  LAG(t.post_salary,1,0) OVER(PARTITION BY t.dept_code ORDER BY t.dept_code,t.post_salary) lag_value,  LEAD(t.post_salary,1,0) OVER(PARTITION BY t.dept_code ORDER BY t.dept_code,t.post_salary) lead_value,
  ROW_NUMBER() OVER(PARTITION BY t.dept_code ORDER BY t.dept_code,t.post_salary) row_numberFROM demo.t_staff t ORDER BY t.dept_code,t.post_salary;

示例 3：

SELECT t.staff_name,t.gender,t.dept_code,t.post_salary,  MIN(t.post_salary) KEEP(DENSE_RANK FIRST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) first_value1,  MAX(t.post_salary) KEEP(DENSE_RANK FIRST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) first_value2,  MIN(t.post_salary) KEEP(DENSE_RANK LAST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) last_value1,  MAX(t.post_salary) KEEP(DENSE_RANK LAST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) last_value2FROM demo.t_staff t ORDER BY t.gender,t.dept_code;

2.3、数据分布类函数

RATIO_TO_REPORT(expr)：计算当前行的值与组中所有行的值之和的比率。
NTILE(expr)：将一组有序的数据集分为若干桶，并为每一行分配相应的桶编号。expr 必须是能被解析成正整数的值或表达式，如果带小数，小数部分将被截取。桶编号从 1 开始到 TRUNC(expr)，每桶的数据行数最多相差 1。如果行数不能被桶数整除，那么靠前的桶将优先被填充，靠后的桶则会少一行数据。例如 expr=3，行数=16，则桶编号为 1 的有 6 行，桶编号为 2 或 3 的有 5 行。
CUME_DIST()：计算一组值的累计分布，并返回大于 0、小于或等于 1 的数，该数表示该行在组中的相对位置，值相等的相邻行会得到相同的累计分布值。
PERCENT_RANK()：与 CUME_DIST 函数类似，每行的值等于该行的行号先减去 1，再除以组中总行数减去 1，因此返回值总是大于或等于 0、小于或等于 1。
PERCENTILE_DISC(expr)：返回一个与分布

本文链接：http://www.cnblogs.com/hanzongze/p/Oracle-Over.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！

http://www.cnblogs.com/hanzongze/p/Oracle-Over.html

分类导航