ARRAY
ARRAY(subquery)
说明
ARRAY
函数返回一个 ARRAY
,其中子查询中的每一行都对应一个元素。
如果 subquery
生成一个 SQL 表,则此表必须只能包含一列。ARRAY
输出中的每个元素都是此表中这一列某行的值。
如果 subquery
生成一个值表,则输出 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
必须是 DATEend_date
必须是 DATEINT64_expr
必须是 INT64date_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 |
+----------------------------------+---------------+----------------+