ORACLE之 分析函数（一）

ORACLE 分析函数（一）

定义
分析函数通过将多行分组后，在计算这些分组的值。它们与聚合函数不同之处 在于能够对每一分组返回多行值；而聚合函数对于每个组只返回一行。
分析函数根据 analytic_clause（分析子句）将行分组，一个分组称为一个窗口。每一行都对应有一个在行上滑动的窗口，该窗口确定当行的计算范围。窗口大小可以用多个物理进行度量，也可以使用逻辑区间进行度量，比如时间。
分析函数是查询中除需要在最终处理的 order by 子句中之外最后执行的操作。所有连接和所有 where 、group by 和 having 子句都要在处理分析函数之前进行计算。因此分析函数只能用于选择列或 order by 子句中。

 

作用
分析函数通常用于计算数据累计值、数据移动值、数据中间值和输出集合报表。

 

语法
analytic_function_name( [ argument | expression ] )
OVER ( analytic_clause )
其中 analytic_clause 包括如下：
[ query_partition_clause ]
[ order_by_clause [ windowing_clause ] ]

 

KEYWORDS
analytic_function_name ( argument )
指定分析函数的名称。
分析函数可取0-3个参数，参数可以是表达式，可以是任何数字类型或是可以隐式转换为数字类型的数据类型。Oracle 根据最高数字优先级别确定函数参数，并且隐式地将需要处理的参数转换为数字类型。函数的返回类型也为数字类型，除非此函数另有说明。

 

over analytic_clause
over 是一个关键字。用于标识分析函数，否则查询分析器不能区别是 聚合函数 还是 分析函数。
analytic_clause 用以指明函数操作的是一个查询结果集。也就是说分析函数是在 from、where、group by 和 having 子句之后才开始进行计算的。因此在选择列或 order by 子句中可以使用分析函数。为了过滤分析函数计算的查询结果，可以将它作为子查询嵌套在外部查询中，然后在外部查询中过滤其查询结果。
注：
analytic_clause 中不能包含其他任何分析函数。也就是说，分析函数不能嵌套。但是可以在一个子查询中应用分析函数，并且通过它计算另外的分析函数。

 

query_partition_clause
PARTITION BY
{ value_expr... [, value_expr ]  | ( value_expr [, value_expr ]... ) }
partition by 子句根据一个或多个 value_expr 将查询结果集分成若干组。如不使用该子句，那么函数将查询结果集的所有行当作一个组。
注：
1、在分析函数中使用 query_partition_clause，应该使用语法图中上分支中的语法（不带圆括号）。在model查询（位于 model_column_clauses 中）或被分隔的外部连接（位于outer_join_clause 中）中使用该子句，应该使用语法图中下分支中的语法（带有圆括号 ）。
2、在同一查询中可以使用多个分析函数，它们可以有相同或不同的 partition by 键值。
3、假如查询对象具有并行特性，并且分析函数中包含 query_partition_clause，那么函数的计算也是并行的。
4、value_expr 的有效值包括：常量、表列、非分析函数、函数表达式或之前元素的任意组合表达式。

 

 

order_by_clause
ORDER [ SIBLINGS ] BY { expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
 [, { expr | position | c_alias }
  [ ASC | DESC ]
  [ NULLS FIRST | NULLS LAST ]
 ]...

order_by_clause 用以指定分组中数据的排序形式。除 PERCENTILE_CONT 和 PERCENTILE_DISC 之外（它们只能取唯一的键值）外的分析函数，分组中可以使用多个键值对值进行排序，每个键值在 value_expr 中定义，并且被排序序列限定。
每个函数内可以指定多个排序表达式。当使用函数给值排名时，尤其显得意义非凡，因为第二个表达式能够解决按照第一个表达式排序后仍然存在相同排名的问题。
只要使用 order_by_clause 后，仍存在值相同的行，则每一行都会返回相同的结果。

asc | desc
指定排序顺序（升序或降序）。缺省值是 asc。

nulls first | nulls last
若返回行包含空值，指定该值应该出现在排序序列的开始还是末尾。升序排序的缺省值是 nulls last，降序排序的缺省值是 nulls first。

分析函数总是按 order_by_clause 对行排序。然而分析函数中的 order_by_clause 只对各个分组进行排序，而不能保证查询结果有序。要保证最后的查询结果有序，可以使用查询的 order_by_clause。

 

 

order by 子句的限制：
1、分析函数中的 order_by_clause 必须是一个表达式（expression）。
sibling 关键字在此处是非法的（它仅仅与层次查询有关）；位置（position）和列别名（c_alias）也是非法的。除此之外 order_by_clause 的用法与整个查询或子查询中的相同。
2、当分析函数使用 range 关键字限定窗口时，若使用的是窗口下列两个窗口之一，那么可以在分析函数的 order by 子句中使用多个排序键值。
① range between unbounded perceding and current row
简写成： range unbounded preceding
② range betwwen current row and unbounded following
简写成：range unbounded following
3、若窗口范围由 range 关键字指定的分析函数中指定的不是这两个窗口范围，那么 order by 子句中仅能使用一个排序键值。
4、若分析函数的窗口范围由 row 关键字指定，order by 子句中排序键值的使用没有这个限制。

 

windowing_clause
{ ROWS | RANGE }
{ BETWEEN
 { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } }
   AND
 { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } }
   | { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING }
}

windowing_clause
只有指定 order_by_clause 后才能指定 windowing_clause。有些 range 子句定义的窗口范围只能在 order_by_clause 中指定一个排序表达式。

row | range
row | range 关键字为每一行定义一个窗口。该窗口用于计算函数结果（物理或逻辑的行的集合）。然后对窗口中的每一行应用分析函数。
窗口在查询结果集或分组中从上至下移动。
① row  指定窗口以物理单位（行）构成。
② range  指定窗口以逻辑偏移量构成。
一个带逻辑偏移量的分析函数的返回值总是确定的。除非排序表达式能产生唯一的排序，否则带有物理偏移量的分析函数的返回值可能会产生不确定的结果。为了解决此问题，你可能不得不在 order_by_clause 中指定多个列以获得唯一的排序。

between ... and
between ... and 子句用来指定窗口的起点和终点。第一个表达式定义起点，第二个表达式定义终点。
若不使用 between 而仅指定一个终点，那么 oracle 认为它是起点，终点默认为当前行。

unbounded preceding
unbounded preceding 指定 unbounded preceding 指明窗口开始于分组的第一行。它只用于指定起点而不能用于指定终点。

unbounded following
unbounded following 指定 unbounded following 指明窗口结束于分组的最后一行。它只用于指定终点而不能指定起点。

current row
用作起点： current row 指定窗口开始于当前行或当前值（依赖于是否分别指定 row 或 range）。这种情况下不能为 value_expr preceding。
用作终点： current row 指定窗口结束于当前行或当前值（依赖于是否分别指定 row 或 range）。这种情况下不能为 value_expr following。

range 或 range 中的 value_expr preceding | following
*假如 value_expr following 是起点，那么终点必须是 value_expr following。
*假如 value_expr preceding 是终点，那么起点必须是 value_expr preceding。
注：
1、若要定义一个数字格式的时间间隔的逻辑窗口，那么可能需要用到转换函数。
2、若 windowing_clause 由 rows 指定：
① value_expr 是一个物理偏移量，它必须是一个常量或值为正数值的表达式。
② 若 value_expr 是起点的一部分，那么它必须在终点之前对行求值。
3、若 windowing_clause 由 range 指定：
① value_expr 是一个逻辑偏移量，它必须是一个常量、值为正数值的表达式或时间间隔文字常量。
② 若 value_expr 是起点的一部分，那么它必须在终点之前对行求值。
③ 只能在 order_by_clause 中指定一个表达式。
④ 若 value_expr 求值为一个数字值，那么 order by expr 必须为数字或 date 类型。
⑤ 若 value_expr 求值问一个间隔值，那么 order by expr 必须是一个date 类型。
4、若完全忽略 windowing_clause，那么默认值为 range between unbounded preceding and current row。

 

 

 分析函数通常用于数据仓库环境中。下列是分析函数列表，其中带星号（*）的函数可以包含 windowing_clause。

*	avg
*	corr
*	covar_pop
*	covar_samp
*	count
	cume_dist
  	dense_rank
  	first
*	first_value
  	lag
  	last
*	last_value
	lead
*	max
*	min
	ntile
	percent_rank
	percentile_cont
	percentile_disc
	rank
	ratio_to_report
*	regr( Linear Regression ) Functions
	row_number
*	stddev
* 	stddev_pop
*	stddev_samp
*	sum
*	var_pop
*	var_samp
*	variance