集計関数

集計関数は、グループの行を 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 の値が含まれます。どちらも指定されない場合、結果には 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 句をご覧ください。

返されるデータ型

入力	INT64	NUMERIC	FLOAT64
出力	FLOAT64	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 |
+------+

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]
)

Description

1. 入力内の行数を返します。
2. expression が NULL 以外の値と評価された行の数を返します。

サポートされる引数の型

expression には任意のデータ型を指定できます。DISTINCT が存在する場合、expression にはグループ化可能なデータ型のみ指定できます。

省略可能な句

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

1. DISTINCT: expression の重複を除いた値を集約して結果を返します。
2. HAVING MAX または HAVING MIN: 関数が集計する行のセットを最大値または最小値で制限します。詳細については、HAVING MAX 句と HAVING MIN 句をご覧ください。

戻りデータの型

INT64

例

COUNT 関数を使用して、テーブル内の行数または式で重複しない値の数を返すことができます。次に例を示します。

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

特定の条件を満たす式で重複しない値の数をカウントする場合は、次のようなレシピを使用できます。

COUNT(DISTINCT IF(condition, expression, NULL))

ここで、IF は、condition が TRUE の場合は expression の値を返し、それ以外の場合は NULL を返します。周辺の COUNT(DISTINCT ...) では NULL 値が無視されるため、condition が TRUE である expression の個別の値のみがカウントされます。

たとえば、x の重複しない正の値をカウントするには:

SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;

+-------------------+
| distinct_positive |
+-------------------+
| 3                 |
+-------------------+

また、特定の種類のイベントが発生した個別の日付の数をカウントするには:

WITH Events AS (
  SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
  COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
    AS distinct_dates_with_failures
FROM Events;

+------------------------------+
| distinct_dates_with_failures |
+------------------------------+
| 2                            |
+------------------------------+

COUNTIF

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

説明

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

expression は BOOL でなければならないため、通常、COUNTIF(DISTINCT ...) の形式は有用ではありません。これは、TRUE に個別の値が 1 つのみ存在するためです。そのため、COUNTIF(DISTINCT ...) は、expression が 1 つ以上の入力行に対して TRUE として評価される場合は 1 を返し、それ以外の場合は 0 を返します。通常、COUNTIF と DISTINCT を組み合わせる必要がある場合は、特定の条件を満たす式の個別の値の数をカウントします。これを実現するレシピの 1 つは次のとおりです。

COUNT(DISTINCT IF(condition, expression, NULL))

これは COUNTIF ではなく COUNT を使用していることに注意してください。IF の部分が内部に移動しています。詳細については、COUNT の例をご覧ください。

サポートされる引数の型

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 を返します。

サポートされる引数の型

任意の順序付け可能なデータ型。

省略可能な句

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 を返します。

サポートされる引数の型

任意の順序付け可能なデータ型。

省略可能な句

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]
)

Description

非 NULL 値を結合して得られた値（STRING または BYTES のいずれか）を返します。 入力行がない場合、または expression がすべての行で NULL と評価される場合に NULL を返します。

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	NUMERIC	FLOAT64
出力	INT64	NUMERIC	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 値は無視されます。

これらの句では、順序付け可能なデータ型のみがサポートされます。

例

この例では、直近の 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       |
+---------+