標準 SQL の ARRAY 関数

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 関数は STRUCTARRAY を返します。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_expressionNULL である場合、NULL を返します。

戻り値の型

INT64

WITH items AS
      (SELECT ["coffee", NULL, "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 |
    +---------------------------------+------+
    | [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_expressionend_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_expressionend_expression に同じ値を使用して配列を返します。

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

    +---------------+
    | example_array |
    +---------------+
    | [4]           |
    +---------------+
    

以下の場合、start_expressionend_expression よりも大きく、step_expression が正の値であるため、空の配列を返します。

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

    +---------------+
    | example_array |
    +---------------+
    | []            |
    +---------------+
    

以下の場合、end_expressionNULL であるため、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_dateend_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_dateend_date に同じ値を使用して配列を返します。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
      '2016-10-05', INTERVAL 8 DAY) AS example;

    +--------------+
    | example      |
    +--------------+
    | [2016-10-05] |
    +--------------+

    

以下の場合、start_dateend_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)
    

説明

指定の間隔で区切られた TIMESTAMPSARRAY を返します。start_timestampend_timestamp のパラメータは、ARRAY の包括的な上限と下限を示します。

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

  • start_timestamp: TIMESTAMP
  • end_timestamp: TIMESTAMP
  • step_expression: INT64
  • 指定できる date_part 値は次のとおりです。

    MICROSECONDMILLISECOND

    SECONDMINUTEHOURDAY

step_expression パラメータは、タイムスタンプの生成に使用される増分を示します。

戻りデータの型

0 個以上の TIMESTAMP 値が含まれている ARRAY

次の例では、間隔 1 日の TIMESTAMPARRAY を返します。

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

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

次の例では、間隔が負である TIMESTAMPSARRAY を返します。

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_timestampend_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_timestampend_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_timestampend_timestamp の値を含む列から TIMESTAMPARRAY を生成します。

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 をそれぞれ入れる必要があります。デフォルトの動作はありません。

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

戻り値の型

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

説明

OFFSETORDINAL と同じですが、インデックスが範囲外の場合に 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           |
    +----------------------------------+---------------+----------------+