分析函数语法解析

分析函数语法分析函数语法解解析

1 分析函数整体语法

分析函数的语法如下：

analytic_function([ arguments ]) OVER (analytic_clause)

这里：

Analytic_function 是分析函数的名称；

arguments 是分析函数的参数；

over 用来标识函数是一个分析函数；

analytic_clause 用来确定分析函数的操作规则。包括query_partition_clause 、order_by_clause 和windowing_clause 三个子句。

2 Analytic_function

分析函数的名称。Oracle10gR2带的内置分析函数有39个；对于用户自定义的分析函数，分析函数名称需要满足标识符规则。

3 Arguments

分析函数所带参数，内置分析函数一般带0-3个参数。参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。Oracle 根据最高数字优先级别确定函数参数，并且隐式地将需要处理的参数转换为数字类型。

用户自定义分析函数的参数，可以根据实际情况使用。

4 Over

用以标识函数是一个分析函数，对于既可作为聚集函数又可作为分析函数的函数，Oracle 无法识别，必须用over 来标识此函数为分析函数。也不是说只可用作分析函数的函数就可以省略该关键字，over 关键字是必须的，是分析函数就必须使用。

Over 后面的()中是analytic_clause ，即使analytic_clause 的三个部分全不使用，()也必不可省，否则Oracle 因不能识别函数为分析函数而报错。

5 Analytic_clause

Analytic_clause 的语法如下：

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

这里：

query_partition_clause 是查询分组子句；

order_by_clause 是分组排序子句；

windowing_clause 是窗口范围子句。

分析函数在查询结果集确定之后才开始进行计算，Analytic_clause就是用来定义函数怎样对查询结果集进行分组计算的。

根据Oracle对查询和分析函数的处理方法可知，在select和order by子句中都可以使用分析函数。

query_partition_by、order_by_clause和windowing_clause三个子句是可选的，将三个子句分别简记为p，o，w。

合法的组合方式有如下6种：

1). Pow

(query_partition_clause order_by_clause windowing_clause)

分组，排序，定义窗口范围

2). Po

(query_partition_clause order_by_clause)

分组，排序，窗口默认为range between unbounded preceding and current row

3). P

(query_partition_clause)

分组，不排序，没有窗口

4). Ow

(order_by_clause windowing_clause)

分组为整个查询结果集，排序，定义窗口范围

5). O

(order_by_clause)

分组为整个查询结果集，排序，窗口默认为range between unbounded preceding and current row 6). Null

()

分组为整个查询结果集，不排序，没有窗口

因为只有存在order_by_clause，才能有windowing_clause，故不存在如下两种形式的组合：pw(query_partition_clause windowing_clause)

w(windowing_clause)

总结：

1). 对于是否存在order_by_clause，分析函数可以分为两类，含有order_by_clause的一般称为windowing function，不含的称为reporting function。

2). Windowing function，对查询结果集进行分组，排序，根据窗口范围计算分组中每一行的函数结果。

3). Reporting function，对查询结果集进行分组，不排序，窗口范围为整个分组，在每一个分组内，计算整个分组的函数值，再将函数值分别赋给分组内的每一行。

注意事项：

1)．Analytic_clause内不能包含任何分析函数。

2). 用户自定义分析函数和内置函数分析函数都可以使用over (analytic_clause)。

5.1 Query_partition_clause

query_partition_clause的语法如下：

PARTITION BY value_expr[, value_expr ]...

这里：

partition by是关键字，表示要将查询结果集分组；

value_expr是分组表达式。

Partition by子句根据一个或多个value_expr将查询结果集分成若干组。分组的原理与group by分组的原理类似，将value_expr相同的行集中在一起。

若不使用该子句，那末函数将整个查询结果集作为一个分组。

value_expr可以为常量、表列、非分析函数、函数表达式，或者前面这些元素的任意组合表达式。

5.2 Order_by_clause

Order_by_clause的语法如下：

ORDER BY expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

[, expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]...

这里：

order by是关键字，表示要将分组排序；

expr是排序表达式；

asc|desc和nulls first|null last是排序的附加规则。

Order_by_clause根据一个或多个expr对分组进行排序。

Expr只能为表达式，不能为位置编号或列别名。

注意事项：

1). 当窗口范围为

Range between unbounded preceding and current row 或

Range between current row and unbounded following 时，

可以在order_by_clause中使用多个expr对分组进行排序。其他range窗口范围只能使用一个expr。使用rows定义窗口范围无此限制。

2). asc|desc 指定排序是按expr升序还是降序排序，若不指定则默认按asc进行升序排序。

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

5.3 Windowing_clause

Windowing_clause的语法如下：

{ ROWS | RANGE }

{ BETWEEN