SQL 中的分析函数

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

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

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

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

SQL 中分析函数的语法

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

分析函数中的组件

分析函数

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

参数

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

SQL 软件会确定哪个参数具有最高的数字优先级，当有多个参数传递到分析函数时。然后它会将参数左侧的数据类型转换为该数据类型。函数返回的值也具有具有最高优先级的参数的相同数据类型。如果用户为特定函数指定了另一种数据类型，则可以更改这一点。

Analytic_Clause

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

关于 analytic_clause 的说明

• 用户不能通过在 analytic _clause 的任何部分提及另一个分析函数来嵌套分析函数。但是，您可以在子查询中提及分析函数，并对其进行另一个分析函数计算。
• 您可以提及带用户定义的分析函数和预定义分析函数的 OVER analytic_clause。
• PARTITION BY 和 ORDER BY 是分析子句中的两个子句，它们对排序敏感。

query_partition_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

ROW_NUMBER 在 order_by_clause 实现后，如果存在两个或多个行具有相同值，则为每行分配不同的值。此不同值由系统处理行的顺序计算。如果 ORDER BY 未能确保总排序，则此方法可能不是确定的。

对于所有其他分析函数，结果可能会根据窗口规范而有所不同。用户可以通过实现 RANGE 关键字来指定逻辑窗口。这将允许所有分析函数为每行提供相同的结果，即使窗口规范不同。如果用户使用 ROW 关键字指定物理窗口，结果将不是确定的。

Order By Clause 的限制

对 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 中包含多个排序键。所有其他边界只能在分析函数的 ORDER BY 子句中包含一个排序键。此限制不适用于由 ROW 关键字确定的窗口边界。

ASC | DESC

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

NULLS FIRST | NULLS LAST

它确定带有 NULL 值的返回行是否应出现在排序序列的开头或结尾。虽然在升序中 NULL LAST 设置为默认值，而在降序中 NULL FIRST 设置为默认值。

分析函数仅在 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。由 RANGE 子句确定的某些窗口允许用户在 order_by_clause 中仅提及一个表达式。
• 分析函数返回的值具有逻辑偏移量，因为窗口始终是确定性的。而使用行作为窗口物理偏移量的分析函数返回的值可能导致非确定性的结果。在这种情况下，只有当排序表达式为窗口中的每一行产生唯一排序时，结果才能是确定性的。用户可能需要在 order_by_clause 中指定多个列来生成行的唯一排序。

BETWEEN ... AND

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

如果用户不使用 BETWEEN 关键字而仅提及窗口的结束点。那么 SQL 将该点视为开始点，窗口的结束点将设置为当前行。

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

对于 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。

一些常见的分析函数

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

1. AVG：此函数返回函数中传入的表达式的平均值。该函数接受任何数值数据类型作为输入参数。用户还可以输入可以隐式转换为数值的非数值数据类型。函数返回的值的数据类型与输入参数的数据类型相同。
2. COUNT：此分析函数返回查询返回的行数。此函数可以用作聚合函数或分析函数。
   • 如果用户可以指定 DISTINCT，则用户只能使用
   • 分析函数的查询分区子句。这意味着用户不能使用该函数的 order by 子句或 windowing 子句。
   • 如果用户将表达式添加为函数参数，则 COUNT 分析函数返回表达式值不为 NULL 的行数。用户可以计算所有行或具有该表达式的不同值的行。
3. CUME_DIST：此分析函数计算一组值中特定值的累积分布。此函数返回的值范围大于 0 或小于等于 1。如果用户计算相同值的累积分布，则函数将返回相同的累积分布值。
   • 此函数可以具有任何可以隐式转换为数字的数字或非数字数据类型。DBMS 负责确定具有最高优先级的参数并相应地执行计算。它将参数转换为函数所需的数据类型，并返回数值。
4. PERCENT_RANK：它类似于 CUME_DIST（累积分布）函数。它返回一个范围从 0 到 1 的值，包括 0 和 1。任何集合中的第一行具有 0 的 PERCENT_RANK。该函数返回一个数值结果。
5. 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}：它告诉系统计算是从窗口的开始行还是结束行开始。默认情况下，计算从窗口的第一行开始。
6. MIN：此分析函数将返回 expr 中的最小值。该函数可以用作聚合函数或分析函数。
7. MAX：此分析函数将返回 expr 中的最大值。该函数可以用作聚合函数或分析函数。