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áusulaORDER BY
, la funciónARRAY
mostrará unARRAY
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 tipoARRAY
, la funciónARRAY
muestra un error: BigQuery no admite losARRAY
con elementos del tipoARRAY
. - Si la subconsulta muestra cero filas, la función
ARRAY
muestra unARRAY
vacío. Nunca muestra unNULL
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 STRUCT
. 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
.
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
oDAY
.
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] |
+--------------------------------------------------------------------------+
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] |
| [] | [] |
+-----------+-------------+