ARRAY
ARRAY(subquery)
説明
ARRAY
関数は、サブクエリによって返される行ごとに 1 つの要素を含む ARRAY
を返します。
subquery
によって SQL テーブルが生成される場合、そのテーブルの列数は 1 つである必要があります。出力 ARRAY
の各要素は、テーブルの行に 1 つだけある列の値となります。
subquery
によって値テーブルが生成される場合、出力 ARRAY
の各要素は、値テーブルの対応する行全体となります。
制約
- サブクエリは順序付けされていないため、出力
ARRAY
の要素は、サブクエリのソーステーブルの順序を保証するものではありません。ただし、サブクエリにORDER BY
句が含まれている場合、ARRAY
関数はその句を使用するARRAY
を返します。 - サブクエリが複数の列を返す場合、
ARRAY
関数はエラーを返します。 - サブクエリが
ARRAY
型の列またはARRAY
型の行を返す場合、ARRAY
関数は、「ARRAY
型の要素を含むARRAY
は BigQuery でサポートされていない」というエラーを返します。 - サブクエリがゼロ行を返す場合、
ARRAY
関数は空のARRAY
を返します。NULL
ARRAY
は返しません。
戻り値の型
ARRAY
例
SELECT ARRAY
(SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS new_array;
+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+
複数の列を含むサブクエリから ARRAY
を構成するには、SELECT AS STRUCT
を使用するようにサブクエリを変更します。これで ARRAY
関数は STRUCT
の ARRAY
を返します。ARRAY
では、サブクエリの各行に 1 つの STRUCT
が含まれます。こうした STRUCT
には、その行の各列のフィールドが含まれます。
SELECT
ARRAY
(SELECT AS STRUCT 1, 2, 3
UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;
+------------------------+
| new_array |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+
同様に、1 つまたは複数の ARRAY
を含むサブクエリから ARRAY
を構成するには、SELECT AS STRUCT
を使用するサブクエリを変更します。
SELECT ARRAY
(SELECT AS STRUCT [1, 2, 3] UNION ALL
SELECT AS STRUCT [4, 5, 6]) AS new_array;
+----------------------------+
| new_array |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+
ARRAY_CONCAT
ARRAY_CONCAT(array_expression[, ...])
説明
同じ要素型の 1 つまたは複数の配列を 1 つの配列に連結します。
入力引数が NULL
の場合、この関数は NULL
を返します。
戻り値の型
ARRAY
例
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
ARRAY_LENGTH
ARRAY_LENGTH(array_expression)
説明
配列のサイズを返します。空の配列の場合は 0 を返します。array_expression
が NULL
である場合、NULL
を返します。
戻り値の型
INT64
例
WITH items AS
(SELECT ["coffee", NULL, "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT ARRAY_TO_STRING(list, ', ', 'NULL'), ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;
+--------------------+------+
| list | size |
+--------------------+------+
| coffee, NULL, milk | 3 |
| cake, pie | 2 |
+--------------------+------+
ARRAY_TO_STRING
ARRAY_TO_STRING(array_expression, delimiter[, null_text])
説明
array_expression
内の要素の連結を STRING として返します。array_expression
の値は STRING または BYTES データ型のいずれかの配列にできます。
null_text
パラメータが使用されている場合、この関数によって、配列内のすべての NULL
値が null_text
の値に置き換えられます。
null_text
パラメータが使用されていない場合、この関数は NULL
値とその前にある区切り文字を無視します。
例
WITH items AS
(SELECT ['coffee', 'tea', 'milk' ] as list
UNION ALL
SELECT ['cake', 'pie', NULL] as list)
SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie |
+--------------------------------+
WITH items AS
(SELECT ['coffee', 'tea', 'milk' ] as list
UNION ALL
SELECT ['cake', 'pie', NULL] as list)
SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie--MISSING |
+--------------------------------+
GENERATE_ARRAY
GENERATE_ARRAY(start_expression, end_expression[, step_expression])
説明
値の配列を返します。start_expression
と end_expression
のパラメータは、配列の包括的な開始点と終了点を示します。
GENERATE_ARRAY
関数は、入力として次のデータ型を受け入れます。
INT64
NUMERIC
BIGNUMERIC
FLOAT64
step_expression
パラメータは、配列の値を生成するために使用される増分を示します。このパラメータのデフォルト値は 1
です。
この関数は、step_expression
が 0 に設定されているか入力が NaN
の場合、エラーを返します。
引数が NULL
の場合、関数は NULL
配列を返します。
戻りデータの型
ARRAY
例
以下の場合、整数の配列を返します。デフォルトのステップは 1 です。
SELECT GENERATE_ARRAY(1, 5) AS example_array;
+-----------------+
| example_array |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
以下の場合、ユーザーが指定したステップサイズを使用して配列を返します。
SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9] |
+---------------+
以下の場合、負の値を使用して配列を返します。ステップサイズは -3
です。
SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;
+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+
以下の場合、start_expression
と end_expression
に同じ値を使用して配列を返します。
SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;
+---------------+
| example_array |
+---------------+
| [4] |
+---------------+
以下の場合、start_expression
が end_expression
よりも大きく、step_expression
が正の値であるため、空の配列を返します。
SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [] |
+---------------+
以下の場合、end_expression
が NULL
であるため、NULL
の配列を返します。
SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;
+---------------+
| example_array |
+---------------+
| NULL |
+---------------+
以下は、複数の配列を返します。
SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;
+---------------+
| example_array |
+---------------+
| [3, 4, 5] |
| [4, 5] |
| [5] |
+---------------+
GENERATE_DATE_ARRAY
GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])
説明
日付の配列を返します。start_date
と end_date
のパラメータは、配列の包括的な開始点と終了点を示します。
GENERATE_DATE_ARRAY
関数は、入力として次のデータ型を受け入れます。
start_date
は DATE にする必要があります。end_date
は DATE にする必要があります。INT64_expr
には INT64 にする必要があります。date_part
は、DAY、WEEK、MONTH、QUARTER または YEAR にする必要があります。
INT64_expr
パラメータは、日付の生成に使用される増分を示します。このパラメータのデフォルト値は 1 日です。
この関数は、INT64_expr
が 0 に設定されている場合、エラーを返します。
戻りデータの型
0 個以上の DATE 値が含まれている配列。
例
以下の場合、日付の配列を返します。デフォルトのステップは 1 です。
SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;
+--------------------------------------------------+
| example |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+
以下の場合、ユーザーが指定したステップサイズを使用して配列を返します。
SELECT GENERATE_DATE_ARRAY(
'2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;
+--------------------------------------+
| example |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+
以下の場合、負の値を使用して配列を返します。ステップサイズは -3
です。
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL -3 DAY) AS example;
+--------------------------+
| example |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+
以下の場合、start_date
と end_date
に同じ値を使用して配列を返します。
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-05', INTERVAL 8 DAY) AS example;
+--------------+
| example |
+--------------+
| [2016-10-05] |
+--------------+
以下の場合、start_date
が end_date
よりも大きく、step
が正の値であるため、空の配列を返します。
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL 1 DAY) AS example;
+---------+
| example |
+---------+
| [] |
+---------+
以下の場合、入力値の 1 つが NULL
であるため、NULL
の配列を返します。
SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;
+---------+
| example |
+---------+
| NULL |
+---------+
以下の場合、date_part
の間隔に MONTH を使用して、日付の配列を返します。
SELECT GENERATE_DATE_ARRAY('2016-01-01',
'2016-12-31', INTERVAL 2 MONTH) AS example;
+--------------------------------------------------------------------------+
| example |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+
以下の場合、非定数の日付を使用して配列を生成します。
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;
+--------------------------------------------------------------+
| date_range |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+
GENERATE_TIMESTAMP_ARRAY
GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
INTERVAL step_expression date_part)
説明
指定の間隔で区切られた TIMESTAMPS
の ARRAY
を返します。start_timestamp
と end_timestamp
のパラメータは、ARRAY
の包括的な上限と下限を示します。
GENERATE_TIMESTAMP_ARRAY
関数は、入力として次のデータ型を受け入れます。
start_timestamp
:TIMESTAMP
end_timestamp
:TIMESTAMP
step_expression
:INT64
date_part
に使用できる値は、MICROSECOND
、MILLISECOND
、SECOND
、MINUTE
、HOUR
、DAY
です。
step_expression
パラメータは、タイムスタンプの生成に使用される増分を示します。
戻りデータの型
0 個以上の TIMESTAMP
値が含まれている ARRAY
。
例
次の例では、間隔 1 日の TIMESTAMP
の ARRAY
を返します。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
INTERVAL 1 DAY) AS timestamp_array;
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
+--------------------------------------------------------------------------+
次の例では、間隔 1 秒の TIMESTAMP
の ARRAY
を返します。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
INTERVAL 1 SECOND) AS timestamp_array;
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+
次の例では、間隔が負である TIMESTAMPS
の ARRAY
を返します。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
INTERVAL -2 DAY) AS timestamp_array;
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+
次の例では、start_timestamp
と end_timestamp
の値が同じであるため、要素が 1 つの ARRAY
を返します。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
INTERVAL 1 HOUR) AS timestamp_array;
+--------------------------+
| timestamp_array |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+
次の例では、start_timestamp
が end_timestamp
の後であるため、空の ARRAY
を返します。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
INTERVAL 1 HOUR) AS timestamp_array;
+-----------------+
| timestamp_array |
+-----------------+
| [] |
+-----------------+
次の例では、入力の 1 つが NULL
であるため、null の ARRAY
を返します。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
AS timestamp_array;
+-----------------+
| timestamp_array |
+-----------------+
| NULL |
+-----------------+
次の例では、start_timestamp
と end_timestamp
の値を含む列から TIMESTAMP
の ARRAY
を生成します。
SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
AS timestamp_array
FROM
(SELECT
TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
UNION ALL
SELECT
TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
UNION ALL
SELECT
TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016-10-06 01:59:00+00] |
+--------------------------------------------------------------------------+
ARRAY_REVERSE
ARRAY_REVERSE(value)
説明
要素のある ARRAY 入力を逆順で返します。
戻り値の型
ARRAY
例
WITH example AS (
SELECT [1, 2, 3] AS arr UNION ALL
SELECT [4, 5] AS arr UNION ALL
SELECT [] AS arr
)
SELECT
arr,
ARRAY_REVERSE(arr) AS reverse_arr
FROM example;
+-----------+-------------+
| arr | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1] |
| [4, 5] | [5, 4] |
| [] | [] |
+-----------+-------------+