分析函数说明

分析函数与聚合函数，都是对行集组（一组行的集合）进行聚合计算。不同的是，聚合函数每组只能返回一个值（一行），而分析函数每组可以返回多个值（多行）。这组行又称为窗口（Window），由 analytic_clause 定义。窗口大小取决于实际的行数或逻辑间隔（例如时间），组内每一行都是基于窗口的逻辑计算的结果。

触发一个分析函数需要特殊的关键字 OVER 来指定窗口。一个窗口包含以下三个组成部分：

• 分区规范：用于将输入行分裂到不同的分区中。这个过程和 GROUP BY 子句的分裂过程相似。

• 排序规范：用于决定输入数据行在分析函数中执行的顺序。

• 窗口边界：指定计算数据的窗口边界。默认值为 RANGE UNBOUNDED PRECEDING 。这个边界包含当前分区中所有从开始到目前行所有数据。

分析函数是查询中执行的最后一组操作，除了最后的 ORDER BY 子句。在处理分析函数之前，必须完成所有 JOIN 以及 WHERE、GROUP BY 和 HAVING 子句。因此，分析函数只能出现在选择列表或 ORDER BY 子句中。

语法

analytic_function

analytic_function 指定了分析函数的名称。语法如下：

analytic_function([ arguments ]) OVER (analytic_clause)

分析函数采用 0~3 个 arguments 参数。参数可以是任何数字数据类型或可以隐式转换为数字数据类型的任何非数字数据类型。OceanBase 根据数据类型优先级确定具有最高数值优先级的参数，然后将其余参数隐式转换为这个具有最高数值优先级参数的数据类型。返回类型也是具有最高数值优先级参数的数据类型，除非对单个函数另有说明。

analytic_clause

分析子句（analytic_clause），使用 OVER analytic_clause 指示函数在查询结果集上操作。此子句在 FROM、WHERE、GROUP BY 和 HAVING 子句之后计算。您可以在选择列表或 ORDER BY 子句中用此子句指定分析函数。如果要筛选基于分析函数的查询结果，请将这些函数嵌套在父查询中，然后筛选嵌套子查询的结果。语法如下：

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

注意

• 您不能通过在分析子句中指定分析函数来嵌套分析函数。但是您可以在子查询中指定分析函数，并在其上计算另一个分析函数。
• 您可以使用用户定义的解析函数以及内置的解析函数指定 analytic_clause。

query_partition_clause

分区子句（query_partition_clause），使用 PARTITION BY 子句将查询结果集划分为基于一个或多个 value_expr 的组。如果省略此子句，则函数将查询结果集的所有行视为单个组。

您可以在同一查询中指定多个分析函数，每个函数通过键具有相同或不同的分区。如果您用 query_partition_clause 指定了一个分析函数，并且被查询的对象具有并行属性，那么函数计算也是并行化的。

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

语法如下：

PARTITION BY { expr[, expr ]... | ( expr[, expr ]... ) }

order_by_clause

使用排序子句 order_by_clause 指定如何在分区内对数据进行排序。 对于所有分析函数，您可以在多个键上的分区中对值进行排序，每个键由 value_expr 定义，由排序序列限定。

在每个函数中，您可以指定多个排序表达式。当使用对值进行排序的函数时，这样做特别有用。

当 order_by_clause 对多行产生相同的值时，函数的行为如下：

• CUME_DIST、DENSE_RANK、NTILE、PERCENT_RANK 和 RANK 为每一行返回相同的结果。

• 即使有一个基于 order_by_clause 的值，ROW_NUMBER 也会为每一行分配一个不同的值。该值基于行处理的顺序，如果 ORDER BY 不能实现总排序，则该顺序可能是不确定的。

• 对于其他分析函数，它的结果取决于窗口规则。如果您指定了一个带有 RANGE 关键字的逻辑窗口，则函数将为每个行返回相同的结果。如果您用 ROWS 关键字指定了物理窗口，则结果是不确定的。

语法如下：

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

ASC 或 DESC 关键字

指定排序序列，ASC 指升序，DESC 指降序。默认为升序（ASC）。

NULLS FIRST 或 NULLS LAST 关键字

order_by_clause 中的 nulls first 和 nulls last， nulls first 表示排序时 NULL 值作为最小值处理，而 nulls last 表示排序时 NULL 值作为最大值处理。

ORDER BY 子句的限制

使用 ORDER BY 子句会受到以下限制：

• 在分析函数中，order_by_clause 必须使用表达式（expr）。SIBLINGS 关键字无效（仅与分层查询中相关）。位置（position）和列别名（c_alias）也无效。否则，该 order_by_clause 与整个查询或子查询的排序命令相同。

• 使用 RANGE 关键字的分析函数可以在其函数的 ORDER BY 子句中使用多个排序键。您需要指定以下窗口：

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(简称 RANGE UNBOUNDED PRECEDING)
  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  • RANGE BETWEEN CURRENT ROW AND CURRENT ROW
  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

除了以上四个窗口之外，窗口边界只能在分析函数的 ORDER BY 子句中有一个排序键。 此限制不适用于 ROW 关键字指定的窗口边界。

windowing_clause

有一些分析函数可以使用 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}}

ROWS 或 RANGE 关键字

这些关键字为每一行定义一个用于计算函数结果的窗口，然后将该函数应用于窗口中的所有行。窗口从上到下通过查询结果集或分区移动。窗口也称为 FRAME，OceanBase 数据库同时支持以下窗口语句：

• ROWS：以物理单位（行）指定窗口。

• RANGE：将窗口指定为逻辑偏移量。默认方式是 RANGE UNBOUNDED PRECEDING。

您可以在分析函数中使用 windowing_clause，就必须添加 order_by_clause。如果 windowing_clause 由 RANGE 子句定义窗口边界，那么在 order_by_clause 中您只能指定一个表达式。请参见 ORDER BY 子句的限制 。

具有逻辑偏移的分析函数返回的值总是确定性的。但是具有物理偏移量的分析函数返回的值可能会产生不确定的结果。排序表达式返回唯一的排序才能使具有物理偏移量的分析函数返回确定的值，因此，您必须在 order_by_clause 中指定多个列实现唯一的排序。

BETWEEN ... AND 关键字

使用 BETWEEN ... AND 子句指定窗口的起点和终点。第一个表达式（AND 之前）定义起点，第二个表达式（AND 之后）定义终点。 如果省略 BETWEEN 并仅指定一个终点，则 OceanBase 数据库将其视为起点，并且终点默认为当前行。

UNBOUNDED PRECEDING 关键字

UNBOUNDED PRECEDING 指示窗口从分区的第一行开始。这用于起点，不能用于终点。

UNBOUNDED FOLLOWING 关键字

UNBOUNDED FOLLOWING 表示窗口在分区的最后一行结束。这用于终点，不能用于起点。

CURRENT ROW 关键字

作为起点，CURRENT ROW 指定了窗口从当前行或当前值开始（取决于分别指定了 ROW 还是 RANGE）。在这种情况下，端点不能为 value_expr PRECEDING。作为终点，CURRENT ROW 指定窗口在当前行或值处结束（分别取决于您是否指定了 ROW 或 RANGE）。在这种情况下，起点不能为 value_expr FOLLOWING。

value_expr PRECEDING 或 value_expr FOLLOWING 关键字

• 如果 value_expr FOLLOWING 是起点，则终点必须是 value_expr FOLLOWING。

• 如果 value_expr PRECEDING 是终点，则起点必须是 value_expr PRECEDING。

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

如果您指定 ROWS：

• value_expr 是物理偏移量。则它必须是一个常数或表达式，并且必须计算为正数。

• 如果 value_expr 是起点的一部分，则它必须把起点与终点之前的部分当作一行计算。

如果您指定 RANGE：

• value_expr 是逻辑偏移量。它必须是一个常数或表达式，其结果为正数值或间隔字面量。

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

• 如果 value_expr 为数值，则 ORDER BY expr 必须为数值或 DATE 数据类型。

• 如果 value_expr 为间隔值，则 ORDER BY expr 必须为 DATE 数据类型。如果您完全省略 windowing_clause，则默认值为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。