ARRAY
ARRAY(subquery)
Descrição
A função ARRAY
retorna uma ARRAY
com um elemento para cada linha em uma subconsulta.
Se subquery
produzir uma tabela SQL, a tabela precisará ter exatamente uma coluna. Cada elemento na ARRAY
de saída é o valor da coluna única de uma linha na tabela.
Se subquery
produzir uma tabela de valores, cada elemento na ARRAY
de saída será toda a linha correspondente da tabela de valores.
Restrições
- As subconsultas não são ordenadas, portanto, não há garantia de que os elementos da
ARRAY
de saída preservem qualquer ordem na tabela de origem para a subconsulta. No entanto, se a subconsulta incluir uma cláusulaORDER BY
, a funçãoARRAY
retornará umARRAY
que honre essa cláusula. - Se a subconsulta retornar mais de uma coluna, a função
ARRAY
retornará um erro. - Se a subconsulta retornar uma coluna de tipo
ARRAY
ou linhas de tipoARRAY
, a funçãoARRAY
vai retornar um erro: Cloud Spanner SQL não é compatível comARRAY
s com elementos do tipoARRAY
. - Se a subconsulta não retornar nenhuma linha, a função
ARRAY
retornará umaARRAY
vazia. Ela nunca retorna umaNULL
ARRAY
.
Tipo de retorno
ARRAY
Exemplos
SELECT ARRAY
(SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS new_array;
+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+
Para construir um ARRAY
a partir de uma subconsulta que contenha várias colunas, altere a subconsulta para usar SELECT AS STRUCT
. Agora a função ARRAY
retornará ARRAY
de STRUCT
s. A ARRAY
conterá um STRUCT
para cada linha na subconsulta e cada um destes STRUCT
s conterá um campo para cada coluna nessa linha.
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}] |
+------------------------+
Da mesma forma, para construir um ARRAY
a partir de uma subconsulta que contenha um ou mais ARRAY
s, altere a subconsulta para usar 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])
Descrição
Concatena uma ou mais matrizes com o mesmo tipo de elemento em uma única matriz.
Tipo de retorno
ARRAY
Exemplos
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)
Descrição
Retorna o tamanho da matriz. Retorna 0 para uma matriz vazia. Retorna NULL
se array_expression
for NULL
.
Tipo de retorno
INT64
Exemplos
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])
Descrição
Retorna uma concatenação dos elementos em array_expression
como um STRING. O valor de array_expression
pode ser uma matriz de tipos de dados BYTES ou STRING.
Se o parâmetro null_text
for usado, a função substituirá todos os valores NULL
na matriz pelo valor de null_text
.
Se o parâmetro null_text
não for usado, a função omitirá o valor NULL
e seu delimitador anterior.
Exemplos
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])
Descrição
Retorna uma matriz de valores. Os parâmetros start_expression
e end_expression
determinam o início e o fim inclusivos da matriz.
A função GENERATE_ARRAY
aceita estes tipos de dados como entradas:
- INT64
- NUMERIC
- FLOAT64
O parâmetro step_expression
determina o incremento usado para gerar valores de matriz. O valor padrão desse parâmetro é 1
.
Essa função retornará um erro se step_expression
for definido como 0 ou se alguma entrada for NaN
.
Se qualquer argumento for NULL
, a função retornará uma matriz NULL
.
Tipo de dados retornados
ARRAY
Exemplos
O seguinte retorna uma matriz de números inteiros, com uma etapa padrão de 1.
SELECT GENERATE_ARRAY(1, 5) AS example_array;
+-----------------+
| example_array |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
O seguinte retorna uma matriz usando um tamanho do passo especificado pelo usuário.
SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9] |
+---------------+
O seguinte retorna uma matriz usando um valor negativo -3
para o tamanho do passo.
SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;
+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+
O seguinte retorna uma matriz usando o mesmo valor para start_expression
e end_expression
.
SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;
+---------------+
| example_array |
+---------------+
| [4] |
+---------------+
O seguinte retorna uma matriz vazia, porque start_expression
é maior que end_expression
, e o valor step_expression
é positivo.
SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [] |
+---------------+
O seguinte retorna uma matriz NULL
porque end_expression
é NULL
.
SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;
+---------------+
| example_array |
+---------------+
| NULL |
+---------------+
O seguinte retorna várias matrizes.
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])
Descrição
Retorna uma matriz de datas. Os parâmetros start_date
e end_date
determinam o início e o fim inclusivos da matriz.
A função GENERATE_DATE_ARRAY
aceita estes tipos de dados como entradas:
start_date
precisa ser uma DATEend_date
precisa ser uma DATEINT64_expr
precisa ser um INT64date_part
precisa ser DAY, WEEK, MONTH, QUARTER ou YEAR.
O parâmetro INT64_expr
determina o incremento usado para gerar datas. O valor padrão dele é de 1 dia.
Essa função retornará um erro se INT64_expr
for definido como 0.
Tipo de dados retornados
Um ARRAY contendo 0 ou mais valores DATE.
Exemplos
O seguinte retorna uma matriz de datas, com uma etapa padrão de 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] |
+--------------------------------------------------+
O seguinte retorna uma matriz usando um tamanho do passo especificado pelo usuário.
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] |
+--------------------------------------+
O seguinte retorna uma matriz usando um valor negativo -3
para o tamanho do passo.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL -3 DAY) AS example;
+--------------------------+
| example |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+
O seguinte retorna uma matriz usando o mesmo valor para start_date
e end_date
.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-05', INTERVAL 8 DAY) AS example;
+--------------+
| example |
+--------------+
| [2016-10-05] |
+--------------+
O seguinte retorna uma matriz vazia, porque start_date
é maior que end_date
, e o valor step
é positivo.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL 1 DAY) AS example;
+---------+
| example |
+---------+
| [] |
+---------+
O seguinte retorna uma matriz NULL
, porque uma das entradas é
NULL
.
SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;
+---------+
| example |
+---------+
| NULL |
+---------+
O seguinte retorna uma matriz de datas, usando MONTH como o intervalo 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] |
+--------------------------------------------------------------------------+
O seguinte usa datas não constantes para gerar uma matriz.
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 e ORDINAL
array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]
Descrição
Acessa um elemento ARRAY por posição e retorna o elemento. OFFSET
significa que a numeração começa em zero e ORDINAL
, que a numeração começa em 1.
Uma matriz pode ser interpretada como sendo baseada em 0 ou em 1. Quando acessar um elemento de matriz, preceda a posição da matriz com OFFSET
ou ORDINAL
, respectivamente. Não há comportamento padrão.
OFFSET
e ORDINAL
geram um erro se o índice estiver fora do intervalo.
Tipo de retorno
Varia de acordo com os elementos em ARRAY.
Exemplos
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)
Descrição
Retorna o ARRAY de entrada com elementos na ordem inversa.
Tipo de retorno
ARRAY
Exemplos
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)
Descrição
Retorna verdadeiro se a matriz não contiver elementos repetidos, usando a mesma lógica de comparação de igualdade de SELECT DISTINCT
.
Tipo de retorno
BOOL
Exemplos
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 e SAFE_ORDINAL
array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]
Descrição
Idênticos a OFFSET
e ORDINAL
, exceto por retornar NULL
se o índice estiver fora do intervalo.
Tipo de retorno
Varia de acordo com os elementos em ARRAY.
Exemplo
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 |
+----------------------------------+---------------+----------------+