数组函数

ARRAY

ARRAY(subquery)

说明

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

如果 subquery 生成一个 SQL 表，则此表必须只能包含一列。ARRAY 输出中的每个元素都是此表中这一列某行的值。

限制条件

• 子查询是无序的，因此不保证输出 ARRAY 的元素为子查询保留源表中的任何顺序。但是，如果子查询包含 ORDER BY 子句，则 ARRAY 函数将返回一个遵循此子句的 ARRAY。
• 如果子查询返回多个列，则 ARRAY 函数会返回错误。
• 如果子查询返回 ARRAY 类型的列或 ARRAY 类型的行，则 ARRAY 函数会返回错误，说明 Cloud Spanner SQL 不支持元素为 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 都包含一个 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_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
• 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 |
+---------------+
| []            |
+---------------+

以下命令返回一个 NULL 数组，因为 end_expression 为 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 值的 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_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 |
+---------+
| []      |
+---------+

以下命令返回一个 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] |
+--------------------------------------------------------------+

OFFSET 和 ORDINAL

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

说明

按位置访问 ARRAY 元素并返回该元素。OFFSET 表示从 0 开始计数，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。

返回类型

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

ARRAY_IS_DISTINCT

ARRAY_IS_DISTINCT(value)

说明

如果数组不包含重复元素，且使用与 SELECT DISTINCT 相同的相等比较逻辑，则返回 true。

返回类型

BOOL

示例

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [1, 1, 1] AS arr UNION ALL
  SELECT [1, 2, NULL] AS arr UNION ALL
  SELECT [1, 1, NULL] AS arr UNION ALL
  SELECT [1, NULL, NULL] AS arr UNION ALL
  SELECT [] AS arr UNION ALL
  SELECT CAST(NULL AS ARRAY<INT64>) AS arr
)
SELECT
  arr,
  ARRAY_IS_DISTINCT(arr) as is_distinct
FROM example;

+-----------------+-------------+
| arr             | is_distinct |
+-----------------+-------------+
| [1, 2, 3]       | true        |
| [1, 1, 1]       | false       |
| [1, 2, NULL]    | true        |
| [1, 1, NULL]    | false       |
| [1, NULL, NULL] | false       |
| []              | true        |
| NULL            | NULL        |
+-----------------+-------------+

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