Funções de matriz no SQL padrão

ARRAY

ARRAY(subquery)

Descrição

A função ARRAY retorna uma ARRAY com um elemento para cada linha em uma subconsulta.

Se a subquery produzir uma tabela SQL padrão, 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 a 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áusula ORDER BY, a função ARRAY retornará uma ARRAY que honra essa cláusula.
  • Se a subconsulta retornar mais de uma coluna, a função ARRAY retornará um erro.
  • Se a subconsulta retornar uma coluna tipada ARRAY ou linhas tipadas ARRAY, a função ARRAY retornará um erro: o BigQuery não aceita ARRAYs com elementos do tipo ARRAY.
  • Se a subconsulta retornar zero linhas, a função ARRAY retornará uma ARRAY vazia. Nunca retornará uma NULL 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 uma ARRAY de uma subconsulta que contenha várias colunas, altere a subconsulta para usar SELECT AS STRUCT. Agora, a função ARRAY retornará uma ARRAY de STRUCTs. A ARRAY conterá um STRUCT para cada linha na subconsulta e cada um destes STRUCTs 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 uma ARRAY de uma subconsulta que contenha um ou mais ARRAYs, 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 ["apples", "bananas", NULL, "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [apples, bananas, NULL, grapes] | 4    |
| [coffee, tea, 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 uma STRING. O valor de array_expression pode ser uma matriz de tipos de dados STRING ou BYTES.

Se o parâmetro null_text for usado, a função substituirá 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 o delimitador precedente.

Exemplos


WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  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                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| 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 inclusive da matriz.

A função GENERATE_ARRAY aceita os seguintes tipos de dados como entradas:

  • INT64
  • NUMERIC
  • FLOAT64

O parâmetro step_expression determina o incremento usado para gerar valores da matriz. O valor padrão desse parâmetro é 1.

Essa função retornará um erro, se step_expression for definido como 0 ou se houver uma entrada 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 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 de etapa especificada 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 da etapa.

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

O seguinte retorna uma matriz usando o mesmo valor de 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 de 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 inclusive da matriz.

A função GENERATE_DATE_ARRAY aceita estes tipos de dados como entrada:

  • start_date precisa ser DATE;
  • end_date precisa ser DATE;
  • INT64_expr precisa ser INT64;
  • date_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 de etapa especificada 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 da etapa.

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 de 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 de 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 comando usa datas não constantes para gerar uma matriz.

WITH StartsAndEnds AS (
  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"
)
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM StartsAndEnds;

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

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

Descrição

Retorna uma ARRAY de TIMESTAMPS separada por um determinado intervalo. Os parâmetros start_timestamp e end_timestamp determinam os limites inferior e superior inclusos do ARRAY.

A função GENERATE_TIMESTAMP_ARRAY aceita os seguintes tipos de dados como entradas:

  • start_timestamp: TIMESTAMP
  • end_timestamp: TIMESTAMP
  • step_expression: INT64
  • valores date_part permitidos: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR ou DAY

O parâmetro step_expression determina o incremento usado para gerar carimbos de data/hora.

Tipo de dados retornados

Uma ARRAY contendo 0 ou mais valores de TIMESTAMP.

Exemplos

O exemplo a seguir retorna uma ARRAY de TIMESTAMPs em intervalos de 1 segundo.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                INTERVAL 1 SECOND) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+

O exemplo a seguir retorna uma ARRAY de TIMESTAMPS com um intervalo negativo.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
                                INTERVAL -2 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+

O exemplo a seguir retorna uma ARRAY com um único elemento, porque start_timestamp e end_timestamp têm o mesmo valor.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+--------------------------+
| timestamp_array          |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+

O exemplo a seguir retorna uma ARRAY vazia, porque start_timestamp é posterior a end_timestamp.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| []              |
+-----------------+

O exemplo a seguir retorna uma ARRAY nula, porque uma das entradas é NULL.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
  AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| NULL            |
+-----------------+

O exemplo a seguir gera ARRAYs de TIMESTAMPs de colunas que contêm valores para start_timestamp e end_timestamp.

SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
  AS timestamp_array
FROM
  (SELECT
    TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
    TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016-10-06 01:59:00+00] |
+--------------------------------------------------------------------------+

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

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           |
+----------------------------------+---------------+----------------+
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

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