标准 SQL 中的聚合函数

聚合函数是对一组值执行计算的函数。例如,COUNTMINMAX 都是聚合函数。

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

以下部分介绍了 BigQuery 支持的聚合函数。

ANY_VALUE

ANY_VALUE(expression)  [OVER (...)]

说明

返回输入的任何值;如果不存在任何输入行,则返回 NULL。返回的值是不确定的,这意味着您在每次使用此函数时都可能得到不同的结果。

支持的参数类型

任意

可选子句

OVER:指定一个窗口。请参见分析函数

返回的数据类型

与输入数据类型相匹配。

示例

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+
SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+--------+-----------+
| fruit  | any_value |
+--------+-----------+
| pear   | pear      |
| apple  | pear      |
| banana | apple     |
+--------+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
          [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

说明

返回 ARRAY 类型的 expression 值。

支持的参数类型

除 ARRAY 以外的所有数据类型。

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。此子句目前与 ARRAY_AGG() 内的所有其他子句均不兼容。

  2. DISTINCTexpression 的每个不同的值仅聚合到结果中一次。

  3. IGNORE NULLSRESPECT NULLS:如果指定了 IGNORE NULLS,则从结果中排除 NULL 值。如果指定了 RESPECT NULLS 或者未指定任何一个子句,结果中会包含 NULL 值(但如果最终查询结果中的数组包含 NULL 元素,则会报错)。

  4. ORDER BY:指定值的顺序。

    • 对于每个排序键,默认排序方向均为 ASC
    • NULL:在 ORDER BY 子句的上下文中,NULL 是最小值:即以 ASC 方向排序时,NULL 出现在最前位置;以 DESC 方向排序时,出现在最后位置。
    • 浮点数据类型:请参阅浮点语义,了解排序和分组。
    • 如果同时还指定了 DISTINCT,排序键必须与 expression 相同。
    • 如果未指定 ORDER BY,输出数组中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。
  5. LIMIT:指定结果中 expression 输入的最大数量。上限 n 必须是 INT64 常量。

返回的数据类型

ARRAY

如果不存在输入行,则此函数返回 NULL

示例

SELECT FORMAT("%T", ARRAY_AGG(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT FORMAT("%T", ARRAY_AGG(x IGNORE NULLS)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-----------+
| array_agg |
+-----------+
| [-2, 1]   |
+-----------+
SELECT
  x,
  FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+----+-------------------------+
| x  | array_agg               |
+----+-------------------------+
| 1  | [1, 1]                  |
| 1  | [1, 1]                  |
| 2  | [1, 1, 2, -2, -2, 2]    |
| -2 | [1, 1, 2, -2, -2, 2]    |
| -2 | [1, 1, 2, -2, -2, 2]    |
| 2  | [1, 1, 2, -2, -2, 2]    |
| 3  | [1, 1, 2, -2, -2, 2, 3] |
+----+-------------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])

说明

连接类型为 ARRAY 的 expression 中的元素,返回一个 ARRAY 作为结果。此函数会忽略 NULL 输入数组,但会考虑非 NULL 输入数组中的 NULL 元素(但如果最终查询结果中的数字包含 NULL 元素,则会引发错误)。

支持的参数类型

ARRAY

可选子句

子句按照以下顺序应用

  1. ORDER BY:指定值的顺序。

    • 对于每个排序键,默认排序方向均为 ASC
    • 不支持数组排序,因此排序键不能与 expression 相同。
    • NULL:在 ORDER BY 子句的上下文中,NULL 是最小值:即以 ASC 方向排序时,NULL 出现在最前位置;以 DESC 方向排序时,出现在最后位置。
    • 浮点数据类型:请参阅浮点语义,了解排序和分组。
    • 如果未指定 ORDER BY,输出数组中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。
  2. LIMIT:指定结果中 expression 输入的最大数量。此上限适用于输入数组数量,而非数组中的元素数量。空数组计为 1。NULL 数组不计数。上限 n 必须是 INT64 常量。

返回的数据类型

ARRAY

如果不存在任何输入行或所有行的 expression 计算结果均为 NULL,则返回 NULL

示例

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4]       |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+--------------------------+
| array_concat_agg         |
+--------------------------+
| [1, 2, 3, 4, 5, 6]       |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

AVG([DISTINCT] expression)  [OVER (...)]

说明

返回非 NULL 输入值的平均值;如果输入包含 NaN,则返回 NaN

支持的参数类型

任何数字输入类型,例如 INT64。请注意,浮点输入类型的返回结果是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。此子句目前与 AVG() 内的所有其他子句均不兼容。

  2. DISTINCTexpression 的每个不同的值仅聚合到结果中一次。

返回的数据类型

  • 如果输入类型是 NUMERIC,则返回 NUMERIC。
  • FLOAT64

示例

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+
SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+------+
| x    | avg  |
+------+------+
| NULL | NULL |
| 0    | 0    |
| 2    | 1    |
| 4    | 3    |
| 4    | 4    |
| 5    | 4.5  |
+------+------+

BIT_AND

BIT_AND(expression)

说明

expression 执行按位与运算并返回结果。

支持的参数类型

  • INT64

返回的数据类型

INT64

示例

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR(expression)

说明

expression 执行按位或运算并返回结果。

支持的参数类型

  • INT64

返回的数据类型

INT64

示例

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression)

说明

expression 执行按位异或运算并返回结果。

支持的参数类型

  • INT64

可选子句

DISTINCTexpression 的每个不同的值均仅聚合到结果中一次。

返回的数据类型

INT64

示例

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1. COUNT(*) [OVER (...)]

2. COUNT([DISTINCT] expression) [OVER (...)]

说明

  1. 返回输入中的行数。
  2. expression 的计算结果为 NULL 以外的任何值时,返回行数。

支持的参数类型

expression 可为任何数据类型。

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数

  2. DISTINCTexpression 的每个不同的值仅聚合到结果中一次。

返回数据类型

INT64

示例

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------+------------+--------------+
| x    | count_star | count_dist_x |
+------+------------+--------------+
| 1    | 3          | 2            |
| 4    | 3          | 2            |
| 4    | 3          | 2            |
| 5    | 1          | 1            |
+------+------------+--------------+
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------+------------+---------+
| x    | count_star | count_x |
+------+------------+---------+
| NULL | 1          | 0       |
| 1    | 3          | 3       |
| 4    | 3          | 3       |
| 4    | 3          | 3       |
| 5    | 1          | 1       |
+------+------------+---------+

COUNTIF

COUNTIF(expression)  [OVER (...)]

说明

返回 expressionTRUE 值计数。如果不存在任何输入行或所有行的 expression 计算结果均为 FALSE,则返回 0

支持的参数类型

BOOL

可选子句

OVER:指定一个窗口。请参见分析函数

返回数据类型

INT64

示例

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+
SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

LOGICAL_AND

LOGICAL_AND(expression)

说明

返回所有非 NULL 表达式的逻辑与。如果不存在任何输入行或所有行的 expression 计算结果均为 NULL,则返回 NULL

支持的参数类型

BOOL

返回数据类型

BOOL

示例

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression)

说明

返回所有非 NULL 表达式的逻辑或。如果不存在任何输入行或所有行的 expression 计算结果均为 NULL,则返回 NULL

支持的参数类型

BOOL

返回数据类型

BOOL

示例

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression)  [OVER (...)]

说明

返回非 NULL 表达式的最大值。如果不存在任何输入行或所有行的 expression 计算结果均为 NULL,则返回 NULL。如果输入包含 NaN,则返回 NaN

支持的参数类型

任意数据类型(ARRAYSTRUCT 除外)

可选子句

OVER:指定一个窗口。请参见分析函数

返回数据类型

与用作输入值的数据类型相同。

示例

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | max  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 8    |
| 4    | 8    |
| 37   | 55   |
| 55   | 55   |
+------+------+

MIN

MIN(expression)  [OVER (...)]

说明

返回非 NULL 表达式的最小值。如果不存在任何输入行或所有行的 expression 计算结果均为 NULL,则返回 NULL。如果输入包含 NaN,则返回 NaN

支持的参数类型

任意数据类型(ARRAYSTRUCT 除外)

可选子句

OVER:指定一个窗口。请参见分析函数

返回数据类型

与用作输入值的数据类型相同。

示例

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | min  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 4    |
| 4    | 4    |
| 37   | 37   |
| 55   | 37   |
+------+------+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter]  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

说明

返回通过连接非 NULL 值获得的值(STRING 或 BYTES)。

如果指定了 delimiter,则连接的值由此分隔符进行分隔;否则使用逗号作为分隔符。

支持的参数类型

STRING BYTES

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。此子句目前与 STRING_AGG() 内的所有其他子句均不兼容。

  2. DISTINCTexpression 的每个不同的值仅聚合到结果中一次。

  3. ORDER BY:指定值的顺序。

    • 对于每个排序键,默认排序方向均为 ASC
    • NULL:在 ORDER BY 子句的上下文中,NULL 是最小值:即以 ASC 方向排序时,NULL 出现在最前位置;以 DESC 方向排序时,出现在最后位置。
    • 浮点数据类型:请参阅浮点语义,了解排序和分组。
    • 如果同时还指定了 DISTINCT,排序键必须与 expression 相同。
    • 如果未指定 ORDER BY,输出数组中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。
  4. LIMIT:指定结果中 expression 输入的最大数量。此上限适用于输入字符串数量,而非输入中的字符数量或字节数量。空字符串计为 1。NULL 字符串不计数。上限 n 必须是 INT64 常量。

返回数据类型

STRING BYTES

示例

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+--------------+
| string_agg   |
+--------------+
| apple & pear |
+--------------+
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+---------------+
| string_agg    |
+---------------+
| pear & banana |
+---------------+
SELECT
  fruit,
  STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+--------+------------------------------+
| fruit  | string_agg                   |
+--------+------------------------------+
| NULL   | NULL                         |
| pear   | pear & pear                  |
| pear   | pear & pear                  |
| apple  | pear & pear & apple          |
| banana | pear & pear & apple & banana |
+--------+------------------------------+

SUM

SUM([DISTINCT] expression)  [OVER (...)]

说明

返回非 NULL 值之和。

如果表达式是浮点值,则总和是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

支持的参数类型

任何支持的数字数据类型。

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数

  2. DISTINCTexpression 的每个不同的值仅聚合到结果中一次。

返回数据类型

  • 如果输入是整数,则返回 INT64。

  • 如果输入类型是 NUMERIC,则返回 NUMERIC。

  • 如果输入是浮点值,则返回 FLOAT64。

如果输入仅包含 NULL,则返回 NULL

如果输入包含 Inf,则返回 Inf

如果输入包含 -Inf,则返回 -Inf

如果输入包含 NaN,则返回 NaN

如果输入包含 Inf-Inf 的组合,则返回 NaN

示例

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 6   |
| 3 | 6   |
| 1 | 10  |
| 4 | 10  |
| 4 | 10  |
| 1 | 10  |
| 2 | 9   |
| 5 | 9   |
| 2 | 9   |
+---+-----+
SELECT
  x,
  SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 3   |
| 3 | 3   |
| 1 | 5   |
| 4 | 5   |
| 4 | 5   |
| 1 | 5   |
| 2 | 7   |
| 5 | 7   |
| 2 | 7   |
+---+-----+
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

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