标准 SQL 中的数组函数

ARRAY

ARRAY(subquery)

说明

ARRAY 函数返回 ARRAY 类型,其中子查询中的每一行都对应一个元素。

如果 subquery 生成一个标准的 SQL 表,此表只能具有一列。ARRAY 输出中的每个元素都是此表中这一列某行的值。

如果 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 函数将返回 STRUCTARRAY 类型。ARRAY 将为子查询中的每一行包含一个 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 ["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_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_expressionstep_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_datestep 值为正数。

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

以下命令返回一个日期数组,使用作为 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] |
+--------------------------------------------------------------------------+

以下命令使用非常量日期生成一个数组。

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 ARRAYstart_timestampend_timestamp 参数确定 ARRAY 所包含的上边界和下边界。

GENERATE_TIMESTAMP_ARRAY 函数接受以下数据类型的输入:

  • start_timestampTIMESTAMP
  • end_timestampTIMESTAMP
  • step_expressionINT64
  • 允许的 date_part 值为 MICROSECONDMILLISECONDSECONDMINUTEHOURDAY

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

以下示例返回一个包含单个元素的 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 |
+-----------------+
| []              |
+-----------------+

以下示例返回一个 null 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 值的列中生成 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 表示从 0 开始计数,ORDINAL 表示从 1 开始计数。

给定数组既可解释为从 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           |
+----------------------------------+---------------+----------------+
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面