集計関数

集計関数は、グループの行を 1 つの値に集約する関数です。COUNT、MIN、MAX などが集計関数の例として挙げられます。

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 句と組み合わせて使用すると、集約されたグループには通常、少なくとも 1 つの行があります。関連付けられた SELECT に GROUP BY 句がない場合、またはある集計関数の修飾子によって集約されたグループの行がフィルタされる場合、集計関数は空のグループを集計する必要がある可能性があります。この場合、COUNT 関数と COUNTIF 関数は 0 を返し、他のすべての集計関数は NULL を返します。

次のセクションでは、Cloud Spanner SQL でサポートされている集計関数について説明します。

ANY_VALUE

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

説明

グループから選択された行の expression を返します。どの行が選択されるかは非決定的で、ランダムではありません。入力によって行が生成されない場合、NULL を返します。グループ内のすべての行で expression が NULL の場合、NULL を返します。

ANY_VALUE は、IGNORE NULLS が指定されているかのように動作します。expression が NULL である行は考慮されず、そのような行は選択されません。

サポートされる引数の型

すべて

省略可能な句

HAVING MAX または HAVING 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 を除くすべてのデータ型。

省略可能な句

次の順番で句が適用されます。

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. IGNORE NULLS または RESPECT NULLS: IGNORE NULLS が指定されている場合、NULL 値は結果から除外されます。RESPECT NULLS を指定した場合、あるいは両方とも指定しない場合、結果に NULL 値が含まれます。
  3. HAVING MAX または HAVING 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 から要素を結合し、結果として単一の ARRAY を返します。この関数は NULL 入力配列を無視しますが、NULL 以外の入力配列では NULL 要素を尊重します。

サポートされる引数の型

ARRAY

省略可能な句

HAVING MAX または HAVING 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. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING 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. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING 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. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING 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. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING 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. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING 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])

説明

expression に対する TRUE の値の数を返します。入力行がない場合、または expression がすべての行に対して FALSE または NULL と評価される場合に 0 を返します。

サポートされる引数の型

BOOL

省略可能な句

次の順番で句が適用されます。

  1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING 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 式の論理 AND を返します。入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。

サポートされる引数の型

BOOL

省略可能な句

HAVING MAX または HAVING 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 式の論理 OR を返します。入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。

サポートされる引数の型

BOOL

省略可能な句

HAVING MAX または HAVING 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 を返します。

サポートされる引数の型

ARRAY、STRUCT を除くすべてのデータ型

省略可能な句

HAVING MAX または HAVING 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 を返します。

サポートされる引数の型

ARRAY、STRUCT を除くすべてのデータ型

省略可能な句

HAVING MAX または HAVING 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. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING 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. DISTINCT: expression の重複を除いた値を集約して結果を返します。
  2. HAVING MAX または HAVING 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 MAX と HAVING MIN という 2 つのオプションの句をサポートし、関数が集計する行のセットを特定の列の最大値または最小値を持つ行に制限します。通常、構文は次のようになります。

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX: 関数が集計する行のセットを、グループ内の expression2 の最大値と等しい expression2 の値を持つ行に制限します。最大値は MAX(expression2) の結果と同じです。
  • HAVING MIN: 関数が集計する行のセットを、グループ内の expression2 の最小値と等しい expression2 の値を持つ行に制限します。最小値は MIN(expression2) の結果と同じです。

これらの句では、すべての行に対して expression2 が NULL と評価されない限り、最大値または最小値を計算する際には、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 列に最大値を持つ行を取得します。次の 2 つがあります。

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

最後に、クエリは inches 列の値（9 と 1）の平均を計算して、次の結果を返します。

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