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 columna única 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 ARRAY NULL.

Tipo de contenido 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 STRUCT. El ARRAY contendrá un STRUCT por cada fila de la subconsulta y cada uno de estos 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}] |
+------------------------+

Del mismo modo, para construir un ARRAY a partir de una subconsulta que contiene uno o más ARRAY, debes modificar 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_1 [, array_expression_n])

Descripción

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

Tipo de contenido 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 array_expression es NULL.

Tipo de contenido que se muestra

INT64

Ejemplos

WITH items AS
  (SELECT ["coffee", NULL, "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 |
+---------------------------------+------+
| [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 array_expression como una STRING. El valor de 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
  • FLOAT64

El parámetro step_expression determina el incremento usado 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 que se muestran

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

En el siguiente ejemplo, se muestra un arreglo que usa 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 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 usado 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 que se muestran

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 el límite inferior y el superior inclusivos del ARRAY.

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

  • start_timestamp: TIMESTAMP
  • end_timestamp: TIMESTAMP
  • step_expression: INT64
  • Los valores de date_part permitidos son los que se muestran a continuación:

    MICROSECOND, MILLISECOND,

    SECOND, MINUTE, HOUR o DAY.

El parámetro step_expression determina el incremento usado para generar marcas de tiempo.

Tipo de datos que se muestran

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

Ejemplos

En el siguiente ejemplo, se muestra un ARRAY de 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 contenido 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 contenido 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 contenido 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           |
+----------------------------------+---------------+----------------+
¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.