En esta página, se explican las expresiones de BigQuery, incluidas las funciones y los operadores.
Reglas de llamada a funciones
Las siguientes reglas se aplican a todas las funciones, a menos que se indique lo contrario de forma explícita en la descripción de la función:
- Para las funciones que aceptan tipos numéricos, si un operando es de punto flotante y el otro es otro tipo numérico, ambos operandos se convierten en FLOAT64 antes de evaluar la función.
- Si un operando es
NULL
, el resultado esNULL
, a excepción del operador IS. - Para las funciones que son sensibles a la zona horaria (como se indica en la descripción de la función), la zona horaria predeterminada, UTC, se usa si no se especifica una zona horaria.
Prefijo SAFE.
Sintaxis:
SAFE.function_name()
Descripción
Si comienzas una función con el prefijo SAFE.
, se mostrará NULL
en lugar de un error. El prefijo SAFE.
solo previene errores de la función con el prefijo en sí: no evita los errores que se producen cuando se evalúan las expresiones de argumento. El prefijo SAFE.
solo previene errores que se producen debido al valor de las entradas de función, como los errores “valor fuera de rango”. Aún pueden ocurrir otros errores, como errores internos o del sistema. Si la función no muestra un error, SAFE.
no tiene efecto en el resultado. Si la función nunca muestra un error, como RAND
, entonces SAFE.
no tiene ningún efecto.
Los operadores, como +
y =
, no admiten el prefijo SAFE.
. Para evitar errores de una operación de división, usa SAFE_DIVIDE. Algunos operadores, como IN
, ARRAY
y UNNEST
, se parecen a funciones, pero no admiten el prefijo SAFE.
. Las funciones CAST
y EXTRACT
tampoco admiten el prefijo SAFE.
. Para evitar la transmisión de errores, usa SAFE_CAST.
Ejemplo
En el siguiente ejemplo, el primer uso de la función SUBSTR
normalmente mostraría un error, porque la función no admite argumentos de longitud con valores negativos. Sin embargo, el prefijo SAFE.
hace que la función muestre NULL
en su lugar. El segundo uso de la función SUBSTR
proporciona el resultado esperado: el prefijo SAFE.
no tiene ningún efecto.
SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;
+-------------+
| safe_output |
+-------------+
| NULL |
| ba |
+-------------+
Funciones compatibles
BigQuery admite el uso del prefijo SAFE.
con la mayoría de las funciones escalares que pueden generar errores, incluidas las funciones STRING, las funciones matemáticas, las funciones DATE, las funciones DATETIME y las funciones TIMESTAMP. BigQuery no admite el uso del prefijo SAFE.
con funciones agregadas, analíticas o funciones definidas por el usuario.
Llama a funciones definidas por el usuario (UDF) persistentes
Después de crear una UDF persistente, puedes llamarla como se haría con cualquier otra función, precedida por el nombre del conjunto de datos en el que se define como un prefijo.
Sintaxis
[`project_name`].dataset_name.function_name([parameter_value[, ...]])
Si se llama a una UDF en un proyecto que no sea el que se usa para ejecutar la consulta, se necesita project_name
.
Ejemplos
En el siguiente ejemplo, se crea una UDF denominada multiply_by_three
y se la llama desde el mismo proyecto.
CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);
SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15
En el siguiente ejemplo, se llama a una UDF persistente desde un proyecto diferente.
CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
AS (x * y * 2);
SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24
Reglas de conversión
“Conversión” incluye, entre otros, conversión de tipos y coerción.
- La conversión de tipos es una conversión explícita y usa la función
CAST()
. - La coerción es la conversión implícita, que realiza BigQuery de forma automática según las condiciones que se detallan a continuación.
- Existe un tercer grupo de funciones de conversión con sus propios nombres de funciones, como
UNIX_DATE()
.
En la siguiente tabla, se resumen todas las posibilidades de coerción y CAST
para los tipos de datos de BigQuery. “Coerción a” se aplica a todas las expresiones de un tipo de datos determinado (por ejemplo, una columna), pero los literales y los parámetros también se pueden coercionar. Consulta Coerción literal y Coerción de parámetros para obtener más detalles.
De tipo | CONVERSIÓN a | Coerción a |
---|---|---|
INT64 | BOOL INT64 NUMERIC FLOAT64 STRING |
NUMERIC FLOAT64 |
NUMERIC | INT64 NUMERIC FLOAT64 STRING |
FLOAT64 |
FLOAT64 | INT64 NUMERIC FLOAT64 STRING |
|
BOOL | BOOL INT64 STRING |
|
STRING | BOOL INT64 NUMERIC FLOAT64 STRING BYTES DATE DATETIME TIME TIMESTAMP |
|
BYTES | STRING BYTES |
|
DATE | STRING DATE DATETIME TIMESTAMP |
|
DATETIME | STRING DATE DATETIME TIME TIMESTAMP |
|
TIME | STRING TIME |
|
TIMESTAMP | STRING DATE DATETIME TIME TIMESTAMP |
|
ARRAY | ARRAY | |
STRUCT | STRUCT |
Conversión de tipos
Sintaxis:
CAST(expr AS typename)
La sintaxis de conversión de tipos se usa en una consulta para indicar que el tipo de resultado de una expresión debe convertirse en algún otro tipo.
Ejemplo:
CAST(x=1 AS STRING)
Esto da como resultado "true"
si x
es 1
, "false"
para cualquier otro valor que no sea NULL
, y NULL
si x
es NULL
.
Las conversiones entre tipos admitidos que no se mapean con éxito del valor original al dominio de destino producen errores en el entorno de ejecución. Por ejemplo, la conversión de BYTES a STRING cuando la secuencia de bytes no es un resultado UTF-8 válido devuelve un error en el entorno de ejecución.
Cuando se convierte el tipo de una expresión x
de los siguientes tipos, se aplican estas reglas:
De | A | Reglas para la conversión de tipos de x |
---|---|---|
INT64 | FLOAT64 | Muestra un valor FLOAT64 cercano, pero potencialmente no exacto. |
INT64 | BOOL | Muestra FALSE si x es 0 , y TRUE en caso contrario. |
NUMERIC | Floating Point | NUMERIC se convertirá al número de punto flotante más cercano con una pérdida de precisión posible. |
FLOAT64 | INT64 | Muestra el valor INT64 más cercano. Los casos de punto medio, como 1.5 o -0.5, se redondean en dirección opuesta al cero. |
FLOAT64 | STRING | Muestra una representación aproximada de la string. |
FLOAT64 | NUMERIC | El número de punto flotante se redondeará medio punto hacia arriba de cero. Si conviertes NaN , +inf o -inf , se mostrará un error. Si conviertes el tipo de un valor fuera del rango de NUMERIC , se mostrará un error de desbordamiento.
|
BOOL | INT64 | Muestra 1 si x es TRUE , y 0 en caso contrario. |
BOOL | STRING | Muestra "true" si x es TRUE , y "false" en caso contrario. |
STRING | FLOAT64 | Muestra x como un valor FLOAT64 y lo interpreta como si tuviera el mismo formato que un literal FLOAT64 válido.También admite conversiones de tipo desde "inf" , "+inf" , "-inf" y "nan" .Las conversiones no distinguen entre mayúsculas y minúsculas. |
STRING | NUMERIC | El literal numérico incluido en STRING no debe exceder la precisión o rango máximo del tipo NUMERIC . De lo contrario, se producirá un error. Si la cantidad de dígitos tras el punto decimal es mayor que nueve, el valor NUMERIC resultante se redondeará con los valores medios en dirección opuesta al cero para que tenga nueve dígitos tras el punto decimal.
|
STRING | BOOL | Muestra TRUE si x es "true" y FALSE si x es "false" Todos los demás valores de x no son válidos y muestran un error en lugar de convertir el tipo en BOOL.Las STRING no distinguen entre mayúsculas y minúsculas cuando se convierten en BOOL. |
STRING | BYTES | Las STRING se convierten en BYTES mediante la codificación UTF-8. Por ejemplo, la STRING "©", cuando se la transforma en BYTES, se convierte en una secuencia de 2 bytes con valores hexadecimales C2 y A9. |
BYTES | STRING | Muestra x interpretado como una STRING UTF-8.Por ejemplo, el literal de BYTES b'\xc2\xa9' , cuando se convierte su tipo en STRING, se interpreta como UTF-8 y se convierte en el carácter Unicode “©”.Se produce un error si x no es un valor de UTF-8 válido. |
ARRAY | ARRAY | Debe ser exactamente el mismo tipo de ARRAY. |
STRUCT | STRUCT | Se permite si se cumplen las siguientes condiciones:
|
Conversión segura
Cuando usas CAST
, la consulta puede fallar si BigQuery no puede realizar la conversión de tipos. Por ejemplo, la siguiente consulta genera un error:
SELECT CAST("apple" AS INT64) AS not_a_number;
Si deseas proteger tus consultas de estos tipos de errores, puedes usar SAFE_CAST
. SAFE_CAST
es idéntico a CAST
, pero muestra NULL en vez de generar un error.
SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;
+--------------+
| not_a_number |
+--------------+
| NULL |
+--------------+
Si conviertes el tipo de bytes en strings, también puedes usar la función SAFE_CONVERT_BYTES_TO_STRING
. Los caracteres UTF-8 no válidos se reemplazan por el carácter de reemplazo de Unicode, U+FFFD
. Consulta SAFE_CONVERT_BYTES_TO_STRING para obtener más información.
Cómo convertir strings hexadecimales en números enteros
Si trabajas con strings hexadecimales (0x123
), puedes convertirlas en números enteros:
SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;
+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123 | 291 |
+-----------+------------+
SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;
+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123 | -291 |
+-----------+------------+
Convierte tipos de hora
BigQuery admite la conversión de tipos de hora desde strings y hacia strings como se muestra a continuación:
CAST(time_expression AS STRING)
CAST(string_expression AS TIME)
La conversión de un tipo de hora a una string es independiente de la zona horaria y tiene el formato HH:MM:SS
. Cuando se realiza una conversión desde una string hacia una hora, la string debe cumplir con el formato de hora admitido y es independiente de la zona horaria. Se producirá un error si la expresión de la string no es válida o si representa una hora fuera del rango mínimo o máximo admitido.
Convierte tipos de fecha
BigQuery admite las conversiones de tipos de fechas desde y hacia strings como se muestra a continuación:
CAST(date_expression AS STRING)
CAST(string_expression AS DATE)
La conversión de un tipo de fecha a una string es independiente de la zona horaria y tiene la forma YYYY-MM-DD
. Cuando se realiza una conversión de string a fecha, la string debe cumplir con el formato de fecha admitido y es independiente de la zona horaria. Se producirá un error si la expresión de la string no es válida o si representa una fecha fuera del rango mínimo o máximo admitido.
Convierte tipos de fecha y hora
BigQuery admite la conversión de tipos de fecha y hora de strings y a ellas como se muestra a continuación:
CAST(datetime_expression AS STRING)
CAST(string_expression AS DATETIME)
La conversión de un tipo de fecha y hora a una string es independiente de la zona horaria y tiene el formato YYYY-MM-DD HH:MM:SS
. Cuando se realiza una conversión de string a fecha y hora, la string debe cumplir con el formato de fecha y hora admitido y es independiente de la zona horaria. Se producirá un error si la expresión de la string no es válida o representa una fecha y hora que está fuera del rango mínimo o máximo admitido.
Convierte tipos de marca de tiempo
BigQuery admite la conversión de tipos de marcas de tiempo desde y hacia strings de la siguiente manera:
CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)
Cuando se realiza una conversión de tipos de marca de tiempo a una string, la marca de tiempo se interpreta con la zona horaria predeterminada, UTC. La cantidad de dígitos de subsegundos producidos depende de la cantidad de ceros finales en la parte de subsegundos: la función CAST truncará cero, tres o seis dígitos.
Cuando se realiza una conversión de una string a una marca de tiempo, string_expression
debe cumplir con los formatos de marca de tiempo admitidos o, de lo contrario, ocurrirá un error en el entorno de ejecución. string_expression
puede contener un time_zone
; consulta zonas horarias.
Si hay una zona horaria en string_expression
, se usa para la conversión; de lo contrario, se usa la zona horaria predeterminada, UTC.
Si la string tiene menos de seis dígitos, entonces se ensancha de forma implícita.
Se produce un error si string_expression
no es válida, tiene más de seis dígitos de subsegundos (es decir, la precisión es superior a microsegundos) o representa una hora externa al rango de marca de tiempo admitido.
Convierte entre tipos de fecha, fecha y hora y marca de tiempo
BigQuery admite la conversión entre tipos de fecha, fecha y hora y marca de tiempo como se muestra en la tabla de reglas de conversión.
CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)
La conversión de una fecha a una marca de tiempo interpreta date_expression
a partir de la medianoche (inicio del día) en la zona horaria predeterminada, UTC. La conversión de una marca de tiempo a una fecha trunca de forma efectiva la marca de tiempo a partir de la zona horaria predeterminada.
CAST(datetime_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATETIME)
En la conversión de una fecha y hora a una marca de tiempo, se interpreta datetime_expression
a partir de la medianoche (inicio del día) en la zona horaria predeterminada, UTC.
Coerción
BigQuery coerciona el tipo de resultado de una expresión a otro tipo si necesita hacer coincidir las firmas de la función. Por ejemplo, si la función func() se define para que tenga un solo argumento del tipo INT64 y se usa una expresión como argumento que tiene un tipo de resultado de FLOAT64, entonces el resultado de la expresión se coercionará al tipo INT64 antes de que se calcule la función func().
Coerción literal
BigQuery es compatible con los siguientes tipos de coerción literal:
Tipo de datos de entrada | Tipo de datos del resultado | Notas |
---|---|---|
Literal de STRING | DATE DATETIME TIME TIMESTAMP |
La coerción literal es necesaria cuando el tipo literal real es diferente al tipo que espera la función en cuestión. Por ejemplo, si la función func()
toma un argumento DATE, la expresión func("2014-09-27")
es válida porque el literal de STRING "2014-09-27"
se coerciona a DATE.
La conversión literal se evalúa en el momento del análisis y genera un error si el literal de entrada no se puede convertir con éxito al tipo de destino.
Nota: Los literales de string no se coercionan a tipos de números.
Coerción de parámetros
BigQuery admite las siguientes coerciones de parámetros:
Tipo de datos de entrada | Tipo de datos del resultado |
---|---|
Parámetro de la STRING |
Aparecerá un error si el valor del parámetro no se puede forzar correctamente al tipo de destino.
Funciones de conversión adicionales
BigQuery proporciona las siguientes funciones de conversión adicionales:
Funciones de agregación
Una función de agregación es una función que resume las filas de un grupo en un solo valor. COUNT
, MIN
y MAX
son ejemplos de funciones de agregación.
SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
SELECT "apple" as fruit UNION ALL
SELECT "pear" as fruit UNION ALL
SELECT "orange" as fruit)
+-------------+----------------+-------+------+
| total_count | non_null_count | min | max |
+-------------+----------------+-------+------+
| 4 | 3 | apple | pear |
+-------------+----------------+-------+------+
Cuando se usa junto con una cláusula GROUP BY
, los grupos resumidos suelen tener al menos una fila. Cuando la SELECT
asociada no tiene una cláusula GROUP BY
o cuando ciertos modificadores de funciones de agregación filtran filas del grupo que se resumirá, es posible que la función necesite resumir un grupo vacío. En este caso, las funciones COUNT
y COUNTIF
muestran 0
, mientras que todas las otras funciones de agregación muestran NULL
.
En las siguientes secciones, se describen las funciones de agregación que admite BigQuery.
ANY_VALUE
ANY_VALUE(expression) [OVER (...)]
Descripción
Muestra expression
para algunas filas que se eligieron del grupo. La elección de la fila no es determinista ni aleatoria. Muestra NULL
cuando la entrada no produce filas. Muestra NULL
cuando expression
es NULL
para todas las filas del grupo.
ANY_VALUE
se comporta como si se especificara RESPECT NULLS
; se consideran y pueden seleccionarse las filas en las que expression
es NULL
.
Tipos de argumentos admitidos
Cualquiera
Cláusula opcional
OVER
: especifica una ventana. Consulta la sección sobre Funciones analíticas.
Tipos de datos mostrados
Coincide con el tipo de datos de entrada.
Ejemplos
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+-----------+
| any_value |
+-----------+
| apple |
+-----------+
SELECT
fruit,
ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+--------+-----------+
| fruit | any_value |
+--------+-----------+
| pear | pear |
| apple | pear |
| banana | apple |
+--------+-----------+
ARRAY_AGG
ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
[ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]
Descripción
Muestra un ARRAY de valores expression
.
Tipos de argumentos admitidos
Todos los tipos de datos excepto ARRAY.
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas. En este momento, esta cláusula no es compatible con todas las otras cláusulas dentro deARRAY_AGG()
.DISTINCT
: cada valor distinto deexpression
se agrega solo una vez en el resultado.IGNORE NULLS
oRESPECT NULLS
: si se especificaIGNORE NULLS
, los valoresNULL
se excluyen del resultado. SiRESPECT NULLS
se especifica o si no se especifica ninguno, los valoresNULL
se incluyen en el resultado. Se genera un error si un arreglo en el resultado final de la consulta contiene un elementoNULL
.ORDER BY
: especifica el orden de los valores.- Para cada clave de orden, la dirección de orden predeterminada es
ASC
. - NULL: en el contexto de la cláusula
ORDER BY
, NULL es el valor mínimo posible; es decir, los valores NULL aparecen primeros enASC
y últimos enDESC
. - Tipos de datos de punto flotante: consulta la página sobre semántica de punto flotante en la que se explica cómo ordenar y agrupar.
- Si también se especifica
DISTINCT
, la clave de clasificación debe ser igual queexpression
. - Si no se especifica
ORDER BY
, el orden de los elementos en el arreglo de resultado no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.
- Para cada clave de orden, la dirección de orden predeterminada es
LIMIT
: especifica la cantidad máxima de entradasexpression
en el resultado. El límiten
debe ser una constante INT64.
Tipos de datos mostrados
ARRAY
Si hay cero filas de entrada, esta función muestra NULL
.
Ejemplos
SELECT FORMAT("%T", ARRAY_AGG(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+---------------+
| array_agg |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT FORMAT("%T", ARRAY_AGG(x IGNORE NULLS)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-----------+
| array_agg |
+-----------+
| [-2, 1] |
+-----------+
SELECT
x,
FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+----+-------------------------+
| x | array_agg |
+----+-------------------------+
| 1 | [1, 1] |
| 1 | [1, 1] |
| 2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| -2 | [1, 1, 2, -2, -2, 2] |
| 2 | [1, 1, 2, -2, -2, 2] |
| 3 | [1, 1, 2, -2, -2, 2, 3] |
+----+-------------------------+
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG(expression [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
Descripción
Concatena elementos de expression
de tipo ARRAY y muestra un solo ARRAY como resultado. Esta función ignora los arreglos de entrada NULL, pero respeta los elementos NULL en arreglos de entrada que no son NULL (no obstante, se genera un error si un arreglo en el resultado final de la consulta contiene un elemento NULL).
Tipos de argumentos admitidos
ARRAY
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
ORDER BY
especifica el orden de los valores.- Para cada clave de orden, la dirección de orden predeterminada es
ASC
. - No se admite el ordenamiento de arreglos y, por lo tanto, la clave de orden no puede ser la misma que en
expression
. - NULL: en el contexto de la cláusula
ORDER BY
, NULL es el valor mínimo posible; es decir, los valores NULL aparecen primeros enASC
y últimos enDESC
. - Tipos de datos de punto flotante: consulta la página sobre semántica de punto flotante en la que se explica cómo ordenar y agrupar.
- Si no se especifica
ORDER BY
, el orden de los elementos en el arreglo de resultado no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.
- Para cada clave de orden, la dirección de orden predeterminada es
LIMIT
: especifica la cantidad máxima de entradasexpression
en el resultado. El límite se aplica al número de arreglos de entrada, no al número de elementos en el arreglo. Un arreglo vacío cuenta como 1. Un arreglo NULL no se cuenta. El límiten
debe ser una constante INT64.
Tipos de datos mostrados
ARRAY
Muestra NULL
si no hay filas de entrada o si expression
se evalúa como NULL en todas las filas.
Ejemplos
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
SELECT [NULL, 1, 2, 3, 4] AS x
UNION ALL SELECT NULL
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+--------------------------+
| array_concat_agg |
+--------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
SELECT [1, 2, 3, 4] AS x
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9] |
+------------------+
AVG
AVG([DISTINCT] expression) [OVER (...)]
Descripción
Muestra el promedio de valores de entrada que no son NULL
, o muestra NaN
si la entrada contiene un NaN
.
Tipos de argumentos admitidos
Cualquier tipo de entrada numérico, como INT64. Ten en cuenta que para los tipos de entrada de punto flotante, el resultado mostrado es no determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas. En este momento, esta cláusula no es compatible con todas las otras cláusulas dentro deAVG()
.DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.
Tipos de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | FLOAT64 |
Ejemplos
SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;
+-----+
| avg |
+-----+
| 3 |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;
+------+
| avg |
+------+
| 2.75 |
+------+
SELECT
x,
AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;
+------+------+
| x | avg |
+------+------+
| NULL | NULL |
| 0 | 0 |
| 2 | 1 |
| 4 | 3 |
| 4 | 4 |
| 5 | 4.5 |
+------+------+
BIT_AND
BIT_AND(expression)
Descripción
Realiza una operación AND a nivel de bits en expression
y muestra el resultado.
Tipos de argumentos admitidos
- INT64
Tipos de datos mostrados
INT64
Ejemplos
SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;
+---------+
| bit_and |
+---------+
| 1 |
+---------+
BIT_OR
BIT_OR(expression)
Descripción
Realiza una operación OR a nivel de bits en expression
y muestra el resultado.
Tipos de argumentos admitidos
- INT64
Tipos de datos mostrados
INT64
Ejemplos
SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;
+--------+
| bit_or |
+--------+
| 61601 |
+--------+
BIT_XOR
BIT_XOR([DISTINCT] expression)
Descripción
Realiza una operación XOR a nivel de bits en expression
y muestra el resultado.
Tipos de argumentos admitidos
- INT64
Cláusula opcional
DISTINCT
: Cada valor distinto de expression
se agrega solo una vez en el resultado.
Tipos de datos mostrados
INT64
Ejemplos
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 5678 |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
COUNT
1. COUNT(*) [OVER (...)]
2. COUNT([DISTINCT] expression) [OVER (...)]
Descripción
- Muestra la cantidad de filas en la entrada.
- Muestra la cantidad de filas con
expression
evaluado como cualquier valor distinto deNULL
.
Tipos de argumentos admitidos
expression
puede ser cualquier tipo de datos. Si DISTINCT
está presente, expression
solo puede ser un tipo de datos agrupable.
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas.DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.
Tipos de datos mostrados
INT64
Ejemplos
SELECT
COUNT(*) AS count_star,
COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4 | 3 |
+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------+------------+--------------+
| x | count_star | count_dist_x |
+------+------------+--------------+
| 1 | 3 | 2 |
| 4 | 3 | 2 |
| 4 | 3 | 2 |
| 5 | 1 | 1 |
+------+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
+------+------------+---------+
| x | count_star | count_x |
+------+------------+---------+
| NULL | 1 | 0 |
| 1 | 3 | 3 |
| 4 | 3 | 3 |
| 4 | 3 | 3 |
| 5 | 1 | 1 |
+------+------------+---------+
COUNTIF
COUNTIF(expression) [OVER (...)]
Descripción
Muestra el recuento de valores TRUE
para expression
. Muestra 0
si hay cero filas de entrada o si la expression
se evalúa como FALSE
o NULL
para todas las filas.
Tipos de argumentos admitidos
BOOL
Cláusula opcional
OVER
: especifica una ventana. Consulta la sección sobre Funciones analíticas.
Tipos de datos mostrados
INT64
Ejemplos
SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;
+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3 | 4 |
+--------------+--------------+
SELECT
x,
COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;
+------+--------------+
| x | num_negative |
+------+--------------+
| NULL | 0 |
| 0 | 1 |
| -2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| -7 | 2 |
| -10 | 2 |
+------+--------------+
LOGICAL_AND
LOGICAL_AND(expression)
Descripción
Muestra el AND lógico para todas las expresiones que no son NULL
. Muestra NULL
si hay cero filas de entrada o si expression
se evalúa como NULL
para todas las filas.
Tipos de argumentos admitidos
BOOL
Tipos de datos mostrados
BOOL
Ejemplos
SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;
+-------------+
| logical_and |
+-------------+
| false |
+-------------+
LOGICAL_OR
LOGICAL_OR(expression)
Descripción
Muestra el OR lógico para todas las expresiones que no son NULL
. Muestra NULL
si hay cero filas de entrada o si expression
se evalúa como NULL
para todas las filas.
Tipos de argumentos admitidos
BOOL
Tipos de datos mostrados
BOOL
Ejemplos
SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;
+------------+
| logical_or |
+------------+
| true |
+------------+
MAX
MAX(expression) [OVER (...)]
Descripción
Muestra el valor máximo de las expresiones que no son NULL
. Muestra NULL
si hay cero filas de entrada o si expression
se evalúa como NULL
para todas las filas.
Muestra NaN
si la entrada contiene un NaN
.
Tipos de argumentos admitidos
Cualquier tipo de datos excepto: ARRAY
STRUCT
GEOGRAPHY
Cláusula opcional
OVER
: especifica una ventana. Consulta la sección sobre Funciones analíticas.
Tipos de datos mostrados
Igual al tipo de datos usado como valores de entrada.
Ejemplos
SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| max |
+-----+
| 55 |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | max |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 8 |
| 4 | 8 |
| 37 | 55 |
| 55 | 55 |
+------+------+
MIN
MIN(expression) [OVER (...)]
Descripción
Muestra el valor mínimo de las expresiones que no son NULL
. Muestra NULL
si hay cero filas de entrada o si expression
se evalúa como NULL
para todas las filas.
Muestra NaN
si la entrada contiene un NaN
.
Tipos de argumentos admitidos
Cualquier tipo de datos excepto: ARRAY
STRUCT
GEOGRAPHY
Cláusula opcional
OVER
: especifica una ventana. Consulta la sección sobre Funciones analíticas.
Tipos de datos mostrados
Igual al tipo de datos usado como valores de entrada.
Ejemplos
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| min |
+-----+
| 4 |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | min |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 4 |
| 4 | 4 |
| 37 | 37 |
| 55 | 37 |
+------+------+
STRING_AGG
STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]
Descripción
Muestra un valor (ya sea STRING o BYTES) obtenido mediante la concatenación de valores no nulos.
Si especificas un delimiter
, los valores concatenados se separan con ese delimitador; de lo contrario, se usa una coma.
Tipos de argumentos admitidos
STRING BYTES
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas. En este momento, esta cláusula no es compatible con todas las otras cláusulas dentro deSTRING_AGG()
.DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.ORDER BY
especifica el orden de los valores.- Para cada clave de orden, la dirección de orden predeterminada es
ASC
. - NULL: en el contexto de la cláusula
ORDER BY
, NULL es el valor mínimo posible; es decir, los valores NULL aparecen primeros enASC
y últimos enDESC
. - Tipos de datos de punto flotante: consulta la página sobre semántica de punto flotante en la que se explica cómo ordenar y agrupar.
- Si también se especifica
DISTINCT
, la clave de clasificación debe ser igual queexpression
. - Si no se especifica
ORDER BY
, el orden de los elementos en el arreglo de resultado no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.
- Para cada clave de orden, la dirección de orden predeterminada es
LIMIT
: especifica la cantidad máxima de entradasexpression
en el resultado. El límite se aplica al número de strings de entrada, no al número de caracteres o bytes en las entradas. Una string vacía cuenta como 1. Una string NULL no se cuenta. El límiten
debe ser una constante INT64.
Tipos de datos mostrados
STRING BYTES
Ejemplos
SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+------------------------+
| string_agg |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+-----------------------+
| string_agg |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+--------------+
| string_agg |
+--------------+
| apple & pear |
+--------------+
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+---------------+
| string_agg |
+---------------+
| pear & banana |
+---------------+
SELECT
fruit,
STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+--------+------------------------------+
| fruit | string_agg |
+--------+------------------------------+
| NULL | NULL |
| pear | pear & pear |
| pear | pear & pear |
| apple | pear & pear & apple |
| banana | pear & pear & apple & banana |
+--------+------------------------------+
SUM
SUM([DISTINCT] expression) [OVER (...)]
Descripción
Muestra la suma de valores no nulos.
Si la expresión es un valor de punto flotante, la suma es no determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.
Tipos de argumentos admitidos
Cualquier tipo de datos numéricos admitido.
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas.DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.
Tipos de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | INT64 | NUMERIC | FLOAT64 |
Casos especiales:
Muestra NULL
si la entrada solo contiene NULL
.
Muestra NULL
si la entrada no contiene filas.
Muestra Inf
si la entrada contiene Inf
.
Muestra -Inf
si la entrada contiene -Inf
.
Muestra NaN
si la entrada contiene un NaN
.
Muestra NaN
si la entrada contiene una combinación de Inf
y -Inf
.
Ejemplos
SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 25 |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 15 |
+-----+
SELECT
x,
SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+---+-----+
| x | sum |
+---+-----+
| 3 | 6 |
| 3 | 6 |
| 1 | 10 |
| 4 | 10 |
| 4 | 10 |
| 1 | 10 |
| 2 | 9 |
| 5 | 9 |
| 2 | 9 |
+---+-----+
SELECT
x,
SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+---+-----+
| x | sum |
+---+-----+
| 3 | 3 |
| 3 | 3 |
| 1 | 5 |
| 4 | 5 |
| 4 | 5 |
| 1 | 5 |
| 2 | 7 |
| 5 | 7 |
| 2 | 7 |
+---+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;
+------+
| sum |
+------+
| NULL |
+------+
Funciones de agregación estadística
BigQuery admite las siguientes funciones de agregación estadística.
CORR
CORR(X1, X2) [OVER (...)]
Descripción
Muestra el coeficiente de correlación de Pearson de un conjunto de pares de números. Para cada par de números, el primero es la variable dependiente y el segundo es la variable independiente.
El resultado que se muestra está entre -1
y 1
. Un resultado de 0
indica que no hay correlación.
Esta función ignora los pares de entrada que contengan uno o más valores NULL. Si hay menos de dos pares de entrada sin valores NULL, la función muestra NULL.
Tipos de entrada admitidos
FLOAT64
Cláusula opcional
OVER
: especifica una ventana. Consulta la sección sobre Funciones analíticas.
Tipo de datos mostrados
FLOAT64
COVAR_POP
COVAR_POP(X1, X2) [OVER (...)]
Descripción
Muestra la covarianza poblacional de un conjunto de pares de números. El primer número es la variable dependiente; el segundo es la variable independiente. El resultado que se muestra está entre -Inf
y +Inf
.
Esta función ignora los pares de entrada que contengan uno o más valores NULL. Si no hay ningún par de entrada sin valores NULL, esta función muestra NULL. Si hay un par de entrada sin valores NULL, esta función muestra 0.
Tipos de entrada admitidos
FLOAT64
Cláusula opcional
OVER
: especifica una ventana. Consulta la sección sobre Funciones analíticas.
Tipo de datos mostrados
FLOAT64
COVAR_SAMP
COVAR_SAMP(X1, X2) [OVER (...)]
Descripción
Muestra la covarianza de ejemplo de un conjunto de pares de números. El primer número es la variable dependiente; el segundo es la variable independiente. El resultado que se muestra está entre -Inf
y +Inf
.
Esta función ignora los pares de entrada que contengan uno o más valores NULL. Si hay menos de dos pares de entrada sin valores NULL, la función muestra NULL.
Tipos de entrada admitidos
FLOAT64
Cláusula opcional
OVER
: especifica una ventana. Consulta la sección sobre Funciones analíticas.
Tipo de datos mostrados
FLOAT64
STDDEV_POP
STDDEV_POP([DISTINCT] expression) [OVER (...)]
Descripción
Muestra la desviación estándar poblacional (sesgada) de los valores. El resultado que se muestra está entre 0
y +Inf
.
Esta función ignora cualquier entrada NULL. Si se ignoran todas las entradas, esta función muestra NULL.
Si esta función recibe una entrada única que no es NULL, muestra 0
.
Tipos de entrada admitidos
FLOAT64
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas. En este momento, esta cláusula no es compatible con todas las otras cláusulas dentro deSTDDEV_POP()
.DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.
Tipo de datos mostrados
FLOAT64
STDDEV_SAMP
STDDEV_SAMP([DISTINCT] expression) [OVER (...)]
Descripción
Muestra la desviación estándar muestral (imparcial) de los valores. El resultado que se muestra está entre 0
y +Inf
.
Esta función ignora cualquier entrada NULL. Si hay menos de dos entradas que no sean NULL, esta función muestra NULL.
Tipos de entrada admitidos
FLOAT64
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas. En este momento, esta cláusula no es compatible con todas las otras cláusulas dentro deSTDDEV_SAMP()
.DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.
Tipo de datos mostrados
FLOAT64
STDDEV
STDDEV([DISTINCT] expression) [OVER (...)]
Descripción
Un alias de STDDEV_SAMP.
VAR_POP
VAR_POP([DISTINCT] expression) [OVER (...)]
Descripción
Muestra la varianza poblacional (sesgada) de los valores. El resultado que se muestra está entre 0
y +Inf
.
Esta función ignora cualquier entrada NULL. Si se ignoran todas las entradas, esta función muestra NULL.
Si esta función recibe una entrada única que no es NULL, muestra 0
.
Tipos de entrada admitidos
FLOAT64
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas. En este momento, esta cláusula no es compatible con todas las otras cláusulas dentro deVAR_POP()
.DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.
Tipo de datos mostrados
FLOAT64
VAR_SAMP
VAR_SAMP([DISTINCT] expression) [OVER (...)]
Descripción
Muestra la varianza muestral (imparcial) de los valores. El resultado que se muestra está entre 0
y +Inf
.
Esta función ignora cualquier entrada NULL. Si hay menos de dos entradas que no sean NULL, esta función muestra NULL.
Tipos de entrada admitidos
FLOAT64
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
OVER
: especifica una ventana. Consulta Conceptos de funciones analíticas. En este momento, esta cláusula no es compatible con todas las otras cláusulas dentro deVAR_SAMP()
.DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.
Tipo de datos mostrados
FLOAT64
VARIANCE
VARIANCE([DISTINCT] expression) [OVER (...)]
Descripción
Un alias de VAR_SAMP.
Funciones de agregación aproximada
Las funciones de agregación aproximada son escalables en términos de tiempo y uso de memoria, pero generan resultados aproximados en lugar de resultados exactos. Las funciones de agregación aproximada requieren menos memoria que las funciones de agregación exacta, como COUNT(DISTINCT ...)
, pero también generan incertidumbre estadística.
Esto hace que la agregación aproximada sea apropiada en grandes flujos de datos en los que el uso de memoria lineal no es práctico y para los datos que ya son aproximados.
Las funciones de agregación aproximadas de esta sección funcionan directamente en los datos de entrada, en lugar de una estimación intermedia de los datos. Estas funciones no permiten que los usuarios especifiquen la precisión de la estimación con esbozos. Si deseas especificar la precisión con esbozos, consulta lo siguiente:
- Funciones HyperLogLog++ para estimar la cardinalidad.
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT(expression)
Descripción
Muestra el resultado aproximado para COUNT(DISTINCT expression)
. El valor que se mostró es una estimación estadística, no es necesariamente el valor real.
Esta función es menos precisa que COUNT(DISTINCT expression)
, pero funciona mejor en entradas grandes.
Tipos de argumentos admitidos
Cualquier tipo de datos excepto: ARRAY
STRUCT
Tipos de datos mostrados
INT64
Ejemplos
SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;
+-----------------+
| approx_distinct |
+-----------------+
| 5 |
+-----------------+
APPROX_QUANTILES
APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])
Descripción
Muestra los límites aproximados para un grupo de valores expression
, en los que number
representa la cantidad de cuantiles que se crearán. Esta función muestra un arreglo de number
+ 1 elementos, en el que el primer elemento es el mínimo aproximado y el último es el máximo aproximado.
Tipos de argumentos admitidos
expression
puede ser cualquier tipo de datos admitido, excepto: ARRAY
STRUCT
number
debe ser INT64.
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
DISTINCT
: cada valor distinto deexpression
se agrega solo una vez en el resultado.IGNORE NULLS
oRESPECT NULLS
: si se especificaIGNORE NULLS
, los valoresNULL
se excluyen del resultado. SiRESPECT NULLS
se especifica o si no se especifica ninguno, los valoresNULL
se incluyen en el resultado. Se genera un error si un arreglo en el resultado final de la consulta contiene un elementoNULL
.
Tipos de datos mostrados
Un ARRAY del tipo especificado por el parámetro expression
.
Muestra NULL
si no hay filas de entrada o si expression
se evalúa como NULL en todas las filas.
Ejemplos
SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10] |
+------------------+
SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;
+---------------+
| percentile_90 |
+---------------+
| 9 |
+---------------+
SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10] |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10] |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10] |
+------------------+
APPROX_TOP_COUNT
APPROX_TOP_COUNT(expression, number)
Descripción
Muestra los elementos principales aproximados de expression
. El parámetro number
especifica la cantidad de elementos que se muestran.
Tipos de argumentos admitidos
expression
puede ser de cualquier tipo de datos que admita la cláusula GROUP BY
.
number
debe ser INT64.
Tipos de datos mostrados
Un ARRAY del tipo STRUCT.
El STRUCT contiene dos campos. El primer campo (llamado value
) contiene un valor de entrada. El segundo campo (llamado count
) contiene un INT64 que especifica la cantidad de veces que se mostró el valor.
Muestra NULL
si no hay filas de entrada.
Ejemplos
SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;
+-------------------------+
| approx_top_count |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+
Manejo de NULL
APPROX_TOP_COUNT no ignora los NULL de la entrada. Por ejemplo:
SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;
+------------------------+
| approx_top_count |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+
APPROX_TOP_SUM
APPROX_TOP_SUM(expression, weight, number)
Descripción
Muestra los elementos principales aproximados de expression
, en función de la suma de un weight
asignado. El parámetro number
especifica la cantidad de elementos que se muestran.
Si la entrada weight
es negativa o si es NaN
, esta función muestra un error.
Tipos de argumentos admitidos
expression
puede ser de cualquier tipo de datos que admita la cláusula GROUP BY
.
weight
debe ser una de las siguientes opciones:
- INT64
- FLOAT64
number
debe ser INT64.
Tipos de datos mostrados
Un ARRAY del tipo STRUCT.
El STRUCT contiene dos campos: value
y sum
.
El campo value
contiene el valor de la expresión de entrada. El campo sum
es del mismo tipo que weight
y es la suma aproximada del peso de entrada asociado con el campo value
.
Muestra NULL
si no hay filas de entrada.
Ejemplos
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
STRUCT("apple" AS x, 3 AS weight),
("pear", 2),
("apple", 0),
("banana", 5),
("pear", 4)
]);
+--------------------------+
| approx_top_sum |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+
Manejo de NULL
APPROX_TOP_SUM no ignora los valores NULL de los parámetros expression
y weight
.
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);
+----------------------------+
| approx_top_sum |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);
+-------------------------+
| approx_top_sum |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);
+----------------------------+
| approx_top_sum |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+
Funciones HyperLogLog++
El algoritmo HyperLogLog++ (HLL++) estima la cardinalidad de los esbozos. Si no deseas trabajar con esbozos y no necesitas una precisión personalizada, considera usar funciones de agregación aproximadas con una precisión definida por el sistema.
Las funciones HLL++ son funciones de agregación aproximadas.
La agregación aproximada suele requerir menos memoria que las funciones de agregación exacta, como COUNT(DISTINCT)
, pero también generan incertidumbre estadística.
Esto hace que las funciones HLL++ sean apropiadas en grandes flujos de datos en los que el uso de la memoria lineal es poco práctico, así como para los datos que ya son aproximados.
BigQuery admite las siguientes funciones HLL++:
HLL_COUNT.INIT
HLL_COUNT.INIT(input [, precision])
Descripción
Una función agregada que toma uno o más valores input
y los agrega en un esbozo HLL++. Cada esbozo se representa con el tipo de datos BYTES
. Luego, puedes combinar los bocetos con HLL_COUNT.MERGE
o HLL_COUNT.MERGE_PARTIAL
. Si no es necesaria la combinación, puedes extraer el recuento final de los valores distintos del boceto con HLL_COUNT.EXTRACT
.
Esta función admite un parámetro opcional, precision
. Este parámetro define la precisión de la estimación a costo de memoria adicional necesaria para procesar los bocetos o almacenarlos en el disco. La siguiente tabla muestra los valores de precisión permitidos, el tamaño máximo del boceto por grupo y el intervalo de confianza (IC) de las precisiones típicas:
Precisión | Tamaño máx. del boceto (KiB) | IC 65% | IC 95% | IC 99% |
---|---|---|---|---|
10 | 1 | ±1.63% | ±3.25% | ±6.50% |
11 | 2 | ±1.15% | ±2.30% | ±4.60% |
12 | 4 | ±0.81% | ±1.63% | ±3.25% |
13 | 8 | ±0.57% | ±1.15% | ±1.72% |
14 | 16 | ±0.41% | ±0.81% | ±1.22% |
15 (predeterminada) | 32 | ±0.29% | ±0.57% | ±0.86% |
16 | 64 | ±0.20% | ±0.41% | ±0.61% |
17 | 128 | ±0.14% | ±0.29% | ±0.43% |
18 | 256 | ±0.10% | ±0.20% | ±0.41% |
19 | 512 | ±0.07% | ±0.14% | ±0.29% |
20 | 1,024 | ±0.05% | ±0.10% | ±0.20% |
21 | 2,048 | ±0.04% | ±0.07% | ±0.14% |
22 | 4,096 | ±0.03% | ±0.05% | ±0.10% |
23 | 8192 | ±0.02% | ±0.04% | ±0.07% |
24 | 16384 | ±0.01% | ±0.03% | ±0.05% |
Si la entrada es NULL, esta función muestra NULL.
Para obtener más información, consulta HyperLogLog en la práctica: Ingeniería algorítmica de un algoritmo de estimación de cardinalidad de última generación.
Tipos de entrada admitidos
INT64, NUMERIC, STRING, BYTES
Tipo de datos que se muestra
BYTES
Ejemplo
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country;
HLL_COUNT.MERGE
HLL_COUNT.MERGE(sketch)
Descripción
Una función agregada que muestra la cardinalidad de varios esbozos de conjuntos HLL++ mediante el cálculo de su unión.
Cada sketch
debe tener la misma precisión y debe inicializarse en el mismo tipo.
Los intentos de combinar bocetos con diferentes precisiones o para diferentes tipos dan como resultado un error. Por ejemplo, no puedes combinar un boceto inicializado a partir de datos INT64 con uno inicializado a partir de datos STRING.
Esta función ignora los valores NULL durante la combinación de bocetos. Si la combinación se realiza en cero filas o solo en valores NULL, la función muestra 0
.
Tipos de entrada admitidos
BYTES
Tipo de datos que se muestra
INT64
Ejemplo
SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country)
GROUP BY flavor;
HLL_COUNT.MERGE_PARTIAL
HLL_COUNT.MERGE_PARTIAL(sketch)
Descripción
Una función agregada que toma una o más entradas de sketch
HLL++ y las combina en un esbozo nuevo.
Esta función muestra NULL si no hay entrada o si todas las entradas son NULL.
Tipos de entrada admitidos
BYTES
Tipo de datos que se muestra
BYTES
Ejemplo
SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country)
GROUP BY flavor;
HLL_COUNT.EXTRACT
HLL_COUNT.EXTRACT(sketch)
Descripción
Una función escalar que extrae una estimación de cardinalidad de un solo esbozo HLL++.
Si sketch
es NULL, esta función muestra una estimación de cardinalidad de 0
.
Tipos de entrada admitidos
BYTES
Tipo de datos que se muestra
INT64
Ejemplo
SELECT
flavor,
country,
HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country);
+------------+---------+-----------------+
| flavor | country | num_respondents |
+------------+---------+-----------------+
| Vanilla | CH | 1 |
| Chocolate | CH | 1 |
| Chocolate | US | 1 |
| Strawberry | US | 1 |
+------------+---------+-----------------+
Información acerca del algoritmo HLL++
El algoritmo HLL++ mejora el algoritmo HLL ya que estima con más precisión las cardinalidades muy pequeñas y grandes. El algoritmo HLL++ incluye una función hash de 64 bits, una representación dispersa que reduce los requisitos de memoria para las estimaciones de cardinalidad pequeña y una corrección del sesgo empírico para las estimaciones de cardinalidad pequeña.
Información acerca de los esbozos
Un esbozo es un resumen de un gran flujo de datos. Puedes extraer estadísticas de un esbozo a fin de estimar estadísticas específicas de los datos originales, o combinar esbozos para resumir varios flujos de datos. Un esbozo tiene estas características:
- Se comprimen datos sin procesar en una representación de memoria fija.
- Es asintóticamente más pequeño que la entrada.
- Es la forma serializada de una estructura de datos sublineal en memoria.
- En general, requiere menos memoria que la entrada que se usó para crearlo.
Los esbozos permiten la integración con otros sistemas. Por ejemplo, es posible compilar esbozos en aplicaciones externas, como Cloud Dataflow, o Apache Spark y consumirlos en BigQuery o viceversa. Los esbozos también permiten compilar agregaciones intermedias para funciones no aditivas, como COUNT(DISTINCT)
.
Funciones de numeración
En las siguientes secciones, se describen las funciones de numeración que admite BigQuery. Las funciones de numeración son un subconjunto de funciones analíticas. Para obtener una explicación de cómo operan las funciones analíticas, consulta Conceptos de funciones analíticas. Para obtener una descripción de cómo operan las funciones de numeración, consulta Conceptos de funciones de numeración.
A continuación, se detallan los requisitos de la cláusula OVER
:
PARTITION BY
: Es opcional.ORDER BY
: Obligatorio, excepto paraROW_NUMBER()
window_frame_clause
: No permitido
RANK
Descripción
Muestra el rango ordinal (que se basa en 1) de cada fila dentro de la partición ordenada.
Todas las filas del mismo nivel reciben el mismo valor de rango. La siguiente fila o conjunto de filas del mismo nivel recibe un valor de rango que aumenta según el número de filas del mismo nivel con el valor de rango anterior, en lugar de DENSE_RANK
, que siempre aumenta en 1.
Tipos de argumentos admitidos
INT64
DENSE_RANK
Descripción
Muestra el rango ordinal (que se basa en 1) de cada fila dentro de la partición de la ventana. Todas las filas del mismo nivel reciben el mismo valor de rango y el valor de rango posterior se incrementa en uno.
Tipos de argumentos admitidos
INT64
PERCENT_RANK
Descripción
Muestra el rango percentil de una fila definida como (RK-1) o (NR-1), en el que RK es el RANK
de la fila y NR es el número de filas en la partición.
Muestra 0 si NR=1.
Tipos de argumentos admitidos
FLOAT64
CUME_DIST
Descripción
Muestra el rango relativo de una fila definida como NP o NR. NP se define como el número de filas que se encuentran antes o en el mismo nivel que la fila actual. NR es el número de filas en la partición.
Tipos de argumentos admitidos
FLOAT64
NTILE
NTILE(constant_integer_expression)
Descripción
Esta función divide las filas en depósitos constant_integer_expression
según su orden y muestra el número de depósito que se basa en 1 asignado a cada fila. El número de filas en los depósitos puede variar como máximo en 1.
Los valores de resto (el resto del número de filas dividido por los depósitos) se distribuyen uno por cada depósito, el primero es el depósito 1. Si constant_integer_expression
se evalúa como NULL, 0 o negativo, se muestra un error.
Tipos de argumentos admitidos
INT64
ROW_NUMBER
Descripción
No requiere la cláusula ORDER BY
. Muestra la fila secuencial ordinal (que se basa en 1) de cada fila para cada partición ordenada. Si no se especifica la cláusula ORDER BY
, el resultado no es determinista.
Tipos de argumentos admitidos
INT64
Funciones de bits
BigQuery admite las siguientes funciones de bits.
BIT_COUNT
BIT_COUNT(expression)
Descripción
La entrada, expression
, debe ser un número entero o BYTES.
Muestra el número de bits establecidos en la entrada expression
.
Para números enteros con signo, esta es la cantidad de bits en forma de complemento de a dos.
Tipo de datos mostrados
INT64
Ejemplo
SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
(0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
(-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
(NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;
+-------+--------+---------------------------------------------+--------+
| a | a_bits | b | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0 | 0 | b"" | 0 |
| 0 | 0 | b"\x00" | 0 |
| 5 | 2 | b"\x05" | 2 |
| 8 | 1 | b"\x00\x08" | 1 |
| 65535 | 16 | b"\xff\xff" | 16 |
| -2 | 63 | b"\xff\xff\xff\xff\xff\xff\xff\xfe" | 63 |
| -1 | 64 | b"\xff\xff\xff\xff\xff\xff\xff\xff" | 64 |
| NULL | NULL | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80 |
+-------+--------+---------------------------------------------+--------+
Funciones matemáticas
Todas las funciones matemáticas tienen los siguientes comportamientos:
- Muestran
NULL
si alguno de los parámetros de entrada esNULL
. - Muestran
NaN
si alguno de los argumentos esNaN
.
ABS
ABS(X)
Descripción
Calcula el valor absoluto. Muestra un error si el argumento es un número entero y el valor de resultado no se puede representar como el mismo tipo; esto sucede solo para el valor de entrada negativo más grande, que no tiene una representación positiva. Muestra +inf
para un argumento +/-inf
.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | INT64 | NUMERIC | FLOAT64 |
SIGN
SIGN(X)
Descripción
Muestra -1, 0 o +1 para los argumentos negativos, de cero y positivos, respectivamente. Para argumentos de punto flotante, esta función no distingue entre cero positivo y negativo. Muestra NaN
para un argumento NaN
.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | INT64 | NUMERIC | FLOAT64 |
IS_INF
IS_INF(X)
Descripción
Muestra TRUE
si el valor es infinito positivo o negativo.
Muestra NULL
para las entradas NULL
.
IS_NAN
IS_NAN(X)
Descripción
Muestra TRUE
si el valor es NaN
.
Muestra NULL
para las entradas NULL
.
IEEE_DIVIDE
IEEE_DIVIDE(X, Y)
Descripción
Divide X por Y; esta función nunca presenta errores. Muestra FLOAT64
. A diferencia del operador de división (/), esta función no genera errores de divisiones por cero ni se desborda.
Casos especiales:
- Si el resultado se desborda, muestra
+/-inf
. - Si Y=0 y X=0, muestra
NaN
. - Si Y=0 y X!=0, muestra
+/-inf
. - Si X =
+/-inf
y Y =+/-inf
, muestraNaN
.
El comportamiento de IEEE_DIVIDE
se muestra con más detalle en la tabla a continuación.
Casos especiales de IEEE_DIVIDE
En la siguiente tabla, se enumeran los casos especiales de IEEE_DIVIDE
.
Tipo de datos del numerador (X) | Tipo de datos del denominador (Y) | Valor del resultado |
---|---|---|
Todo excepto 0 | 0 | +/-inf |
0 | 0 | NaN |
0 | NaN |
NaN |
NaN |
0 | NaN |
+/-inf |
+/-inf |
NaN |
RAND
RAND()
Descripción
Genera un valor seudoaleatorio de tipo FLOAT64 en el rango de [0, 1), el cual incluye 0 pero no 1.
SQRT
SQRT(X)
Descripción
Calcula la raíz cuadrada de X. Genera un error si X es menor que 0.
Muestra +inf
si X es +inf
.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | FLOAT64 |
POW
POW(X, Y)
Descripción
Muestra el valor de X elevado a la potencia de Y. Si el resultado se subdesborda y no es representable, entonces la función muestra un valor de cero. Muestra un error si uno de los siguientes enunciados es verdadero:
- X es un valor finito menor que 0 e Y es un número no entero.
- X es 0 e Y es un valor finito menor que 0.
Tipo de datos mostrados. El tipo de datos que se muestran se determina a partir de los tipos de argumentos con la siguiente tabla.
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
INT64 | FLOAT64 | NUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
Casos especiales de POW(X, Y)
Los siguientes son casos especiales de POW(X, Y)
.
X | Y | POW(X, Y) o POWER(X, Y) |
---|---|---|
1.0 | Cualquier valor, incluido NaN |
1.0 |
Cualquiera, incluido NaN |
0 | 1.0 |
-1.0 | +/-inf |
1.0 |
ABS(X) < 1 | -inf |
+inf |
ABS(X) > 1 | -inf |
0 |
ABS(X) < 1 | +inf |
0 |
ABS(X) > 1 | +inf |
+inf |
-inf |
Y < 0 | 0 |
-inf |
Y > 0 | -inf si Y es un número entero impar, +inf si es lo contrario |
+inf |
Y < 0 | 0 |
+inf |
Y > 0 | +inf |
POWER
POWER(X, Y)
Descripción
Sinónimo de POW(X, Y)
.
EXP
EXP(X)
Descripción
Calcula e elevado a X, también llamada función exponencial natural. Si el resultado se subdesborda, esta función muestra un cero. Genera un error si el resultado se desborda. Si X es +/-inf
, esta función muestra +inf
o 0.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | FLOAT64 |
LN
LN(X)
Descripción
Calcula el logaritmo natural de X. Genera un error si X es menor o igual que cero. Si X es +inf
, esta función muestra +inf
.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | FLOAT64 |
LOG
LOG(X [, Y])
Descripción
Si solo X está presente, LOG
es un sinónimo de LN
. Si Y también está presente, LOG
calcula el logaritmo de X en base Y. Genera un error en los siguientes casos:
- X es menor o igual que cero.
- Y es 1.0.
- Y es menor o igual que cero.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
INT64 | FLOAT64 | NUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
El comportamiento de LOG(X, Y)
se muestra con más detalle en la tabla que se encuentra a continuación.
Casos especiales de LOG(X, Y)
X | Y | LOG(X, Y) |
---|---|---|
-inf |
Cualquier valor | NaN |
Cualquier valor | +inf |
NaN |
+inf |
0.0 Y < 1.0 | -inf |
+inf |
Y > 1.0 | +inf |
LOG10
LOG10(X)
Descripción
Similar a LOG
, pero calcula el logaritmo en base 10.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | FLOAT64 |
GREATEST
GREATEST(X1,...,XN)
Descripción
Muestra NULL
si alguna de las entradas es NULL
. De lo contrario, muestra NaN
si alguna de las entradas es NaN
. Si no, muestra el valor más grande entre X1,…,XN según la comparación <.
Tipos de datos mostrados
Tipo de datos de los valores de entrada.
LEAST
LEAST(X1,...,XN)
Descripción
Muestra NULL
si alguna de las entradas es NULL
. Muestra NaN
si alguna de las entradas es NaN
. Si no, muestra el valor más pequeño entre X1,…,XN según la comparación >.
Tipos de datos mostrados
Tipo de datos de los valores de entrada.
DIV
DIV(X, Y)
Descripción
Muestra el resultado de la división del número entero de X por Y. La división por cero muestra un error. La división por -1 se puede desbordar.
Tipo de datos mostrados
El tipo de datos que se muestra se determina según los tipos de argumentos de la siguiente tabla.
INPUT | INT64 | NUMERIC |
---|---|---|
INT64 | INT64 | NUMERIC |
NUMERIC | NUMERIC | NUMERIC |
SAFE_DIVIDE
SAFE_DIVIDE(X, Y)
Descripción
Equivalente al operador de división (X / Y
), pero muestra NULL
si se produce un error, como un error de división por cero.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
INT64 | FLOAT64 | NUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
SAFE_MULTIPLY
SAFE_MULTIPLY(X, Y)
Descripción
Equivalente al operador de multiplicación (*
), pero muestra NULL
si se produce un desbordamiento.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
INT64 | INT64 | NUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
SAFE_NEGATE
SAFE_NEGATE(X)
Descripción
Equivalente al operador unario menos (-
), pero muestra NULL
si se produce un desbordamiento.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | INT64 | NUMERIC | FLOAT64 |
SAFE_ADD
SAFE_ADD(X, Y)
Descripción
Equivalente al operador de suma (+
), pero muestra NULL
si se produce un desbordamiento.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
INT64 | INT64 | NUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
SAFE_SUBTRACT
SAFE_SUBTRACT(X, Y)
Descripción
Muestra el resultado de Y que se restó de X.
Equivalente al operador de resta (-
), pero muestra NULL
si se produce un desbordamiento.
Tipo de datos mostrados
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
INT64 | INT64 | NUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
MOD
MOD(X, Y)
Descripción
Función de módulo: muestra el resto de la división de X por Y. El valor que se muestra tiene el mismo signo que X. Se generará un error si Y es 0.
Tipo de datos mostrados
El tipo de datos que se muestra se determina según los tipos de argumentos de la siguiente tabla.
INPUT | INT64 | NUMERIC |
---|---|---|
INT64 | INT64 | NUMERIC |
NUMERIC | NUMERIC | NUMERIC |
ROUND
ROUND(X [, N])
Descripción
Si solo está presente X, ROUND
redondea X al número entero más cercano. Si N está presente, ROUND
redondea X a N decimales después del punto decimal. Si N es negativo, ROUND
redondeará los dígitos a la izquierda del punto decimal. Se redondean casos de punto medio en dirección opuesta al cero. Genera un error si se produce un desbordamiento.
TRUNC
TRUNC(X [, N])
Descripción
Si solo está presente X, TRUNC
redondea X al número entero más cercano cuyo valor absoluto no es mayor que el valor absoluto de X. Si N también está presente, TRUNC
se comporta como ROUND(X, N)
, pero siempre se redondea hacia cero y nunca se desborda.
CEIL
CEIL(X)
Descripción
Muestra el valor integral más pequeño (con el tipo FLOAT64) que no es menor que X.
CEILING
CEILING(X)
Descripción
Sinónimo de CEIL(X)
FLOOR
FLOOR(X)
Descripción
Muestra el valor integral más grande (con el tipo FLOAT64) que no es mayor que X.
Ejemplo del comportamiento de la función de redondeo
Ejemplo del comportamiento de las funciones de redondeo de BigQuery:
Entrada “X” | ROUND(X) | TRUNC(X) | CEIL(X) | FLOOR(X) |
---|---|---|---|---|
2.0 | 2.0 | 2.0 | 2.0 | 2.0 |
2.3 | 2.0 | 2.0 | 3.0 | 2.0 |
2.8 | 3.0 | 2.0 | 3.0 | 2.0 |
2.5 | 3.0 | 2.0 | 3.0 | 2.0 |
-2.3 | -2.0 | -2.0 | -2.0 | -3.0 |
-2.8 | -3.0 | -2.0 | -2.0 | -3.0 |
-2.5 | -3.0 | -2.0 | -2.0 | -3.0 |
0 | 0 | 0 | 0 | 0 |
+/-inf |
+/-inf |
+/-inf |
+/-inf |
+/-inf |
NaN |
NaN |
NaN |
NaN |
NaN |
COS
COS(X)
Descripción
Calcula el coseno de X, en el que X se especifica en radianes. Nunca falla.
COSH
COSH(X)
Descripción
Calcula el coseno hiperbólico de X, en el que X se especifica en radianes. Genera un error si se produce un desbordamiento.
ACOS
ACOS(X)
Descripción
Calcula el valor principal del coseno inverso de X. El valor de muestra está en el rango [0,π]. Genera un error si X es un valor fuera del rango [-1, 1].
ACOSH
ACOSH(X)
Descripción
Calcula el coseno hiperbólico inverso de X. Genera un error si X es un valor inferior a 1.
SIN
SIN(X)
Descripción
Calcula el seno de X, en el que X se especifica en radianes. Nunca falla.
SINH
SINH(X)
Descripción
Calcula el seno hiperbólico de X, en el que X se especifica en radianes. Genera un error si se produce un desbordamiento.
ASIN
ASIN(X)
Descripción
Calcula el valor principal del seno inverso de X. El valor que se muestra está en el rango [-π/2,π/2]. Genera un error si X está fuera del rango [-1, 1].
ASINH
ASINH(X)
Descripción
Calcula el seno hiperbólico inverso de X. No falla.
TAN
TAN(X)
Descripción
Calcula la tangente de X, en la que X se especifica en radianes. Genera un error si se produce un desbordamiento.
TANH
TANH(X)
Descripción
Calcula la tangente hiperbólica de X, en la que X se especifica en radianes. No falla.
ATAN
ATAN(X)
Descripción
Calcula el valor principal de la tangente inversa de X. El valor que se muestra está en el rango [-π/2,π/2]. No falla.
ATANH
ATANH(X)
Descripción
Calcula la tangente hiperbólica inversa de X. Genera un error si X está fuera del rango [-1, 1].
ATAN2
ATAN2(Y, X)
Descripción
Calcula el valor principal de la tangente inversa de Y/X con los signos de los dos argumentos para determinar el cuadrante. El valor que se muestra está en el rango [-π,π].
El comportamiento de esta función se ilustra con más detalle en la siguiente tabla.
Casos especiales de ATAN2()
Y | X | ATAN2(Y, X) |
---|---|---|
NaN |
Cualquier valor | NaN |
Cualquier valor | NaN |
NaN |
0 | 0 | 0, π o -π según el signo de X y de Y |
Valor finito | -inf |
π o -π según el signo de Y |
Valor finito | +inf |
0 |
+/-inf |
Valor finito | π/2 o π/2 según el signo de Y |
+/-inf |
-inf |
¾π o -¾π según el signo de Y |
+/-inf |
+inf |
π/4 o -π/4 según el signo de Y |
Casos especiales de funciones de redondeo hiperbólicas y trigonométricas
X | COS(X) | COSH(X) | ACOS(X) | ACOSH(X) | SIN(X) | SINH(X) | ASIN(X) | ASINH(X) | TAN(X) | TANH(X) | ATAN(X) | ATANH(X) |
---|---|---|---|---|---|---|---|---|---|---|---|---|
+/-inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+1.0 | π/2 | NaN |
-inf |
NaN |
=+inf |
NaN |
NaN |
NaN |
-inf |
NaN |
-inf |
NaN |
-1.0 | -π/2 | NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
RANGE_BUCKET
RANGE_BUCKET(point, boundaries_array)
Descripción
RANGE_BUCKET
examina un arreglo ordenado y muestra la posición basada en 0 del límite superior del punto. Esto puede ser útil si necesitas agrupar tus datos para compilar particiones, histogramas, reglas definidas por el negocio y mucho más.
RANGE_BUCKET
sigue estas reglas:
Si el punto existe en el arreglo, muestra el índice del siguiente valor más grande.
RANGE_BUCKET(20, [0, 10, 20, 30, 40]) -- 3 is return value RANGE_BUCKET(20, [0, 10, 20, 20, 40, 40]) -- 4 is return value
Si el punto no existe en el arreglo, pero oscila entre dos valores, muestra el índice del valor más grande.
RANGE_BUCKET(25, [0, 10, 20, 30, 40]) -- 3 is return value
Si el punto es más pequeño que el primer valor del arreglo, muestra 0.
RANGE_BUCKET(-10, [5, 10, 20, 30, 40]) -- 0 is return value
Si el punto es mayor o igual que el último valor del arreglo, muestra la longitud de este.
RANGE_BUCKET(80, [0, 10, 20, 30, 40]) -- 5 is return value
Si el arreglo está vacío, muestra 0.
RANGE_BUCKET(80, []) -- 0 is return value
Si el punto es
NULL
oNaN
, muestraNULL
.RANGE_BUCKET(NULL, [0, 10, 20, 30, 40]) -- NULL is return value
El tipo de datos del punto y el arreglo debe ser compatible.
RANGE_BUCKET('a', ['a', 'b', 'c', 'd']) -- 1 is return value RANGE_BUCKET(1.2, [1, 1.2, 1.4, 1.6]) -- 2 is return value RANGE_BUCKET(1.2, [1, 2, 4, 6]) -- execution failure
La falla de ejecución se produce en los siguientes casos:
El arreglo tiene un valor
NaN
oNULL
.RANGE_BUCKET(80, [NULL, 10, 20, 30, 40]) -- execution failure
El arreglo no se ordenó de forma ascendente.
RANGE_BUCKET(30, [10, 30, 20, 40, 50]) -- execution failure
Parámetros
point
: Un valor genérico.boundaries_array
: Un arreglo genérico de valores.
Valor que se muestra
INT64
Ejemplos
En una tabla denominada students
, verifica cuántos registros existirían en cada depósito de age_group
, según la edad del alumno:
- age_group 0 (age < 10)
- age_group 1 (age >= 10, age < 20)
- age_group 2 (age >= 20, age < 30)
- age_group 3 (age >= 30)
WITH students AS
(
SELECT 9 AS age UNION ALL
SELECT 20 AS age UNION ALL
SELECT 25 AS age UNION ALL
SELECT 31 AS age UNION ALL
SELECT 32 AS age UNION ALL
SELECT 33 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1
+--------------+-------+
| age_group | count |
+--------------+-------+
| 0 | 1 |
| 2 | 2 |
| 3 | 3 |
+--------------+-------+
Funciones de navegación
En las siguientes secciones, se describen las funciones de navegación que admite BigQuery. Las funciones de navegación son un subconjunto de funciones analíticas. Para obtener una explicación de cómo funcionan las funciones analíticas, consulta conceptos de funciones analíticas. Para obtener una explicación de cómo funcionan las funciones de navegación, consulta los conceptos de funciones de navegación.
FIRST_VALUE
FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
Descripción
Muestra el valor de value_expression
para la primera fila en el marco de ventana actual.
Esta función incluye valores NULL
en el cálculo, a menos que IGNORE NULLS
esté presente. Si IGNORE NULLS
está presente, la función excluye los valores NULL
del cálculo.
Tipos de argumentos admitidos
value_expression
puede ser cualquier tipo de datos que pueda mostrar una expresión.
Tipo de datos mostrados
Mismo tipo que value_expression
.
Ejemplos
El siguiente ejemplo procesa el tiempo más rápido para cada división.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
division,
FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
TIMESTAMP_DIFF(finish_time, fastest_time, SECOND) AS delta_in_seconds
FROM (
SELECT name,
finish_time,
division,
FIRST_VALUE(finish_time)
OVER (PARTITION BY division ORDER BY finish_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fastest_time
FROM finishers);
+-----------------+-------------+----------+--------------+------------------+
| name | finish_time | division | fastest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte | 03:08:58 | F25-29 | 03:08:58 | 0 |
| Sophia Liu | 02:51:45 | F30-34 | 02:51:45 | 0 |
| Nikki Leith | 02:59:01 | F30-34 | 02:51:45 | 436 |
| Jen Edwards | 03:06:36 | F30-34 | 02:51:45 | 891 |
| Meghan Lederer | 03:07:41 | F30-34 | 02:51:45 | 956 |
| Lauren Reasoner | 03:10:14 | F30-34 | 02:51:45 | 1109 |
| Lisa Stelzner | 02:54:11 | F35-39 | 02:54:11 | 0 |
| Lauren Matthews | 03:01:17 | F35-39 | 02:54:11 | 426 |
| Desiree Berry | 03:05:42 | F35-39 | 02:54:11 | 691 |
| Suzy Slane | 03:06:24 | F35-39 | 02:54:11 | 733 |
+-----------------+-------------+----------+--------------+------------------+
LAST_VALUE
LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
Descripción
Muestra el valor de value_expression
para la última fila en el marco de la ventana actual.
Esta función incluye valores NULL
en el cálculo, a menos que IGNORE NULLS
esté presente. Si IGNORE NULLS
está presente, la función excluye los valores NULL
del cálculo.
Tipos de argumentos admitidos
value_expression
puede ser cualquier tipo de datos que pueda mostrar una expresión.
Tipo de datos mostrados
Mismo tipo que value_expression
.
Ejemplos
El siguiente ejemplo procesa el tiempo más lento para cada división.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
division,
FORMAT_TIMESTAMP('%X', slowest_time) AS slowest_time,
TIMESTAMP_DIFF(slowest_time, finish_time, SECOND) AS delta_in_seconds
FROM (
SELECT name,
finish_time,
division,
LAST_VALUE(finish_time)
OVER (PARTITION BY division ORDER BY finish_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS slowest_time
FROM finishers);
+-----------------+-------------+----------+--------------+------------------+
| name | finish_time | division | slowest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte | 03:08:58 | F25-29 | 03:08:58 | 0 |
| Sophia Liu | 02:51:45 | F30-34 | 03:10:14 | 1109 |
| Nikki Leith | 02:59:01 | F30-34 | 03:10:14 | 673 |
| Jen Edwards | 03:06:36 | F30-34 | 03:10:14 | 218 |
| Meghan Lederer | 03:07:41 | F30-34 | 03:10:14 | 153 |
| Lauren Reasoner | 03:10:14 | F30-34 | 03:10:14 | 0 |
| Lisa Stelzner | 02:54:11 | F35-39 | 03:06:24 | 733 |
| Lauren Matthews | 03:01:17 | F35-39 | 03:06:24 | 307 |
| Desiree Berry | 03:05:42 | F35-39 | 03:06:24 | 42 |
| Suzy Slane | 03:06:24 | F35-39 | 03:06:24 | 0 |
+-----------------+-------------+----------+--------------+------------------+
NTH_VALUE
NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])
Descripción
Muestra el valor de value_expression
en la enésima fila del marco de ventana actual, en el que N está definido por constant_integer_expression
. Muestra NULL si no hay tal fila.
Esta función incluye valores NULL
en el cálculo, a menos que IGNORE NULLS
esté presente. Si IGNORE NULLS
está presente, la función excluye los valores NULL
del cálculo.
Tipos de argumentos admitidos
value_expression
puede ser cualquier tipo de datos que se pueda mostrar a partir de una expresión.constant_integer_expression
puede ser cualquier expresión constante que muestre un número entero.
Tipo de datos mostrados
Mismo tipo que value_expression
.
Ejemplos
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
division,
FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
FORMAT_TIMESTAMP('%X', second_fastest) AS second_fastest
FROM (
SELECT name,
finish_time,
division,finishers,
FIRST_VALUE(finish_time)
OVER w1 AS fastest_time,
NTH_VALUE(finish_time, 2)
OVER w1 as second_fastest
FROM finishers
WINDOW w1 AS (
PARTITION BY division ORDER BY finish_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING));
+-----------------+-------------+----------+--------------+----------------+
| name | finish_time | division | fastest_time | second_fastest |
+-----------------+-------------+----------+--------------+----------------+
| Carly Forte | 03:08:58 | F25-29 | 03:08:58 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | 02:51:45 | 02:59:01 |
| Nikki Leith | 02:59:01 | F30-34 | 02:51:45 | 02:59:01 |
| Jen Edwards | 03:06:36 | F30-34 | 02:51:45 | 02:59:01 |
| Meghan Lederer | 03:07:41 | F30-34 | 02:51:45 | 02:59:01 |
| Lauren Reasoner | 03:10:14 | F30-34 | 02:51:45 | 02:59:01 |
| Lisa Stelzner | 02:54:11 | F35-39 | 02:54:11 | 03:01:17 |
| Lauren Matthews | 03:01:17 | F35-39 | 02:54:11 | 03:01:17 |
| Desiree Berry | 03:05:42 | F35-39 | 02:54:11 | 03:01:17 |
| Suzy Slane | 03:06:24 | F35-39 | 02:54:11 | 03:01:17 |
+-----------------+-------------+----------+--------------+----------------+
LEAD
LEAD (value_expression[, offset [, default_expression]])
Descripción
Muestra el valor de value_expression
en una fila posterior. Cambiar el valor de offset
cambia qué fila posterior se muestra; el valor predeterminado es 1
, que indica la siguiente fila en el marco de ventana. Si offset
es NULL o un valor negativo, se produce un error.
Se usa la default_expression
opcional si no hay una fila en el marco de la ventana del desplazamiento especificado. Esta expresión debe ser constante y su tipo debe ser coercible de forma implícita al tipo de value_expression
. Si no se especifica, default_expression
tiene el valor predeterminado NULL.
Tipos de argumentos admitidos
value_expression
puede ser cualquier tipo de datos que se pueda mostrar a partir de una expresión.offset
debe ser un literal o parámetro de número entero no negativo.default_expression
debe ser compatible con el tipo de expresión de valor.
Tipo de datos mostrados
Mismo tipo que value_expression
.
Ejemplos
En el siguiente ejemplo, se ilustra un uso básico de la función LEAD
.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by
FROM finishers;
+-----------------+-------------+----------+-----------------+
| name | finish_time | division | followed_by |
+-----------------+-------------+----------+-----------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | Nikki Leith |
| Nikki Leith | 02:59:01 | F30-34 | Jen Edwards |
| Jen Edwards | 03:06:36 | F30-34 | Meghan Lederer |
| Meghan Lederer | 03:07:41 | F30-34 | Lauren Reasoner |
| Lauren Reasoner | 03:10:14 | F30-34 | NULL |
| Lisa Stelzner | 02:54:11 | F35-39 | Lauren Matthews |
| Lauren Matthews | 03:01:17 | F35-39 | Desiree Berry |
| Desiree Berry | 03:05:42 | F35-39 | Suzy Slane |
| Suzy Slane | 03:06:24 | F35-39 | NULL |
+-----------------+-------------+----------+-----------------+
En el siguiente ejemplo, se usa el parámetro opcional offset
.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name, 2)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;
+-----------------+-------------+----------+------------------+
| name | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | Jen Edwards |
| Nikki Leith | 02:59:01 | F30-34 | Meghan Lederer |
| Jen Edwards | 03:06:36 | F30-34 | Lauren Reasoner |
| Meghan Lederer | 03:07:41 | F30-34 | NULL |
| Lauren Reasoner | 03:10:14 | F30-34 | NULL |
| Lisa Stelzner | 02:54:11 | F35-39 | Desiree Berry |
| Lauren Matthews | 03:01:17 | F35-39 | Suzy Slane |
| Desiree Berry | 03:05:42 | F35-39 | NULL |
| Suzy Slane | 03:06:24 | F35-39 | NULL |
+-----------------+-------------+----------+------------------+
En el siguiente ejemplo, se reemplazan los valores NULL por un valor predeterminado.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name, 2, 'Nobody')
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;
+-----------------+-------------+----------+------------------+
| name | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte | 03:08:58 | F25-29 | Nobody |
| Sophia Liu | 02:51:45 | F30-34 | Jen Edwards |
| Nikki Leith | 02:59:01 | F30-34 | Meghan Lederer |
| Jen Edwards | 03:06:36 | F30-34 | Lauren Reasoner |
| Meghan Lederer | 03:07:41 | F30-34 | Nobody |
| Lauren Reasoner | 03:10:14 | F30-34 | Nobody |
| Lisa Stelzner | 02:54:11 | F35-39 | Desiree Berry |
| Lauren Matthews | 03:01:17 | F35-39 | Suzy Slane |
| Desiree Berry | 03:05:42 | F35-39 | Nobody |
| Suzy Slane | 03:06:24 | F35-39 | Nobody |
+-----------------+-------------+----------+------------------+
LAG
LAG (value_expression[, offset [, default_expression]])
Descripción
Muestra el valor de value_expression
en una fila anterior. Cuando se cambia el valor de offset
, cambia la fila anterior que se muestra; el valor predeterminado es 1
, que indica la fila anterior en el marco de la ventana. Si offset
es NULL o un valor negativo, se produce un error.
Se usa la default_expression
opcional si no hay una fila en el marco de la ventana del desplazamiento especificado. Esta expresión debe ser constante y su tipo debe ser coercible de forma implícita al tipo de value_expression
. Si no se especifica, default_expression
tiene el valor predeterminado NULL.
Tipos de argumentos admitidos
value_expression
puede ser cualquier tipo de datos que se pueda mostrar a partir de una expresión.offset
debe ser un literal o parámetro de número entero no negativo.default_expression
debe ser compatible con el tipo de expresión de valor.
Tipo de datos mostrados
Mismo tipo que value_expression
.
Ejemplos
En el siguiente ejemplo, se ilustra un uso básico de la función LAG
.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LAG(name)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers;
+-----------------+-------------+----------+------------------+
| name | finish_time | division | preceding_runner |
+-----------------+-------------+----------+------------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | NULL |
| Nikki Leith | 02:59:01 | F30-34 | Sophia Liu |
| Jen Edwards | 03:06:36 | F30-34 | Nikki Leith |
| Meghan Lederer | 03:07:41 | F30-34 | Jen Edwards |
| Lauren Reasoner | 03:10:14 | F30-34 | Meghan Lederer |
| Lisa Stelzner | 02:54:11 | F35-39 | NULL |
| Lauren Matthews | 03:01:17 | F35-39 | Lisa Stelzner |
| Desiree Berry | 03:05:42 | F35-39 | Lauren Matthews |
| Suzy Slane | 03:06:24 | F35-39 | Desiree Berry |
+-----------------+-------------+----------+------------------+
En el siguiente ejemplo, se usa el parámetro opcional offset
.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LAG(name, 2)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;
+-----------------+-------------+----------+-------------------+
| name | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | NULL |
| Nikki Leith | 02:59:01 | F30-34 | NULL |
| Jen Edwards | 03:06:36 | F30-34 | Sophia Liu |
| Meghan Lederer | 03:07:41 | F30-34 | Nikki Leith |
| Lauren Reasoner | 03:10:14 | F30-34 | Jen Edwards |
| Lisa Stelzner | 02:54:11 | F35-39 | NULL |
| Lauren Matthews | 03:01:17 | F35-39 | NULL |
| Desiree Berry | 03:05:42 | F35-39 | Lisa Stelzner |
| Suzy Slane | 03:06:24 | F35-39 | Lauren Matthews |
+-----------------+-------------+----------+-------------------+
En el siguiente ejemplo, se reemplazan los valores NULL por un valor predeterminado.
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LAG(name, 2, 'Nobody')
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;
+-----------------+-------------+----------+-------------------+
| name | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte | 03:08:58 | F25-29 | Nobody |
| Sophia Liu | 02:51:45 | F30-34 | Nobody |
| Nikki Leith | 02:59:01 | F30-34 | Nobody |
| Jen Edwards | 03:06:36 | F30-34 | Sophia Liu |
| Meghan Lederer | 03:07:41 | F30-34 | Nikki Leith |
| Lauren Reasoner | 03:10:14 | F30-34 | Jen Edwards |
| Lisa Stelzner | 02:54:11 | F35-39 | Nobody |
| Lauren Matthews | 03:01:17 | F35-39 | Nobody |
| Desiree Berry | 03:05:42 | F35-39 | Lisa Stelzner |
| Suzy Slane | 03:06:24 | F35-39 | Lauren Matthews |
+-----------------+-------------+----------+-------------------+
PERCENTILE_CONT
PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])
Descripción
Calcula el valor de percentil especificado para value_expression con interpolación lineal.
Esta función ignora los valores NULL si RESPECT NULLS
no está presente. Si RESPECT
NULLS
está presente:
- La interpolación entre dos valores
NULL
muestraNULL
. - La interpolación entre un valor
NULL
y un valor noNULL
muestra el valor noNULL
.
Tipos de argumentos admitidos
value_expression
ypercentile
deben tener uno de los siguientes tipos:NUMERIC
FLOAT64
percentile
debe ser un literal en el rango[0, 1]
.
Tipo de datos mostrados
El tipo de datos que se muestra se determina según los tipos de argumentos de la siguiente tabla.
INPUT | NUMERIC | FLOAT64 |
---|---|---|
NUMERIC | NUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 |
Ejemplos
El siguiente ejemplo procesa el valor para algunos percentiles de una columna de valores a la vez que ignora los valores nulos.
SELECT
PERCENTILE_CONT(x, 0) OVER() AS min,
PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
PERCENTILE_CONT(x, 0.5) OVER() AS median,
PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;
+-----+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+-----+-------------+--------+--------------+-----+
| 0 | 0.03 | 1.5 | 2.7 | 3 |
+-----+-------------+--------+--------------+-----+
El siguiente ejemplo procesa el valor de algunos percentiles a partir de una columna de valores y respeta los valores nulos.
SELECT
PERCENTILE_CONT(x, 0 RESPECT NULLS) OVER() AS min,
PERCENTILE_CONT(x, 0.01 RESPECT NULLS) OVER() AS percentile1,
PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER() AS median,
PERCENTILE_CONT(x, 0.9 RESPECT NULLS) OVER() AS percentile90,
PERCENTILE_CONT(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;
+------+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+------+-------------+--------+--------------+-----+
| NULL | 0 | 1 | 2.6 | 3 |
+------+-------------+--------+--------------+-----+
PERCENTILE_DISC
PERCENTILE_DISC (value_expression, percentile [{RESPECT | IGNORE} NULLS])
Descripción
Calcula el valor de percentil especificado para una value_expression
discreta. El valor mostrado es el primer valor ordenado de value_expression
con una distribución acumulada mayor o igual al valor de percentile
dado.
Esta función ignora los valores NULL
, a menos que RESPECT NULLS
esté presente.
Tipos de argumentos admitidos
value_expression
puede ser cualquier tipo que se pueda ordenar.percentile
debe ser un literal en el rango[0, 1]
, con uno de los siguientes tipos:NUMERIC
FLOAT64
Tipo de datos mostrados
Mismo tipo que value_expression
.
Ejemplos
El siguiente ejemplo procesa el valor para algunos percentiles de una columna de valores a la vez que ignora los valores nulos.
SELECT
x,
PERCENTILE_DISC(x, 0) OVER() AS min,
PERCENTILE_DISC(x, 0.5) OVER() AS median,
PERCENTILE_DISC(x, 1) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;
+------+-----+--------+-----+
| x | min | median | max |
+------+-----+--------+-----+
| c | a | b | c |
| NULL | a | b | c |
| b | a | b | c |
| a | a | b | c |
+------+-----+--------+-----+
En el siguiente ejemplo, se procesa el valor de algunos percentiles a partir de una columna de valores y se respetan los valores nulos.
SELECT
x,
PERCENTILE_DISC(x, 0 RESPECT NULLS) OVER() AS min,
PERCENTILE_DISC(x, 0.5 RESPECT NULLS) OVER() AS median,
PERCENTILE_DISC(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;
+------+------+--------+-----+
| x | min | median | max |
+------+------+--------+-----+
| c | NULL | a | c |
| NULL | NULL | a | c |
| b | NULL | a | c |
| a | NULL | a | c |
+------+------+--------+-----+
Funciones analíticas de agregación
En las siguientes secciones se describen las funciones analíticas de agregación que admite BigQuery. Para obtener una explicación de cómo operan las funciones analíticas, consulta Conceptos de funciones analíticas. Para obtener una explicación de cómo operan las funciones analíticas agregadas, consulta Conceptos de funciones analíticas agregadas.
BigQuery admite las siguientes funciones de agregación como funciones analíticas:
- ANY_VALUE
- ARRAY_AGG
- AVG
- CORR
- COUNT
- COUNTIF
- COVAR_POP
- COVAR_SAMP
- MAX
- MIN
- ST_CLUSTERDBSCAN
- STDDEV_POP
- STDDEV_SAMP
- STRING_AGG
- SUM
- VAR_POP
- VAR_SAMP
A continuación, se detallan los requisitos de la cláusula OVER
:
PARTITION BY
: Es opcional.ORDER BY
: Es opcional. No se permite siDISTINCT
está presente.window_frame_clause
: Es opcional. No se permite siDISTINCT
está presente.
Ejemplo:
COUNT(*) OVER (ROWS UNBOUNDED PRECEDING)
SUM(DISTINCT x) OVER ()
Funciones de generación de hash
FARM_FINGERPRINT
FARM_FINGERPRINT(value)
Descripción
Calcula la huella digital de la entrada de STRING
o BYTES
mediante la función Fingerprint64
de la biblioteca de FarmHash de código abierto. El resultado de esta función para una entrada particular nunca cambiará.
Tipo de datos que se muestra
INT64
Ejemplos
WITH example AS (
SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
SELECT 3 AS x, "" AS y, true AS z
)
SELECT
*,
FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
AS row_fingerprint
FROM example;
+---+-------+-------+----------------------+
| x | y | z | row_fingerprint |
+---+-------+-------+----------------------+
| 1 | foo | true | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259 |
| 3 | | true | -4880158226897771312 |
+---+-------+-------+----------------------+
MD5
MD5(input)
Descripción
Calcula el hash de la entrada mediante el uso del algoritmo MD5. La entrada puede ser STRING
o BYTES
. La versión de string trata a la entrada como un arreglo de bytes.
Esta función muestra 16 bytes.
Tipo de datos que se muestra
BYTES
Ejemplo
SELECT MD5("Hello World") as md5;
-- Note that the result of MD5 is of type BYTES, displayed as a base64-encoded string.
+--------------------------+
| md5 |
+--------------------------+
| sQqNsWTgdUEFt6mb5y4/5Q== |
+--------------------------+
SHA1
SHA1(input)
Descripción
Calcula el hash de la entrada mediante el uso del algoritmo SHA-1. La entrada puede ser STRING
o BYTES
. La versión de string trata a la entrada como un arreglo de bytes.
Esta función muestra 20 bytes.
Tipo de datos que se muestra
BYTES
Ejemplo
SELECT SHA1("Hello World") as sha1;
-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
+------------------------------+
| sha1 |
+------------------------------+
| Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
+------------------------------+
SHA256
SHA256(input)
Descripción
Calcula el hash de la entrada mediante el uso del algoritmo SHA-256. La entrada puede ser STRING
o BYTES
. La versión de string trata a la entrada como un arreglo de bytes.
Esta función muestra 32 bytes.
Tipo de datos que se muestra
BYTES
Ejemplo
SELECT SHA256("Hello World") as sha256;
SHA512
SHA512(input)
Descripción
Calcula el hash de la entrada mediante el uso del algoritmo SHA-512. La entrada puede ser STRING
o BYTES
. La versión de string trata a la entrada como un arreglo de bytes.
Esta función muestra 64 bytes.
Tipo de datos que se muestra
BYTES
Ejemplo
SELECT SHA512("Hello World") as sha512;
Funciones de string
Estas funciones de string funcionan en dos valores diferentes: tipos de datos STRING
y BYTES
. Los valores STRING
deben ser UTF-8 y tener el formato correcto.
Las funciones que muestran valores de posición, como STRPOS, codifican esas posiciones como INT64
. El valor 1
se refiere al primer carácter (o byte), 2
se refiere al segundo, y así sucesivamente.
El valor 0
indica un índice no válido. Cuando se trabaja en los tipos de STRING
, las posiciones que se muestran hacen referencia a las posiciones de los caracteres.
Todas las comparaciones de string se realizan byte a byte, sin importar la equivalencia canónica Unicode.
ASCII
ASCII(value)
Descripción
Muestra el código ASCII del primer carácter o byte en value
. Muestra 0
si el value
está vacío o el código ASCII es 0
para el primer carácter o byte.
Tipo de datos que se muestra
INT64
Ejemplos
SELECT ASCII('abcd') as A, ASCII('a') as B, ASCII('') as C, ASCII(NULL) as D;
+-------+-------+-------+-------+
| A | B | C | D |
+-------+-------+-------+-------+
| 97 | 97 | 0 | NULL |
+-------+-------+-------+-------+
BYTE_LENGTH
BYTE_LENGTH(value)
Descripción
Muestra la longitud del valor STRING
o BYTES
en BYTES
, sin importar si el tipo de valor es STRING
o BYTES
.
Tipo de datos que se muestra
INT64
Ejemplos
WITH example AS
(SELECT "абвгд" AS characters, b"абвгд" AS bytes)
SELECT
characters,
BYTE_LENGTH(characters) AS string_example,
bytes,
BYTE_LENGTH(bytes) AS bytes_example
FROM example;
+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд | 10 | абвгд | 10 |
+------------+----------------+-------+---------------+
CHAR_LENGTH
CHAR_LENGTH(value)
Descripción
Muestra la longitud de STRING
en caracteres.
Tipo de datos que se muestra
INT64
Ejemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHAR_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHARACTER_LENGTH
CHARACTER_LENGTH(value)
Descripción
Sinónimo de CHAR_LENGTH.
Tipo de datos que se muestra
INT64
Ejemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHARACTER_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHR
CHR(value)
Descripción
Toma un punto de código Unicode y muestra el carácter que coincide con el punto de código. Cada punto de código válido debe estar dentro del rango de [0, 0xD7FF] y [0xE000, 0x10FFFF]. Muestra una string vacía si el punto de código es 0
. Se muestra un error si se especifica un punto de código Unicode no válido.
Para trabajar con un arreglo de puntos de código Unicode, consulta CODE_POINTS_TO_STRING
.
Tipo de datos que se muestra
STRING
Ejemplos
SELECT CHR(65) AS A, CHR(255) AS B, CHR(513) AS C, CHR(1024) AS D;
+-------+-------+-------+-------+
| A | B | C | D |
+-------+-------+-------+-------+
| A | ÿ | ȁ | Ѐ |
+-------+-------+-------+-------+
SELECT CHR(97) AS A, CHR(0xF9B5) AS B, CHR(0) AS C, CHR(NULL) AS D;
+-------+-------+-------+-------+
| A | B | C | D |
+-------+-------+-------+-------+
| a | 例 | | NULL |
+-------+-------+-------+-------+
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_BYTES(ascii_values)
Descripción
Toma un arreglo de puntos de código ASCII extendidos (ARRAY
de INT64
) y muestra BYTES
.
Para convertir BYTES
en un arreglo de puntos de código, consulta TO_CODE_POINTS.
Tipo de datos que se muestra
BYTES
Ejemplos
El siguiente es un ejemplo básico que usa CODE_POINTS_TO_BYTES
.
SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;
-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes |
+----------+
| QWJDZA== |
+----------+
En el siguiente ejemplo, se usa un algoritmo de rotación por 13 lugares (ROT13) para codificar una string.
SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
(SELECT
CASE
WHEN chr BETWEEN b'a' and b'z'
THEN TO_CODE_POINTS(b'a')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)
WHEN chr BETWEEN b'A' and b'Z'
THEN TO_CODE_POINTS(b'A')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)
ELSE code
END
FROM
(SELECT code, CODE_POINTS_TO_BYTES([code]) chr)
) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;
-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+
CODE_POINTS_TO_STRING
CODE_POINTS_TO_STRING(value)
Descripción
Toma un arreglo de puntos de código Unicode (ARRAY
de INT64
) y muestra una STRING
. Si un punto de código es 0, no muestra un carácter para él en la STRING
.
Para convertir una string en un arreglo de puntos de código, consulta TO_CODE_POINTS.
Tipo de datos que se muestra
STRING
Ejemplos
Los siguientes son ejemplos básicos del uso de CODE_POINTS_TO_STRING
.
SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;
+--------+
| string |
+--------+
| AÿȁЀ |
+--------+
SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;
+--------+
| string |
+--------+
| a例 |
+--------+
SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;
+--------+
| string |
+--------+
| NULL |
+--------+
En el siguiente ejemplo, se calcula la frecuencia de las letras en un conjunto de palabras.
WITH Words AS (
SELECT word
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
CODE_POINTS_TO_STRING([code_point]) AS letter,
COUNT(*) AS letter_count
FROM Words,
UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;
+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a | 5 |
| f | 3 |
| r | 2 |
| b | 2 |
| l | 2 |
| o | 2 |
| g | 1 |
| z | 1 |
| e | 1 |
| m | 1 |
| i | 1 |
+--------+--------------+
CONCAT
CONCAT(value1[, ...])
Descripción
Concatena uno o más valores en un solo resultado. Todos los valores deben ser BYTES
o tipos de datos que se puedan convertir en STRING
.
La función muestra NULL
si algún argumento de entrada es NULL
.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
SELECT CONCAT("T.P.", " ", "Bar") as author;
+---------------------+
| author |
+---------------------+
| T.P. Bar |
+---------------------+
SELECT CONCAT("Summer", " ", 1923) as release_date;
+---------------------+
| release_date |
+---------------------+
| Summer 1923 |
+---------------------+
With Employees AS
(SELECT
"John" AS first_name,
"Doe" AS last_name
UNION ALL
SELECT
"Jane" AS first_name,
"Smith" AS last_name
UNION ALL
SELECT
"Joe" AS first_name,
"Jackson" AS last_name)
SELECT
CONCAT(first_name, " ", last_name)
AS full_name
FROM Employees;
+---------------------+
| full_name |
+---------------------+
| John Doe |
| Jane Smith |
| Joe Jackson |
+---------------------+
ENDS_WITH
ENDS_WITH(value1, value2)
Descripción
Toma dos valores STRING
o BYTES
. Muestra TRUE
si el segundo valor es un sufijo del primero.
Tipo de datos que se muestra
BOOL
Ejemplos
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
ENDS_WITH(item, "e") as example
FROM items;
+---------+
| example |
+---------+
| True |
| False |
| True |
+---------+
FORMAT
BigQuery admite una función FORMAT()
para formatear strings. Esta función es similar a la función printf
de C. Produce una STRING
a partir de una string de formato que contiene cero o más especificadores de formato, junto con una lista de longitud variable de argumentos adicionales que coincide con los especificadores de formato.
A continuación, se incluyen algunos ejemplos:
Descripción | Statement | Resultado |
---|---|---|
Número entero simple | FORMAT("%d", 10) | 10 |
Número entero con relleno en blanco a la izquierda | FORMAT("|%10d|", 11) | | 11| |
Número entero con relleno de ceros a la izquierda | FORMAT("+%010d+", 12) | +0000000012+ |
Número entero con comas | FORMAT("%'d", 123456789) | 123,456,789 |
STRING | FORMAT("-%s-", 'abcd efg') | -abcd efg- |
FLOAT64 | FORMAT("%f %E", 1.1, 2.2) | 1.100000 2.200000E+00 |
DATE | FORMAT("%t", date "2015-09-01") | 2015-09-01 |
TIMESTAMP | FORMAT("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") | 2015‑09‑01 19:34:56+00 |
La función FORMAT()
no proporciona un formato personalizable por completo para todos los tipos y valores, ni un formato sensible a la configuración regional.
Si el formato personalizado es necesario para un tipo, primero debes formatearlo con funciones de formato específicas del tipo, como FORMAT_DATE()
o FORMAT_TIMESTAMP()
.
Por ejemplo:
SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));
Muestra
date: January 02, 2015!
Sintaxis
La sintaxis FORMAT()
toma una string de formato y una lista de argumentos de longitud variable y produce un resultado de STRING
:
FORMAT(format_string, ...)
La expresión format_string
puede contener cero o más especificadores de formato.
Cada especificador de formato se ingresa mediante el símbolo %
y debe asignarse a uno o más de los argumentos restantes. En general, esta es una asignación uno a uno, excepto cuando está presente el especificador *
. Por ejemplo, %.*i
se asigna a dos argumentos: uno de longitud y uno de número entero con signo. Si el número de argumentos relacionado con los especificadores de formato no es el mismo que el número de argumentos, se genera un error.
Especificadores de formato admitidos
El especificador de formato de función FORMAT()
sigue este prototipo:
%[flags][width][.precision]specifier
Los especificadores de formato admitidos se identifican en la siguiente tabla. Las desviaciones de printf() se identifican en cursiva.
Especificador | Descripción | Ejemplos | Tipos |
d o i |
Número entero decimal | 392 | INT64 |
o |
Octal | 610 | INT64* |
x |
Número entero hexadecimal | 7fa | INT64* |
X |
Número entero hexadecimal (mayúsculas) | 7FA | INT64* |
f |
Notación decimal, en [-](parte de número entero).(parte fraccionaria) para valores finitos y en minúsculas para valores no finitos | 392.650000 inf nan |
NUMERIC FLOAT64 |
F |
Notación decimal, en [-](parte de número entero).(parte fraccionaria) para valores finitos y en mayúsculas para valores no finitos | 392.650000 INF NAN |
NUMERIC FLOAT64 |
e |
Notación científica (mantisa/exponente), minúsculas | 3.926500e+02 inf nan |
NUMERIC FLOAT64 |
E |
Notación científica (mantisa/exponente), mayúsculas | 3.926500E+02 INF NAN |
NUMERIC FLOAT64 |
g |
Notación decimal o científica, según el exponente del valor de entrada y la precisión especificada. Minúscula. Consulta el comportamiento de %g y %G para obtener más detalles. | 392.65 3.9265e+07 inf nan |
NUMERIC FLOAT64 |
G |
Notación decimal o científica, según el exponente del valor de entrada y la precisión especificada. Mayúscula. Consulta el comportamiento de %g y %G para obtener más detalles. |
392.65 3.9265E+07 INF NAN |
NUMERIC FLOAT64 |
s |
String de caracteres | sample | STRING |
t |
Muestra una string imprimible que representa el valor. A menudo, se parece a convertir el tipo del argumento en STRING .
Consulta el comportamiento de %t y %T.
|
muestra 2014‑01‑01 |
<any> |
T |
Genera una string que es una constante válida de BigQuery con un tipo similar al tipo del valor (puede ser más amplio o una string). Consulta el comportamiento de %t y %T. |
'sample' b'bytes sample' 1234 2.3 date '2014‑01‑01' |
<any> |
% |
“%%” genera un solo “%” | % | No corresponde |
* Los especificadores %o
, %x
y %X
generan un error si se usan valores negativos.
El especificador de formato puede contener de forma opcional los subespecificadores identificados antes en el prototipo del especificador.
Estos subespecificadores deben cumplir con las siguientes especificaciones.
Marcas
Marcas | Descripción |
- |
Justificar a la izquierda dentro del ancho de campo dado; la justificación a la derecha es la predeterminada (ver subespecificador de ancho) |
+ |
Obliga a anteponer un signo más o menos al resultado (+ o - ) incluso para números positivos. De forma predeterminada, solo los números negativos van precedidos de un signo - |
<space> | Si no se va a escribir ningún signo, se inserta un espacio en blanco antes del valor. |
# |
|
0 |
Rellena a la izquierda del número con ceros (0) en lugar de espacios cuando se especifica el relleno (ver subespecificador de ancho). |
' |
Da formato a números enteros con el carácter de agrupación apropiado. Por ejemplo:
Esta marca solo es relevante para valores decimales, hexadecimales y octales. |
Las marcas se pueden especificar en cualquier orden. Las marcas duplicadas no son un error. Cuando las marcas no son relevantes para algún tipo de elemento, se ignoran.
Ancho
Ancho | Descripción |
<number> | Cantidad mínima de caracteres que se deben imprimir. Si el valor que se debe imprimir es más corto que este número, el resultado se rellena con espacios en blanco. El valor no se trunca aunque el resultado sea mayor. |
* |
El ancho no se especifica en la string de formato, sino como un argumento de número entero adicional que precede al argumento al que se debe dar formato. |
Precisión
Precisión | Descripción |
. <number> |
|
.* |
La precisión no se especifica en la string de formato, sino como un argumento de número entero adicional que precede al argumento al que se debe dar formato. |
Comportamiento de %g y %G
Los especificadores de formato %g
y %G
eligen la notación decimal (como los especificadores %f
y %F
) o la notación científica (como los especificadores %e
y %E
), según el exponente del valor de entrada y la precisión especificada.
Supongamos que p representa la precisión especificada (el valor predeterminado es 6; 1 si la precisión especificada es menor que 1). Primero el valor de entrada se convierte en notación científica con precisión = (p - 1). Si la parte x del exponente resultante es menor que -4 o no menor que p, se usa la notación científica con precisión = (p - 1); de lo contrario, se usa la notación decimal con precisión = (p - 1 - x).
A menos que esté presente la marca #
, se quitan los ceros finales después del punto decimal y el punto decimal si no hay dígitos después de este.
Comportamiento de %t y %T
Los especificadores de formato %t
y %T
están definidos para todos los tipos. El ancho, la precisión y las marcas actúan como lo hacen en %s
: el ancho es el ancho mínimo, la STRING
se rellenará hasta llegar a ese tamaño; y la precisión es el ancho máximo de contenido que se mostrará, la STRING
se truncará a ese tamaño, antes de rellenar el ancho.
El especificador %t
siempre debe ser un formato legible del valor.
El especificador %T
siempre es un literal de SQL válido de un tipo similar, como un tipo numérico más amplio.
El literal no incluirá conversiones de tipos o un nombre de tipo, excepto en el caso especial de los valores de punto flotante no finitos.
Se le da formato a la STRING
de la siguiente manera:
Tipo | %t | %T |
NULL de cualquier tipo |
NULL |
NULL |
INT64 |
123 | 123 |
NUMERIC | 123.0 (siempre con .0) | NUMERIC "123.0" |
FLOAT64 |
123.0 (siempre con .0) 123e+10 inf -inf NaN
|
123.0 (siempre con .0) 123e+10 CAST("inf" AS <type>) CAST("-inf" AS <type>) CAST("nan" AS <type>) |
STRING | valor de string sin comillas | literal de string entre comillas |
BYTES |
bytes escapados sin comillas p. ej., abc\x01\x02 |
literal de bytes entre comillas, p. ej., b"abc\x01\x02" |
DATE | 2011-02-03 | DATE "2011-02-03" |
TIMESTAMP | 2011-02-03 04:05:06+00 | TIMESTAMP "2011-02-03 04:05:06+00" |
ARRAY | [value, value, …] ; aquí, los valores se formatean con %t |
[value, value, …] ; aquí, los valores se formatean con %T |
STRUCT | [value, value, …] ; aquí, los campos se formatean con %t |
[value, value, …] ; aquí, los campos se formatean con %T Casos especiales: cero campos: STRUCT() Un campo: STRUCT(value) |
Condiciones de error
Si un especificador de formato no es válido o no es compatible con el tipo de argumento relacionado, o si se proporciona número o argumentos incorrectos, se genera un error. Por ejemplo, las siguientes expresiones <format_string>
no son válidas:
FORMAT('%s', 1)
FORMAT('%')
Control de argumentos NULL
Una string de formato NULL
da como resultado una STRING
de resultado NULL
. Cualquier otro argumento se ignora en este caso.
Por lo general, la función produce un valor NULL
si hay un argumento NULL
presente.
Por ejemplo, FORMAT('%i', NULL_expression)
produce una NULL STRING
como resultado.
Sin embargo, existen algunas excepciones: si el especificador de formato es %t o %T (ambos producen STRING
s que coinciden de forma efectiva con la semántica de valores literales y CAST), un valor NULL
produce “NULL” (sin comillas) en la STRING
de resultado. Por ejemplo, la siguiente función:
FORMAT('00-%t-00', NULL_expression);
Muestra
00-NULL-00
Reglas semánticas adicionales
Los valores de FLOAT64
pueden ser +/-inf
o NaN
.
Cuando un argumento tiene uno de esos valores, el resultado de los especificadores de formato %f
, %F
, %e
, %E
, %g
, %G
y %t
son inf
, -inf
o nan
(o lo mismo en mayúsculas), según corresponda. Esto es coherente con la forma en que BigQuery convierte el tipo de estos valores en STRING
. En %T
, BigQuery muestra strings entre comillas para los valores FLOAT64
que no tienen representaciones literales que no sean de string.
FROM_BASE32
FROM_BASE32(string_expr)
Descripción
Convierte la entrada codificada en base32 string_expr
en el formato BYTES
. Para convertir BYTES
en una STRING
codificada en base32, usa TO_BASE32.
Tipo de datos que se muestra
BYTES
Ejemplo
SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;
-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/ |
+-----------+
FROM_BASE64
FROM_BASE64(string_expr)
Descripción
Convierte la string_expr
de entrada codificada en base64 en formato BYTES
. Para convertir BYTES
en una STRING
codificada en base64, usa TO_BASE64.
Tipo de datos que se muestra
BYTES
Ejemplo
SELECT FROM_BASE64('3q2+7w==') AS byte_data;
-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| 3q2+7w== |
+-----------+
FROM_HEX
FROM_HEX(string)
Descripción
Convierte una STRING
con codificación hexadecimal en formato BYTES
. Muestra un error si la STRING
de entrada contiene caracteres fuera del rango (0..9, A..F, a..f)
. No importa si los caracteres están en mayúsculas o minúsculas. Si la STRING
de entrada tiene una cantidad impar de caracteres, la función actúa como si la entrada tuviera un 0
inicial adicional. Para convertir BYTES
en una STRING
con codificación hexadecimal, usa TO_HEX.
Tipo de datos que se muestra
BYTES
Ejemplo
WITH Input AS (
SELECT '00010203aaeeefff' AS hex_str UNION ALL
SELECT '0AF' UNION ALL
SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;
-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str | bytes_str |
+------------------+--------------+
| 0AF | AAECA6ru7/8= |
| 00010203aaeeefff | AK8= |
| 666f6f626172 | Zm9vYmFy |
+------------------+--------------+
INITCAP
INITCAP(value[, delimiters])
Descripción
Toma una STRING
y la muestra con el primer carácter en cada palabra en mayúsculas y todos los demás caracteres en minúsculas. Los caracteres que no son alfanuméricos no se modifican.
delimiters
es un argumento de string opcional que se usa para anular el conjunto predeterminado de caracteres usados a fin de separar palabras. Si no se especifica delimiters
, tomará de forma predeterminada los siguientes caracteres:
<whitespace> [ ] ( ) { } / | \ < > ! ? @ " ^ # $ & ~ _ , . : ; * % + -
Si value
o delimiters
es NULL
, la función mostrará NULL
.
Tipo de datos que se muestra
STRING
Ejemplos
WITH example AS
(
SELECT "Hello World-everyone!" AS value UNION ALL
SELECT "tHe dog BARKS loudly+friendly" AS value UNION ALL
SELECT "apples&oranges;&pears" AS value UNION ALL
SELECT "καθίσματα ταινιών" AS value
)
SELECT value, INITCAP(value) AS initcap_value FROM example
+-------------------------------+-------------------------------+
| value | initcap_value |
+-------------------------------+-------------------------------+
| Hello World-everyone! | Hello World-Everyone! |
| tHe dog BARKS loudly+friendly | The Dog Barks Loudly+Friendly |
| apples&oranges;&pears | Apples&Oranges;&Pears |
| καθίσματα ταινιών | Καθίσματα Ταινιών |
+-------------------------------+-------------------------------+
WITH example AS
(
SELECT "hello WORLD!" AS value, "" AS delimiters UNION ALL
SELECT "καθίσματα ταιντιώ@ν" AS value, "τ@" AS delimiters UNION ALL
SELECT "Apples1oranges2pears" AS value, "12" AS delimiters UNION ALL
SELECT "tHisEisEaESentence" AS value, "E" AS delimiters
)
SELECT value, delimiters, INITCAP(value, delimiters) AS initcap_value FROM example;
+----------------------+------------+----------------------+
| value | delimiters | initcap_value |
+----------------------+------------+----------------------+
| hello WORLD! | | Hello world! |
| καθίσματα ταιντιώ@ν | τ@ | ΚαθίσματΑ τΑιντΙώ@Ν |
| Apples1oranges2pears | 12 | Apples1Oranges2Pears |
| tHisEisEaESentence | E | ThisEIsEAESentence |
+----------------------+------------+----------------------+
INSTR
INSTR(source_value, search_value[, position[, occurrence]])
Descripción
Muestra el índice basado en 1 más bajo de search_value
en source_value
. 0 se muestra cuando no se encuentra ninguna coincidencia. source_value
y search_value
deben ser del mismo tipo, STRING
o BYTES
.
Si se especifica la position
, la búsqueda comienza en esta posición en source_value
; de lo contrario, empieza al principio del source_value
. Si la position
es negativa, la función busca hacia atrás desde el final del source_value
, con -1 que indica el último carácter. position
no puede ser 0.
Si se especifica occurrence
, la búsqueda muestra la posición de una instancia específica de search_value
en source_value
. De lo contrario, mostrará el índice del primer caso. Si occurrence
es mayor que la cantidad de coincidencias encontradas, se mostrará 0. Para occurrence
> 1, la función busca casos superpuestos, es decir, la función busca casos adicionales que comienzan con el segundo carácter en el caso anterior.
occurrence
no puede ser 0 ni negativo.
Tipo de datos que se muestra
INT64
Ejemplos
WITH example AS
(SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 2 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 3 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'ann' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 2 as
occurrence
)
SELECT source_value, search_value, position, occurrence, INSTR(source_value,
search_value, position, occurrence) AS instr
FROM example;
+--------------+--------------+----------+------------+-------+
| source_value | search_value | position | occurrence | instr |
+--------------+--------------+----------+------------+-------+
| banana | an | 1 | 1 | 2 |
| banana | an | 1 | 2 | 4 |
| banana | an | 1 | 3 | 0 |
| banana | an | 3 | 1 | 4 |
| banana | an | -1 | 1 | 4 |
| banana | an | -3 | 1 | 4 |
| banana | ann | 1 | 1 | 0 |
| helloooo | oo | 1 | 1 | 5 |
| helloooo | oo | 1 | 2 | 6 |
+--------------+--------------+----------+------------+-------+
LEFT
LEFT(value, length)
Descripción
Muestra un valor STRING
o BYTES
que consiste en la cantidad especificada de caracteres o bytes más a la izquierda de value
. length
es un INT64
que especifica la longitud del valor que se muestra. Si el value
es del tipo BYTES
, la length
es la cantidad de bytes que se encuentran más a la izquierda y que se mostrarán. Si value
es STRING
, la length
es la cantidad de caracteres más a la izquierda que se mostrarán.
Si la length
es 0, se mostrará un valor de STRING
o BYTES
vacío. Si la length
es negativa, se mostrará un error. Si la length
excede la cantidad de caracteres o bytes del value
, se mostrará el value
original.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;
+---------+--------------+
| example | left_example |
+---------+--------------+
| apple | app |
| banana | ban |
| абвгд | абв |
+---------+--------------+
WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;
-- Note that the result of LEFT is of type BYTES, displayed as a base64-encoded string.
+----------+--------------+
| example | left_example |
+----------+--------------+
| YXBwbGU= | YXBw |
| YmFuYW5h | YmFu |
| q83vqrs= | q83v |
+----------+--------------+
LENGTH
LENGTH(value)
Descripción
Muestra la longitud del valor STRING
o BYTES
. El valor mostrado está en caracteres para los argumentos de STRING
y en bytes para el argumento de BYTES
.
Tipo de datos que se muestra
INT64
Ejemplos
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
LENGTH(characters) AS string_example,
LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;
+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд | 5 | 10 |
+------------+----------------+---------------+
LPAD
LPAD(original_value, return_length[, pattern])
Descripción
Muestra un valor de STRING
o BYTES
que consta de original_value
con pattern
antepuesto. return_length
es un INT64
que especifica la longitud del valor mostrado. Si original_value
es del tipo BYTES
, return_length
es la cantidad de bytes. Si original_value
es del tipo STRING
, return_length
es la cantidad de caracteres.
El valor predeterminado de pattern
es un espacio en blanco.
original_value
y pattern
deben tener el mismo tipo de datos.
Si return_length
es menor o igual que la longitud de original_value
, esta función muestra el valor original_value
, truncado al valor de return_length
. Por ejemplo, LPAD("hello world", 7);
muestra "hello w"
.
Si original_value
, return_length
o pattern
es NULL
, esta función muestra NULL
.
Esta función muestra un error si se da una de estas condiciones:
return_length
es negativo.pattern
está vacía.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
+------+-----+----------+
| t | len | LPAD |
|------|-----|----------|
| abc | 5 | " abc" |
| abc | 2 | "ab" |
| 例子 | 4 | " 例子" |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
+------+-----+---------+--------------+
| t | len | pattern | LPAD |
|------|-----|---------|--------------|
| abc | 8 | def | "defdeabc" |
| abc | 5 | - | "--abc" |
| 例子 | 5 | 中文 | "中文中例子" |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
+-----------------+-----+------------------+
| t | len | LPAD |
|-----------------|-----|------------------|
| b"abc" | 5 | b" abc" |
| b"abc" | 2 | b"ab" |
| b"\xab\xcd\xef" | 4 | b" \xab\xcd\xef" |
+-----------------+-----+------------------+
SELECT
FORMAT("%T", t) AS t,
len,
FORMAT("%T", pattern) AS pattern,
FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
+-----------------+-----+---------+-------------------------+
| t | len | pattern | LPAD |
|-----------------|-----|---------|-------------------------|
| b"abc" | 8 | b"def" | b"defdeabc" |
| b"abc" | 5 | b"-" | b"--abc" |
| b"\xab\xcd\xef" | 5 | b"\x00" | b"\x00\x00\xab\xcd\xef" |
+-----------------+-----+---------+-------------------------+
LOWER
LOWER(value)
Descripción
Para los argumentos de STRING
, muestra la string original con todos los caracteres alfabéticos en minúsculas. La asignación entre minúsculas y mayúsculas se realiza de acuerdo con la base de datos de caracteres Unicode, sin tener en cuenta las asignaciones específicas del lenguaje.
Para los argumentos de BYTES
, el argumento se trata como texto ASCII, con todos los bytes mayores a 127 intactos.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH items AS
(SELECT
"FOO" as item
UNION ALL
SELECT
"BAR" as item
UNION ALL
SELECT
"BAZ" as item)
SELECT
LOWER(item) AS example
FROM items;
+---------+
| example |
+---------+
| foo |
| bar |
| baz |
+---------+
LTRIM
LTRIM(value1[, value2])
Descripción
Es idéntico a TRIM, pero solo quita caracteres iniciales.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH items AS
(SELECT " apple " as item
UNION ALL
SELECT " banana " as item
UNION ALL
SELECT " orange " as item)
SELECT
CONCAT("#", LTRIM(item), "#") as example
FROM items;
+-------------+
| example |
+-------------+
| #apple # |
| #banana # |
| #orange # |
+-------------+
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
LTRIM(item, "*") as example
FROM items;
+-----------+
| example |
+-----------+
| apple*** |
| banana*** |
| orange*** |
+-----------+
WITH items AS
(SELECT "xxxapplexxx" as item
UNION ALL
SELECT "yyybananayyy" as item
UNION ALL
SELECT "zzzorangezzz" as item
UNION ALL
SELECT "xyzpearxyz" as item)
SELECT
LTRIM(item, "xyz") as example
FROM items;
+-----------+
| example |
+-----------+
| applexxx |
| bananayyy |
| orangezzz |
| pearxyz |
+-----------+
NORMALIZE
NORMALIZE(value[, normalization_mode])
Descripción
Toma un valor de string y lo muestra como una string normalizada.
La normalización se usa para garantizar que dos strings sean equivalentes. La normalización suele usarse en situaciones en las que dos strings se renderizan de la misma forma en la pantalla, pero tienen puntos de código Unicode diferentes.
NORMALIZE
admite cuatro modos de normalización opcionales:
Valor | Nombre | Descripción |
---|---|---|
NFC | Composición canónica de la forma normalizada | Descompone y recompone los caracteres por equivalencia canónica. |
NFKC | Composición de compatibilidad de la forma normalizada | Descompone los caracteres por compatibilidad y los recompone por equivalencia canónica. |
NFD | Descomposición canónica de la forma normalizada | Descompone los caracteres por equivalencia canónica, y múltiples caracteres de combinación se organizan en un orden específico. |
NFKD | Descomposición de compatibilidad de la forma normalizada | Descompone los caracteres por compatibilidad, y múltiples caracteres de combinación se organizan en un orden específico. |
El modo de normalización predeterminado es NFC
.
Tipo de datos que se muestra
STRING
Ejemplos
SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;
+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true |
+---+---+------------+
En el siguiente ejemplo, se normalizan diferentes caracteres de espacio.
WITH EquivalentNames AS (
SELECT name
FROM UNNEST([
'Jane\u2004Doe',
'John\u2004Smith',
'Jane\u2005Doe',
'Jane\u2006Doe',
'John Smith']) AS name
)
SELECT
NORMALIZE(name, NFKC) AS normalized_name,
COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;
+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith | 2 |
| Jane Doe | 3 |
+-----------------+------------+
NORMALIZE_AND_CASEFOLD
NORMALIZE_AND_CASEFOLD(value[, normalization_mode])
Descripción
Toma una STRING
, value
, y realiza las mismas acciones que NORMALIZE
, además de la combinación de mayúsculas y minúsculas para operaciones que no distinguen entre mayúsculas y minúsculas.
NORMALIZE_AND_CASEFOLD
admite cuatro modos de normalización opcionales:
Valor | Nombre | Descripción |
---|---|---|
NFC | Composición canónica de la forma normalizada | Descompone y recompone los caracteres por equivalencia canónica. |
NFKC | Composición de compatibilidad de la forma normalizada | Descompone los caracteres por compatibilidad y los recompone por equivalencia canónica. |
NFD | Descomposición canónica de la forma normalizada | Descompone los caracteres por equivalencia canónica, y múltiples caracteres de combinación se organizan en un orden específico. |
NFKD | Descomposición de compatibilidad de la forma normalizada | Descompone los caracteres por compatibilidad, y múltiples caracteres de combinación se organizan en un orden específico. |
El modo de normalización predeterminado es NFC
.
Tipo de datos que se muestra
STRING
Ejemplo
WITH Strings AS (
SELECT '\u2168' AS a, 'IX' AS b UNION ALL
SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;
+---+----+-------+-------+------+------+
| a | b | nfd | nfc | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å | true | true | true | true |
+---+----+-------+-------+------+------+
OCTET_LENGTH
OCTET_LENGTH(value)
Alias de BYTE_LENGTH
.
REGEXP_CONTAINS
REGEXP_CONTAINS(value, regexp)
Descripción
Muestra TRUE
si value
es una coincidencia parcial para la expresión regular, regexp
.
Si el argumento regexp
no es válido, la función muestra un error.
Puedes buscar una coincidencia completa con ^
(principio del texto) y $
(final del texto). Debido a la precedencia del operador de expresión regular, es una buena práctica encerrar entre paréntesis todo lo que está entre ^
y $
.
Tipo de datos que se muestra
BOOL
Ejemplos
SELECT
email,
REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
(SELECT
["foo@example.com", "bar@example.org", "www.example.net"]
AS addresses),
UNNEST(addresses) AS email;
+-----------------+----------+
| email | is_valid |
+-----------------+----------+
| foo@example.com | true |
| bar@example.org | true |
| www.example.net | false |
+-----------------+----------+
# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
email,
REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
AS valid_email_address,
REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
AS without_parentheses
FROM
(SELECT
["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
AS addresses),
UNNEST(addresses) AS email;
+----------------+---------------------+---------------------+
| email | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com | true | true |
| a@foo.computer | false | true |
| b@bar.org | true | true |
| !b@bar.org | false | true |
| c@buz.net | false | false |
+----------------+---------------------+---------------------+
REGEXP_EXTRACT
REGEXP_EXTRACT(value, regexp[, position[, occurrence]])
Descripción
Muestra la substring en el value
que coincide con la expresión regular, regexp
.
Muestra NULL
si no hay coincidencia.
Si la expresión regular contiene un grupo de captura, la función muestra la substring que coincide con él. Si la expresión no contiene un grupo de captura, la función muestra toda la substring coincidente.
Si se especifica la position
, la búsqueda comienza en esta posición en value
; de lo contrario, empieza al principio del value
. El valor position
debe ser un número entero positivo y no puede ser 0. Si el valor position
es mayor que la longitud de value
, se muestra NULL
.
Si se especifica occurrence
, la búsqueda mostrará un caso específico de regexp
en value
. De lo contrario, muestra la primera coincidencia. Si occurrence
es mayor que la cantidad de coincidencias encontradas, se mostrará NULL
. Para occurrence
> 1, la función busca casos adicionales que comienzan con el carácter que sigue al caso anterior.
Muestra un error si se da una de estas condiciones:
- La expresión regular no es válida.
- La expresión regular tiene más de un grupo de captura.
position
no es un número entero positivo.occurrence
no es un número entero positivo.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH email_addresses AS
(SELECT "foo@example.com" as email
UNION ALL
SELECT "bar@example.org" as email
UNION ALL
SELECT "baz@example.net" as email)
SELECT
REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
AS user_name
FROM email_addresses;
+-----------+
| user_name |
+-----------+
| foo |
| bar |
| baz |
+-----------+
WITH email_addresses AS
(SELECT "foo@example.com" as email
UNION ALL
SELECT "bar@example.org" as email
UNION ALL
SELECT "baz@example.net" as email)
SELECT
REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
AS top_level_domain
FROM email_addresses;
+------------------+
| top_level_domain |
+------------------+
| com |
| org |
| net |
+------------------+
WITH example AS
(SELECT 'Hello Helloo and Hellooo' AS value, 'H?ello+' AS regex, 1 as position,
1 AS occurrence UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 4 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 2, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 20, 1 UNION ALL
SELECT 'cats&dogs&rabbits' ,'\\w+&', 1, 2 UNION ALL
SELECT 'cats&dogs&rabbits', '\\w+&', 2, 3
)
SELECT value, regex, position, occurrence, REGEXP_EXTRACT(value, regex,
position, occurrence) AS regexp_value FROM example;
+--------------------------+---------+----------+------------+--------------+
| value | regex | position | occurrence | regexp_value |
+--------------------------+---------+----------+------------+--------------+
| Hello Helloo and Hellooo | H?ello+ | 1 | 1 | Hello |
| Hello Helloo and Hellooo | H?ello+ | 1 | 2 | Helloo |
| Hello Helloo and Hellooo | H?ello+ | 1 | 3 | Hellooo |
| Hello Helloo and Hellooo | H?ello+ | 1 | 4 | NULL |
| Hello Helloo and Hellooo | H?ello+ | 2 | 1 | ello |
| Hello Helloo and Hellooo | H?ello+ | 3 | 1 | Helloo |
| Hello Helloo and Hellooo | H?ello+ | 3 | 2 | Hellooo |
| Hello Helloo and Hellooo | H?ello+ | 3 | 3 | NULL |
| Hello Helloo and Hellooo | H?ello+ | 20 | 1 | NULL |
| cats&dogs&rabbits | \w+& | 1 | 2 | dogs& |
| cats&dogs&rabbits | \w+& | 2 | 3 | NULL |
+--------------------------+---------+----------+------------+--------------+
REGEXP_EXTRACT_ALL
REGEXP_EXTRACT_ALL(value, regexp)
Descripción
Muestra un arreglo de todas las substrings de value
que coinciden con la expresión regular, regexp
.
La función REGEXP_EXTRACT_ALL
solo muestra coincidencias no superpuestas. Por ejemplo, usar esta función para extraer ana
de banana
muestra solo una substring, no dos.
Tipo de datos que se muestra
Un ARRAY
de STRING
o BYTES
Ejemplos
WITH code_markdown AS
(SELECT "Try `function(x)` or `function(y)`" as code)
SELECT
REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;
+----------------------------+
| example |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+
REGEXP_INSTR
REGEXP_INSTR(source_value, regexp [, position[, occurrence, [occurrence_position]]])
Descripción
Muestra el índice basado en 1 más bajo de una expresión regular, regexp
, en source_value
. Muestra 0
cuando no se encuentra ninguna coincidencia o la expresión regular está vacía. Muestra un error si la expresión regular no es válida o si tiene más de un grupo de captura. source_value
y regexp
deben ser del mismo tipo, STRING
o BYTES
.
Si se especifica la position
, la búsqueda comienza en esta posición en source_value
; de lo contrario, empieza al principio del source_value
. Si la position
es negativa, la función busca hacia atrás desde el final del source_value
, con -1 que indica el último carácter. position
no puede ser 0.
Si se especifica occurrence
, la búsqueda muestra la posición de una instancia específica de regexp
en source_value
. De lo contrario, mostrará el índice del primer caso. Si occurrence
es mayor que la cantidad de coincidencias encontradas, se mostrará 0. Para occurrence
> 1, la función busca casos superpuestos, es decir, la función busca casos adicionales que comienzan con el segundo carácter en el caso anterior.
occurrence
no puede ser 0 ni negativo.
De forma opcional, puedes usar occurrence_position
para especificar dónde comienza una posición en relación con un occurrence
. Tus opciones son las siguientes: + 0
, que muestra la posición inicial del caso, y
+ 1
, que muestra la primera posición después del final del caso. Si el final del caso también es el final de la entrada, se muestra uno al final del caso. Por ejemplo, la longitud de una string + 1.
Tipo de datos que se muestra
INT64
Ejemplos
WITH example AS (
SELECT 'ab@gmail.com' AS source_value, '@[^.]*' AS regexp UNION ALL
SELECT 'ab@mail.com', '@[^.]*' UNION ALL
SELECT 'abc@gmail.com', '@[^.]*' UNION ALL
SELECT 'abc.com', '@[^.]*')
SELECT source_value, regexp, REGEXP_INSTR(source_value, regexp) AS instr
FROM example;
+---------------+--------+-------+
| source_value | regexp | instr |
+---------------+--------+-------+
| ab@gmail.com | @[^.]* | 3 |
| ab@mail.com | @[^.]* | 3 |
| abc@gmail.com | @[^.]* | 4 |
| abc.com | @[^.]* | 0 |
+---------------+--------+-------+
WITH example AS (
SELECT 'a@gmail.com b@gmail.com' AS source_value, '@[^.]*' AS regexp, 1 AS position UNION ALL
SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 2 UNION ALL
SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 3 UNION ALL
SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 4)
SELECT
source_value, regexp, position,
REGEXP_INSTR(source_value, regexp, position) AS instr
FROM example;
+-------------------------+--------+----------+-------+
| source_value | regexp | position | instr |
+-------------------------+--------+----------+-------+
| a@gmail.com b@gmail.com | @[^.]* | 1 | 2 |
| a@gmail.com b@gmail.com | @[^.]* | 2 | 2 |
| a@gmail.com b@gmail.com | @[^.]* | 3 | 14 |
| a@gmail.com b@gmail.com | @[^.]* | 4 | 14 |
+-------------------------+--------+----------+-------+
WITH example AS (
SELECT 'a@gmail.com b@gmail.com c@gmail.com' AS source_value,
'@[^.]*' AS regexp, 1 AS position, 1 AS occurrence UNION ALL
SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 2 UNION ALL
SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 3)
SELECT
source_value, regexp, position, occurrence,
REGEXP_INSTR(source_value, regexp, position, occurrence) AS instr
FROM example;
+-------------------------------------+--------+----------+------------+-------+
| source_value | regexp | position | occurrence | instr |
+-------------------------------------+--------+----------+------------+-------+
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1 | 1 | 2 |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1 | 2 | 14 |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1 | 3 | 26 |
+-------------------------------------+--------+----------+------------+-------+
WITH example AS (
SELECT 'a@gmail.com' AS source_value, '@[^.]*' AS regexp,
1 AS position, 1 AS occurrence, 0 AS o_position UNION ALL
SELECT 'a@gmail.com', '@[^.]*', 1, 1, 1)
SELECT
source_value, regexp, position, occurrence, o_position,
REGEXP_INSTR(source_value, regexp, position, occurrence, o_position) AS instr
FROM example;
+--------------+--------+----------+------------+------------+-------+
| source_value | regexp | position | occurrence | o_position | instr |
+--------------+--------+----------+------------+------------+-------+
| a@gmail.com | @[^.]* | 1 | 1 | 0 | 2 |
| a@gmail.com | @[^.]* | 1 | 1 | 1 | 8 |
+--------------+--------+----------+------------+------------+-------+
REGEXP_REPLACE
REGEXP_REPLACE(value, regexp, replacement)
Descripción
Muestra una STRING
en la que todas las substrings de value
que coinciden con la expresión regular regexp
se reemplazan por replacement
.
Puedes usar dígitos con escape de barras invertidas (de \1 a \9) dentro del argumento replacement
para insertar texto que coincida con el grupo entre paréntesis correspondiente en el patrón regexp
. Usa \0 para hacer referencia a todo el texto coincidente.
La función REGEXP_REPLACE
solo reemplaza las coincidencias no superpuestas. Por ejemplo, si se reemplaza ana
dentro de banana
, ocurre un solo reemplazo, no dos.
Si el argumento regexp
no es una expresión regular válida, esta función muestra un error.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH markdown AS
(SELECT "# Heading" as heading
UNION ALL
SELECT "# Another heading" as heading)
SELECT
REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
AS html
FROM markdown;
+--------------------------+
| html |
+--------------------------+
| <h1>Heading</h1> |
| <h1>Another heading</h1> |
+--------------------------+
REGEXP_SUBSTR
REGEXP_SUBSTR(value, regexp[, position[, occurrence]])
Descripción
Sinónimo de REGEXP_EXTRACT
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH example AS
(SELECT 'Hello World Helloo' AS value, 'H?ello+' AS regex, 1 AS position, 1 AS
occurrence
)
SELECT value, regex, position, occurrence, REGEXP_SUBSTR(value, regex,
position, occurrence) AS regexp_value FROM example;
+--------------------+---------+----------+------------+--------------+
| value | regex | position | occurrence | regexp_value |
+--------------------+---------+----------+------------+--------------+
| Hello World Helloo | H?ello+ | 1 | 1 | Hello |
+--------------------+---------+----------+------------+--------------+
REPLACE
REPLACE(original_value, from_value, to_value)
Descripción
Reemplaza todos los casos de from_value
por to_value
en original_value
.
Si from_value
está vacío, no se realiza ningún reemplazo.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH desserts AS
(SELECT "apple pie" as dessert
UNION ALL
SELECT "blackberry pie" as dessert
UNION ALL
SELECT "cherry pie" as dessert)
SELECT
REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;
+--------------------+
| example |
+--------------------+
| apple cobbler |
| blackberry cobbler |
| cherry cobbler |
+--------------------+
REPEAT
REPEAT(original_value, repetitions)
Descripción
Muestra un valor STRING
o BYTES
que consta de original_value
, repetido.
El parámetro repetitions
especifica la cantidad de veces que se debe repetir original_value
. Muestra NULL
si original_value
o repetitions
son NULL
.
Esta función muestra un error si el valor de repetitions
es negativo.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
STRUCT('abc' AS t, 3 AS n),
('例子', 2),
('abc', null),
(null, 3)
]);
+------+------+-----------+
| t | n | REPEAT |
|------|------|-----------|
| abc | 3 | abcabcabc |
| 例子 | 2 | 例子例子 |
| abc | NULL | NULL |
| NULL | 3 | NULL |
+------+------+-----------+
REVERSE
REVERSE(value)
Descripción
Muestra la inversa de STRING
o BYTES
de entrada.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH example AS (
SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
sample_string,
REVERSE(sample_string) AS reverse_string,
sample_bytes,
REVERSE(sample_bytes) AS reverse_bytes
FROM example;
+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo | oof | bar | rab |
| абвгд | дгвба | 123 | 321 |
+---------------+----------------+--------------+---------------+
RIGHT
RIGHT(value, length)
Descripción
Muestra un valor STRING
o BYTES
que consiste en la cantidad especificada de caracteres o bytes más a la derecha de value
. length
es un INT64
que especifica la longitud del valor que se muestra. Si value
es BYTES
, length
es la cantidad de bytes que se muestran más a la derecha. Si value
es STRING
, length
es la cantidad de caracteres más a la derecha que se muestran.
Si la length
es 0, se mostrará un valor de STRING
o BYTES
vacío. Si la length
es negativa, se mostrará un error. Si la length
excede la cantidad de caracteres o bytes del value
, se mostrará el value
original.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;
+---------+---------------+
| example | right_example |
+---------+---------------+
| apple | ple |
| banana | ana |
| абвгд | вгд |
+---------+---------------+
WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;
-- Note that the result of RIGHT is of type BYTES, displayed as a base64-encoded string.
+----------+---------------+
| example | right_example |
+----------+---------------+
| YXBwbGU= | cGxl |
| YmFuYW5h | YW5h |
| q83vqrs= | 76q7 |
+----------+---------------+
RPAD
RPAD(original_value, return_length[, pattern])
Descripción
Muestra un valor STRING
o BYTES
que consta de original_value
con pattern
agregado. El parámetro return_length
es un INT64
que especifica la longitud del valor mostrado. Si original_value
es BYTES
, return_length
es la cantidad de bytes. Si original_value
es STRING
, return_length
es la cantidad de caracteres.
El valor predeterminado de pattern
es un espacio en blanco.
original_value
y pattern
deben tener el mismo tipo de datos.
Si return_length
es menor o igual que la longitud de original_value
, esta función muestra el valor original_value
, truncado al valor de return_length
. Por ejemplo, RPAD("hello world", 7);
muestra "hello w"
.
Si original_value
, return_length
o pattern
es NULL
, esta función muestra NULL
.
Esta función muestra un error si se da una de estas condiciones:
return_length
es negativo.pattern
está vacía.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
+------+-----+----------+
| t | len | RPAD |
|------|-----|----------|
| abc | 5 | "abc " |
| abc | 2 | "ab" |
| 例子 | 4 | "例子 " |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
+------+-----+---------+--------------+
| t | len | pattern | RPAD |
|------|-----|---------|--------------|
| abc | 8 | def | "abcdefde" |
| abc | 5 | - | "abc--" |
| 例子 | 5 | 中文 | "例子中文中" |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
+-----------------+-----+------------------+
| t | len | RPAD |
|-----------------|-----|------------------|
| b"abc" | 5 | b"abc " |
| b"abc" | 2 | b"ab" |
| b"\xab\xcd\xef" | 4 | b"\xab\xcd\xef " |
+-----------------+-----+------------------+
SELECT
FORMAT("%T", t) AS t,
len,
FORMAT("%T", pattern) AS pattern,
FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
+-----------------+-----+---------+-------------------------+
| t | len | pattern | RPAD |
|-----------------|-----|---------|-------------------------|
| b"abc" | 8 | b"def" | b"abcdefde" |
| b"abc" | 5 | b"-" | b"abc--" |
| b"\xab\xcd\xef" | 5 | b"\x00" | b"\xab\xcd\xef\x00\x00" |
+-----------------+-----+---------+-------------------------+
RTRIM
RTRIM(value1[, value2])
Descripción
Es idéntico a TRIM, pero solo quita caracteres finales.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
RTRIM(item, "*") as example
FROM items;
+-----------+
| example |
+-----------+
| ***apple |
| ***banana |
| ***orange |
+-----------+
WITH items AS
(SELECT "applexxx" as item
UNION ALL
SELECT "bananayyy" as item
UNION ALL
SELECT "orangezzz" as item
UNION ALL
SELECT "pearxyz" as item)
SELECT
RTRIM(item, "xyz") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
SAFE_CONVERT_BYTES_TO_STRING
SAFE_CONVERT_BYTES_TO_STRING(value)
Descripción
Convierte una secuencia de BYTES
en una STRING
. Los caracteres UTF-8 no válidos se reemplazan por el carácter de reemplazo de Unicode, U+FFFD
.
Tipo de datos que se muestra
STRING
Ejemplos
La siguiente instrucción muestra el carácter de reemplazo de Unicode, �.
SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;
SOUNDEX
SOUNDEX(value)
Descripción
Muestra una STRING
que representa el código de Soundex del value
.
SOUNDEX produce una representación fonética de una string. Indexa palabras por sonido, como se pronuncia en inglés. Por lo general, se usa para determinar si dos strings, por ejemplo, nombres de familias, como Levine o Lavine, o las palabras to y too, tienen una pronunciación similar en inglés.
El resultado de SOUNDEX consta de una letra seguida de 3 dígitos. Los caracteres que no sean latinos se ignorarán. Si la string restante está vacía después de quitar los caracteres que no son latinos, se mostrará una STRING
vacía.
Tipo de datos que se muestra
STRING
Ejemplos
WITH example AS (
SELECT 'Ashcraft' AS value UNION ALL
SELECT 'Raven' AS value UNION ALL
SELECT 'Ribbon' AS value UNION ALL
SELECT 'apple' AS value UNION ALL
SELECT 'Hello world!' AS value UNION ALL
SELECT ' H3##!@llo w00orld!' AS value UNION ALL
SELECT '#1' AS value UNION ALL
SELECT NULL AS value
)
SELECT value, SOUNDEX(value) AS soundex
FROM example;
+----------------------+---------+
| value | soundex |
+----------------------+---------+
| Ashcraft | A261 |
| Raven | R150 |
| Ribbon | R150 |
| apple | a140 |
| Hello world! | H464 |
| H3##!@llo w00orld! | H464 |
| #1 | |
| NULL | NULL |
+----------------------+---------+
SPLIT
SPLIT(value[, delimiter])
Descripción
Divide value
mediante el argumento delimiter
.
Para STRING
, el delimitador predeterminado es la coma ,
.
Para BYTES
, debes especificar un delimitador.
La división en un delimitador vacío produce un arreglo de caracteres UTF-8 para los valores de STRING
y un arreglo de BYTES
para los valores de BYTES
.
Dividir una STRING
vacía muestra un ARRAY
con una única STRING
vacía.
Tipo de datos que se muestra
ARRAY
de tipo STRING
o ARRAY
de tipo BYTES
Ejemplos
WITH letters AS
(SELECT "" as letter_group
UNION ALL
SELECT "a" as letter_group
UNION ALL
SELECT "b c d" as letter_group)
SELECT SPLIT(letter_group, " ") as example
FROM letters;
+----------------------+
| example |
+----------------------+
| [] |
| [a] |
| [b, c, d] |
+----------------------+
STARTS_WITH
STARTS_WITH(value1, value2)
Descripción
Toma dos valores STRING
o BYTES
. Muestra TRUE
si el segundo valor es un prefijo del primero.
Tipo de datos que se muestra
BOOL
Ejemplos
WITH items AS
(SELECT "foo" as item
UNION ALL
SELECT "bar" as item
UNION ALL
SELECT "baz" as item)
SELECT
STARTS_WITH(item, "b") as example
FROM items;
+---------+
| example |
+---------+
| False |
| True |
| True |
+---------+
STRPOS
STRPOS(string, substring)
Descripción
Muestra el índice basado en 1 del primer caso de substring
dentro de string
. Muestra 0
si no se encuentra substring
.
Tipo de datos que se muestra
INT64
Ejemplos
WITH email_addresses AS
(SELECT
"foo@example.com" AS email_address
UNION ALL
SELECT
"foobar@example.com" AS email_address
UNION ALL
SELECT
"foobarbaz@example.com" AS email_address
UNION ALL
SELECT
"quxexample.com" AS email_address)
SELECT
STRPOS(email_address, "@") AS example
FROM email_addresses;
+---------+
| example |
+---------+
| 4 |
| 7 |
| 10 |
| 0 |
+---------+
SUBSTR
SUBSTR(value, position[, length])
Descripción
Muestra una substring del valor STRING
o BYTES
proporcionado. El argumento position
es un número entero que especifica la posición inicial de la substring, en el que position = 1 indica el primer carácter o byte. El argumento length
es la cantidad máxima de caracteres para los argumentos STRING
o bytes para los argumentos BYTES
.
Si position
es negativo, la función cuenta desde el final de value
, y -1 indica el último carácter.
Si position
es una posición más allá del extremo izquierdo de la STRING
(position
= 0 o position
< -LENGTH(value)
), la función comienza desde position = 1. Si length
excede la longitud de value
, la función muestra menos caracteres que length
.
Si length
es menor que 0, la función muestra un error.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2) as example
FROM items;
+---------+
| example |
+---------+
| pple |
| anana |
| range |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2, 2) as example
FROM items;
+---------+
| example |
+---------+
| pp |
| an |
| ra |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, -2) as example
FROM items;
+---------+
| example |
+---------+
| le |
| na |
| ge |
+---------+
SUBSTRING
SUBSTRING(value, position[, length])
Alias de SUBSTR
.
TO_BASE32
TO_BASE32(bytes_expr)
Descripción
Convierte una secuencia de BYTES
en una STRING
codificada en base32. Para convertir una STRING
codificada en base32 en BYTES
, usa FROM_BASE32.
Tipo de datos que se muestra
STRING
Ejemplo
SELECT TO_BASE32(b'abcde\xFF') AS base32_string;
+------------------+
| base32_string |
+------------------+
| MFRGGZDF74====== |
+------------------+
TO_BASE64
TO_BASE64(bytes_expr)
Descripción
Convierte una secuencia de BYTES
en una STRING
codificada en base64. Para convertir una STRING
codificada en base64 en BYTES
, usa FROM_BASE64.
Tipo de datos que se muestra
STRING
Ejemplo
SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;
+---------------+
| base64_string |
+---------------+
| 3q2+7w== |
+---------------+
TO_CODE_POINTS
TO_CODE_POINTS(value)
Descripción
Toma un valor y muestra un arreglo de INT64
.
- Si
value
es unaSTRING
, cada elemento del arreglo que se muestra representa un punto de código. Cada punto de código está dentro del rango entre [0, 0xD7FF] y [0xE000, 0x10FFFF]. - Si
value
esBYTES
, cada elemento del arreglo es un valor de caracteres ASCII extendido en el rango [0, 255].
Para convertir un arreglo de puntos de código en STRING
o BYTES
, consulta CODE_POINTS_TO_STRING o CODE_POINTS_TO_BYTES.
Tipo de datos que se muestra
ARRAY
de INT64
Ejemplos
En el siguiente ejemplo, se obtienen los puntos de código para cada elemento de un arreglo de palabras.
SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;
+---------+------------------------------------+
| word | code_points |
+---------+------------------------------------+
| foo | [102, 111, 111] |
| bar | [98, 97, 114] |
| baz | [98, 97, 122] |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama | [108, 108, 97, 109, 97] |
+---------+------------------------------------+
En el siguiente ejemplo, se convierten representaciones de números enteros de BYTES
en sus valores de caracteres ASCII correspondientes.
SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;
+------------------+------------------------+
| word | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255] |
| foo | [102, 111, 111] |
+------------------+------------------------+
En el siguiente ejemplo, se muestra la diferencia entre un resultado de BYTES
y un resultado de STRING
.
SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;
+------------+----------+
| b_result | s_result |
+------------+----------+
| [196, 128] | [256] |
+------------+----------+
Observa que el carácter, Ā, se representa como una secuencia Unicode de dos bytes. Como resultado, la versión BYTES
de TO_CODE_POINTS
muestra un arreglo con dos elementos, mientras que la versión STRING
muestra un arreglo con un solo elemento.
TO_HEX
TO_HEX(bytes)
Descripción
Convierte una secuencia de BYTES
en una STRING
hexadecimal. Convierte cada byte de la STRING
en dos caracteres hexadecimales dentro del rango (0..9, a..f)
. Para convertir una STRING
con codificación hexadecimal en BYTES
, usa FROM_HEX.
Tipo de datos que se muestra
STRING
Ejemplo
WITH Input AS (
SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;
+----------------------------------+------------------+
| byte_string | hex_string |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar | 666f6f626172 |
+----------------------------------+------------------+
TRANSLATE
TRANSLATE(expression, source_characters, target_characters)
Descripción
En expression
, reemplaza cada carácter de source_characters
por el carácter correspondiente en target_characters
. Todas las entradas deben ser del mismo tipo, STRING
o BYTES
.
- Cada carácter en
expression
se traduce una vez como máximo. - Un carácter en
expression
que no está presente ensource_characters
se deja sin cambios enexpression
. - Un carácter en
source_characters
sin un carácter correspondiente entarget_characters
se omite del resultado. - Un carácter duplicado en
source_characters
genera un error.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH example AS (
SELECT 'This is a cookie' AS expression, 'sco' AS source_characters, 'zku' AS
target_characters UNION ALL
SELECT 'A coaster' AS expression, 'co' AS source_characters, 'k' as
target_characters
)
SELECT expression, source_characters, target_characters, TRANSLATE(expression,
source_characters, target_characters) AS translate
FROM example;
+------------------+-------------------+-------------------+------------------+
| expression | source_characters | target_characters | translate |
+------------------+-------------------+-------------------+------------------+
| This is a cookie | sco | zku | Thiz iz a kuukie |
| A coaster | co | k | A kaster |
+------------------+-------------------+-------------------+------------------+
TRIM
TRIM(value1[, value2])
Descripción
Quita todos los caracteres iniciales y finales que coinciden con value2
. Si no se especifica value2
, se quitan todos los caracteres de espacio en blanco iniciales y finales (como se define en el estándar Unicode). Si el primer argumento es de tipo BYTES
, el segundo argumento es obligatorio.
Si value2
contiene más de un carácter o byte, la función quita todos los caracteres o bytes iniciales o finales que contiene value2
.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH items AS
(SELECT " apple " as item
UNION ALL
SELECT " banana " as item
UNION ALL
SELECT " orange " as item)
SELECT
CONCAT("#", TRIM(item), "#") as example
FROM items;
+----------+
| example |
+----------+
| #apple# |
| #banana# |
| #orange# |
+----------+
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
TRIM(item, "*") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
+---------+
WITH items AS
(SELECT "xxxapplexxx" as item
UNION ALL
SELECT "yyybananayyy" as item
UNION ALL
SELECT "zzzorangezzz" as item
UNION ALL
SELECT "xyzpearxyz" as item)
SELECT
TRIM(item, "xyz") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
UNICODE
UNICODE(value)
Descripción
Muestra el punto de código Unicode para el primer carácter en value
. Muestra 0
si value
está vacío o si el punto de código Unicode resultante es 0
.
Tipo de datos que se muestra
INT64
Ejemplos
SELECT UNICODE('âbcd') as A, UNICODE('â') as B, UNICODE('') as C, UNICODE(NULL) as D;
+-------+-------+-------+-------+
| A | B | C | D |
+-------+-------+-------+-------+
| 226 | 226 | 0 | NULL |
+-------+-------+-------+-------+
UPPER
UPPER(value)
Descripción
Para los argumentos de STRING
, muestra la string original con todos los caracteres alfabéticos en mayúsculas. La asignación entre mayúsculas y minúsculas se realiza de acuerdo con la base de datos de caracteres Unicode, sin tener en cuenta las asignaciones específicas del lenguaje.
Para los argumentos de BYTES
, el argumento se trata como texto ASCII, con todos los bytes mayores a 127 intactos.
Tipo de datos que se muestra
STRING
o BYTES
Ejemplos
WITH items AS
(SELECT
"foo" as item
UNION ALL
SELECT
"bar" as item
UNION ALL
SELECT
"baz" as item)
SELECT
UPPER(item) AS example
FROM items;
+---------+
| example |
+---------+
| FOO |
| BAR |
| BAZ |
+---------+
Funciones JSON
BigQuery admite funciones que te ayudan a recuperar datos almacenados en strings con formato JSON y funciones que te ayudan a transformar datos en strings con formato JSON.
JSON_EXTRACT o JSON_EXTRACT_SCALAR
JSON_EXTRACT(json_string_expr,
json_path_format)
, que muestra valores JSON como STRING.
JSON_EXTRACT_SCALAR(json_string_expr,
json_path_format)
, que muestra valores JSON escalares como STRING.
Descripción
Extrae valores JSON o valores escalares JSON como strings.
json_string_expr
: Una string con formato JSON. Por ejemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_format
: El formato JSONpath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Sijson_path_format
muestra unnull
de JSON, este se convierte en unNULL
de SQL.
En los casos en los que una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes.
Tipo de datos que se muestra
STRING
Ejemplos
SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
En los casos en los que una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes, [' ']
. Por ejemplo:
SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;
+-------+
| hello |
+-------+
| world |
+-------+
JSON_EXTRACT_ARRAY
JSON_EXTRACT_ARRAY(json_string_expr[, json_path_format])
Descripción
Extrae un arreglo de una string con formato JSON.
json_string_expr
: Una string con formato JSON. Por ejemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_format
: El formato JSONpath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Si no se proporciona este parámetro opcional, se aplica el símbolo$
de JSONPath, lo que significa que se analiza toda la string con formato JSON.
En los casos en los que una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes.
Tipo de datos que se muestra
ARRAY<STRING>
Ejemplos
En este ejemplo, se extraen los elementos de una string con formato JSON en un arreglo de strings:
SELECT JSON_EXTRACT_ARRAY('[1,2,3]') as string_array
+----------------+
| string_array |
+----------------+
| ['1','2','3'] |
+----------------+
En este ejemplo, se extrae un arreglo de strings y se lo convierte en un arreglo de números enteros:
SELECT ARRAY(
SELECT CAST(integer_element as INT64)
FROM UNNEST(
JSON_EXTRACT_ARRAY('[1,2,3]','$')
) AS integer_element
) AS integer_array
+---------------+
| integer_array |
+---------------+
| [1,2,3] |
+---------------+
En este ejemplo, se extraen los valores de string de una string con formato JSON en un arreglo:
-- Don't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') as string_array
+--------------------------------------+
| string_array |
+--------------------------------------+
| ['"apples"','"oranges"','"grapes"'] |
+--------------------------------------+
-- Strip the double quotes
SELECT ARRAY(
SELECT JSON_EXTRACT_SCALAR(string_element, '$')
FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array
+---------------------------------+
| string_array |
+---------------------------------+
| ['apples', 'oranges', 'grapes'] |
+---------------------------------+
En este ejemplo, se extraen solo los elementos de fruit
en un arreglo:
SELECT JSON_EXTRACT_ARRAY(
'{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}}',
'$.fruit'
) as string_array
+----------------------------------------------------------------------+
| string_array |
+----------------------------------------------------------------------+
| ['{"apples" : 5, "oranges" : 10}' , '{"apples" : 2, "oranges" : 4}'] |
+----------------------------------------------------------------------+
Estos son equivalentes:
JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') as string_array
JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') as string_array
-- The queries above produce this result:
+---------------------------------------+
| string_array |
+---------------------------------------+
| [""apples"", ""oranges"", ""grapes""] |
+---------------------------------------+
En los casos en los que una clave JSON usa caracteres JSONPath no válidos, puedes escapar esos caracteres mediante el uso de comillas simples y corchetes, [' ']
. Por ejemplo:
SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;
+---------+
| hello |
+---------+
| [world] |
+---------+
En estos ejemplos, se analiza cómo se manejan las solicitudes no válidas y los arreglos vacíos:
- Si un JSONPath no es válido, se produce un error.
- Si una string con formato JSON no es válida, el resultado es NULL.
- Se permite tener arreglos vacíos en la string con formato JSON.
-- An error is thrown if you provide an invalid JSONPath.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result
-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result
+--------+
| result |
+--------+
| NULL |
+--------+
-- Empty arrays in JSON-formatted strings are supported.
JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') as result
+--------+
| result |
+--------+
| [] |
+--------+
JSON_QUERY o JSON_VALUE
JSON_QUERY(json_string_expr, json_path_format)
, que muestra valores JSON como STRING.
JSON_VALUE(json_string_expr, json_path_format)
, que muestra valores JSON escalares como STRING.
Descripción
Extrae valores JSON o valores escalares JSON como strings.
json_string_expr
: Una string con formato JSON. Por ejemplo:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_format
: El formato JSONpath. Esto identifica el valor o los valores que deseas obtener de la string con formato JSON. Sijson_path_format
muestra unnull
de JSON, este se convierte en unNULL
de SQL.
En los casos en que una clave JSON use caracteres JSONPath no válidos, puedes escapar esos caracteres mediante comillas dobles.
Ejemplos
SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
En los casos en que una clave JSON use caracteres JSONPath no válidos, puedes escapar esos caracteres mediante comillas dobles. Por ejemplo:
SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;
+-------+
| hello |
+-------+
| world |
+-------+
TO_JSON_STRING
TO_JSON_STRING(value[, pretty_print])
Descripción
Muestra una representación de una string con formato JSON de value
. Esta función admite un parámetro booleano opcional llamado pretty_print
. Si pretty_print
es true
, el valor que se muestre tendrá un formato para una lectura fácil.
Tipo de datos de entrada | Valor mostrado |
---|---|
NULL de cualquier tipo | null |
BOOL | true o false . |
INT64 | Lo mismo que -1 0 12345678901 9007199254740992 -9007199254740992 "9007199254740993"
|
NUMERIC | Igual que -1 0 "9007199254740993" "123.56"
|
FLOAT64 | +/-inf y NaN se representan como Infinity , -Infinity y NaN , respectivamente.De lo contrario, es lo mismo que |
STRING | Valor de string entre comillas, evitado según el estándar JSON.
En específico, se escapan " , \ y los caracteres de control de U+0000 a U+001F . |
BYTES | Valor evitado RFC 4648 de base64. Por ejemplo:
|
DATE | Fecha entre comillas. Por ejemplo: "2017-03-06"
|
TIMESTAMP | Fecha y hora ISO 8601 entre comillas, donde T separa la fecha y hora y Zulu/UTC representa la zona horaria. Por ejemplo: "2017-03-06T12:34:56.789012Z"
|
DATETIME | Fecha y hora ISO 8601 entre comillas, donde T separa la fecha y hora. Por ejemplo: "2017-03-06T12:34:56.789012"
|
TIME | Hora ISO 8601 entre comillas. Por ejemplo: "12:34:56.789012" |
ARRAY |
Arreglo de cero o más elementos. Cada elemento tiene un formato según su tipo. Ejemplo sin formato: ["red", "blue", "green"] Ejemplo con formato: [ "red", "blue", "green" ] |
STRUCT |
Un objeto que contiene cero o más pares clave-valor. Cada valor tiene un formato según su tipo. Ejemplo sin formato: {"colors":["red","blue"],"purchases":12,"inStock": true} Ejemplo con formato: { "color":[ "red", "blue" ] "purchases":12, "inStock": true }
Los campos con nombres duplicados pueden dar como resultado JSON no analizable. Los campos anónimos se representan con
Los nombres de campo UTF-8 no válidos pueden dar como resultado JSON no analizable. Los valores de string se escapan según el estándar JSON. En específico, se escapan |
Tipo de datos que se muestra
Representación de la string JSON del valor.
Ejemplos
Convierte las filas en una tabla a JSON.
With CoordinatesTable AS (
(SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
(SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
(SELECT 3 AS id, [50,60] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t) AS json_data
FROM CoordinatesTable as t;
+--------+-------------+--------------------------------+
| id | coordinates | json_data |
+--------+-------------+--------------------------------+
| 1 | [10,20] | {"id":1,"coordinates":[10,20]} |
| 2 | [30,40] | {"id":2,"coordinates":[30,40]} |
| 3 | [50,60] | {"id":3,"coordinates":[50,60]} |
+--------+-------------+--------------------------------+
Convierte las filas en una tabla a JSON con formato.
With CoordinatesTable AS (
(SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
(SELECT 2 AS id, [30,40] AS coordinates)
SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data
FROM CoordinatesTable as t;
+--------+-------------+---------------------+
| id | coordinates | json_data |
+--------+-------------+---------------------+
| 1 | [10,20] | { |
| | | "id":1, |
| | | "coordinates":[ |
| | | 10, |
| | | 20 |
| | | ] |
| | | } |
| 2 | [30,40] | { |
| | | "id":2, |
| | | "coordinates":[ |
| | | 30, |
| | | 40 |
| | | ] |
| | | } |
+--------+-------------+---------------------+
Formato JSONPath
La mayoría de las funciones JSON pasan en un parámetro json_string_expr
y json_path_format
. El parámetro json_string_expr
pasa una string con formato JSON y el parámetro json_path_format
identifica el valor o los valores que deseas obtener de la string con formato JSON.
El parámetro json_string_expr
debe ser una string JSON con el siguiente formato:
{"class" : {"students" : [{"name" : "Jane"}]}}
Crea el parámetro json_path_format
mediante el formato JSONPath. Como parte de este formato, este parámetro debe comenzar con un símbolo $
, que hace referencia al nivel más externo de la string con formato JSON. Puedes identificar los valores secundarios mediante puntos. Si el objeto JSON es un arreglo, puedes usar corchetes para especificar el índice del arreglo. Si las claves contienen $
, puntos o corchetes, consulta cada función JSON para saber cómo escaparlas.
JSONPath | Descripción | Ejemplo | Resultado mediante el json_string_expr anterior |
---|---|---|---|
$ | Objeto o elemento raíz | “$” | {"class":{"students":[{"name":"Jane"}]}} |
. | Operador secundario | “$.class.students” | [{"name":"Jane"}] |
[] | Operador subíndice | “$.class.students[0]” | {"name":"Jane"} |
Una función JSON muestra NULL
si el parámetro json_path_format
no coincide con un valor en json_string_expr
. Si el valor seleccionado para una función escalar no es escalar, como un objeto o un arreglo, la función muestra NULL
.
Si JSONPath no es válido, la función genera un error.
Funciones de arreglo
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_1 [, array_expression_n])
Descripción
Concatena uno o más arreglos con el mismo tipo de elemento en un solo arreglo.
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
- 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] |
+--------------------------------------------------------------------------+
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 |
+----------------------------------+---------------+----------------+
Funciones de fecha
BigQuery admite las siguientes funciones DATE
.
CURRENT_DATE
CURRENT_DATE([time_zone])
Descripción
Muestra la fecha actual de la zona horaria especificada o predeterminada.
Esta función admite un parámetro time_zone
opcional. Este parámetro es una string que representa la zona horaria que se debe usar. Si no se especifica una zona horaria, se usa la predeterminada, UTC. Consulta las Definiciones de la zona horaria para obtener información sobre cómo especificar una zona horaria.
Si el parámetro time_zone
se evalúa como NULL
, esta función muestra NULL
.
Tipo de datos mostrados
DATE
Ejemplo
SELECT CURRENT_DATE() as the_date;
+--------------+
| the_date |
+--------------+
| 2016-12-25 |
+--------------+
EXTRACT
EXTRACT(part FROM date_expression)
Descripción
Muestra el valor correspondiente a la parte de fecha especificada. El valor part
debe ser uno de los siguientes:
DAYOFWEEK
: muestra valores en el rango [1,7] con el domingo como primer día de la semana.DAY
DAYOFYEAR
WEEK
: muestra el número de semana de la fecha en el rango [0, 53]. Las semanas comienzan con el domingo y las fechas anteriores al primer domingo del año están en la semana 0.WEEK(<WEEKDAY>)
: muestra el número de semana de la fecha en el rango [0, 53]. Las semanas comienzan elWEEKDAY
. Las fechas anteriores al primerWEEKDAY
del año están en la semana 0. Los valores válidos paraWEEKDAY
sonSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
ySATURDAY
.ISOWEEK
: muestra el número de semana ISO 8601 dedate_expression
. Los valoresISOWEEK
comienzan el lunes. Los valores de muestra están en el rango [1, 53]. El primer valorISOWEEK
de cada año ISO comienza el lunes anterior al primer jueves del año del calendario gregoriano.MONTH
QUARTER
: muestra valores en el rango [1,4].YEAR
ISOYEAR
: muestra el año de numeración de la semana ISO 8601, que es el año calendario gregoriano que contiene el jueves de la semana a la que pertenecedate_expression
.
Tipo de datos mostrados
INT64
Ejemplos
En el siguiente ejemplo, EXTRACT
muestra un valor que corresponde a la parte DAY
de la fecha.
SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;
+---------+
| the_day |
+---------+
| 25 |
+---------+
En el siguiente ejemplo, EXTRACT
muestra valores que corresponden a diferentes partes de la fecha de una columna de fechas cerca del final del año.
SELECT
date,
EXTRACT(ISOYEAR FROM date) AS isoyear,
EXTRACT(ISOWEEK FROM date) AS isoweek,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015 | 52 | 2015 | 51 |
| 2015-12-24 | 2015 | 52 | 2015 | 51 |
| 2015-12-25 | 2015 | 52 | 2015 | 51 |
| 2015-12-26 | 2015 | 52 | 2015 | 51 |
| 2015-12-27 | 2015 | 52 | 2015 | 52 |
| 2015-12-28 | 2015 | 53 | 2015 | 52 |
| 2015-12-29 | 2015 | 53 | 2015 | 52 |
| 2015-12-30 | 2015 | 53 | 2015 | 52 |
| 2015-12-31 | 2015 | 53 | 2015 | 52 |
| 2016-01-01 | 2015 | 53 | 2016 | 0 |
| 2016-01-02 | 2015 | 53 | 2016 | 0 |
| 2016-01-03 | 2015 | 53 | 2016 | 1 |
| 2016-01-04 | 2016 | 1 | 2016 | 1 |
| 2016-01-05 | 2016 | 1 | 2016 | 1 |
| 2016-01-06 | 2016 | 1 | 2016 | 1 |
| 2016-01-07 | 2016 | 1 | 2016 | 1 |
| 2016-01-08 | 2016 | 1 | 2016 | 1 |
| 2016-01-09 | 2016 | 1 | 2016 | 1 |
+------------+---------+---------+------+------+
En el siguiente ejemplo, date_expression
es un domingo. EXTRACT
calcula la primera columna mediante las semanas que comienzan los días domingo y calcula la segunda columna con las semanas que comienzan los días lunes.
WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
date,
EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;
+------------+-------------+-------------+
| date | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45 | 44 |
+------------+-------------+-------------+
DATE
1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])
3. DATE(datetime_expression)
Descripción
- Construye un valor DATE a partir de los valores INT64 que representan el año, mes y día.
- Extrae la DATE de una expresión de TIMESTAMP. Admite un parámetro opcional para especificar una zona horaria. Si no se especifica una zona horaria, se usa la predeterminada, UTC.
- Extrae el valor de DATE de una expresión DATETIME.
Tipo de datos mostrados
DATE
Ejemplo
SELECT
DATE(2016, 12, 25) as date_ymd,
DATE(DATETIME "2016-12-25 23:59:59") as date_dt,
DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;
+------------+------------+------------+
| date_ymd | date_dt | date_tstz |
+------------+------------+------------+
| 2016-12-25 | 2016-12-25 | 2016-12-24 |
+------------+------------+------------+
DATE_ADD
DATE_ADD(date_expression, INTERVAL int64_expression date_part)
Descripción
Agrega un intervalo de tiempo especificado a un valor DATE.
DATE_ADD
admite los siguientes valores date_part
:
DAY
WEEK
, que equivale a 7DAY
MONTH
QUARTER
YEAR
Se requiere un manejo especial para las partes MONTH, QUARTER y YEAR cuando la fecha es el último día del mes o está cerca de este. Si el mes del resultado tiene menos días que el de la fecha original, el día del resultado será el último día del nuevo mes.
Tipo de datos mostrados
DATE
Ejemplo
SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;
+--------------------+
| five_days_later |
+--------------------+
| 2008-12-30 |
+--------------------+
DATE_SUB
DATE_SUB(date_expression, INTERVAL int64_expression date_part)
Descripción
Resta un intervalo de tiempo especificado a un valor DATE.
DATE_SUB
admite los siguientes valores date_part
:
DAY
WEEK
, que equivale a 7DAY
MONTH
QUARTER
YEAR
Se requiere un manejo especial para las partes MONTH, QUARTER y YEAR cuando la fecha es el último día del mes o está cerca de este. Si el mes del resultado tiene menos días que el de la fecha original, el día del resultado será el último día del nuevo mes.
Tipo de datos mostrados
DATE
Ejemplo
SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;
+---------------+
| five_days_ago |
+---------------+
| 2008-12-20 |
+---------------+
DATE_DIFF
DATE_DIFF(date_expression_a, date_expression_b, date_part)
Descripción
Muestra la cantidad de intervalos date_part
completos especificados entre dos objetos DATE
(date_expression_a
y date_expression_b
). Si el primer DATE
es anterior al segundo, el resultado es negativo.
DATE_DIFF
admite los siguientes valores date_part
:
DAY
WEEK
: esta parte de fecha comienza el domingo.WEEK(<WEEKDAY>)
: Esta parte de fecha comienza elWEEKDAY
. Los valores válidos paraWEEKDAY
sonSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
ySATURDAY
.ISOWEEK
: Usa los límites de la semana ISO 8601. Las semanas ISO comienzan el lunes.MONTH
QUARTER
YEAR
ISOYEAR
: Usa el límite del año de numeración de semana ISO 8601. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año calendario gregoriano correspondiente.
Tipo de datos mostrados
INT64
Ejemplo
SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;
+-----------+
| days_diff |
+-----------+
| 559 |
+-----------+
SELECT
DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;
+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1 | 1 |
+-----------+------------+
En el ejemplo anterior, se muestra el resultado de DATE_DIFF
durante dos días seguidos.
DATE_DIFF
con la parte de fecha WEEK
muestra 1 porque DATE_DIFF
cuenta el número de límites de parte de fecha en este rango de fechas. Cada WEEK
comienza el domingo, por lo que hay un límite de parte de fecha entre el sábado, 2017-10-14, y el domingo, 2017-10-15.
En el siguiente ejemplo, se muestra el resultado de DATE_DIFF
para dos fechas en años diferentes. DATE_DIFF
con la parte de fecha YEAR
muestra 3 porque cuenta el número de límites del año calendario gregoriano entre las dos fechas. DATE_DIFF
con la parte de fecha ISOYEAR
muestra 2 porque la segunda fecha pertenece al año ISO 2015. El primer jueves del año calendario 2015 fue 2015-01-01, por lo que el año ISO 2015 comienza el lunes anterior, 2014-12-29.
SELECT
DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;
+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3 | 2 |
+-----------+--------------+
En el siguiente ejemplo, se muestra el resultado de DATE_DIFF
para dos días seguidos. La primera fecha es un lunes y la segunda es un domingo. DATE_DIFF
con la parte WEEK
de la fecha muestra 0 porque esta parte de la fecha usa semanas que comienzan el domingo. DATE_DIFF
con la parte de fecha WEEK(MONDAY)
muestra 1. DATE_DIFF
con la parte de fecha ISOWEEK
también muestra 1 porque las semanas ISO comienzan el lunes.
SELECT
DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
DATE_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;
+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0 | 1 | 1 |
+-----------+-------------------+--------------+
DATE_TRUNC
DATE_TRUNC(date_expression, date_part)
Descripción
Trunca la fecha al nivel de detalle especificado.
DATE_TRUNC
admite los siguientes valores para date_part
:
DAY
WEEK
WEEK(<WEEKDAY>)
: Truncadate_expression
al límite de la semana anterior, en el que las semanas comienzan enWEEKDAY
. Los valores válidos paraWEEKDAY
sonSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
ySATURDAY
.ISOWEEK
: truncadate_expression
al límite de la semana ISO 8601 anterior. Los valoresISOWEEK
comienzan el lunes. El primerISOWEEK
de cada año ISO contiene el primer jueves del año calendario gregoriano correspondiente. Cualquier valordate_expression
anterior a esto se truncará al lunes anterior.MONTH
QUARTER
YEAR
ISOYEAR
: truncadate_expression
al límite de año de numeración de la semana ISO 8601. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año calendario gregoriano correspondiente.
Tipo de datos mostrados
DATE
Ejemplos
SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;
+------------+
| month |
+------------+
| 2008-12-01 |
+------------+
En el siguiente ejemplo, la fecha original es un domingo. Debido a que date_part
es WEEK(MONDAY)
, DATE_TRUNC
muestra DATE
para el lunes anterior.
SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);
+------------+------------+
| original | truncated |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+
En el siguiente ejemplo, el valor date_expression
original está en el año calendario gregoriano 2015. Sin embargo, DATE_TRUNC
con la parte de fecha ISOYEAR
trunca el valor date_expression
al comienzo del año ISO, no el año calendario gregoriano. El primer jueves del año calendario 2015 fue 2015-01-01, por lo que el año ISO 2015 comienza el lunes anterior, 2014-12-29.
Por lo tanto, el límite del año ISO que precede al valor date_expression
2015-06-15 es 2014-12-29.
SELECT
DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;
+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29 | 2015 |
+------------------+----------------+
DATE_FROM_UNIX_DATE
DATE_FROM_UNIX_DATE(int64_expression)
Descripción
Interpreta int64_expression
como el número de días desde 1970-01-01.
Tipo de datos mostrados
DATE
Ejemplo
SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;
+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25 |
+-----------------+
FORMAT_DATE
FORMAT_DATE(format_string, date_expr)
Descripción
Da formato a date_expr
de acuerdo con el format_string
especificado.
Consulta la sección sobre elementos de formato admitidos para DATE a fin de obtener una lista de los elementos de formato que admite esta función.
Tipo de datos mostrados
STRING
Ejemplos
SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;
+------------+
| US_format |
+------------+
| 12/25/08 |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec 2008 |
+-------------+
LAST_DAY
LAST_DAY(date_expression[, date_part])
Descripción
Muestra el último día de una expresión de fecha. Por lo general, se usa para mostrar el último día del mes.
De forma opcional, puedes especificar la parte de fecha para la que se muestra el último día.
Si no se usa este parámetro, el valor predeterminado es MONTH
.
LAST_DAY
admite los siguientes valores para date_part
:
YEAR
QUARTER
MONTH
WEEK
, que equivale a 7DAY
.WEEK(<WEEKDAY>)
.<WEEKDAY>
representa el día de inicio de la semana. Los valores válidos sonSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
ySATURDAY
.ISOWEEK
. Usa los límites de la semana ISO 8601. Las semanas ISO comienzan el lunes.ISOYEAR
. Usa el límite de año de numeración de semanas ISO 8601. El límite del año ISO es el lunes de la primera semana cuyo jueves pertenece al año calendario gregoriano correspondiente.
Tipo de datos mostrados
DATE
Ejemplo
Ambas opciones muestran el último día del mes:
SELECT LAST_DAY(DATE '2008-11-25', MONTH) AS last_day
+------------+
| last_day |
+------------+
| 2008-11-30 |
+------------+
SELECT LAST_DAY(DATE '2008-11-25') AS last_day
+------------+
| last_day |
+------------+
| 2008-11-30 |
+------------+
Esto muestra el último día del año:
SELECT LAST_DAY(DATE '2008-11-25', YEAR) AS last_day
+------------+
| last_day |
+------------+
| 2008-12-31 |
+------------+
Esto muestra el último día de la semana de una semana que comienza el domingo:
SELECT LAST_DAY(DATE '2008-11-10', WEEK(SUNDAY)) AS last_day
+------------+
| last_day |
+------------+
| 2008-11-15 |
+------------+
Esto muestra el último día de la semana de una semana que comienza el lunes:
SELECT LAST_DAY(DATE '2008-11-10', WEEK(MONDAY)) AS last_day
+------------+
| last_day |
+------------+
| 2008-11-16 |
+------------+
PARSE_DATE
PARSE_DATE(format_string, date_string)
Descripción
Convierte una representación de string de fecha en un objeto DATE
.
format_string
contiene los elementos de formato que definen cómo se da formato a date_string
. Cada elemento en date_string
debe tener un elemento correspondiente en format_string
. La ubicación de cada elemento en format_string
debe coincidir con la ubicación de cada elemento en date_string
.
-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")
-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")
-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")
-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")
La string de formato admite por completo la mayoría de los elementos de formato, excepto %Q
, %a
, %A
, %g
, %G
, %j
, %u
, %U
, %V
, %w
y %W
.
Cuando uses PARSE_DATE
, ten en cuenta lo siguiente:
- Campos sin especificar. Todo campo sin especificar se inicializa desde
1970-01-01
. - Nombres sin distinción entre mayúsculas y minúsculas. Los nombres, como
Monday
,February
, etc., no distinguen entre mayúsculas y minúsculas. - Espacio en blanco. Uno o más espacios en blanco consecutivos en la string que coinciden con cero o más espacios en blanco consecutivos en la string de fecha. Además, los espacios en blanco iniciales y finales en la string de fecha siempre están permitidos, incluso si no están en la string de formato.
- Precedencia del formato. Cuando dos (o más) elementos de formato tienen información superpuesta (por ejemplo, si
%F
y%Y
afectan el año), por lo general, el último anula los anteriores.
Tipo de datos mostrados
DATE
Ejemplos
En este ejemplo, se convierte una string con formato MM/DD/YY
en un objeto DATE
:
SELECT PARSE_DATE("%x", "12/25/08") as parsed;
+------------+
| parsed |
+------------+
| 2008-12-25 |
+------------+
En este ejemplo, se convierte una string con formato YYYYMMDD
en un objeto DATE
:
SELECT PARSE_DATE("%Y%m%d", "20081225") as parsed;
+------------+
| parsed |
+------------+
| 2008-12-25 |
+------------+
UNIX_DATE
UNIX_DATE(date_expression)
Descripción
Muestra el número de días desde 1970-01-01.
Tipo de datos mostrados
INT64
Ejemplo
SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;
+-----------------+
| days_from_epoch |
+-----------------+
| 14238 |
+-----------------+
Elementos de formato admitidos para DATE
A menos que se indique lo contrario, las funciones de DATE que usan strings de formato admiten los siguientes elementos:
Elemento de formato | Descripción |
%A | El nombre completo del día de la semana |
%a | El nombre abreviado del día de la semana |
%B | El nombre completo del mes |
%b o %h | El nombre del mes abreviado |
%C | El siglo (un año dividido por 100 y truncado a un número entero) como un número decimal (00-99) |
%D | La fecha en el formato %m/%d/%y |
%d | El día del mes como número decimal (01-31) |
%e | El día del mes como número decimal (1-31); los dígitos individuales están precedidos por un espacio |
%F | La fecha en el formato %Y-%m-%d |
%G | El año ISO 8601 con siglo como número decimal. Cada año ISO comienza el lunes anterior al primer jueves del año calendario gregoriano. Ten en cuenta que %G y %Y pueden producir resultados diferentes cerca de los límites del año gregoriano, en donde el año gregoriano y el año ISO pueden divergir. |
%g | El año ISO 8601 sin el siglo como número decimal (00-99). Cada año ISO comienza el lunes anterior al primer jueves del año calendario gregoriano. Ten en cuenta que %y y %g pueden producir resultados diferentes cerca de los límites del año gregoriano, en los que el año gregoriano y el año ISO pueden divergir. |
%j | El día del año como número decimal (001-366). |
%m | El mes como número decimal (01-12) |
%n | Un carácter de salto de línea. |
%Q | El trimestre como un número decimal (1-4). |
%t | Un tabulador |
%U | El número de la semana del año (con el domingo como primer día de la semana) como número decimal (00-53) |
%u | El día de la semana (lunes como primer día de la semana) como número decimal (1-7) |
%V | El número de semana |