SQL 分析函数

分析函数允许用户根据行的分组计算聚合值。这些函数与聚合函数不同，因为它们为每个组返回多行。返回的行被称为窗口，可以由analytic_clause定义。

对于数据库中的每一行，定义了一个滑动窗口。滑动窗口确定用于计算当前行的值的行的限制。滑动窗口的大小可以是确定的行数，也可以依赖于逻辑间隔，如时间。

分析函数是查询中执行的最后一组操作。唯一的例外是在SQL中使用的ORDER BY子句。在实现分析函数之前，所有连接和所有WHERE、GROUP BY和HAVING子句都会执行。因此，只能在select列表或使用ORDER BY子句时实现分析函数。

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

SQL中分析函数的语法

实现SQL中分析函数的语法如下：

analytic_function([ arguments ]) OVER (analytic_clause)

分析函数中的组件

分析函数

SQL 分析函数

要在查询中实现分析函数，请指定要执行的函数名称。您可以参考分析函数列表来确定要实现的函数。

参数

分析函数可使用0-3个参数值。在函数中输入的参数可以是数值或可以转换为数值数据类型的非数值数据类型。

当在分析函数中传递多个参数时，SQL软件会确定具有最高数值优先级的参数。然后，它将将参数转换为特定的数据类型。函数返回的值还具有与最高优先级参数相同的数据类型。如果用户为特定函数指定了其他数据类型，则可以更改此类型。

分析子句

SQL 分析函数

OVER是用户用来指示在查询结果集上实现的分析函数的analytic_clause。一旦执行了FROM、WHERE、GROUP BY和HAVING子句，就会执行这个子句。用户可以在列表中使用OVER子句指定分析函数，也可以使用ORDER BY子句。这些子句使用户能够过滤实现分析函数生成的结果。它允许将这些函数嵌套在父查询中，然后过滤SQL中嵌套子查询的结果。

关于analytic_clause的说明:

用户不能通过在analytic_clause的任何部分中提及另一个分析函数来嵌套分析函数。但是可以在子查询中提及分析函数并计算另一个分析函数。
用户可以在USER定义的分析函数和预定义的分析函数中提及OVER analytic_clause。
PARTITION BY和ORDER BY是collation敏感的analytic_clause中的两个子句。

query_partition_clause

另一种类型的analytic_clause是PARTITION BY子句。这个子句用于根据一个或多个值表达式将查询结果集分组。如果用户决定从查询中删除这个子句，则分析函数将把所有查询结果行视为一组。

如果用户想要在分析函数中实现query_partition_clause，那么用户必须执行语法的上层分支（不需要括号）。如果用户想在模型查询或分区外连接中实现该子句，使用带括号的语法。

它允许用户在同一个查询中实现多个分析函数。可以通过使用相同或不同的PARTITION BY键来实现。如果被查询的对象具有并行属性，用户还可以使用query_partition_clause提及分析函数。这也将使得执行的计算是并行的。

value_expr中可以使用的有效值包括常量、列、非分析函数、函数表达式或包含上述任何内容的表达式。

Order_by_clause

这个子句用于在分区内指定数据的排序顺序。用户可以为每个分析函数的分区内的值指定多个排序键。每个传递给分析函数的值都由一个value_expr定义，并且通过排序序列来限定表达式。

对于不同的分析函数，用户可以指定多个排序表达式。当用户实现对值进行排名的函数时，这一点尤为有用，因为第二个表达式可以用来解决第一个表达式相同值之间的并列关系。

如果order_by_clause导致多行的结果相同，则分析函数的行为如下:

以下分析函数将为每一行提供相同的结果:

CUME_DIST
DENSE_RANK
NTILE
PERCENT_RANK
RANK

如果在order_by_clause执行后，任何两行或更多行具有相同的值，则ROW_NUMBER为每一行分配不同的值。这个不同的值是通过系统处理行的顺序来计算的。如果ORDER BY不能确保完全排序，则该方法可能是非确定性的。

除了窗口规范之外，所有其他分析函数的结果都可能不同。用户可以通过实现RANGE关键字来指定逻辑窗口。这将使所有分析函数为每一行提供相同的结果，即使窗口规范不同。如果用户用ROW关键字指定物理窗口，结果将是非确定性的。

Order By子句的限制

对ORDER BY子句的实现施加了一些限制:

当order_by_clause与分析函数一起实现时，必须向子句传递一个表达式（expr）。SIBLINGS关键字在这种情况下无效（它只能在分层查询中使用）。在这种情况下，位置和列别名（c_alias）也无效。否则，这将返回与用于对整个查询或子查询排序的order_by_clause相同的结果。

采用RANGE关键字实现的分析函数可以在order_by子句中使用多个排序键，如果它提及下面提到的任何一个窗口之一:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：此窗口的简写形式是RANGE UNBOUNDED PRECEDING。
RANGE BETWEEN CURRENT ROW AND CURRENT ROW
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

这四个窗口只能在order_by_clause的analytic函数中具有多个排序键。所有其他边界只能在analytic函数的ORDER BY子句中有一个排序键。这个限制不适用于由ROW关键字确定的窗口边界。

ASC | DESC

这些关键字用于指定排序序列。顺序可以是升序或降序。默认情况下，排序顺序设置为ASC。

NULLS FIRST | NULLS LAST

它确定是否将具有空值的返回行出现在排序序列的开头或末尾。在升序排序时，NULLS LAST被设置为默认值，在降序排序时，默认值为NULLS FIRST。

分析函数仅对函数的order_by_clause指定的行进行操作。由于函数的order_by_clause不能保证产生结果的顺序。用户可以实现查询的order_by_clause来确保最终结果是有序的。

Windowing_clause

某些分析函数支持windowing_clause。允许用户在windowing_clause中使用的一些分析函数如下:

COUNT（计数）：返回窗口中的行数。
CORR（相关系数）：计算窗口中两个指定表达式之间的相关系数。
FIRST_VALUE（第一个值）：返回窗口中的第一个值。
LAST_VALUE（最后一个值）：返回窗口中的最后一个值。
AVG（平均值）：计算窗口中所有值的平均值。

ROWS | RANGE（行 | 范围）

这些关键词分别为窗口中的每一行定义。它可以是用于计算函数结果的物理或逻辑行的集合。然后，函数可以在窗口中的所有行中实现。指定的窗口从顶部到底部移动结果查询集或分区。
ROWS使用物理单位（行）确定窗口，而RANGE关键字以逻辑偏移值确定窗口。
如果用户想要实现此子句，则用户必须指定order_by_clause。范围子句确定的窗口边界中的一些允许用户在order_by_clause中仅提及一个表达式。
分析函数返回的值具有逻辑偏移量，因为窗口始终是确定性的。然而，通过行作为窗口的物理偏移返回的分析函数的值可能会导致非确定性结果。在这种情况下，只有在排序表达式为窗口中的每一行产生唯一排序时，结果才能确定。用户可能需要在order_by_clause中指定多个列来为行产生唯一的排序。

BETWEEN … AND（在…和之间）

这些子句用于确定窗口的特定起点和终点。在AND之前输入的第一个表达式指定窗口的起点，AND之后输入的第二个表达式指定窗口的终点。

假设用户不使用BETWEEN关键字，只提及窗口的终点。那么SQL将把该点视为起点，窗口的终点将被设置为当前行。

UNBOUNDED PRECEDING（无限PRECEDING）

用户使用UNBOUNDED PRECEDING指定窗口从分区的第一行开始。这只能用作起点规范，不能用作终点规范。

UNBOUNDED FOLLOWING（无限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

对于RANGE或ROW：

如果value_expr FOLLOWING用作起点，则终点将被赋予value_expr FOLLOWING中的值。
如果value_expr PRECEDING用作终点，则起点将被赋予value_expr PRECEDING中的值。
如果用户使用时间间隔以数字格式定义逻辑窗口，则用户可能需要执行给定的转换函数。
如果用户指定了ROWS，则value_expr是窗口的物理偏移。该参数是可以转换为正数数值的常量或表达式。
如果用户指定了RANGE，则value_expr是窗口的逻辑偏移。该参数是可以转换为正数数值或文字区间的常量或表达式。
用户只能在order_by_clause中提及一个表达式。这意味着值可以是数值或区间值。
如果value_expr包含数值，则用于ORDER BY的表达式必须是数值或DATE数据类型。
如果value_expr包含区间值，则ORDER BY expr必须是DATE数据类型。
如果用户不想包含windowing_clause，则默认值为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

一些常见的分析函数

一些分析函数及其执行的功能如下：

AVG: 此函数返回传递给函数的表达式的平均值。该函数接受任何数值数据类型作为输入参数。用户还可以输入可以隐式转换为数值的非数值数据类型。函数返回值的数据类型与输入参数的数据类型相同。
COUNT: 此分析函数返回查询返回的行数。此函数可用作聚合函数或分析函数。

如果用户可以指定DISTINCT，则只能使用该分析函数的查询分区子句。这意味着用户不能使用此函数的order by子句或窗口子句。
如果用户将表达式添加为函数参数，则COUNT分析函数将返回值为特定表达式的值不为null的行数。用户可以计算所有行或具有表达式的不同值的行数。
1. CUME_DIST: 此分析函数计算一组值中特定值的累积分布。该函数返回的值范围大于0且小于等于1。如果用户计算相同值的累积分布，则函数将返回相同的累积分布值。
此函数可以具有可以隐式更改为数值类型的任何数值或非数值数据类型。DBMS负责确定具有最高优先级的参数，并相应地执行计算。它将参数转换为函数所需的数据类型，并返回数值。
1. PERCENT_RANK: 它类似于CUME_DIST（累积分布）函数。它返回一个从0到1的值，包括0和1。任何一组中的第一行的PERCENT_RANK为0。函数返回一个数值作为结果。
2. NTH_VALUE: 此函数计算窗口中第n行的measure_expr值。该行必须包含在分析子句中定义的窗口中。返回值具有与measure_expr的数据类型相同的数据类型。
{RESPECT | IGNORE} NULLS：它确定在计算分析函数时是否包含或排除具有null值的measure_expr。默认情况下，在计算过程中考虑null值；该值设置为RESPECT NULLS。
在此函数中，n表示函数将计算值的第n行。N可以是一个常量值、绑定变量、列或包含这些值之一的表达式。唯一的要求是产生的整数必须有一个正值。它必须包含n行以计算结果。如果源窗口少于n行，则该函数将返回一个NULL值。如果值是，则NULL函数返回一个错误。
FROM {FIRST | LAST}：它告诉系统计算是从窗口的第一行还是最后一行开始的。默认情况下，计算从窗口的第一行开始。
1. MIN: 此分析函数将返回expr中的最小值。该函数可用作聚合函数或分析函数。
2. MAX: 此分析函数将返回expr中的最大最小值。该函数可用作聚合函数或分析函数。