标准 SQL 中的分析函数概念

分析函数概念

本主题说明了分析函数在 BigQuery 中的工作原理。如需了解 BigQuery 支持的不同分析函数,请参阅导航函数编号函数聚合分析函数相关的参考主题。

在数据库中,分析函数是指对一组行计算聚合值的函数。与对一组行返回单个聚合值的聚合函数不同,分析函数对一组输入行计算函数,从而对每一行返回一个值。

分析函数是一种强大的机制,可简洁地表示复杂的分析操作,它还实现了高效的评估,让用户无需在 SQL 查询之外执行开销巨大的自连接 (self-JOIN) 或计算。

分析函数在 SQL 标准数据库和一些商业数据库中也称为“(分析)窗口函数”。这是因为分析函数针对一组被称为 windowwindow frame 的行进行评估。在其他一些数据库中,此类函数可能被称作在线分析处理 (OLAP) 函数。

简化语法:

analytic_function_name ( [ argument_list ] )
  OVER (
    [ PARTITION BY partition_expression_list ]
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ window_frame_clause ]
  )

分析函数需使用 OVER 子句,后者定义分析函数要评估的 window frameOVER 子句包含下面三个可选子句。BigQuery 按照写入顺序评估 OVER 子句的子句。

  • PARTITION BY 子句将输入行划分成多个分区。该子句与 GROUP BY 类似,但并不会实际合并具有相同键的行。
  • ORDER BY 子句指定每个分区中的排序方式。
  • window_frame_clause 定义当前分区中的 window frame

OVER 子句还可为空 (OVER());此情况下,window frame 包括所有输入行。

系统会先进行聚合(GROUP BY 和非分析聚合函数),再评估分析函数。

示例:假设某公司希望为每个部门创建一个排行榜,显示每位员工的“资历排名”,即显示哪些员工在公司的时间最长。Employees 表包含列 NameStartDateDepartment

以下查询计算每位员工在其部门中的排名:

SELECT firstname, department, startdate,
  RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

概念计算过程如图 1 所示。

Markdown 图片图 1:分析函数图示

BigQuery 按照 OVER 子句中各子句的出现顺序进行评估:

  1. PARTITION BY:首先根据 department 将表分成两个分区。
  2. ORDER BY:每个分区中的员工行按 startdate 进行排序。
  3. 框架:无。RANK() 禁止使用窗口框架子句,因为它适用于所有编号函数
  4. RANK():针对 window frame 计算每一行的资历排名。

分析函数语法

analytic_function_name ( [ argument_list ] )
  OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

分析函数只能在查询的以下两处位置显示为标量表达式或标量表达式操作数:

  • SELECT 列表。如果分析函数显示在 SELECT 列表中,则其 argument_list 不能引用同一 SELECT 列表中引入的别名。
  • ORDER BY 子句。如果分析函数显示在查询的 ORDER BY 子句中,则其 argument_list 可引用 SELECT 列表别名。

此外,分析函数不能引用其 argument_listOVER 子句中的其他分析函数,即使通过别名间接引用也不行。

无效:

SELECT ROW_NUMBER() OVER () AS alias1
FROM Singers
ORDER BY ROW_NUMBER() OVER(PARTITION BY alias1)

在上述查询中,分析函数 alias1 解析为分析函数 ROW_NUMBER() OVER()

OVER 子句

语法:

OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY sort_specification_list ]
  [ window_frame_clause ]

OVER 子句可能由三部分组成:

  • PARTITION BY 子句
  • ORDER BY 子句
  • window_frame_clausewindow_name,表示 WINDOW 子句中定义的 window_specification

如果 OVER 子句为空(即 OVER()),则分析函数将对包含所有输入行的单个分区进行计算,这表示它将为每个输出行生成相同的结果。

PARTITION BY 子句

语法:

PARTITION BY expression [, ... ]

PARTITION BY 子句将输入行划分成单独的分区,在分区上分别评估分析函数。PARTITION BY 子句可具有多个 expressions

expression 的数据类型必须可分组且支持分区。这表示 expression 不能是以下任何一种数据类型:

  • 浮点
  • 结构体
  • 数组

此列表与 GROUP BY 不支持的数据类型列表几乎一样,只是额外排除了浮点类型(请参阅 BigQuery 数据类型顶部的“数据类型属性”表中的“可分组”)。

如果没有 PARTITION BY 子句,则 BigQuery 将整个输入视为单个分区。

ORDER BY 子句

语法:

ORDER BY expression [ ASC | DESC ] [, ... ]

ORDER BY 子句定义每个分区中的排序方式。如果没有 ORDER BY 子句,则分区中的行排序是不确定的。一些分析函数需使用 ORDER BY;详见分析函数的每个类别。即使具有 ORDER BY 子句,一些函数也不区分 window frame 中的排序(如 COUNT)。

OVER 子句中的 ORDER BY 子句与普通 ORDER BY 子句的相同之处如下:

  • 可存在多个 expressions
  • expression 必须具有支持排序的类型。
  • 每个 expression 可选择性地使用 ASC/DESC 规范。
  • NULL 值的顺序用作最小可能值(作为 ASC 的第一个最小可能值,作为 DESC 的最后一个最小可能值)

数据类型支持与普通 ORDER BY 子句的相同之处是,以下类型支持排序:

  • 数组
  • 结构体

如果 OVER 子句包含 ORDER BY 子句但不包含 window_frame_clause,则 ORDER BYwindow_frame_clause 隐式定义为:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

如果 window_frame_clauseORDER BY 子句均不存在,则 window frame 默认为整个分区。

窗口框架子句

语法:

{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_clause 定义评估分析函数的分区中当前行周围的 window framewindow_frame_clause 允许物理窗口框架(由 ROWS 定义)和逻辑窗口框架(由 RANGE 定义)。如果 OVER 子句包含 ORDER BY 子句但不包含 window_frame_clause,则 ORDER BYwindow_frame_clause 隐式定义为:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

如果 window_frame_clauseORDER BY 子句均不存在,则 window frame 默认为整个分区。

numeric_expression 只能是常量或查询参数,且两者都必须具有非负值。否则 BigQuery 将生成错误。

窗口框架子句示例:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • 包含整个分区。
  • 示例用途:对分区计算总和。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 包含分区中当前行之前(或包含当前行)的所有行。
  • 示例用途:计算累计和。
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  • 包含当前行前面两行与其后面两行之间的所有行。
  • 示例用途:计算移动平均值。

如果 window_frame_spec 使用 BETWEEN 子句:

  • window_frame_boundary_start 指定的边界的开始位置不得在 window_frame_boundary_end 的后面。这将带来以下后果:
    1. 如果 window_frame_boundary_start 包含 CURRENT ROW,则 window_frame_boundary_end 不能包含 PRECEDING
    2. 如果 window_frame_boundary_start 包含 FOLLOWING,则 window_frame_boundary_end 不能包含 CURRENT ROWPRECEDING
  • window_frame_boundary_start 没有默认值。

否则,指定的 window_frame_spec 边界表示窗口框架的开始位置,且窗口框架边界的结束位置默认为“CURRENT ROW”。因此,

ROWS 10 PRECEDING

等效于

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
ROWS

基于 ROWS 的窗口框架根据到当前行的物理偏移量计算 window frame。例如,以下窗口框架定义了当前行周围大小为 5(上限)的窗口框架。

ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING

window_frame_clause 中的 numeric_expression 被解释为距当前行的行数,且必须是常量非负整数。它还可能是查询参数。

如果给定行的 window frame 超出了分区的开始或结束位置,则 window frame 仅包含该分区中的行。

示例:假设下表包含列 zxy

z x y
1 5 AA
2 2 AA
3 11 AB
4 2 AA
5 8 AC
6 10 AB
7 1 AB

请考虑使用以下分析函数:

SUM(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING)

PARTITION BY 子句根据 y 值将表拆分为 3 个分区,ORDER BY 在每个分区中按 z 值进行排序。

分区 1(共 3 个):

z x y
1 5 AA
2 2 AA
4 2 AA

分区 2(共 3 个):

z x y
3 11 AB
6 10 AB
7 1 AB

分区 3(共 3 个):

z x y
5 8 AC

在下表中,粗体表示当前正在评估的行,彩色单元格表示该行的 window frame 中的所有行。

  • 对于 y = AA 分区中的第一行,即使 window_frame_spec 表示窗口大小为 3,window frame 仍然只包括两行,原因是第一行前面为空。对于第一行,分析函数的结果为 7。
z x y
1 5 AA
2 2 AA
4 2 AA
  • 对于分区中的第二行,window frame 包括全部三行。对于第二行,分析函数的结果为 9。
z x y
1 5 AA
2 2 AA
4 2 AA
  • 对于分区中的最后一行,window frame 仅包含两行,原因是该行后面为空。对于第三行,分析函数的结果为 4。
z x y
1 5 AA
2 2 AA
4 2 AA
RANGE

基于 RANGE 的窗口框架根据当前行周围的行的逻辑范围计算 window frame,且此逻辑范围根据当前行的 ORDER BY 键值得出。用当前行的键值加上或减去所提供的范围值,以定义 window frame 的开始或结束范围边界。

必须指定 ORDER BY 子句,除非窗口是:

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

window_frame_clause 中的 numeric_expression 被解释为到当前行的 ORDER BY 键值的偏移量。numeric_expression 必须是数字类型。目前不支持 DATE 和 TIMESTAMP。此外,numeric_expression 必须是常量非负整数或参数。

在基于 RANGE 的窗口框架中,ORDER BY 子句中最多只能有一个 expression,且 expression 必须具有数字类型。

具有单个分区的基于 RANGE 的窗口框架示例:

SELECT x, COUNT(*) OVER ( ORDER BY x
  RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) AS count_x
FROM T;

在下表中,粗体表示当前正在评估的行,彩色单元格表示该行的 window frame 中的所有行。

  • 对于第 1 行,x = 5,因此 COUNT(*) 将仅包含 3 <= x <= 7 的行
x count_x
5 1
2
11
2
8
10
1
  • 对于第 2 行,x = 2,因此 COUNT(*) 将仅包含 0 <= x <= 4 的行
x count_x
5 1
2 3
11
2
8
10
1
  • 对于第 3 行,x = 11,因此 COUNT(*) 将仅包含 9 <= x <= 13 的行
x count_x
5 1
2 3
11 2
2
8
10
1

WINDOW 子句

语法:

WINDOW window_definition [, ...]
window_definition: window_name AS ( window_specification )

WINDOW 子句定义了一列命名窗口,且窗口的 window_name 可在 SELECT 列表的分析函数中引用。如果要对多个分析函数使用同一个 window_frame_clause,则这一点非常有用。

WINDOW 子句只能出现在 SELECT 子句的末尾,如查询语法中所示。

命名窗口

定义 WINDOW 子句后,您可在分析函数中使用命名窗口,但只能在 SELECT 列表中使用;不能在 ORDER BY 子句中使用命名窗口。命名窗口可单独出现,也可嵌入到 OVER 子句中。命名窗口可引用 SELECT 列表别名。

示例:

SELECT SUM(x) OVER window_name FROM ...
SELECT SUM(x) OVER (
  window_name
  PARTITION BY...
  ORDER BY...
  window_frame_clause)
FROM ...

OVER 子句中嵌入时,与 window_name 关联的 window_specification 必须与同一 OVER 子句中的 PARTITION BYORDER BYwindow_frame_clause 兼容。

以下规则适用于命名窗口:

  • 只能在 SELECT 列表中引用命名窗口;不能在 ORDER BY 子句、外部查询或任何子查询中引用它们。
  • 窗口 W1(无论是否命名)可引用命名窗口 NW2,规则如下:
    1. 如果 W1 是命名窗口,则引用的命名窗口 NW2 必须在同一 WINDOW 子句中位于 W1 的前面。
    2. W1 不能包含 PARTITION BY 子句。
    3. W1 和 NW2 不能都包含 ORDER BY 子句。
    4. NW2 不能包含 window_frame_clause
  • 如果窗口 W1(无论是否命名)引用命名窗口 NW2,则生成的窗口规范按以下项进行定义:
    1. NW2 的 PARTITION BY(若有)。
    2. W1 或 NW2 的 ORDER BY(若定义了任一项);两者不可能同时具有 ORDER BY 子句。
    3. W1 的 window_frame_clause(若有)。

本主题阐释了分析导航函数的工作原理。如需了解 BigQuery 支持的分析导航函数,请参阅导航函数的函数参考

导航函数通常针对当前行窗口框架中的其他行计算一些 value_expressionOVER 子句语法因导航函数而异。

OVER 子句要求:

  • PARTITION BY:可选。
  • ORDER BY
    1. 不可用于 PERCENTILE_CONTPERCENTILE_DISC
    2. FIRST_VALUELAST_VALUENTH_VALUELEADLAG 的必需项。
  • window_frame_clause
    1. 不可用于 PERCENTILE_CONTPERCENTILE_DISCLEADLAG
    2. FIRST_VALUELAST_VALUENTH_VALUE 的可选项。

在所有导航函数中,结果数据类型与 value_expression 的类型相同。

编号函数

本主题阐释了分析编号函数的工作原理。如需了解 BigQuery 支持的分析编号函数,请参阅编号函数参考

编号函数根据行在指定窗口中的位置向每一行分配整数值。

RANK()DENSE_RANK()ROW_NUMBER() 示例:

SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS row_num
FROM ...
x rank dense_rank row_num
1 1 1 1
2 2 2 2
2 2 2 3
5 4 3 4
8 5 4 5
10 6 5 6
10 6 5 7
  • RANK(): 对于 x=5,rank 返回 4,因为 RANK() 加上了前一个窗口排序组中对等项的数量。
  • DENSE_RANK():对于 x=5,dense_rank 返回 3,因为 DENSE_RANK() 总是递增 1,即绝不跳过值。
  • ROW_NUMBER(): 对于 x=5,row_num 返回 4。

聚合分析函数

BigQuery 支持将某些聚合函数用作分析函数。

借助这些函数,OVER 子句只是附加到聚合函数调用上;函数调用语法保持不变。与其对应的聚合函数一样,这些分析函数执行聚合操作,特别是针对每一行的相关窗口框架执行聚合操作。而且,这些分析函数的结果数据类型与其对应的聚合函数相同。

如需了解 BigQuery 支持的聚合分析函数,请参阅聚合分析函数参考

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面