集計関数は、グループの行を 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 を除くすべてのデータ型。
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。IGNORE NULLS
またはRESPECT NULLS
:IGNORE NULLS
を指定した場合、NULL
値は結果から除外されます。RESPECT NULLS
を指定した場合、結果にはNULL
の値が含まれます。どちらも指定されない場合、結果にはNULL
の値が含まれます。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 のような、任意の数値入力型。浮動小数点入力型の場合、戻り値の結果は変動する可能性があります、つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。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
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。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
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。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
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。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])
説明
- 入力内の行数を返します。
expression
がNULL
以外の値と評価された行の数を返します。
サポートされる引数の型
expression
には任意のデータ型を指定できます。DISTINCT
が存在する場合、expression
にはグループ化可能なデータ型のみ指定できます。
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。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
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。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
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。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 以外の値の合計を返します。
式が浮動小数点値である場合、合計は変動する可能性があります。つまり、この関数を使用するたびに異なる結果を受け取る可能性があります。
サポートされる引数の型
サポートされているすべての数値データ型。
省略可能な句
次の順番で句が適用されます。
DISTINCT
:expression
の重複を除いた値を集約して結果を返します。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
値は無視されます。
これらの句は、データ型 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 |
+---------+