Expresiones, funciones y operadores en SQL estándar

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 es NULL, 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: Los dos STRUCT tienen la misma cantidad de campos. Los tipos de campo STRUCT original se pueden transmitir a los tipos de campos STRUCT de destino correspondientes (según se define por orden y no nombre de campo).

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 DATE
• Funciones DATETIME
• Funciones TIME
• Funciones TIMESTAMP

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:

1. 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 de ARRAY_AGG().
2. DISTINCT: cada valor distinto de expression se agrega solo una vez en el resultado.
3. IGNORE NULLS o RESPECT NULLS: si se especifica IGNORE NULLS, los valores NULL se excluyen del resultado. Si RESPECT NULLS se especifica o si no se especifica ninguno, los valores NULL se incluyen en el resultado. Se genera un error si un arreglo en el resultado final de la consulta contiene un elemento NULL.
4. 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 en ASC y últimos en DESC.
   • 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 que expression.
   • 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.
5. LIMIT: especifica la cantidad máxima de entradas expression en el resultado. El límite n 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:

1. 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 en ASC y últimos en DESC.
   • 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.
2. LIMIT: especifica la cantidad máxima de entradas expression 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ímite n 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:

1. 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 de AVG().
2. DISTINCT: Cada valor distinto de expression 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

1. Muestra la cantidad de filas en la entrada.
2. Muestra la cantidad de filas con expression evaluado como cualquier valor distinto de NULL.

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:

1. OVER: especifica una ventana. Consulta Conceptos de funciones analíticas.
2. DISTINCT: Cada valor distinto de expression 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:

1. 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 de STRING_AGG().
2. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
3. 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 en ASC y últimos en DESC.
   • 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 que expression.
   • 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.
4. LIMIT: especifica la cantidad máxima de entradas expression 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ímite n 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:

1. OVER: especifica una ventana. Consulta Conceptos de funciones analíticas.
2. DISTINCT: Cada valor distinto de expression 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:

1. 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 de STDDEV_POP().
2. DISTINCT: Cada valor distinto de expression 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:

1. 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 de STDDEV_SAMP().
2. DISTINCT: Cada valor distinto de expression 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:

1. 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 de VAR_POP().
2. DISTINCT: Cada valor distinto de expression 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:

1. 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 de VAR_SAMP().
2. DISTINCT: Cada valor distinto de expression 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:

1. DISTINCT: cada valor distinto de expression se agrega solo una vez en el resultado.
2. IGNORE NULLS o RESPECT NULLS: si se especifica IGNORE NULLS, los valores NULL se excluyen del resultado. Si RESPECT NULLS se especifica o si no se especifica ninguno, los valores NULL se incluyen en el resultado. Se genera un error si un arreglo en el resultado final de la consulta contiene un elemento NULL.

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 para ROW_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 es NULL.
• Muestran NaN si alguno de los argumentos es NaN.

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, muestra NaN.

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 o NaN, muestra NULL.

  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 o NULL.

  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.

Nota: El tipo de datos de point y el tipo de elemento de boundaries_array deben ser equivalentes. El tipo de datos debe ser comparable.

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 muestra NULL.
• La interpolación entre un valor NULL y un valor no NULL muestra el valor no NULL.

Tipos de argumentos admitidos

• value_expression y percentile 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 si DISTINCT está presente.
• window_frame_clause: Es opcional. No se permite si DISTINCT 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.

Nota: También puedes usar el operador de concatenación || para concatenar valores en una string.

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.
#	Para “%o”, “%x” y “%X”, esta marca significa que se debe anteponer 0, 0x o 0X a los valores distintos de cero, de forma respectiva. Para “%f”, “%F”, “%e” y “%E”, esta marca significa que se debe agregar el punto decimal, incluso cuando no hay una parte fraccionaria, a menos que el valor sea no finito. En el caso de “%g” y “%G”, esta marca significa que se debe agregar el punto decimal, incluso cuando no hay una parte fraccionaria, a menos que el valor sea no finito, y que nunca se deben quitar los ceros finales después del punto decimal.
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: FORMAT("%'d", 12345678) muestra 12,345,678 FORMAT("%'x", 12345678) muestra bc:614e FORMAT("%'o", 55555) muestra 15,4403 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>	Para especificadores de números enteros “%d”, “%i”, “%o”, “%u”, “%x” y “%X”: la precisión especifica la cantidad mínima de dígitos que se escribirán. Si el valor que se debe escribir es más corto que este número, el resultado se rellena con ceros finales. El valor no se trunca aunque el resultado sea más largo. Una precisión de 0 significa que no se escribe ningún carácter para el valor 0. Para los especificadores “%a”, “%A”, “%e”, “%E”, “%f” y “%F”: esta es la cantidad de dígitos que se imprimirán después del punto decimal. El valor predeterminado es 6. Para los especificadores “%g” y “%G”: esta es la cantidad de dígitos significativos que se imprimirán antes de quitar los ceros finales después del punto decimal. El valor predeterminado es 6.
.*	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 STRINGs 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 $.

Nota: BigQuery proporciona compatibilidad con expresiones regulares mediante la biblioteca re2; consulta esa documentación para obtener su sintaxis de expresión regular.

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
    ["[e-mail]", "[e-mail]", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| [e-mail] | true     |
| [e-mail] | 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
    ["[e-mail]", "[e-mail]", "[e-mail]", "![e-mail]", "[e-mail]"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| [e-mail]      | true                | true                |
| [e-mail] | false               | true                |
| [e-mail]      | true                | true                |
| ![e-mail]     | false               | true                |
| [e-mail]      | 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.

Nota: BigQuery proporciona compatibilidad con expresiones regulares mediante la biblioteca re2; consulta esa documentación para obtener su sintaxis de expresión regular.

Tipo de datos que se muestra

STRING o BYTES

Ejemplos

WITH email_addresses AS
  (SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" 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 "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" 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.

Nota: BigQuery proporciona compatibilidad con expresiones regulares mediante la biblioteca re2; consulta esa documentación para obtener su sintaxis de expresión regular.

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 '[e-mail]' AS source_value, '@[^.]*' AS regexp UNION ALL
  SELECT '[e-mail]', '@[^.]*' UNION ALL
  SELECT '[e-mail]', '@[^.]*' UNION ALL
  SELECT 'abc.com', '@[^.]*')
SELECT source_value, regexp, REGEXP_INSTR(source_value, regexp) AS instr
FROM example;

+---------------+--------+-------+
| source_value  | regexp | instr |
+---------------+--------+-------+
| [e-mail]  | @[^.]* | 3     |
| [e-mail]   | @[^.]* | 3     |
| [e-mail] | @[^.]* | 4     |
| abc.com       | @[^.]* | 0     |
+---------------+--------+-------+

WITH example AS (
  SELECT '[e-mail] [e-mail]' AS source_value, '@[^.]*' AS regexp, 1 AS position UNION ALL
  SELECT '[e-mail] [e-mail]', '@[^.]*', 2 UNION ALL
  SELECT '[e-mail] [e-mail]', '@[^.]*', 3 UNION ALL
  SELECT '[e-mail] [e-mail]', '@[^.]*', 4)
SELECT
  source_value, regexp, position,
  REGEXP_INSTR(source_value, regexp, position) AS instr
FROM example;

+-------------------------+--------+----------+-------+
| source_value            | regexp | position | instr |
+-------------------------+--------+----------+-------+
| [e-mail] [e-mail] | @[^.]* | 1        | 2     |
| [e-mail] [e-mail] | @[^.]* | 2        | 2     |
| [e-mail] [e-mail] | @[^.]* | 3        | 14    |
| [e-mail] [e-mail] | @[^.]* | 4        | 14    |
+-------------------------+--------+----------+-------+

WITH example AS (
  SELECT '[e-mail] [e-mail] [e-mail]' AS source_value,
         '@[^.]*' AS regexp, 1 AS position, 1 AS occurrence UNION ALL
  SELECT '[e-mail] [e-mail] [e-mail]', '@[^.]*', 1, 2 UNION ALL
  SELECT '[e-mail] [e-mail] [e-mail]', '@[^.]*', 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 |
+-------------------------------------+--------+----------+------------+-------+
| [e-mail] [e-mail] [e-mail] | @[^.]* | 1        | 1          | 2     |
| [e-mail] [e-mail] [e-mail] | @[^.]* | 1        | 2          | 14    |
| [e-mail] [e-mail] [e-mail] | @[^.]* | 1        | 3          | 26    |
+-------------------------------------+--------+----------+------------+-------+

WITH example AS (
  SELECT '[e-mail]' AS source_value, '@[^.]*' AS regexp,
         1 AS position, 1 AS occurrence, 0 AS o_position UNION ALL
  SELECT '[e-mail]', '@[^.]*', 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 |
+--------------+--------+----------+------------+------------+-------+
| [e-mail]  | @[^.]* | 1        | 1          | 0          | 2     |
| [e-mail]  | @[^.]* | 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.

Nota: Para agregar una barra invertida a tu expresión regular, primero debes escaparla. Por ejemplo, SELECT REGEXP_REPLACE("abc", "b(.)", "X\\1"); muestra aXc.

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.

Nota: BigQuery proporciona compatibilidad con expresiones regulares mediante la biblioteca re2; consulta esa documentación para obtener su sintaxis de expresión regular.

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
    "[e-mail]" AS email_address
  UNION ALL
  SELECT
    "[e-mail]" AS email_address
  UNION ALL
  SELECT
    "[e-mail]" 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 una STRING, 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 es BYTES, 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 en source_characters se deja sin cambios en expression.
• Un carácter en source_characters sin un carácter correspondiente en target_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. Si json_path_format muestra un null de JSON, este se convierte en un NULL 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. Si json_path_format muestra un null de JSON, este se convierte en un NULL 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 CAST(value AS STRING) cuando value está en el rango de [-2^53, 2^53], que es el rango de números enteros que se pueden representar sin pérdidas como números de puntos flotante de precisión doble IEEE 754. Los valores fuera de este rango se representan como strings entre comillas. Por ejemplo: -1 0 12345678901 9007199254740992 -9007199254740992 "9007199254740993" 9007199254740993 es mayor que 2^53, por lo que se representa como una string entre comillas.
NUMERIC	Igual que CAST(value AS STRING) cuando value está en el rango de [-2^53, 2^53] y no tiene parte fraccionaria. Los valores fuera de este rango se representan como strings entre comillas. Por ejemplo: -1 0 "9007199254740993" "123.56"
FLOAT64	+/-inf y NaN se representan como Infinity, -Infinity y NaN, respectivamente. De lo contrario, es lo mismo que CAST(value AS STRING).
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: "R29vZ2xl" es la representación en base64 de los bytes b"Google"
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 "". Si un campo tiene un arreglo o un objeto no vacío, los elementos o campos tendrán una sangría al nivel adecuado. 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 ", \ y los caracteres de control de U+0000 a U+001F.

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áusula ORDER BY, la función ARRAY mostrará un ARRAY que respete esa cláusula.
• Si la subconsulta muestra más de una columna, la función ARRAY muestra un error.
• Si la subconsulta muestra una columna de tipo ARRAY o filas de tipo ARRAY, la función ARRAY muestra un error: BigQuery no admite los ARRAY con elementos del tipo ARRAY.
• Si la subconsulta muestra cero filas, la función ARRAY muestra un ARRAY vacío. Nunca muestra un NULL ARRAY.

Tipo de datos que se muestra

ARRAY

Ejemplos

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

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

Si deseas construir un ARRAY a partir de una subconsulta que contiene varias columnas, debes modificar la subconsulta para usar SELECT AS STRUCT. Ahora, la función ARRAY mostrará un ARRAY de 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.

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

Tipo de datos que se muestra

ARRAY

Ejemplos

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

+--------------------------------------------------+
| count_to_six                                     |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

Descripción

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

Tipo de datos que se muestra

INT64

Ejemplos

WITH items AS
  (SELECT ["coffee", NULL, "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)
SELECT ARRAY_TO_STRING(list, ', ', 'NULL'), ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [coffee, NULL, milk]            | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

Descripción

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

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

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

Ejemplos

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

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

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

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

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

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

Descripción

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

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

• INT64
• NUMERIC
• FLOAT64

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

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

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

Tipo de datos mostrados

ARRAY

Ejemplos

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

SELECT GENERATE_ARRAY(1, 5) AS example_array;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

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

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

Descripción

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

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

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

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

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

Tipo de datos mostrados

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

Ejemplos

A continuación, se muestra un arreglo de fechas con un paso predeterminado de 1.

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

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

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

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

+--------------------------------------+
| example                              |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+

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

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

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

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

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

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

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

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

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

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

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

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

En el siguiente ejemplo, se muestra un arreglo de fechas con MONTH como el intervalo date_part:

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

+--------------------------------------------------------------------------+
| example                                                                  |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+

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

SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

+--------------------------------------------------------------+
| date_range                                                   |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

Descripción

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

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

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

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

Tipo de datos mostrados

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

Ejemplos

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

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

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

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

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

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

En el siguiente ejemplo, se muestra un ARRAY de TIMESTAMPS con un intervalo negativo.

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

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

En el siguiente ejemplo, se muestra un ARRAY con un solo elemento, porque start_timestamp y end_timestamp tienen el mismo valor.

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

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

En el siguiente ejemplo, se muestra un ARRAY vacío, porque start_timestamp es posterior a end_timestamp.

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

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

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

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

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

En el siguiente ejemplo, se generan ARRAY de TIMESTAMP a partir de columnas que contienen valores de start_timestamp y end_timestamp.

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

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

OFFSET y ORDINAL

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

Descripción

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

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

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

Tipo de datos que se muestra

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

Ejemplos

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

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

ARRAY_REVERSE

ARRAY_REVERSE(value)

Descripción

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

Tipo de datos que se muestra

ARRAY

Ejemplos

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

SAFE_OFFSET y SAFE_ORDINAL

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

Descripción

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

Tipo de datos que se muestra

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

Ejemplo

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

SELECT list,
  list[SAFE_OFFSET(3)] as safe_offset_3,
  list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;

+----------------------------------+---------------+----------------+
| list                             | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes        | pears          |
| [coffee, tea, milk]              | NULL          | milk           |
| [cake, pie]                      | NULL          | NULL           |
+----------------------------------+---------------+----------------+

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 el WEEKDAY. Las fechas anteriores al primer WEEKDAY del año están en la semana 0. Los valores válidos para WEEKDAY son SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY y SATURDAY.
• ISOWEEK: muestra el número de semana ISO 8601 de date_expression. Los valores ISOWEEK comienzan el lunes. Los valores de muestra están en el rango [1, 53]. El primer valor ISOWEEK 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 pertenece date_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

1. Construye un valor DATE a partir de los valores INT64 que representan el año, mes y día.
2. 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.
3. 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 7 DAY
• 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 7 DAY
• 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 el WEEKDAY. Los valores válidos para WEEKDAY son SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY y SATURDAY.
• 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>): Trunca date_expression al límite de la semana anterior, en el que las semanas comienzan en WEEKDAY. Los valores válidos para WEEKDAY son SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY y SATURDAY.
• ISOWEEK: trunca date_expression al límite de la semana ISO 8601 anterior. Los valores ISOWEEK comienzan el lunes. El primer ISOWEEK de cada año ISO contiene el primer jueves del año calendario gregoriano correspondiente. Cualquier valor date_expression anterior a esto se truncará al lunes anterior.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: trunca date_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 7 DAY.
• WEEK(<WEEKDAY>). <WEEKDAY> representa el día de inicio de la semana. Los valores válidos son SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY y SATURDAY.
• 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