No Cloud Spanner SQL, uma matriz é uma lista ordenada composta de zero ou mais valores do mesmo tipo de dados. Você pode criar matrizes de tipos de dados simples, como INT64, e complexos, como STRUCT. A exceção
atual é o tipo de dados ARRAY: as matrizes de outras matrizes
não são compatíveis. As matrizes podem incluir
valores NULL.
Com o SQL do Cloud Spanner, é possível construir literais de matriz, criar matrizes de subconsultas usando a função ARRAY e agregar valores em uma matriz usando a função ARRAY_AGG.
É possível combinar matrizes usando funções como ARRAY_CONCAT() e convertê-las em strings por meio de ARRAY_TO_STRING().
Como construir matrizes
Como usar literais de matrizes
É possível criar um literal de matriz no SQL do Cloud Spanner usando colchetes ([ e ]). Cada elemento em uma matriz é separado por uma vírgula.
SELECT [1, 2, 3] as numbers;
SELECT ["apple", "pear", "orange"] as fruit;
SELECT [true, false, true] as booleans;
Também é possível criar matrizes a partir de quaisquer expressões que tenham tipos compatíveis. Por exemplo:
SELECT [a, b, c]
FROM
(SELECT 5 AS a,
37 AS b,
406 AS c);
SELECT [a, b, c]
FROM
(SELECT CAST(5 AS INT64) AS a,
CAST(37 AS FLOAT64) AS b,
406 AS c);
Perceba que o segundo exemplo contém três expressões: uma que retorna INT64, uma que retorna FLOAT64 e outra que declara um literal. Essa expressão funciona porque todas as três expressões compartilham FLOAT64 como um supertipo.
Para declarar um tipo de dados específico para uma matriz, use parênteses angulares (< e >). Por exemplo:
SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;
Matrizes compostas da maioria dos tipos de dados, como INT64 ou STRING, não exigem que você as declare primeiro.
SELECT [1, 2, 3] as numbers;
É possível escrever uma matriz vazia de um tipo específico usando ARRAY<type>[]. Também é possível escrever uma matriz vazia sem tipo usando []. Nesse caso, o SQL do Cloud Spanner tentará inferir o tipo de matriz com base no contexto ao redor. Se o SQL do Cloud Spanner não puder inferir um tipo, será usado o tipo padrão, ARRAY<INT64>.
Como usar valores gerados
Também é possível construir um ARRAY com valores gerados.
Como gerar matrizes de números inteiros
GENERATE_ARRAY
gera uma matriz de valores a partir de um valores inicial, final e de passo.
Por exemplo, a consulta a seguir gera uma matriz que contém todos os números inteiros ímpares de 11 a 33 (ambos inclusos):
SELECT GENERATE_ARRAY(11, 33, 2) AS odds;
+--------------------------------------------------+
| odds |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+
É possível gerar uma matriz de valores em ordem decrescente, dando um valor de passo negativo:
SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;
+----------------------------------+
| countdown |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+
Como gerar matrizes de datas
GENERATE_DATE_ARRAY
gera uma matriz de DATEs a partir de uma DATE inicial e final e o INTERVAL
de um passo.
É possível gerar um conjunto de valores de DATE usando GENERATE_DATE_ARRAY. Por
exemplo, essa consulta retorna a DATE atual e as
DATEs seguintes em intervalos de uma WEEK até uma DATE posterior:
SELECT
GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
AS date_array;
+--------------------------------------------------------------------------+
| date_array |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
+--------------------------------------------------------------------------+
Como acessar os elementos da matriz
Pense na tabela a seguir, sequences:
CREATE TABLE sequences (
id INT64 NOT NULL,
some_numbers ARRAY<INT64> NOT NULL
) PRIMARY KEY(id);
Suponha que a tabela seja preenchida com as seguintes linhas:
+----+---------------------+
| id | some_numbers |
+----+---------------------+
| 1 | [0, 1, 1, 2, 3, 5] |
| 2 | [2, 4, 8, 16, 32] |
| 3 | [5, 10] |
+----+---------------------+
Essa tabela contém a coluna some_numbers do tipo de dados ARRAY.
Para acessar elementos das matrizes nessa coluna, é necessário especificar que tipo
de indexação você quer usar:
OFFSET
para índices baseados em zero ou
ORDINAL,
para índices baseados em um.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
some_numbers[OFFSET(1)] AS offset_1,
some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;
+--------------------+----------+-----------+
| some_numbers | offset_1 | ordinal_1 |
+--------------------+----------+-----------+
| [0, 1, 1, 2, 3, 5] | 1 | 0 |
| [2, 4, 8, 16, 32] | 4 | 2 |
| [5, 10] | 10 | 5 |
+--------------------+----------+-----------+
Nesta consulta, veja como usar OFFSET() e ORDINAL():
SELECT some_numbers,
some_numbers[OFFSET(1)] AS offset_1,
some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;
+---------------+----------+-----------+
| some_numbers | offset_1 | ordinal_1 |
+---------------+----------+-----------+
| [0,1,1,2,3,5] | 1 | 0 |
+---------------+----------+-----------+
| [2,4,8,16,32] | 4 | 2 |
+---------------+----------+-----------+
| [5,10] | 10 | 5 |
+---------------+----------+-----------+
Como encontrar comprimentos
A função ARRAY_LENGTH() retorna o comprimento de uma matriz.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
ARRAY_LENGTH(some_numbers) AS len
FROM sequences;
+--------------------+--------+
| some_numbers | len |
+--------------------+--------+
| [0, 1, 1, 2, 3, 5] | 6 |
| [2, 4, 8, 16, 32] | 5 |
| [5, 10] | 2 |
+--------------------+--------+
Veja um exemplo de consulta, supondo a mesma definição da tabela sequences como acima, com as mesmas linhas de amostra:
SELECT some_numbers,
ARRAY_LENGTH(some_numbers) AS len
FROM sequences;
+---------------+------+
| some_numbers | len |
+---------------+------+
| [0,1,1,2,3,5] | 6 |
+---------------+------+
| [2,4,8,16,32] | 5 |
+---------------+------+
| [5,10] | 2 |
+---------------+------+
Como nivelar matrizes em tabelas
Para converter um ARRAY em um conjunto de linhas, processo conhecido também
como "nivelamento", use o operador
UNNEST. UNNEST usa uma ARRAY e retorna uma tabela com uma única linha para cada elemento na ARRAY.
Como UNNEST destrói a ordem dos elementos da ARRAY, pode ser necessário restaurar essa ordem na tabela. Para fazer isso, use a cláusula opcional WITH OFFSET para retornar uma coluna adicional com o deslocamento de cada elemento da matriz. Em seguida, use a cláusula ORDER BY para ordenar as linhas pelo deslocamento delas.
Exemplo
SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
AS element
WITH OFFSET AS offset
ORDER BY offset;
+----------+--------+
| element | offset |
+----------+--------+
| foo | 0 |
| bar | 1 |
| baz | 2 |
| qux | 3 |
| corge | 4 |
| garply | 5 |
| waldo | 6 |
| fred | 7 |
+----------+--------+
Para nivelar uma coluna inteira de ARRAYs enquanto preserva os valores
das outras colunas em cada linha, use um
CROSS JOIN
para mesclar a tabela que contém a coluna ARRAY à saída UNNEST
dessa coluna ARRAY.
Isso é uma correlação, ou seja, o operador UNNEST menciona a coluna de ARRAYs a partir de cada linha na tabela de origem, que aparece anteriormente na cláusula FROM. Para cada linha N na tabela de origem, UNNEST nivela a ARRAY a partir da linha N em um conjunto de linhas contendo os elementos da ARRAY. Em seguida, CROSS JOIN une esse novo conjunto de linhas com a única linha N da tabela de origem.
Exemplo
No exemplo a seguir, usamos UNNEST
para retornar uma linha de cada elemento na coluna da matriz. Por causa do CROSS JOIN, a coluna id contém os valores id para a linha em sequences que contém cada número.
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;
+------+-------------------+
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
+------+-------------------+
Observe que, para correlações, o operador UNNEST é opcional e o CROSS JOIN pode ser expresso como um join implícito. Usando essa notação abreviada,
o exemplo acima se torna:
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences, sequences.some_numbers AS flattened_numbers;
+------+-------------------+
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
+------+-------------------+
Como consultar matrizes aninhadas
Se uma tabela contém uma ARRAY de STRUCTs, é possível
nivelar a ARRAY para consultar os campos de STRUCT.
Também é possível nivelar campos do tipo ARRAY de valores STRUCT.
Como consultar elementos STRUCT em uma ARRAY
O exemplo a seguir usa UNNEST com CROSS JOIN para nivelar uma ARRAY de STRUCTs.
SELECT race,
participant.name,
participant.splits
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants
) AS r
CROSS JOIN UNNEST(r.participants) AS participant;
+------+-------------+-----------------------+
| race | name | splits |
+------+-------------+-----------------------+
| 800M | Rudisha | [23.4,26.3,26.4,26.1] |
+------+-------------+-----------------------+
| 800M | Makhloufi | [24.5,25.4,26.6,26.1] |
+------+-------------+-----------------------+
| 800M | Murphy | [23.9,26,27,26] |
+------+-------------+-----------------------+
| 800M | Bosse | [23.6,26.2,26.5,27.1] |
+------+-------------+-----------------------+
| 800M | Rotich | [24.7,25.6,26.9,26.4] |
+------+-------------+-----------------------+
| 800M | Lewandowski | [25,25.7,26.3,27.2] |
+------+-------------+-----------------------+
| 800M | Kipketer | [23.2,26.1,27.3,29.4] |
+------+-------------+-----------------------+
| 800M | Berian | [23.7,26.1,27,29.3] |
+------+-------------+-----------------------+
É possível encontrar informações específicas a partir de campos repetidos. Por exemplo, a consulta a seguir retorna o corredor mais rápido em uma corrida de 800 m.
Esse exemplo não envolve o nivelamento de uma matriz, mas representa um método comum para coletar informações a partir de um campo repetido.
Exemplo
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
ORDER BY (
SELECT SUM(duration)
FROM UNNEST(splits) AS duration) ASC
LIMIT 1) AS fastest_racer
FROM races;
+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha |
+------+---------------+
SELECT race,
(SELECT name
FROM UNNEST(participants)
ORDER BY (
SELECT SUM(duration)
FROM UNNEST(splits) AS duration) ASC
LIMIT 1) AS fastest_racer
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants
) AS r;
+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha |
+------+---------------+
Como consultar campos do tipo ARRAY em um STRUCT
Também é possível encontrar informações em campos repetidos aninhados. Por exemplo, a declaração a seguir retorna o corredor que teve a volta mais rápida em uma corrida de 800 m.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants),
UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
SELECT race,
(SELECT name
FROM UNNEST(participants),
UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants
) AS r;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
Perceba que a consulta anterior usa o operador vírgula (,) para executar um
CROSS JOIN implícito. Isso é o equivalente ao exemplo a seguir, que usa
um CROSS JOIN explícito.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
CROSS JOIN UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
SELECT race,
(SELECT name
FROM UNNEST(participants)
CROSS JOIN UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Berian" AS name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants
) AS r;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
Nivelar matrizes com um CROSS JOIN exclui linhas que têm matrizes
vazias ou NULL. Se você quiser incluir essas linhas, use um LEFT JOIN.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits),
STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
STRUCT("David" as name, NULL as splits)]
AS participants)
SELECT
name, sum(duration) AS finish_time
FROM races, races.participants LEFT JOIN participants.splits duration
GROUP BY name;
+-------------+--------------------+
| name | finish_time |
+-------------+--------------------+
| Murphy | 102.9 |
| Rudisha | 102.19999999999999 |
| David | NULL |
| Rotich | 103.6 |
| Makhloufi | 102.6 |
| Berian | 106.1 |
| Bosse | 103.4 |
| Kipketer | 106 |
| Nathan | NULL |
| Lewandowski | 104.2 |
+-------------+--------------------+
SELECT
name, sum(duration) as duration
FROM
(SELECT "800M" AS race,
[STRUCT("Rudisha" AS name, [23.4, 26.3, 26.4, 26.1] AS splits),
STRUCT("Makhloufi" AS name, [24.5, 25.4, 26.6, 26.1] AS splits),
STRUCT("Murphy" AS name, [23.9, 26.0, 27.0, 26.0] AS splits),
STRUCT("Bosse" AS name, [23.6, 26.2, 26.5, 27.1] AS splits),
STRUCT("Rotich" AS name, [24.7, 25.6, 26.9, 26.4] AS splits),
STRUCT("Lewandowski" AS name, [25.0, 25.7, 26.3, 27.2] AS splits),
STRUCT("Kipketer" AS name, [23.2, 26.1, 27.3, 29.4] AS splits),
STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
STRUCT("David" as name, NULL as splits)]
AS participants) AS races,
races.participants LEFT JOIN participants.splits duration
GROUP BY name;
+-------------+--------------------+
| name | duration |
+-------------+--------------------+
| Murphy | 102.9 |
| Rudisha | 102.19999999999999 |
| David | NULL |
| Rotich | 103.6 |
| Makhloufi | 102.6 |
| Bosse | 103.4 |
| Kipketer | 106 |
| Nathan | NULL |
| Lewandowski | 104.2 |
+-------------+--------------------+
Como criar matrizes a partir de subconsultas
Uma tarefa comum ao trabalhar com matrizes é transformar o resultado de uma subconsulta em uma
matriz. No SQL do Cloud Spanner, faça isso usando a função ARRAY().
Por exemplo, veja a seguinte operação na tabela sequences:
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x) AS doubled
FROM sequences;
+--------------------+---------------------+
| some_numbers | doubled |
+--------------------+---------------------+
| [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
| [2, 4, 8, 16, 32] | [4, 8, 16, 32, 64] |
| [5, 10] | [10, 20] |
+--------------------+---------------------+
SELECT some_numbers,
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x) AS doubled
FROM sequences;
+---------------+----------------+
| some_numbers | doubled |
+---------------+----------------+
| [0,1,1,2,3,5] | [0,2,2,4,6,10] |
+---------------+----------------+
| [2,4,8,16,32] | [4,8,16,32,64] |
+---------------+----------------+
| [5,10] | [10,20] |
+---------------+----------------+
Esse exemplo inicia com uma tabela chamada "sequences". Essa tabela contém uma coluna
some_numbers, do tipo ARRAY<INT64>.
A própria consulta contém uma subconsulta. Essa subconsulta seleciona cada linha na coluna some_numbers e usa UNNEST para retornar a matriz como um conjunto de linhas. A seguir, ela multiplica cada valor por dois e, em seguida,
recombina as linhas em uma matriz usando o operador ARRAY().
Como filtrar matrizes
O exemplo a seguir usa uma cláusula WHERE na subconsulta do operador ARRAY()
para filtrar as linhas retornadas.
Observação: nos exemplos a seguir, as linhas resultantes não estão ordenadas.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x
WHERE x < 5) AS doubled_less_than_five
FROM sequences;
+------------------------+
| doubled_less_than_five |
+------------------------+
| [0, 2, 2, 4, 6] |
| [4, 8] |
| [] |
+------------------------+
SELECT
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x
WHERE x < 5) AS doubled_less_than_five
FROM sequences;
+------------------------+
| doubled_less_than_five |
+------------------------+
| [0,2,2,4,6] |
+------------------------+
| [4,8] |
+------------------------+
| [] |
+------------------------+
Perceba que a terceira linha contém uma matriz vazia, porque os elementos na linha original correspondente ([5, 10]) não atendeu ao requisito x < 5 do filtro.
Também é possível filtrar matrizes usando SELECT DISTINCT para retornar somente
elementos únicos dentro de uma matriz.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT ARRAY(SELECT DISTINCT x
FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences;
+-----------------+
| unique_numbers |
+-----------------+
| [0, 1, 2, 3, 5] |
+-----------------+
SELECT ARRAY(SELECT DISTINCT x
FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences
WHERE id = 1;
+----------------+
| unique_numbers |
+----------------+
| [0,1,2,3,5] |
+----------------+
Também é possível usar a palavra-chave IN
para filtrar linhas de matrizes. Essa
palavra-chave filtra linhas contendo matrizes determinando se um valor específico corresponde a um elemento na matriz.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x
FROM UNNEST(some_numbers) AS x
WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM sequences;
+--------------------+
| contains_two |
+--------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [] |
+--------------------+
SELECT
ARRAY(SELECT x
FROM UNNEST(some_numbers) AS x
WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM sequences;
+---------------+
| contains_two |
+---------------+
| [0,1,1,2,3,5] |
+---------------+
| [2,4,8,16,32] |
+---------------+
| [] |
+---------------+
Mais uma vez, perceba que a terceira linha contém uma matriz vazia, porque a matriz na
linha original correspondente ([5, 10]) não continha 2.
Como verificar informações das matrizes
Para verificar se uma matriz contém um valor específico, use o
operador IN com UNNEST. Se
você quiser verificar se uma matriz contém um valor correspondente a uma condição, use a função EXISTS com UNNEST.
Como verificar valores específicos
Para verificar se há um valor específico em uma matriz, use o operador IN com UNNEST.
Exemplo
O exemplo a seguir retorna true se a matriz contém o número 2.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
+----------------+
| contains_value |
+----------------+
| true |
+----------------+
Para retornar as linhas de uma tabela em que a coluna da matriz contém um valor específico, filtre os resultados de IN UNNEST usando a cláusula WHERE.
Exemplo
O exemplo a seguir retorna o valor de id referente às linhas em que a coluna da matriz contém o valor 2.
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE 2 IN UNNEST(sequences.some_numbers)
ORDER BY matching_rows;
+---------------+
| matching_rows |
+---------------+
| 1 |
| 2 |
+---------------+
Como verificar valores que atendem a uma condição
Para verificar se uma matriz contém valores que correspondem a uma condição, use UNNEST para retornar uma tabela dos elementos na matriz, WHERE para filtrar a tabela resultante em uma subconsulta e EXISTS para verificar se a tabela filtrada contém alguma linha.
Exemplo
O exemplo a seguir retorna o valor de id referente às linhas em que a coluna da matriz contém valores maiores que 5.
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows FROM sequences
WHERE EXISTS (SELECT *
FROM UNNEST(some_numbers) AS x
WHERE x > 5);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+
Como verificar valores de campos STRUCT que atendem a uma condição
Para pesquisar se uma matriz de STRUCTs contém um campo com o valor correspondente a uma condição, use UNNEST para retornar uma tabela com uma coluna para cada campo STRUCT e, em seguida, filtre as linhas não correspondentes da tabela usando WHERE EXISTS.
Exemplo
O exemplo a seguir retorna as linhas em que a coluna da matriz contém um
STRUCT com o campo b tendo um valor maior que 3.
WITH sequences AS
(SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
UNION ALL SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
UNION ALL SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT (7 AS a, 4 AS b)]
AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE EXISTS (SELECT 1
FROM UNNEST(some_numbers)
WHERE b > 3);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+
Matrizes e agregação
Com o SQL do Cloud Spanner, é possível agregar valores em uma matriz usando ARRAY_AGG().
WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;
+-----------------------+
| fruit_basket |
+-----------------------+
| [apple, pear, banana] |
+-----------------------+
Pense na tabela a seguir, fruits:
CREATE TABLE fruits (
fruit STRING(MAX),
id INT64 NOT NULL
) PRIMARY KEY(id);
Suponha que a tabela seja preenchida com os seguintes dados:
+----+--------------+
| id | fruit |
+----+--------------+
| 1 | "apple" |
| 2 | "pear" |
| 3 | "banana" |
+----+--------------+
Nesta consulta, veja como usar ARRAY_AGG():
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;
+---------------------+
| fruit_basket |
+---------------------+
| [apple,pear,banana] |
+---------------------+
A matriz retornada por ARRAY_AGG() está ordenada arbitrariamente, já que não há garantia da ordem em que a função concatena valores.
Também é possível aplicar funções de agregação, como SUM(), aos elementos de uma matriz. Por exemplo, a seguinte consulta retorna a soma dos elementos de matriz em cada linha da tabela sequences.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
(SELECT SUM(x)
FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;
+--------------------+------+
| some_numbers | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12 |
| [2, 4, 8, 16, 32] | 62 |
| [5, 10] | 15 |
+--------------------+------+
SELECT some_numbers,
(SELECT SUM(x)
FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;
+---------------+------+
| some_numbers | sums |
+---------------+------+
| [0,1,1,2,3,5] | 12 |
+---------------+------+
| [2,4,8,16,32] | 62 |
+---------------+------+
| [5,10] | 15 |
+---------------+------+
O Cloud Spanner SQL também permite uma função de agregação, ARRAY_CONCAT_AGG(), que concatena os elementos de uma coluna da matriz em todas as linhas.
WITH aggregate_example AS
(SELECT [1,2] AS numbers
UNION ALL SELECT [3,4] AS numbers
UNION ALL SELECT [5, 6] AS numbers)
SELECT ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg
FROM aggregate_example;
+--------------------------------------------------+
| count_to_six_agg |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
Observação: a matriz retornada por ARRAY_CONCAT_AGG() não é determinística. A ordem em que a função concatena os valores não é garantida.
Como converter matrizes em strings
A função ARRAY_TO_STRING() permite que você converta uma ARRAY<STRING> em um
único valor STRING ou uma ARRAY<BYTES> em um único valor BYTES, em que
o valor resultante é a concatenação ordenada dos elementos da matriz.
O segundo argumento é o separador que a função vai inserir entre entradas para produzir a saída. Esse segundo argumento precisa ser do mesmo tipo que os elementos do primeiro argumento.
Exemplo:
WITH greetings AS
(SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM greetings;
+-------------+
| greetings |
+-------------+
| Hello World |
+-------------+
O terceiro argumento opcional toma o lugar dos valores NULL na matriz de entrada.
Se você omitir esse argumento, a função ignorará os elementos da matriz
NULL.Se você fornecer uma string vazia, a função inserirá um separador para elementos da matriz
NULL.
Exemplo:
SELECT
ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string,
ARRAY_TO_STRING(arr, ".", "") AS empty_string,
ARRAY_TO_STRING(arr, ".") AS omitted
FROM (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr);
+------------------+--------------+---------+
| non_empty_string | empty_string | omitted |
+------------------+--------------+---------+
| a.N.b.N.c.N | a..b..c. | a.b.c |
+------------------+--------------+---------+
Como combinar matrizes
Em alguns casos, convém combinar várias matrizes em apenas uma.
É possível fazer isso usando a função ARRAY_CONCAT().
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
Como criar matrizes de outras matrizes
O Cloud Spanner SQL não é compatível com a criação direta de matrizes de matrizes. Em vez disso, você cria uma matriz de structs. Cada struct contém um campo do tipo ARRAY. Para entender melhor, pense na tabela points a seguir:
CREATE TABLE points (
point ARRAY<INT64>,
id INT64 NOT NULL
) PRIMARY KEY(id);
Suponha que a tabela seja preenchida com as seguintes linhas:
+----+----------+
| id | point |
+----+----------+
| 1 | [1, 5] |
| 2 | [2, 8] |
| 3 | [3, 7] |
| 4 | [4, 1] |
| 5 | [5, 7] |
+----+----------+
Agora, suponha que você queira criar uma matriz composta por cada point na tabela points. Para fazer isso, una a matriz retornada de cada linha em uma STRUCT, conforme mostrado abaixo.
WITH points AS
(SELECT [1, 5] as point
UNION ALL SELECT [2, 8] as point
UNION ALL SELECT [3, 7] as point
UNION ALL SELECT [4, 1] as point
UNION ALL SELECT [5, 7] as point)
SELECT ARRAY(
SELECT STRUCT(point)
FROM points)
AS coordinates;
+-------------------+
| coordinates |
+-------------------+
| [{point: [1,5]}, |
| {point: [2,8]}, |
| {point: [5,7]}, |
| {point: [3,7]}, |
| {point: [4,1]}] |
+--------------------+
SELECT ARRAY(
SELECT STRUCT(point)
FROM points)
AS coordinates;
+--------------+
| coordinates |
+--------------+
| point: [1,5] |
| point: [2,8] |
| point: [3,7] |
| point: [4,1] |
| point: [5,7] |
+--------------+