標準 SQL の配列関数

ARRAY

ARRAY(subquery)

説明

ARRAY 関数は、サブクエリの各行に対して 1 つの要素を含む ARRAY を返します。

subquery によって SQL テーブルが生成される場合、テーブルの列数は 1 つである必要があります。出力 ARRAY の各要素は、テーブルの行に 1 つだけある列の値となります。

subquery によって値テーブルが生成される場合、出力 ARRAY の各要素は、値テーブルの対応する行全体となります。

制約

サブクエリは順序付けされていないため、出力 ARRAY の要素は、サブクエリのソーステーブルの順序を保証するものではありません。ただし、サブクエリに ORDER BY 句が含まれている場合、ARRAY 関数はその句を使用する ARRAY を返します。
サブクエリが複数の列を返す場合、ARRAY 関数はエラーを返します。
サブクエリが ARRAY 型の列または ARRAY 型の行を返す場合、ARRAY 関数は、「BigQuery は型 ARRAY の要素を含む ARRAY はサポートしていない」というエラーを返します。
サブクエリがゼロ行を返す場合、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 [, array_expression_n])

説明

同じ要素型の 1 つまたは複数の配列を 1 つの配列に連結します。

戻り値の型

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 ["apples", "bananas", NULL, "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [apples, bananas, NULL, grapes] | 4    |
| [coffee, tea, 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 ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  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                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

説明

値の配列を返します。start_expression と end_expression のパラメータは、配列の包括的な開始点と終了点を示します。

GENERATE_ARRAY 関数は、入力として次のデータ型を受け入れます。

INT64
NUMERIC
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] |
+--------------------------------------------------------------------------+

以下の場合、非定数の日付を使用して配列を生成します。

WITH StartsAndEnds AS (
  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"
)
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM StartsAndEnds;

+--------------------------------------------------------------+
| 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-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 に同じ値が含まれるため、単一要素の 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] |
+--------------------------------------------------------------------------+

OFFSET と ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

説明

位置によって ARRAY 要素にアクセスし、その要素を返します。OFFSET は番号付けがゼロから始まることを意味し、ORDINAL は番号付けが 1 から始まることを意味します。

指定した配列は、0 ベースまたは 1 ベースのいずれかとして解釈できます。配列要素にアクセスする場合、配列位置の前に OFFSET または ORDINAL をそれぞれ入れる必要があります。デフォルトの動作はありません。

インデックスが範囲外の場合、OFFSET と ORDINAL はどちらもエラーを生成します。

戻り値の型

ARRAY 内の要素によって異なります。

例


WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

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

SAFE_OFFSET と SAFE_ORDINAL

array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]

説明

OFFSET や ORDINAL と同じですが、インデックスが範囲外の場合に NULL を返す点が異なります。

戻り値の型

ARRAY 内の要素によって異なります。

例


WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list,
  list[SAFE_OFFSET(3)] as safe_offset_3,
  list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;

+----------------------------------+---------------+----------------+
| list                             | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes        | pears          |
| [coffee, tea, milk]              | NULL          | milk           |
| [cake, pie]                      | NULL          | NULL           |
+----------------------------------+---------------+----------------+

このページは役立ちましたか？評価をお願いいたします。

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

最終更新日: 8月 6, 2019

標準 SQL の配列関数

ARRAY

ARRAY_CONCAT

ARRAY_LENGTH

ARRAY_TO_STRING

GENERATE_ARRAY

GENERATE_DATE_ARRAY

GENERATE_TIMESTAMP_ARRAY

OFFSET と ORDINAL

ARRAY_REVERSE

SAFE_OFFSET と SAFE_ORDINAL

フィードバックを送信...