BigQuery でいう配列とは、ゼロ個以上の同じデータ型の値で構成された順序付きリストのことです。INT64 のような簡単なデータ型で構成した配列や、STRUCT などの複雑なデータ型で構成した配列などを作成できます。配列の配列はサポートされていないため、現在のところ、ARRAY データ型はこの例外です。NULL の処理を含む ARRAY データ型の詳細については、配列型をご覧ください。
BigQuery では、配列のリテラルを構成し、ARRAY 関数を使用してサブクエリから配列を作成したうえで、ARRAY_AGG 関数を使用して配列に値を集約できます。
ARRAY_CONCAT() のような関数を使用して複数の配列を結合し、ARRAY_TO_STRING() を使用して配列を文字列に変換できます。
配列の作成
配列リテラルの使用
BigQuery ではかっこ（[ と ]）を使用して配列リテラルを作成できます。配列の各要素はカンマで区切ります。
SELECT [1, 2, 3] as numbers;
SELECT ["apple", "pear", "orange"] as fruit;
SELECT [true, false, true] as booleans;
互換性のある型を使用した式から配列を作成することもできます。例:
SELECT [a, b, c]
FROM
(SELECT 5 AS a,
37 AS b,
406 AS c);
SELECT [a, b, c]
FROM
(SELECT CAST(5 AS INT64) AS a,
CAST(37 AS FLOAT64) AS b,
406 AS c);
2 番目の例では、INT64 を返す式、FLOAT64 を返す式、およびリテラルを宣言する式という 3 つの式を使用しています。これら 3 つの式はすべて FLOAT64 をスーパータイプとして共有するため、上記の式は正常に機能します。
配列に特定のデータ型を宣言するには、山かっこ（< と >）を使用します。
SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;
INT64 や STRING など大半のデータ型の配列では、これらのデータ型を先に宣言しておく必要はありません。
SELECT [1, 2, 3] as numbers;
ARRAY<type>[] を使用すると、特定の型の空の配列を作成できます。また、[] を使用して、型のない空の配列を作成することもできます。この場合、BigQuery は周囲のコンテキストから配列の型を推測します。BigQuery が型を推測できない場合は、デフォルトの型 ARRAY<INT64> が使用されます。
生成された値の使用
生成された値を使用して ARRAY を作成することもできます。
整数の配列の生成
GENERATE_ARRAY は、開始値と終了値、ステップ値で構成される配列を生成します。たとえば、次のクエリは、11 から 33 のすべての奇数の整数で構成された配列を生成します。
SELECT GENERATE_ARRAY(11, 33, 2) AS odds;
+--------------------------------------------------+
| odds |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+
負のステップ値を指定して、降順の値で構成された配列を生成することもできます。
SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;
+----------------------------------+
| countdown |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+
日付の配列の生成
GENERATE_DATE_ARRAY は、開始および終了の DATE とステップ INTERVAL で構成される DATE の配列を生成します。
GENERATE_DATE_ARRAY を使用して DATE 値のセットを生成できます。たとえば、次のクエリは、現在の DATE と以降の 1 WEEK ごとの DATE を、後に指定されている DATE まで返します。
SELECT
GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
AS date_array;
+--------------------------------------------------------------------------+
| date_array |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
+--------------------------------------------------------------------------+
配列の要素へのアクセス
以下のテーブル sequences を例に取り上げます。
+---------------------+
| some_numbers |
+---------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [5, 10] |
+---------------------+
このテーブルには、ARRAY データ型の列 some_numbers があります。この列にある配列の要素にアクセスするには、使用するインデックスのタイプを指定する必要があります。ゼロから始まるインデックスであれば OFFSET、1 から始まるインデックスであれば ORDINAL を指定します。
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
some_numbers[OFFSET(1)] AS offset_1,
some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;
+--------------------+----------+-----------+
| some_numbers | offset_1 | ordinal_1 |
+--------------------+----------+-----------+
| [0, 1, 1, 2, 3, 5] | 1 | 0 |
| [2, 4, 8, 16, 32] | 4 | 2 |
| [5, 10] | 10 | 5 |
+--------------------+----------+-----------+
長さの確認
ARRAY_LENGTH() 関数は配列の長さを返します。
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
ARRAY_LENGTH(some_numbers) AS len
FROM sequences;
+--------------------+--------+
| some_numbers | len |
+--------------------+--------+
| [0, 1, 1, 2, 3, 5] | 6 |
| [2, 4, 8, 16, 32] | 5 |
| [5, 10] | 2 |
+--------------------+--------+
配列内の要素をテーブル内の行に変換する
ARRAY を一組の行に変換（フラット化）するには、UNNEST 演算子を使用します。UNNEST は ARRAY を取得し、ARRAY に含まれる各要素を 1 行にしたテーブルを返します。
UNNEST は ARRAY 要素の順序を無視するため、テーブルで順序の復元が必要になる場合があります。その場合、オプションの WITH OFFSET 句を使用して各要素のオフセットを含む追加の列を取得し、ORDER BY 句でそれぞれのオフセットを使用して行を並べ替えます。
例
SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
AS element
WITH OFFSET AS offset
ORDER BY offset;
+----------+--------+
| element | offset |
+----------+--------+
| foo | 0 |
| bar | 1 |
| baz | 2 |
| qux | 3 |
| corge | 4 |
| garply | 5 |
| waldo | 6 |
| fred | 7 |
+----------+--------+
各行の他の列の値を維持したまま ARRAY の列全体をフラット化するには、相関するクロス結合 を使用して、ARRAY 列を含むテーブルとその ARRAY 列の UNNEST 出力を結合します。
相関結合の場合、UNNEST 演算子は、以前に FROM 句に指定したソーステーブルの各行から ARRAY 型の列を参照します。ソーステーブルの N 行ごとに、UNNEST は、ARRAY の行 N を ARRAY 要素を含む一組の行にフラット化し、クロス結合がこの新しい一組の行をソーステーブルの単一行 N と結合します。
例
次の例では、UNNEST を使用して配列の列内の各要素の行を返しています。CROSS JOIN のため、id 列の id の値は、各数値を含む行の sequences 内での値を表します。
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;
+------+-------------------+
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
+------+-------------------+
相関クロス結合の場合、UNNEST 演算子は省略可能で、CROSS JOIN はカンマ結合として表現できます。この省略表記を使用すると、上記の例は次のようになります。
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences, sequences.some_numbers AS flattened_numbers;
+------+-------------------+
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
+------+-------------------+
ネストされた配列のクエリ
テーブルに STRUCT の ARRAY が含まれている場合に、その STRUCT のフィールドをクエリするには、ARRAY をフラット化します。また、STRUCT 値の ARRAY 型フィールドをフラット化することもできます。
ARRAY 内の STRUCT 要素のクエリ
次の例では、UNNEST と CROSS JOIN を使用して、STRUCT の ARRAY をフラット化することもできます。
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps)]
AS participants)
SELECT
race,
participant
FROM races r
CROSS JOIN UNNEST(r.participants) as participant;
+------+---------------------------------------+
| race | participant |
+------+---------------------------------------+
| 800M | {Rudisha, [23.4, 26.3, 26.4, 26.1]} |
| 800M | {Makhloufi, [24.5, 25.4, 26.6, 26.1]} |
| 800M | {Murphy, [23.9, 26, 27, 26]} |
| 800M | {Bosse, [23.6, 26.2, 26.5, 27.1]} |
| 800M | {Rotich, [24.7, 25.6, 26.9, 26.4]} |
| 800M | {Lewandowski, [25, 25.7, 26.3, 27.2]} |
| 800M | {Kipketer, [23.2, 26.1, 27.3, 29.4]} |
| 800M | {Berian, [23.7, 26.1, 27, 29.3]} |
+------+---------------------------------------+
繰り返しフィールドから特定の情報を検索できます。たとえば、次のクエリを実行すると、800m 競走で最速の参加者が返されます。
次の例では、配列のフラット化を行っていませんが、よく使われる方法で繰り返しフィールドから情報を取得しています。
例
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
ORDER BY (
SELECT SUM(duration)
FROM UNNEST(laps) AS duration) ASC
LIMIT 1) AS fastest_racer
FROM races;
+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha |
+------+---------------+
STRUCT 内の ARRAY 型フィールドのクエリ
ネストされた繰り返しフィールドの情報も取得できます。たとえば、次のステートメントでは 800m 競走で最速ラップの参加者が返されます。
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants),
UNNEST(laps) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
前のクエリでは、カンマ演算子（,）で CROSS JOIN を暗黙的に実行していますが、次の例では CROSS JOIN を明示的に実行しています。
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
CROSS JOIN UNNEST(laps) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
CROSS JOIN を使用して配列をフラット化すると、空の配列または NULL 配列を含む行は除外されます。このような行を入れる場合には、LEFT JOIN を使用します。
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as laps),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as laps),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as laps),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as laps),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as laps),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as laps),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as laps),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as laps),
STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as laps),
STRUCT("David" as name, NULL as laps)]
AS participants)
SELECT
name, sum(duration) AS finish_time
FROM races, races.participants LEFT JOIN participants.laps duration
GROUP BY name;
+-------------+--------------------+
| name | finish_time |
+-------------+--------------------+
| Murphy | 102.9 |
| Rudisha | 102.19999999999999 |
| David | NULL |
| Rotich | 103.6 |
| Makhloufi | 102.6 |
| Berian | 106.1 |
| Bosse | 103.4 |
| Kipketer | 106 |
| Nathan | NULL |
| Lewandowski | 104.2 |
+-------------+--------------------+
サブクエリからの配列の作成
配列の操作で多用する作業として、サブクエリを配列に変換する操作があります。BigQuery では、ARRAY() 関数を使用してこのオペレーションを実現できます。
たとえば、sequences テーブルで以下の操作を実行するとします。
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x) AS doubled
FROM sequences;
+--------------------+---------------------+
| some_numbers | doubled |
+--------------------+---------------------+
| [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
| [2, 4, 8, 16, 32] | [4, 8, 16, 32, 64] |
| [5, 10] | [10, 20] |
+--------------------+---------------------+
この例では sequences というテーブルから開始します。このテーブルには、ARRAY<INT64> 型で構成される列 some_numbers があります。
このクエリ自体にサブクエリがあります。このサブクエリは、some_numbers 列の各行を選択し、UNNEST を使用して配列を一組の行として返します。次に各値に 2 を乗算し、ARRAY() 演算子を使用してこれらの行を配列に再結合します。
配列のフィルタ処理
次の例では、ARRAY() 演算子のサブクエリで WHERE 句を使用して、返される行にフィルタを適用します。
注: 次の例では、結果の行が順序どおりになっていません。
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x
WHERE x < 5) AS doubled_less_than_five
FROM sequences;
+------------------------+
| doubled_less_than_five |
+------------------------+
| [0, 2, 2, 4, 6] |
| [4, 8] |
| [] |
+------------------------+
3 番目の行は空の配列となっています。これは、この配列に対応する元の行（[5, 10]）がこのフィルタの要件である x < 5 を満たしていないからです。
SELECT DISTINCT を使用して、配列内の一意の要素のみが返されるようにすることもできます。
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT ARRAY(SELECT DISTINCT x
FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences;
+-----------------+
| unique_numbers |
+-----------------+
| [0, 1, 2, 3, 5] |
+-----------------+
また、IN キーワードを使用して配列の行にフィルタを適用することもできます。このキーワードを使用すると、配列の要素が特定の値に一致するかどうかが判断されて、一致する場合はその配列を含む行にフィルタが適用されます。
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x
FROM UNNEST(some_numbers) AS x
WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM sequences;
+--------------------+
| contains_two |
+--------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [] |
+--------------------+
ここでも、3 番目の行には空の配列があります。この配列に対応する元の行（[5, 10]）に 2 がないからです。
配列のスキャン
配列に特定の値が含まれているかどうかを確認するには、IN 演算子を UNNEST と併用します。条件に一致する値が配列に含まれているかどうかを確認するには、EXISTS 演算子を UNNEST と併用します。
特定の値のスキャン
配列の特定の値をスキャンするには、IN 演算子と UNNEST を使用します。
例
次の例では、配列に数値 2 が含まれている場合に true を返します。
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
+----------------+
| contains_value |
+----------------+
| true |
+----------------+
配列の列に特定の値が含まれているテーブルの行を返すには、WHERE 句を使用して IN UNNEST の結果をフィルタリングします。
例
次の例では、配列の列に値 2 が含まれている行の id 値を返します。
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE 2 IN UNNEST(sequences.some_numbers)
ORDER BY matching_rows;
+---------------+
| matching_rows |
+---------------+
| 1 |
| 2 |
+---------------+
条件を満たす値のスキャン
条件に一致する値が配列に含まれているかどうかスキャンするには、UNNEST を使用して配列内の要素テーブルを取得します。次に、サブクエリで WHERE を使用して、取得したテーブルをフィルタリングします。最後に EXISTS を使用して、フィルタリングされたテーブルに任意の行が含まれているかどうかを確認します。
例
次の例では、配列の列に 5 より大きい値を含む行の id 値を返します。
WITH
Sequences AS (
SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL
SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL
SELECT 3 AS id, [5, 10] AS some_numbers
)
SELECT id AS matching_rows
FROM Sequences
WHERE EXISTS(SELECT * FROM UNNEST(some_numbers) AS x WHERE x > 5);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+
条件を満たす STRUCT フィールド値のスキャン
条件に一致するフィールドの STRUCT 配列を検索するには、UNNEST を使用して、各 STRUCT フィールドの列を含むテーブルを取得し、WHERE EXISTS を使用して条件に一致しない行をフィルタリングします。
例
次の例では、フィールド b の値が 3 より大きい STRUCT を含む配列の行を返します。
WITH
Sequences AS (
SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
UNION ALL
SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
UNION ALL
SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT(7 AS a, 4 AS b)] AS some_numbers
)
SELECT id AS matching_rows
FROM Sequences
WHERE EXISTS(SELECT 1 FROM UNNEST(some_numbers) WHERE b > 3);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+
配列と集約
BigQuery では、ARRAY_AGG() を使用して配列に値を集約できます。
WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;
+-----------------------+
| fruit_basket |
+-----------------------+
| [apple, pear, banana] |
+-----------------------+
ARRAY_AGG() によって返される配列は、この関数では値が連結される順序が保証されていないため不定の順序になります。配列要素の順序を整えるには ORDER BY を使用します。例:
WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket
FROM fruits;
+-----------------------+
| fruit_basket |
+-----------------------+
| [apple, banana, pear] |
+-----------------------+
配列の要素に SUM() などの集約関数を適用することもできます。たとえば、次のクエリは sequences テーブルの行ごとに配列要素の合計を返します。
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
(SELECT SUM(x)
FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;
+--------------------+------+
| some_numbers | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12 |
| [2, 4, 8, 16, 32] | 62 |
| [5, 10] | 15 |
+--------------------+------+
BigQuery では ARRAY_CONCAT_AGG() 関数もサポートしています。この集約関数は、配列の列にある要素を複数の行にわたって連結します。
WITH aggregate_example AS
(SELECT [1,2] AS numbers
UNION ALL SELECT [3,4] AS numbers
UNION ALL SELECT [5, 6] AS numbers)
SELECT ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg
FROM aggregate_example;
+--------------------------------------------------+
| count_to_six_agg |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
注: ARRAY_CONCAT_AGG() では値が連結される順序が保証されていません。この関数によって返される配列要素の順序は変動する可能性があります。
配列から文字列への変換
ARRAY_TO_STRING() 関数を使用すると、ARRAY<STRING> を単一の STRING 値に変換できます。また、ARRAY<BYTES> を単一の BYTES 値に変換することもできます。この場合、変換後の値は、配列の要素が順序付けられて連結された形になります。
2 番目の引数は、関数が出力を生成するために入力項目間に挿入する区切り文字です。この 2 番目の引数は、最初の引数の要素と同じ型でなければなりません。
例:
WITH greetings AS
(SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM greetings;
+-------------+
| greetings |
+-------------+
| Hello World |
+-------------+
オプションの 3 番目の引数は、入力配列に含まれる NULL 値に置き換わります。
この引数を省略すると、関数は
NULL配列要素を無視します。空の文字列を指定すると、関数は
NULL配列要素の代わりに区切り文字を挿入します。
例:
SELECT
ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string,
ARRAY_TO_STRING(arr, ".", "") AS empty_string,
ARRAY_TO_STRING(arr, ".") AS omitted
FROM (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr);
+------------------+--------------+---------+
| non_empty_string | empty_string | omitted |
+------------------+--------------+---------+
| a.N.b.N.c.N | a..b..c. | a.b.c |
+------------------+--------------+---------+
配列の結合
複数の配列を単一の配列に結合することが必要になる場合があります。ARRAY_CONCAT() 関数を使用して、この操作を実現できます。
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
配列の圧縮
同じサイズの 2 つの配列を指定すると、対応する位置から取得した入力配列の要素のペアからなる単一の配列に結合できます。このオペレーションは、圧縮とも呼ばれます。
UNNEST と WITH OFFSET を使用して配列を圧縮できます。この例では、各値のペアが STRUCT として配列に格納されます。
WITH
combinations AS (
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT
ARRAY(
SELECT AS STRUCT
letters[SAFE_OFFSET(index)] AS letter,
numbers[SAFE_OFFSET(index)] AS number
FROM combinations
CROSS JOIN
UNNEST(
GENERATE_ARRAY(
0,
LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
);
+------------------------------+
| pairs |
+------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }] |
+------------------------------+
最初の配列が 2 番目の配列の長さ以下であれば、異なる長さの入力配列を使用できます。圧縮された配列は、最も短い入力配列の長さになります。
入力配列の長さが異なる場合でも、すべての要素を含む圧縮された配列を取得するには、LEAST を GREATEST に変更します。一方の配列で他方の配列に関連する要素がない要素は、NULL とペアリングされます。
WITH
combinations AS (
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT
ARRAY(
SELECT AS STRUCT
letters[SAFE_OFFSET(index)] AS letter,
numbers[SAFE_OFFSET(index)] AS number
FROM combinations
CROSS JOIN
UNNEST(
GENERATE_ARRAY(
0,
GREATEST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
ORDER BY index
);
+-------------------------------+
| pairs |
+-------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }, |
| { letter: null, number: 3 }] |
+-------------------------------+
配列の配列の作成
BigQuery では、配列の配列を直接作成することはできません。代わりの方法として、それぞれが ARRAY 型のフィールドを持つ構造体の配列を作成します。以下の points テーブルはこの方法を示しています。
+----------+
| point |
+----------+
| [1, 5] |
| [2, 8] |
| [3, 7] |
| [4, 1] |
| [5, 7] |
+----------+
ここで、points テーブルの各 point で構成する配列を作成するとします。この処理を行うため、各行から返される配列を STRUCT にラップします。
WITH points AS
(SELECT [1, 5] as point
UNION ALL SELECT [2, 8] as point
UNION ALL SELECT [3, 7] as point
UNION ALL SELECT [4, 1] as point
UNION ALL SELECT [5, 7] as point)
SELECT ARRAY(
SELECT STRUCT(point)
FROM points)
AS coordinates;
+-------------------+
| coordinates |
+-------------------+
| [{point: [1,5]}, |
| {point: [2,8]}, |
| {point: [5,7]}, |
| {point: [3,7]}, |
| {point: [4,1]}] |
+--------------------+