Funciones de arreglo en SQL estándar

ARRAY

ARRAY(subquery)

Descripción

La función ARRAY muestra un ARRAY con un elemento para cada fila en una subconsulta.

Si la subquery genera una tabla de SQL, la tabla debe tener una sola columna. Cada elemento en el ARRAY de salida es el valor de la única columna de una fila en la tabla.

Si la subquery genera una tabla de valores, cada elemento del ARRAY de salida es la fila correspondiente completa de la tabla de valores.

Limitaciones

• Las subconsultas no están ordenadas, por lo que no se garantiza que los elementos del ARRAY de salida conserven el orden en la tabla de origen para la subconsulta. Sin embargo, si la subconsulta incluye una cláusula ORDER BY, la función ARRAY mostrará un ARRAY que respete esa cláusula.
• Si la subconsulta muestra más de una columna, la función ARRAY muestra un error.
• Si la subconsulta muestra una columna de tipo ARRAY o filas de tipo ARRAY, la función ARRAY muestra un error: BigQuery no admite los ARRAY con elementos del tipo ARRAY.
• Si la subconsulta muestra cero filas, la función ARRAY muestra un ARRAY vacío. Nunca muestra un NULL ARRAY.

Tipo de datos que se muestra

ARRAY

Ejemplos

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+

Si deseas construir un ARRAY a partir de una subconsulta que contiene varias columnas, debes modificar la subconsulta para usar SELECT AS STRUCT. Ahora, la función ARRAY mostrará un ARRAY de STRUCTs. El ARRAY contendrá un valor STRUCT por cada fila de la subconsulta y cada uno de estos valores STRUCT contendrá un campo por cada columna de esa fila.

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

De la misma forma, para construir un ARRAY a partir de una subconsulta que contiene uno o más ARRAY, cambia la 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[, ...])

Descripción

Concatena uno o más arreglos con el mismo tipo de elemento en un solo arreglo.

La función muestra NULL si algún argumento de entrada es NULL.

Nota: También puedes usar el operador de concatenación || para concatenar arreglos.

Tipo de datos que se muestra

ARRAY

Ejemplos

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)

Descripción

Muestra el tamaño del arreglo. Muestra 0 para un arreglo vacío. Muestra NULL si la array_expression es NULL.

Tipo de datos que se muestra

INT64

Ejemplos

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

Descripción

Muestra una concatenación de los elementos en la array_expression como una STRING. El valor de la array_expression puede ser un arreglo de tipos de datos STRING o BYTES.

Si se usa el parámetro null_text, la función reemplaza cualquier valor NULL en el arreglo por el valor de null_text.

Si no se usa el parámetro null_text, la función omite el valor NULL y su delimitador anterior.

Ejemplos

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

Descripción

Muestra un arreglo de valores. Los parámetros start_expression y end_expression determinan el inicio y el final inclusivos del arreglo.

La función GENERATE_ARRAY acepta los siguientes tipos de datos como entradas:

• INT64
• NUMERIC
• BIGNUMERIC
• FLOAT64

El parámetro step_expression determina el incremento que se usa para generar valores de arreglo. El valor predeterminado para este parámetro es 1.

Esta función muestra un error si step_expression se establece en 0 o si alguna entrada es NaN.

Si algún argumento es NULL, la función mostrará un arreglo NULL.

Tipo de datos mostrados

ARRAY

Ejemplos

En el siguiente ejemplo, se muestra un arreglo de números enteros, con un paso predeterminado de 1.

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

A continuación, se muestra un arreglo con un tamaño del paso especificado por el usuario.

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

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

A continuación, se muestra un arreglo con un valor negativo, -3, como su tamaño del paso.

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

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

A continuación, se muestra un arreglo con el mismo valor para start_expression y end_expression.

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

+---------------+
| example_array |
+---------------+
| [4]           |
+---------------+

En el siguiente ejemplo, se muestra un arreglo vacío porque start_expression es mayor que end_expression, y el valor step_expression es positivo.

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

+---------------+
| example_array |
+---------------+
| []            |
+---------------+

En el siguiente ejemplo, se muestra un arreglo NULL porque la end_expression es NULL.

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

+---------------+
| example_array |
+---------------+
| NULL          |
+---------------+

En el siguiente ejemplo, se muestran múltiples arreglos.

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

Descripción

Muestra un arreglo de fechas. Los parámetros start_date y end_date determinan el inicio y el final inclusivos del arreglo.

La función GENERATE_DATE_ARRAY acepta los siguientes tipos de datos como entradas:

• start_date debe ser DATE.
• end_date debe ser DATE.
• INT64_expr debe ser INT64.
• date_part debe ser DAY, WEEK, MONTH, QUARTER o YEAR.

El parámetro INT64_expr determina el incremento que se usa para generar fechas. El valor predeterminado para este parámetro es de 1 día.

Esta función muestra un error si INT64_expr se establece en 0.

Tipo de datos mostrados

Un ARRAY que contiene 0 o más valores DATE.

Ejemplos

A continuación, se muestra un arreglo de fechas con un paso predeterminado 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] |
+--------------------------------------------------+

A continuación, se muestra un arreglo con un tamaño del paso especificado por el usuario.

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

A continuación, se muestra un arreglo con un valor negativo, -3, como su tamaño del paso.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

+--------------------------+
| example                  |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+

En el siguiente ejemplo, se muestra un arreglo con el mismo valor para start_date y end_date.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

+--------------+
| example      |
+--------------+
| [2016-10-05] |
+--------------+

En el siguiente ejemplo, se muestra un arreglo vacío porque start_date es mayor que end_date, y el valor step es positivo.

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

+---------+
| example |
+---------+
| []      |
+---------+

En el siguiente ejemplo, se muestra un arreglo NULL porque una de sus entradas es NULL.

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

En el siguiente ejemplo, se muestra un arreglo de fechas con MONTH como el 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] |
+--------------------------------------------------------------------------+

En el siguiente ejemplo, se usan fechas no constantes para generar un arreglo.

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

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

Descripción

Muestra un ARRAY de TIMESTAMPS separados por un intervalo determinado. Los parámetros start_timestamp y end_timestamp determinan los límites inferiores y superiores inclusivos del ARRAY.

La función GENERATE_TIMESTAMP_ARRAY acepta los siguientes tipos de datos como entrada:

• start_timestamp: TIMESTAMP
• end_timestamp: TIMESTAMP
• step_expression: INT64
• Los valores date_part permitidos son: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR o DAY.

El parámetro step_expression determina el incremento que se usa para generar marcas de tiempo.

Tipo de datos mostrados

Un ARRAY que contiene 0 o más valores de TIMESTAMP.

Ejemplos

En el siguiente ejemplo, se muestra un ARRAY de elementos TIMESTAMP en intervalos de 1 día.

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

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
+--------------------------------------------------------------------------+

En el siguiente ejemplo, se muestra un ARRAY de elementos TIMESTAMP en 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] |
+--------------------------------------------------------------------------+

En el siguiente ejemplo, se muestra un ARRAY de TIMESTAMPS con un 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] |
+--------------------------------------------------------------------------+

En el siguiente ejemplo, se muestra un ARRAY con un solo elemento, porque start_timestamp y end_timestamp tienen el mismo 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] |
+--------------------------+

En el siguiente ejemplo, se muestra un ARRAY vacío, porque start_timestamp es 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 |
+-----------------+
| []              |
+-----------------+

En el siguiente ejemplo, se muestra un ARRAY nulo porque una de las entradas es NULL.

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

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

En el siguiente ejemplo, se generan ARRAY de TIMESTAMP a partir de columnas que contienen valores de start_timestamp y 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 y ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

Descripción

Accede a un elemento ARRAY por posición y lo muestra. OFFSET significa que la numeración comienza en cero, ORDINAL significa que comienza en uno.

Un arreglo dado puede interpretarse como basado en 0 o en 1. Cuando accedes a un elemento de arreglo, debes anteponer la posición del arreglo con OFFSET o con ORDINAL, de forma respectiva; no hay comportamiento predeterminado.

OFFSET y ORDINAL generan un error si el índice está fuera de rango.

Tipo de datos que se muestra

Varía según los elementos en el ARRAY.

Ejemplos

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)

Descripción

Muestra el ARRAY de entrada con los elementos en orden inverso.

Tipo de datos que se muestra

ARRAY

Ejemplos

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 y SAFE_ORDINAL

array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]

Descripción

Idéntico a OFFSET y ORDINAL, excepto que muestra NULL si el índice está fuera de rango.

Tipo de datos que se muestra

Varía según los elementos en el ARRAY.

Ejemplo

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