標準 SQL 中的 Array 函式

ARRAY

ARRAY(subquery)

說明

ARRAY 函式會傳回 ARRAY子查詢中的每個資料列都有一個元素。

如果 subquery 產生 SQL 資料表,則該資料表只能有一個資料欄。輸出 ARRAY 中的每個元素都是資料表中資料列的單一資料欄的值。

如果 subquery 產生值資料表,則輸出 ARRAY 中的每個元素都是值資料表的完整對應資料列。

限制

  • 子查詢沒有排序,所以輸出 ARRAY 的元素不會保證保留子查詢來源資料表中的任何順序。但是,如果子查詢包含 ORDER BY 子句,ARRAY 函式將會傳回遵循那個子句的 ARRAY
  • 如果子查詢傳回一個以上的資料欄,ARRAY 函式會傳回錯誤。
  • 如果子查詢傳回 ARRAY 類型資料欄或 ARRAY 類型資料列,則 ARRAY 函式會傳回錯誤:BigQuery 不支援元素類型為 ARRAYARRAY
  • 如果子查詢傳回零個資料列,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 函式將會傳回 STRUCTARRAYARRAY 將會針對子查詢中的每個資料列包含一個 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}] |
+------------------------+

同樣的,若要從包含一或多個 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])

說明

將具有相同元素類型的一或多個陣列串連為單一陣列。

傳回類型

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_expression 大於 end_expression,且 step_expression 值為正。

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

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

以下會傳回 NULL 陣列,因為 end_expressionNULL

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 值的 ARRAY。

範例

以下會以預設步階 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_date 大於 end_date,且 step 值為正。

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

+---------+
| example |
+---------+
| []      |
+---------+

以下會傳回 NULL 陣列,因為它的其中一個輸入是 NULL

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

以下會使用 MONTH 作為 date_part 間隔傳回日期的陣列:

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)

說明

傳回按指定間隔區隔 TIMESTAMPSARRAYstart_timestampend_timestamp 參數決定 ARRAY 的上下界 (含)。

GENERATE_TIMESTAMP_ARRAY 函式接受下列資料類型做為輸入:

  • start_timestampTIMESTAMP
  • end_timestampTIMESTAMP
  • step_expressionINT64
  • 允許的 date_part 值有:

    MICROSECONDMILLISECOND

    SECONDMINUTEHOURDAY

step_expression 參數決定用來產生時間戳記的遞增量。

傳回資料類型

含有 0 或多個 TIMESTAMP 值的 ARRAY

範例

以下範例傳回 TIMESTAMP 以 1 秒為間隔的 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] |
+--------------------------------------------------------------------------+

以下範例傳回單一元素組成的 ARRAY,因為 start_timestampend_timestamp 的值相同。

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

以下範例傳回空的 ARRAY,因為 start_timestamp 的時間比 end_timestamp 晚。

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| []              |
+-----------------+

以下範例傳回空值 ARRAY,因為其中一個輸入是 NULL

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 表示編號從一開始。

指定陣列可解讀為以 0 為基礎或以 1 為基礎。存取陣列元素時,您必須在陣列位置前面分別加上 OFFSETORDINAL;沒有預設行為。

如果索引超出範圍,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           |
+----------------------------------+---------------+----------------+
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
需要協助嗎?請前往我們的支援網頁