ORACLE官方SQL语言参考之解析函数运用详解

解析函数

分析函数根据一组行计算聚合值。它们与聚合函数不同, 因为它们为每个组返回多个行。行组称为窗口, 由analytic_clause定义。对于每一行, 都定义了行的滑动窗口。该窗口确定用于执行当前行计算的行的范围。窗口大小可以基于物理行数或逻辑间隔 (如时间)。

分析函数是在查询中执行的最后一组操作, 除了最后的orderbyby 子句。在处理分析函数之前, 所有联接和所有havingbywhere、group和子句都已完成。因此, 解析函数只能出现在选择列表或orderby子句中。

分析函数通常用于计算累计、移动、居中和报告聚合。

analytic_function::=

插图 "analytic_function" 的说明

analytic_clause::=

插图 "analytic_clause" 的说明

query_partition_clause::=

插图 "query_partition_clause" 的说明

order_by_clause::=

插图 "order_by_clause" 的说明

windowing_clause::=

插图 "windowing_clause" 的说明

下面的部分将讨论此语法的语义。

analytic_function

指定解析函数的名称 (请参见语义讨论后的分析函数列表)。

参数

分析函数需要0到3个参数。参数可以是任何数值数据类型或任何可隐式转换为数值数据类型的数字数据类型。oracle 确定具有最高数值优先级的参数, 并将剩余的参数隐式转换为该数据类型。返回类型也是该数据类型, 除非为单个函数另行说明。

另请参见:

关于数字优先级和表 3-10 "隐式转换矩阵"的信息的"数字优先级" 有关隐式转换的更多信息

analytic_clause

使用overanalytic_clause指示函数在查询结果集上运行。此子句在from、where、groupby和having子句之后计算。可以在 select 列表或orderbyby 子句中使用此子句指定分析函数。若要基于解析函数筛选查询结果, 请在父查询中嵌套这些函数, 然后筛选嵌套子查询的结果。

关于analytic_clause的说明:以下说明适用于analytic_clause:

不能通过在analytic_clause的任何部分指定任何解析函数来嵌套解析函数。但是, 您可以在子查询中指定解析函数, 并计算它的另一个解析函数。

您可以使用over用户定义的分析函数和内置的分析函数来指定analytic_clause。请参阅创建函数.

query_partition_clause

使用partitionby子句将查询结果集划分为基于一个或多个value_expr的组。如果省略此子句, 则该函数将查询结果集的所有行视为单个组。

要在解析函数中使用query_partition_clause, 请使用语法的上部分支 (不带圆括号)。要在模型查询 (在model_column_clauses) 或分区外联接 (在outer_join_clause中) 中使用此子句, 请使用语法的下部分支 (带有圆括号)。

您可以在同一查询中指定多个分析函数, 每个都具有相同或不同的partitionby键。

如果被查询的对象具有并行属性, 并且如果用query_partition_clause指定解析函数, 则函数计算也并行。

value_expr的有效值是常量、列、nonanalytic 函数、函数表达式或涉及其中任何一个的表达式。

order_by_clause

使用order_by_clause指定分区中数据的排序方式。对于所有分析函数, 您可以在多个键上对分区中的值进行排序, 每个项都由value_expr定义, 每个参数由排序序列限定。

在每个函数中, 可以指定多个排序表达式。在使用对值进行排序的函数时, 这样做特别有用, 因为第二个表达式可以解析第一个表达式的相同值之间的关系。

每当order_by_clause为多行生成相同的值时, 该函数的行为如下所示:

cume_dist、dense_rank、ntile、percent_rank和rank返回每个行的相同结果。

row_number分配每行一个不同的值, 即使有一个基于order_by_clause的领带。该值基于处理行的顺序, 如果orderby不保证总订单, 则可能是非确定性的。

对于所有其他解析函数, 结果取决于窗口规范。如果使用range关键字指定一个逻辑窗口, 则该函数将为每个行返回相同的结果。如果使用rows关键字指定了一个物理窗口, 则结果是非确定性的。

对 order by 子句的限制以下限制适用于orderbyby 子句:

在分析函数中使用时,order_by_clause必须采用表达式 (expr)。siblings关键字无效 (仅与分层查询相关)。位置 (position) 和列别名 (c_alias) 也无效。否则, 此order_by_clause与用于排序整个查询或子查询的相同。

如果使用range关键字的解析函数指定了下列任一窗口, 则可以在其orderbyby 子句中使用多个排序键:

rangebetweenunboundedprecedingandcurrentrow。这个短形式的范围无rangeunboundedpreceding.

rangebetweencurrentrowandunboundedfollowing

rangebetweencurrentrowandcurrentrow

rangebetweenunboundedprecedingandunboundedfollowing

除了这四之外, 窗口边界只能在解析函数的orderbyby 子句中有一个排序键。此限制不适用于由row关键字指定的窗口边界。

asc |desc指定排序顺序 (升序或降序)。asc是默认值。

空第一个 |上一个空值指定是否在排序顺序中首先显示包含 null 的返回行。

nullslast是升序顺序的默认值, 而nullsfirst是降序的默认值。

分析函数总是按函数order_by_clause中指定的顺序对行进行操作。但是, 函数的order_by_clause不保证结果的顺序。使用查询的order_by_clause保证最终的结果排序。

另请参见:

order_by_clause选择有关此子句的详细信息

windowing_clause

一些解析函数允许windowing_clause。在本节末尾的分析函数列表中, 允许windowing_clause的函数后跟星号 (*)。

行 |范围这些关键字为每行定义一个窗口 (物理或逻辑行集), 用于计算函数结果。然后将该函数应用于窗口中的所有行。窗口从上到下移动查询结果集或分区。

rows以物理单位 (行) 指定窗口。

range将窗口指定为逻辑偏移量。

除非指定了order_by_clause, 否则不能指定此子句。range子句定义的某些窗口边界允许您在order_by_clause中仅指定一个表达式。请参阅"按条款对订单的限制".

具有逻辑偏移量的解析函数返回的值始终是确定性的。但是, 通过物理偏移量的解析函数返回的值可能产生不确定的结果, 除非排序表达式产生唯一的排序。您可能必须在order_by_clause中指定多个列才能实现此唯一的排序。

之间。。。和使用between..。and子句指定窗口的起始点和结束点。第一个表达式 (前面and) 定义起始点, 第二个表达式 (后and) 定义结束点。

如果省略并between仅指定一个端点, 则 oracle 将其视为起始点, 结束点默认为当前行。

*前指定未unbounded的preceding, 以指示窗口从分区的第一行开始。这是起点规范, 不能用作终点规范。

*跟随指定无unboundedfollowing以指示窗口在分区的最后一行结束。这是终点规范, 不能用作起始点规范。

当前行作为起始点,currentrow指定窗口从当前行或值开始 (取决于您是否分别指定了row或range)。在这种情况下, 结束点不能value_exprpreceding.

作为结束点,currentrow指定窗口结束于当前行或值 (取决于您是否分别指定了row或range)。在这种情况下, 起始点不能value_exprfollowing.

value_expr前面或value_expr以下对于range或row:

如果value_exprfollowing是起点, 则必须value_expr结束点following.

如果preceding的value_expr是结束点, 则起始点必须value_exprpreceding的.

如果您定义的逻辑窗口由时间间隔定义为数值格式, 则可能需要使用转换函数。

另请参见:

numtoyminterval和numtodsinterval有关将数字时间转换为间隔的信息

如果指定了rows:

value_expr是物理偏移量。它必须是常量或表达式, 并且必须计算为正数值。

如果value_expr是起始点的一部分, 则它必须在结束点之前计算为一行。

如果指定的range:

value_expr是逻辑偏移量。它必须是计算为正值数值或间隔文本的常量或表达式。有关间隔文本的信息, 请参阅"文本"。

您只能在order_by_clause中指定一个表达式。.

如果value_expr计算为数值, 则byexpr的order必须是数字或date数据类型。

如果value_expr计算为间隔值, 则byexpr的order必须是date数据类型。

如果完全省略windowing_clause, 则默认值为无unboundedprecedingandcurrentrowbetween的range.

分析函数通常用于数据仓库环境中。在下面的解析函数列表中, 后跟星号 (*) 的函数允许完整语法, 包括windowing_clause.

avg*

corr*

count*

covar_pop*

covar_samp*

cume_dist

dense_rank

first

first_value*

lag

last

last_value*

lead

listagg

max*

median

min*

nth_value*

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*