Como trabalhar com matrizes no SQL padrão

No BigQuery, uma matriz é uma lista ordenada que consiste em 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, ou seja, matrizes de outras matrizes não são compatíveis.

Com o BigQuery, é possível criar literais e matrizes a partir de subconsultas usando a função ARRAY e agregar valores a uma matriz por meio da função ARRAY_AGG.

É possível combinar matrizes usando funções como ARRAY_CONCAT() e convertê-las em strings com ARRAY_TO_STRING().

Como construir matrizes

Como usar literais de matrizes

Você pode criar um literal de matriz no BigQuery 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;

Você pode escrever uma matriz vazia de um tipo específico usando ARRAY<type>[]. Também é possível escrever um matriz vazia sem definição de tipos usando []. Nesse caso, o BigQuery tenta inferir o tipo de matriz pelo contexto adjacente. Se isso não for possível, o tipo ARRAY<INT64> padrão é utilizado.

Como usar valores gerados

Você também pode construir um ARRAY com valores gerados.

Como gerar matrizes de números inteiros

A função GENERATE_ARRAY gera uma matriz de valores a partir de um valor inicial e final e um valor de etapa. Por exemplo, a consulta a seguir gera uma matriz que contém todos os 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] |
+--------------------------------------------------+

Você também pode gerar uma matriz de valores em ordem decrescente, dando um valor de etapa 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 uma etapa.

É possível gerar um conjunto de valores de DATE usando GENERATE_DATE_ARRAY. Por exemplo, essa consulta retorna a DATE atual e os valores de DATEs seguintes em intervalos de uma WEEK até uma DATE posterior, que estará incluída:

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

Considere a seguinte tabela, sequences:

+---------------------+
| some_numbers        |
+---------------------+
| [0, 1, 1, 2, 3, 5]  |
| [2, 4, 8, 16, 32]   |
| [5, 10]             |
+---------------------+

Essa tabela contém a coluna some_numbers do tipo de dados ARRAY. Se você quiser acessar elementos das matrizes nessa coluna, é necessário especificar o tipo de indexação que 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         |
+--------------------+----------+-----------+

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

Como nivelar matrizes

Para converter uma 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 unir a tabela que contém a coluna ARRAY à saída UNNEST dessa coluna ARRAY.

Essa é uma correlação, ou seja, o operador UNNEST faz referência à 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

O exemplo a seguir usa UNNEST a fim de retornar uma linha para 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 |
+------+-------------------+

Como consultar matrizes aninhadas

Se uma tabela tiver um ARRAY de STRUCTs, será possível nivelar o ARRAY para consultar os campos do 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.

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,
  participant
FROM races r
CROSS JOIN UNNEST(r.participants) as participant;

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

Como consultar campos do tipo ARRAY em um STRUCT

Também é possível encontrar informações em campos 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                |
+------+-------------------------+

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

Observe que o nivelamento de matrizes com CROSS JOIN exclui linhas que têm matrizes vazias ou NULL. Se você quiser incluir essas linhas, use 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              |
+-------------+--------------------+

Como criar matrizes a partir de subconsultas

Uma tarefa comum ao trabalhar com matrizes é transformar o resultado de uma subconsulta em uma matriz. No BigQuery, é possível fazer 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]            |
+--------------------+---------------------+

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

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.

Você também pode 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] |
+-----------------+

Também é possível filtrar linhas de matrizes usando a palavra-chave IN. 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]  |
| []                 |
+--------------------+

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 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 onde 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 cujo valor corresponde 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 uma STRUCT cujo campo b tenha 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 BigQuery, você pode 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] |
+-----------------------+

A matriz retornada por ARRAY_AGG() está ordenada arbitrariamente, já que a ordem na qual a função concatena os valores não é garantida. Para ordenar os elementos da matriz, use ORDER BY. Por exemplo:

WITH fruits AS
  (SELECT "apple" AS fruit
   UNION ALL SELECT "pear" AS fruit
   UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket
FROM fruits;

+-----------------------+
| fruit_basket          |
+-----------------------+
| [apple, banana, pear] |
+-----------------------+

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 de elementos da matriz para cada linha da tabela de 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   |
+--------------------+------+

O BigQuery 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 ARRAY<STRING> em um único valor STRING ou 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 NULL da matriz.

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. Isso pode ser feito 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 BigQuery não é compatível com a criação direta de matrizes de outras matrizes. Em vez disso, é necessário criar uma matriz de structs. Cada struct precisa conter um campo do tipo ARRAY. Para entender melhor, pense na seguinte tabela points:

+----------+
| point    |
+----------+
| [1, 5]   |
| [2, 8]   |
| [3, 7]   |
| [4, 1]   |
| [5, 7]   |
+----------+

Agora, suponha que você queira criar uma matriz composta de cada point da 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                                        |
+----------------------------------------------------+
| [{[1, 5]}, {[2, 8]}, {[3, 7]}, {[4, 1]}, {[5, 7]}] |
+----------------------------------------------------+
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.