聚合函数

聚合函数是将组中的行汇总为单个值的函数。例如,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 |
+-------------+----------------+-------+------+

GROUP BY 子句一起使用时,汇总的组通常至少具有一行。当关联的 SELECT 没有 GROUP BY 子句时,或者当某些聚合函数修饰符从要汇总的组中过滤行时,聚合函数可能需要汇总空组。在这种情况下,COUNTCOUNTIF 函数返回 0,而所有其他聚合函数均返回 NULL

以下部分介绍了 Cloud Spanner SQL 支持的聚合函数。

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

说明

对从组中选择的某一行返回 expression。选择哪一行不是确定性的,也不是随机的。如果输入未生成任何行,则返回 NULL。当组中的所有行的 NULLexpression 时,返回 NULL

ANY_VALUE 的行为相当于指定了 IGNORE NULLS;系统不会考虑也不会选择 expressionNULL 的行。

支持的参数类型

任意

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

与输入数据类型相匹配。

示例

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

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

说明

返回一个由 expression 值组成的 ARRAY。

支持的参数类型

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

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. IGNORE NULLSRESPECT NULLS:如果指定了 IGNORE NULLS,则结果中不会包含 NULL 值。如果指定了 RESPECT NULLS 或者未指定任何一个子句,则结果中会包含 NULL 值。
  3. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

输出元素顺序

输出中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

返回的数据类型

ARRAY

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

示例

SELECT 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 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 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] |
+-------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

说明

连接类型为 ARRAY 的 expression 中的元素,并返回一个数组作为结果。此函数会忽略 NULL 输入数组,但会沿用非 NULL 输入数组中的 NULL 元素。

支持的参数类型

ARRAY

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

输出元素顺序

输出中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

返回的数据类型

ARRAY

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

示例

SELECT 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] |
+-----------------------------------+

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

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

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

  • 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 |
+------+

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

  • INT64

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

INT64

示例

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

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

BIT_OR

BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

  • INT64

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

INT64

示例

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

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

BIT_XOR

BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

  • INT64

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

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(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

expression 可以是任何数据类型。

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

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 COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

布尔值

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

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            |
+--------------+--------------+

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

BOOL

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

BOOL

示例

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

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

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

BOOL

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

BOOL

示例

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

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

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

任意数据类型(ARRAYSTRUCT 除外)

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

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

示例

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

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

说明

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

支持的参数类型

任意数据类型(ARRAYSTRUCT 除外)

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

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

示例

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

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

说明

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

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

支持的参数类型

STRING BYTES

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

输出元素顺序

输出中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

返回数据类型

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 |
+-----------------------+

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

返回非 NULL 值之和。

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

支持的参数类型

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

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

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

如果输入仅包含 NULL,则返回 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 SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

常用子句

HAVING MAX 和 HAVING MIN 子句

大多数聚合函数支持两个名为 HAVING MAXHAVING MIN 的可选子句,这些子句将函数聚合的行限制为在某个特定列中具有最大值或最小值的行。语法通常如下所示:

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX:将函数聚合的行限制为 expression2 的值等于该组中 expression2 最大值的行。最大值等于 MAX(expression2) 的结果。
  • HAVING MIN:将函数聚合的行限制为 expression2 的值等于该组中 expression2 最小值的行。最小值等于 MIN(expression2) 的结果。

这些子句在计算最大值或最小值时会忽略 NULL 值,除非所有行的 expression2 求值结果均为 NULL

这些子句不支持以下数据类型:ARRAY STRUCT

示例

在此示例中,返回最近一年(2001 年)的平均降雨量。

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

首先,查询获取 year 列中具有最大值的行。这样的行有两个:

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

最后,查询会对 inches 列中的值(9 和 1)求平均值:

+---------+
| average |
+---------+
| 5       |
+---------+