Sintaxis, funciones y operadores de SQL heredado
En este documento, se detallan los operadores, las funciones y la sintaxis de consulta de SQL heredado. La sintaxis de consulta preferida para BigQuery es GoogleSQL. Para obtener información sobre GoogleSQL, consulta Sintaxis de consulta de GoogleSQL.
Sintaxis de las consultas
Nota: Las palabras clave no distinguen entre mayúsculas y minúsculas. En este documento, las palabras clave como SELECT
se escriben en mayúsculas por fines ilustrativos.
Cláusula SELECT
La cláusula SELECT
especifica una lista de expresiones que deben ser calculadas. Las expresiones de la cláusula SELECT
pueden contener nombres de campo, literales y llamadas a funciones (incluidas las funciones agregadas y las funciones analíticas), así como combinaciones de las tres. La lista de expresiones está separada por comas.
Se puede agregar un espacio seguido de un identificador después de cada expresión para darle un alias. Se puede agregar la palabra clave opcional AS
entre la expresión y el alias para mejorar la legibilidad. Los alias definidos en una cláusula SELECT
pueden hacer referencia a las cláusulas GROUP BY
, HAVING
y ORDER BY
de la consulta, pero no por FROM
, WHERE
o OMIT RECORD IF
ni por otras expresiones en la misma cláusula SELECT
.
Notas:
-
Si usas una función agregada en la cláusula
SELECT
, debes usar una función agregada en todas las expresiones o tu consulta debe tener una cláusulaGROUP BY
que incluya todos los campos no agregados en la cláusulaSELECT
como claves de agrupación. Por ejemplo:#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
-
Puedes usar corchetes para escapar palabras reservadas, así podrás usarlas como nombre de campo y alias. Por ejemplo, si tienes una columna llamada “partición”, que es una palabra reservada en la sintaxis de BigQuery, las consultas que hacen referencia a ese campo fallan con mensajes de error poco claros, a menos que la escapes con corchetes:
SELECT [partition] FROM ...
Ejemplo
Este ejemplo define los alias en la cláusula SELECT
y, luego, hace referencia a una de ellas en la cláusula ORDER BY
. Ten en cuenta que no se puede hacer referencia a la columna palabra mediante word_alias en la cláusula WHERE
; se le debe hacer referencia con su nombre. El alias len tampoco es visible en la cláusula WHERE
. Sería visible a una cláusula HAVING
.
#legacySQL SELECT word AS word_alias, LENGTH(word) AS len FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS 'th' ORDER BY len;
Modificador WITHIN para funciones agregadas
aggregate_function WITHIN RECORD [ [ AS ] alias ]
La palabra clave WITHIN
hace que la función agregada se agregue a valores repetidos dentro de cada registro. Para cada registro de entrada, se producirá exactamente un resultado agregado. Este tipo de agregación se conoce como agregación con alcance. Como la agregación con alcance produce resultados para cada registro, las expresiones no agregadas se pueden seleccionar junto con expresiones agregadas con alcance sin usar una cláusula GROUP BY
.
Por lo general, usarás el alcance RECORD
cuando uses la agregación con alcance. Si tienes un esquema anidado repetido muy complejo, quizás necesites realizar agregaciones dentro de los alcances de subregistro. Esto se puede hacer mediante el reemplazo de la palabra clave RECORD
en la sintaxis anterior con el nombre del nodo del esquema en el que deseas que se realice la agregación.
Para obtener más información sobre ese comportamiento avanzado, consulta la sección sobre cómo trabajar con datos.
Ejemplo
En este ejemplo, se realiza una agregación COUNT
con alcance y, luego, se filtran y ordenan los registros según el valor agregado.
#legacySQL SELECT repository.url, COUNT(payload.pages.page_name) WITHIN RECORD AS page_count FROM [bigquery-public-data:samples.github_nested] HAVING page_count > 80 ORDER BY page_count DESC;
Cláusula FROM
FROM [project_name:]datasetId.tableId [ [ AS ] alias ] | (subquery) [ [ AS ] alias ] |JOIN
clause |FLATTEN
clause | table wildcard function
La cláusula FROM
especifica los datos de origen que se consultarán. Las consultas de BigQuery pueden ejecutarse directamente sobre tablas, subconsultas, tablas unidas y tablas modificadas por operadores de objetivos especiales que se describen a continuación. Las combinaciones de estas fuentes de datos se pueden consultar con la coma, que es el operador UNION ALL
en BigQuery.
Referencia tablas
Cuando se hace referencia a una tabla, se deben especificar datasetId y datasetId; datasetId es opcional. Si no se especifica project_name, BigQuery usa de forma predeterminada el proyecto actual. Si el nombre de tu proyecto incluye un guion, debes rodear toda la referencia de la tabla con corchetes.
Ejemplo
[my-dashed-project:dataset1.tableName]
Se puede otorgar un alias a las tablas si se agrega un espacio seguido de un identificador después del nombre de la tabla. Se puede agregar la palabra clave AS
opcional entre tableId y el alias para mejorar la legibilidad.
Cuando haces referencia a columnas de una tabla, puedes usar el nombre simple de la columna o prefijar el nombre de la columna con el alias, si especificaste uno, o con el datasetId y el datasetId, siempre y cuando no hayas especificado datasetId. No se puede incluir project_name en el prefijo de la columna porque los caracteres de dos puntos no están permitidos en los nombres de campo.
Ejemplos
En este ejemplo, se hace referencia a una columna sin prefijo de tabla.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare];
En este ejemplo, se prefija el nombre de la columna con el datasetId y el datasetId. Ten en cuenta que no se puede incluir project_name en este ejemplo. Este método solo funcionará si el conjunto de datos está en tu proyecto predeterminado actual.
#legacySQL SELECT samples.shakespeare.word FROM samples.shakespeare;
En este ejemplo, se prefija el nombre de la columna con un alias de tabla.
#legacySQL SELECT t.word FROM [bigquery-public-data:samples.shakespeare] AS t;
Tablas particionadas por rango de números enteros
SQL heredado admite el uso de decoradores de tabla para abordar una partición específica en una tabla particionada por rango de números enteros. La clave para dirigirse a una partición de rango es el inicio del rango.
El siguiente ejemplo consulta la partición de rango que comienza con 30:
#legacySQL SELECT * FROM dataset.table$30;
Ten en cuenta que no puedes usar SQL heredado para realizar consultas en una tabla particionada por rango de números enteros. En su lugar, la consulta muestra un error como el siguiente:
Querying tables partitioned on a field is not supported in Legacy SQL
Usa subconsultas
Una subconsulta es una declaración SELECT
anidada entre paréntesis. Las expresiones calculadas en la cláusula SELECT
de la subconsulta están disponibles para la consulta externa como lo estarían las columnas de una tabla.
Las subconsultas pueden usarse para calcular agregaciones y otras expresiones. La gama completa de operadores SQL está disponible en la subconsulta. Esto significa que una subconsulta puede contener otras subconsultas, las subconsultas pueden realizar uniones y agregaciones de agrupamiento, etcétera.
Coma como UNION ALL
A diferencia de GoogleSQL, SQL heredado usa la coma como un operador UNION ALL
en lugar de un operador CROSS JOIN
. Este es un comportamiento heredado que evolucionó porque, históricamente, BigQuery no era compatible con CROSS JOIN
y los usuarios de BigQuery necesitaban escribir consultas UNION ALL
. En GoogleSQL, las consultas que realizan uniones son muy detalladas. El uso de la coma como operador de la unión permite escribir las consultas de manera más eficiente. Por ejemplo, se puede usar esta consulta para ejecutar una sola consulta en los registros de varios días.
#legacySQL SELECT FORMAT_UTC_USEC(event.timestamp_in_usec) AS time, request_url FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503] WHERE event.username = 'root' AND NOT event.source_ip.is_internal;
Por lo general, las consultas que unen una gran cantidad de tablas se ejecutan con mayor lentitud que las que procesan la misma cantidad de datos desde una sola tabla. La diferencia de rendimiento puede ser de hasta 50 ms por tabla adicional. Una sola consulta puede unir como máximo 1,000 tablas.
Funciones comodín de tablas
El término función comodín de tablas hace referencia a un tipo especial de función exclusiva de BigQuery.
Estas funciones se usan en la cláusula FROM
para hacer coincidir una colección de nombres de tablas mediante uno de varios tipos de filtros. Por ejemplo, se puede usar la función TABLE_DATE_RANGE
para consultar solo un conjunto específico de tablas diarias. Para obtener más información sobre estas funciones, consulta Funciones comodín de tablas.
Operador FLATTEN
(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened)) (FLATTEN((subquery), field_to_be_flattened))
A diferencia de los sistemas de procesamiento de SQL típicos, BigQuery está diseñado para administrar datos repetidos. Debido a esto, a veces los usuarios de BigQuery necesitan escribir consultas que manipulan la estructura de registros repetidos. Una forma de hacerlo es con el operador FLATTEN
.
FLATTEN
convierte un nodo en el esquema de repetido a opcional. En un registro con uno o más valores de un campo repetido, FLATTEN
creará varios registros, uno para cada valor en el campo repetido. Todos los demás campos seleccionados del registro se duplican en cada registro de salida nuevo. FLATTEN
puede aplicarse de manera repetida para quitar varios niveles de repetición.
Para obtener más información y ejemplos, consulta la sección sobre cómo trabajar con datos.
Operador JOIN
BigQuery es compatible con varios operadores JOIN
en cada cláusula FROM
.
Las operaciones de JOIN
posteriores usan los resultados de la operación anterior de JOIN
como la entrada izquierda de JOIN
. Los campos de cualquier entrada anterior de JOIN
se pueden usar como claves en las cláusulas ON
de los operadores JOIN
posteriores.
Tipos de JOIN
BigQuery es compatible con las operaciones INNER
, [FULL|RIGHT|LEFT] OUTER
y CROSS JOIN
. Si no se especifica, el valor predeterminado es INNER
.
Las operaciones CROSS JOIN
no permiten cláusulas ON
. CROSS JOIN
puede mostrar una gran cantidad de datos y podría dar como resultado una consulta ineficiente y lenta, o una consulta que exceda el máximo permitido por recursos de consulta. Esas consultas fallarán con un error. Cuando sea posible, usa las consultas que no usen CROSS JOIN
. Por ejemplo, CROSS JOIN
suele usarse en lugares donde las funciones analíticas serían más eficientes.
Modificador EACH
El modificador EACH
es una sugerencia que le dice a BigQuery que ejecute JOIN
con múltiples particiones. Esto es muy útil cuando sabes que ambos lados de la JOIN
son grandes. El modificador EACH
no se puede usar en las cláusulas CROSS JOIN
.
Se solía recomendar el uso de EACH
en muchos casos, pero ya no es así. Cuando sea posible, usa JOIN
sin el modificador EACH
para obtener un mejor rendimiento.
Usa JOIN EACH
cuando tu consulta falle con un mensaje de error de recursos excedidos.
Semiunión y antiunión
Además de ser compatible con JOIN
en la cláusula FROM
, BigQuery también admite dos tipos de uniones en la cláusula WHERE
: semiunión y antisemiunión. Una semiunión se especifica mediante la palabra clave IN
con una subconsulta; antiunión, con las palabras clave NOT IN
.
Ejemplos
La siguiente consulta usa una semiunión para encontrar ngrams en los que la primera palabra es también la segunda palabra de otro ngram que tiene “AND” como tercera palabra.
#legacySQL SELECT ngram FROM [bigquery-public-data:samples.trigrams] WHERE first IN (SELECT second FROM [bigquery-public-data:samples.trigrams] WHERE third = "AND") LIMIT 10;
La siguiente consulta usa una semiunión para mostrar la cantidad de mujeres mayores de 50 años que dieron a luz en los 10 estados con más cantidad de nacimientos.
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
Para ver las cantidades de los otros 40 estados, puedes usar una antiunión. La siguiente consulta es casi idéntica al ejemplo anterior, pero usa NOT IN
en lugar de IN
para mostrar la cantidad de mujeres mayores de 50 años que dieron a luz en los 40 estados con menos nacimientos.
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state NOT IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
Notas:
- BigQuery no es compatible con semi o antisemiuniones correlacionadas. La subconsulta no puede hacer referencia a ningún campo de la consulta externa.
- La subconsulta usada en una semiunión o una antisemiunión debe seleccionar solo un campo.
-
Los tipos del campo seleccionado y el campo que se utiliza desde la consulta externa en la cláusula
WHERE
deben coincidir exactamente. BigQuery no realizará ningún tipo de coerción para las semiuniones o antisemiuniones.
Cláusula WHERE
La cláusula WHERE
, a veces llamada predicado, filtra los registros producidos por la cláusula FROM
mediante una expresión booleana. Se pueden unir varias condiciones mediante cláusulas booleanas AND
y OR
, opcionalmente entre paréntesis () para agruparlas. No es necesario seleccionar los campos enumerados en una cláusula WHERE
en la cláusula SELECT
correspondiente. Además, la expresión de la cláusula WHERE
no puede hacer referencia a las expresiones calculadas en la cláusula SELECT
de la consulta a la que pertenece la cláusula WHERE
.
Nota: Las funciones agregadas no pueden usarse en la cláusula WHERE
. Usa una cláusula HAVING
y una consulta externa si necesitas filtrar los resultados de una función agregada.
Ejemplo
En el siguiente ejemplo, se utiliza una disyunción de expresiones booleanas en la cláusula WHERE
: Las dos expresiones unidas por un operador OR
. Un registro de entrada pasará por el filtro WHERE
si alguna de las expresiones muestra true
.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare] WHERE (word CONTAINS 'prais' AND word CONTAINS 'ing') OR (word CONTAINS 'laugh' AND word CONTAINS 'ed');
Cláusula OMIT RECORD IF
La cláusula OMIT RECORD IF
es una construcción exclusiva de BigQuery. Resulta muy útil cuando se trabaja con esquemas anidados y repetidos. Es similar a una cláusula WHERE
, pero diferente en dos aspectos importantes. Primero, usa una condición de exclusión, lo que significa que los registros se omiten si la expresión muestra true
, pero se mantiene si la expresión muestra false
o null
. En segundo lugar, la cláusula OMIT RECORD IF
puede (y generalmente lo hace) usar funciones agregadas con alcance en su condición.
Además de filtrar registros completos, OMIT...IF
puede especificar un alcance más limitado para filtrar solo partes de un registro. Para ello, utiliza el nombre de un nodo que no sea de hoja en tu esquema en lugar de RECORD
en tu cláusula OMIT...IF
. Los usuarios de BigQuery no suelen usar esta funcionalidad. Puedes encontrar más documentación sobre este comportamiento avanzado en la documentación de WITHIN
arriba.
Si usas OMIT...IF
para excluir una parte de un registro en un campo repetido y la consulta también selecciona otros campos que se repiten de forma independiente, BigQuery omite una parte de los demás registros repetidos en la consulta. Si ves el error Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>,
, te recomendamos cambiar a GoogleSQL. Para obtener información sobre cómo migrar declaraciones OMIT...IF
a GoogleSQL, consulta Migra a GoogleSQL.
Ejemplo
En referencia al ejemplo usado para el modificador WITHIN
, OMIT RECORD IF
se puede usar a fin de lograr lo mismo que WITHIN
y HAVING
.
#legacySQL SELECT repository.url FROM [bigquery-public-data:samples.github_nested] OMIT RECORD IF COUNT(payload.pages.page_name) <= 80;
Cláusula GROUP BY
La cláusula GROUP BY
te permite agrupar filas que tienen los mismos valores para un campo o conjunto de campos determinado a fin de que puedas calcular agregaciones de campos relacionados. La agrupación se produce después del filtrado que se realizó en la cláusula WHERE
, pero antes de que se calculen las expresiones en la cláusula SELECT
. Los resultados de la expresión no se pueden usar como claves de grupo en la cláusula GROUP BY
.
Ejemplo
Esta consulta encuentra las diez primeras palabras más comunes en el conjunto de datos de muestra de trigramas.
Además de demostrar el uso de la cláusula GROUP BY
, demuestra cómo se pueden usar los índices posicionales en lugar de los nombres de campo en las cláusulas GROUP BY
y ORDER BY
.
#legacySQL SELECT first, COUNT(ngram) FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
La agregación que se realiza con una cláusula GROUP BY
se denomina agregación agrupada. A diferencia de la agregación con alcance, la agregación agrupada es común en la mayoría de los sistemas de procesamiento de SQL.
Modificador EACH
El modificador EACH
es una sugerencia que le dice a BigQuery que ejecute GROUP BY
con múltiples particiones. Esto es muy útil cuando sabes que tu conjunto de datos contiene una gran cantidad de valores distintos para las claves de grupo.
Se solía recomendar el uso de EACH
en muchos casos, pero ya no es así.
Por lo general, el uso de GROUP BY
sin el modificador EACH
proporciona un mejor rendimiento.
Usa GROUP EACH BY
cuando tu consulta falle con un mensaje de error de Resources exceeded.
Función ROLLUP
Cuando se usa la función ROLLUP
, BigQuery agrega filas adicionales al resultado de la consulta que representan las agregaciones completas. Todos los campos enumerados después de ROLLUP
deben estar dentro de un único conjunto de paréntesis. En las filas agregadas debido a la función ROLLUP
, NULL
indica las columnas para las cuales se acumula la agregación.
Ejemplo
Esta consulta genera recuentos por año de nacimientos niños y niñas a partir del conjunto de datos de muestra de natalidad.
#legacySQL SELECT year, is_male, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
Estos son los resultados de la consulta. Ten en cuenta que hay filas en las que una o ambas claves de grupo son NULL
. Estas filas son las filas rollup.
+------+---------+----------+ | year | is_male | count | +------+---------+----------+ | NULL | NULL | 12122730 | | 2000 | NULL | 4063823 | | 2000 | false | 1984255 | | 2000 | true | 2079568 | | 2001 | NULL | 4031531 | | 2001 | false | 1970770 | | 2001 | true | 2060761 | | 2002 | NULL | 4027376 | | 2002 | false | 1966519 | | 2002 | true | 2060857 | +------+---------+----------+
Cuando se usa la función ROLLUP
, puedes usar la función GROUPING
para distinguir entre las filas que se agregaron debido a la función ROLLUP
y las que de verdad tienen un valor NULL
para la clave de grupo.
Ejemplo
Esta consulta agrega la función GROUPING
al ejemplo anterior para identificar mejor las filas agregadas debido a la función ROLLUP
.
#legacySQL SELECT year, GROUPING(year) as rollup_year, is_male, GROUPING(is_male) as rollup_gender, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
Estos son los resultados que muestra la nueva consulta.
+------+-------------+---------+---------------+----------+ | year | rollup_year | is_male | rollup_gender | count | +------+-------------+---------+---------------+----------+ | NULL | 1 | NULL | 1 | 12122730 | | 2000 | 0 | NULL | 1 | 4063823 | | 2000 | 0 | false | 0 | 1984255 | | 2000 | 0 | true | 0 | 2079568 | | 2001 | 0 | NULL | 1 | 4031531 | | 2001 | 0 | false | 0 | 1970770 | | 2001 | 0 | true | 0 | 2060761 | | 2002 | 0 | NULL | 1 | 4027376 | | 2002 | 0 | false | 0 | 1966519 | | 2002 | 0 | true | 0 | 2060857 | +------+-------------+---------+---------------+----------+
Notas:
-
Los campos no agregados en la cláusula
SELECT
deben enumerarse en la cláusulaGROUP BY
.#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
- Las expresiones calculadas en la cláusula
SELECT
no se pueden usar en la cláusulaGROUP BY
correspondiente.#legacySQL SELECT word, corpus, COUNT(word) word_count FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus, word_count; /* Fails because word_count is not visible to this
GROUP BY
clause. */ - No se admite la agrupación por valores flotantes y dobles, ya que la función de igualdad para esos tipos no está bien definida.
-
Debido a que el sistema es interactivo, las consultas que producen una gran cantidad de grupos pueden fallar. El uso de la función
TOP
en lugar deGROUP BY
podría resolver algunos problemas de escalamiento.
Cláusula HAVING
La cláusula HAVING
se comporta exactamente igual que la cláusula WHERE
, excepto que se evalúa después de la cláusula SELECT
a fin de que los resultados de todas las expresiones calculadas sean visibles para la cláusula HAVING
. La cláusula HAVING solo puede referirse a los resultados de la cláusula SELECT
correspondiente.
Ejemplo
Esta consulta calcula las primeras palabras más comunes en el conjunto de datos de muestra de ngram que contienen la letra a y aparecen como máximo 10,000 veces.
#legacySQL SELECT first, COUNT(ngram) ngram_count FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 HAVING first contains "a" AND ngram_count < 10000 ORDER BY 2 DESC LIMIT 10;
Cláusula ORDER BY
La cláusula ORDER BY
ordena los resultados de una consulta en orden ascendente o descendente con uno o más campos clave. Para ordenar por múltiples campos o alias, ingrésalos como una lista separada por comas. Los resultados se ordenan en los campos en el orden en que se enumeran.
Usa DESC
(descendente) o ASC
(ascendente) para especificar la dirección de clasificación.
ASC
es la configuración predeterminada. Se puede especificar una dirección de clasificación diferente para cada clave de clasificación.
La cláusula ORDER BY
se evalúa después de la cláusula SELECT
para que pueda hacer referencia al resultado de cualquier expresión calculada en SELECT
. Si a un campo se le asigna un alias en la cláusula SELECT
, el alias se debe usar en la cláusula ORDER BY
.
Cláusula LIMIT
La cláusula LIMIT
limita el número de filas en el conjunto de resultados que se muestra. Dado que las consultas de BigQuery operan habitualmente en un gran número de filas, LIMIT
es una buena manera de procesar solo un subconjunto de las filas para evitar consultas de ejecución larga.
Notas:
-
La cláusula
LIMIT
detendrá el procesamiento y mostrará los resultados cuando satisfaga tus requisitos. Esto puede reducir el tiempo de procesamiento de algunas consultas, pero cuando especificas funciones agregadas como las cláusulas COUNT oORDER BY
, el conjunto de resultados completo todavía debe procesarse antes de mostrar los resultados. La cláusulaLIMIT
es la última que se evalúa. -
Una consulta con una cláusula
LIMIT
puede no ser determinista si no hay un operador en la consulta que garantice el orden del conjunto de resultados de salida. Esto se debe a que BigQuery se ejecuta mediante una gran cantidad de trabajadores paralelos. El orden en el que se muestran los trabajos paralelos no está garantizado. - La cláusula
LIMIT
no puede contener funciones; solo admite una constante numérica.
Gramática de consulta
Las cláusulas individuales de las declaraciones SELECT
de BigQuery se describen en detalle más arriba. Aquí, presentamos la gramática completa de las declaraciones SELECT
de forma compacta con vínculos a las secciones individuales.
query: SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ] [ FROM from_body [ WHERE bool_expression ] [ OMIT RECORD IF bool_expression] [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ] [ HAVING bool_expression ] [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ] [ LIMIT n ] ]; from_body: { from_item [, ...] | # Warning: Comma means UNION ALL here from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] | (FLATTEN({ table_name | (query) }, field_name_or_alias)) | table_wildcard_function } from_item: { table_name | (query) } [ [ AS ] alias ] join_type: { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS } join_predicate: field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...] expression: { literal_value | field_name_or_alias | function_call } bool_expression: { expression_which_results_in_a_boolean_value | bool_expression AND bool_expression | bool_expression OR bool_expression | NOT bool_expression }
Notación:
- Los corchetes “[ ]” indican cláusulas opcionales.
- Las llaves “{ }” encierran un conjunto de opciones.
- La barra vertical “|” indica un OR lógico.
- Una coma o palabra clave seguida de puntos suspensivos entre corchetes “[, ...]” indica que el elemento anterior puede repetirse en una lista con el separador especificado.
- Los paréntesis “( )” indican paréntesis literales.
Operadores y funciones compatibles
La mayoría de las cláusulas de instrucción SELECT
son compatibles con funciones. Los campos a los que se hace referencia en una función no necesitan enumerarse en ninguna cláusula SELECT
. Por lo tanto, la siguiente consulta es válida, aunque el campo clicks
no se muestre directamente:
#legacySQL SELECT country, SUM(clicks) FROM table GROUP BY country;
Funciones de agregación | |
---|---|
AVG() |
Muestra el promedio de los valores para un grupo de filas… |
BIT_AND() |
Muestra el resultado de una operación AND bit a bit… |
BIT_OR() |
Muestra el resultado de una operación OR bit a bit… |
BIT_XOR() |
Muestra el resultado de una operación XOR bit a bit… |
CORR() |
Muestra el coeficiente de correlación de Pearson de un conjunto de pares de números. |
COUNT() |
Muestra la cantidad total de valores… |
COUNT([DISTINCT]) |
Muestra el número total de valores no nulos… |
COVAR_POP() |
Calcula la covarianza de la población de los valores… |
COVAR_SAMP() |
Calcula la covarianza muestral de los valores… |
EXACT_COUNT_DISTINCT() |
Muestra el número exacto de valores no nulos distintos para el campo especificado. |
FIRST() |
Muestra el primer valor secuencial dentro del alcance de la función. |
GROUP_CONCAT() |
Concatena múltiples strings en una sola… |
GROUP_CONCAT_UNQUOTED() |
Concatena múltiples strings en una sola… no agregará comillas dobles… |
LAST() |
Muestra el último valor secuencial… |
MAX() |
Muestra el valor máximo… |
MIN() |
Muestra el valor mínimo… |
NEST() |
Agrega todos los valores dentro del alcance de agregación actual a un campo repetido. |
NTH() |
Muestra el enésimo valor secuencial… |
QUANTILES() |
Calcula mínimos, máximos y cuantiles aproximados… |
STDDEV() |
Muestra la desviación estándar… |
STDDEV_POP() |
Calcula la desviación estándar de la población… |
STDDEV_SAMP() |
Calcula la desviación estándar de la muestra… |
SUM() |
Muestra la suma total de los valores… |
TOP() ... COUNT(*) |
Muestra los registros principales de max_records según la frecuencia. |
UNIQUE() |
Muestra el conjunto de valores no nulos únicos… |
VARIANCE() |
Calcula la varianza de los valores… |
VAR_POP() |
Calcula la varianza poblacional de los valores… |
VAR_SAMP() |
Calcula la varianza muestral de los valores… |
Operadores aritméticos | |
---|---|
+ |
Suma |
- |
Resta |
* |
Multiplicación |
/ |
División |
% |
Modulo |
Funciones bit a bit | |
---|---|
& |
AND bit a bit |
| |
OR bit a bit |
^ |
XOR bit a bit |
<< |
Desplaza bits hacia la izquierda |
>> |
Desplaza bits hacia la derecha |
~ |
NOT bit a bit |
BIT_COUNT() |
Muestra el número de bits… |
Funciones de conversión | |
---|---|
BOOLEAN() |
Conversión a booleano. |
BYTES() |
Conversión a bytes. |
CAST(expr AS type) |
Convierte expr en una variable de tipo type . |
FLOAT() |
Conversión al doble. |
HEX_STRING() |
Conversión a la string hexadecimal. |
INTEGER() |
Conversión a número entero. |
STRING() |
Conversión a una string. |
Funciones de comparación | |
---|---|
expr1 = expr2 |
Muestra true si las expresiones son iguales. |
expr1 != expr2 expr1 <> expr2
|
Muestra true si las expresiones no son iguales. |
expr1 > expr2 |
Muestra true si expr1 es mayor que expr2 . |
expr1 < expr2 |
Muestra true si expr1 es menor que expr2 . |
expr1 >= expr2 |
Muestra true si expr1 es mayor o igual que expr2 . |
expr1 <= expr2 |
Muestra true si expr1 es menor o igual que expr2 . |
expr1 BETWEEN expr2 AND expr3 |
Muestra true si el valor de expr1 está entre expr2 y expr3 , inclusive. |
expr IS NULL |
Muestra true si expr es NULO. |
expr IN() |
Muestra true si expr coincide con expr1 , expr2 o cualquier valor entre paréntesis. |
COALESCE() |
Muestra el primer argumento que no es NULO. |
GREATEST() |
Muestra el parámetro numeric_expr más grande. |
IFNULL() |
Muestra el argumento si no es nulo. |
IS_INF() |
Muestra true si es infinito positivo o negativo. |
IS_NAN() |
Muestra true si el argumento es NaN . |
IS_EXPLICITLY_DEFINED() |
obsoleta: Usa expr IS NOT NULL en su lugar. |
LEAST() |
Muestra el parámetro numeric_expr más pequeño del argumento. |
NVL() |
Si expr no es nulo, muestra expr ; de lo contrario, muestra null_default . |
Funciones de fecha y hora | |
---|---|
CURRENT_DATE() |
Muestra la fecha actual en el formato %Y-%m-%d . |
CURRENT_TIME() |
Muestra la hora actual del servidor en el formato %H:%M:%S . |
CURRENT_TIMESTAMP() |
Muestra la hora actual del servidor en el formato %Y-%m-%d %H:%M:%S . |
DATE() |
Muestra la fecha con el formato %Y-%m-%d . |
DATE_ADD() |
Agrega el intervalo especificado a un tipo de datos TIMESTAMP. |
DATEDIFF() |
Muestra el número de días entre dos tipos de datos TIMESTAMP. |
DAY() |
Muestra el día del mes como un número entero entre 1 y 31. |
DAYOFWEEK() |
Muestra el día de la semana como un número entero entre 1 (domingo) y 7 (sábado). |
DAYOFYEAR() |
Muestra el día del año como un número entero entre 1 y 366. |
FORMAT_UTC_USEC() |
Muestra una marca de tiempo UNIX en el formato YYYY-MM-DD HH:MM:SS.uuuuuu . |
HOUR() |
Muestra la hora de TIMESTAMP como un número entero entre 0 y 23. |
MINUTE() |
Muestra los minutos de una TIMESTAMP como un número entero entre 0 y 59. |
MONTH() |
Muestra el mes de una TIMESTAMP como un número entero entre 1 y 12. |
MSEC_TO_TIMESTAMP() |
Convierte una marca de tiempo UNIX en milisegundos en una TIMESTAMP. |
NOW() |
Muestra la marca de tiempo de UNIX actual en microsegundos. |
PARSE_UTC_USEC() |
Convierte una string de fecha en una marca de tiempo UNIX en microsegundos. |
QUARTER() |
Muestra el trimestre del año de una TIMESTAMP como un número entero entre 1 y 4. |
SEC_TO_TIMESTAMP() |
Convierte una marca de tiempo UNIX en segundos en una TIMESTAMP. |
SECOND() |
Muestra los segundos de una TIMESTAMP como un número entero entre 0 y 59. |
STRFTIME_UTC_USEC() |
Muestra una string de fecha en el formato date_format_str. |
TIME() |
Muestra un TIMESTAMP con el formato %H:%M:%S . |
TIMESTAMP() |
Convierte una string de fecha en un TIMESTAMP. |
TIMESTAMP_TO_MSEC() |
Convierte una TIMESTAMP en una marca de tiempo UNIX en milisegundos. |
TIMESTAMP_TO_SEC() |
Convierte una TIMESTAMP en una marca de tiempo UNIX en segundos. |
TIMESTAMP_TO_USEC() |
Convierte una TIMESTAMP en una marca de tiempo UNIX en microsegundos. |
USEC_TO_TIMESTAMP() |
Convierte una marca de tiempo UNIX en microsegundos en una TIMESTAMP. |
UTC_USEC_TO_DAY() |
Desplaza una marca de tiempo UNIX en microsegundos al comienzo del día en que se produce. |
UTC_USEC_TO_HOUR() |
Desplaza una marca de tiempo UNIX en microsegundos al comienzo de la hora en que se produce. |
UTC_USEC_TO_MONTH() |
Desplaza una marca de tiempo UNIX en microsegundos al comienzo del mes en que se produce. |
UTC_USEC_TO_WEEK() |
Muestra una marca de tiempo UNIX en microsegundos que representa un día de la semana. |
UTC_USEC_TO_YEAR() |
Muestra una marca de tiempo UNIX en microsegundos que representa el año. |
WEEK() |
Muestra la semana de una TIMESTAMP como un número entero entre 1 y 53. |
YEAR() |
Muestra el año de una TIMESTAMP. |
Funciones de IP | |
---|---|
FORMAT_IP() |
Convierte 32 bits menos significativos de integer_value en una string de dirección IPv4 legible. |
PARSE_IP() |
Convierte una string que representa la dirección IPv4 en un valor entero sin firma. |
FORMAT_PACKED_IP() |
Muestra una dirección IP legible con el formato 10.1.5.23 o 2620:0:1009:1:216:36ff:feef:3f . |
PARSE_PACKED_IP() |
Muestra una dirección IP en BYTES. |
Funciones JSON | |
---|---|
JSON_EXTRACT() |
Selecciona un valor de acuerdo con la expresión JSONPath y muestra una string JSON. |
JSON_EXTRACT_SCALAR() |
Selecciona un valor de acuerdo con la expresión JSONPath y muestra un escalar JSON. |
Operadores lógicos | |
---|---|
expr AND expr |
Muestra true si ambas expresiones son verdaderas. |
expr OR expr |
Muestra true si una o ambas expresiones son verdaderas. |
NOT expr |
Muestra true si la expresión es falsa. |
Funciones matemáticas | |
---|---|
ABS() |
Muestra el valor absoluto del argumento. |
ACOS() |
Muestra el arcocoseno del argumento. |
ACOSH() |
Muestra el arcocoseno hiperbólico del argumento. |
ASIN() |
Muestra el arcoseno del argumento. |
ASINH() |
Muestra el arcoseno hiperbólico del argumento. |
ATAN() |
Muestra la arcotangente del argumento. |
ATANH() |
Muestra la arcotangente hiperbólica del argumento. |
ATAN2() |
Muestra la arcotangente de los dos argumentos. |
CEIL() |
Redondea el argumento al siguiente número entero más cercano y muestra el valor redondeado. |
COS() |
Muestra el coseno del argumento. |
COSH() |
Muestra el coseno hiperbólico del argumento. |
DEGREES() |
Convierte radianes en grados. |
EXP() |
Muestra e a la potencia del argumento. |
FLOOR() |
Redondea el argumento al número entero anterior más cercano. |
LN() LOG()
|
Muestra el logaritmo natural del argumento. |
LOG2() |
Muestra el logaritmo en base 2 del argumento. |
LOG10() |
Muestra el logaritmo en base 10 del argumento. |
PI() |
Muestra la constante π. |
POW() |
Muestra el primer argumento a la potencia del segundo argumento. |
RADIANS() |
Convierte grados en radianes. |
RAND() |
Muestra un valor flotante aleatorio en el rango 0.0 <= valor < 1.0. |
ROUND() |
Redondea el argumento hacia arriba o hacia abajo al número entero más cercano. |
SIN() |
Muestra el seno del argumento. |
SINH() |
Muestra el seno hiperbólico del argumento. |
SQRT() |
Muestra la raíz cuadrada de la expresión. |
TAN() |
Muestra la tangente del argumento. |
TANH() |
Muestra la tangente hiperbólica del argumento. |
Funciones de expresión regular | |
---|---|
REGEXP_MATCH() |
Muestra true si el argumento coincide con la expresión regular. |
REGEXP_EXTRACT() |
Muestra la parte del argumento que coincide con el grupo de captura dentro de la expresión regular. |
REGEXP_REPLACE() |
Reemplaza una substring que coincide con una expresión regular. |
Funciones de string | |
---|---|
CONCAT() |
Muestra la concatenación de dos o más strings, o NULO si alguno de los valores es NULO. |
expr CONTAINS 'str' |
Muestra true si expr contiene el argumento de string especificado. |
INSTR() |
Muestra el índice basado en uno del primer caso de una string. |
LEFT() |
Muestra los caracteres más a la izquierda de una string. |
LENGTH() |
Muestra la longitud de la string. |
LOWER() |
Muestra la string original con todos los caracteres en minúscula. |
LPAD() |
Inserta caracteres a la izquierda de una string. |
LTRIM() |
Quita caracteres del lado izquierdo de una string. |
REPLACE() |
Reemplaza todos los casos de una substring. |
RIGHT() |
Muestra los caracteres más a la derecha de una string. |
RPAD() |
Inserta caracteres a la derecha de una string. |
RTRIM() |
Quita los caracteres finales del lado derecho de una string. |
SPLIT() |
Divide una string en substrings repetidas. |
SUBSTR() |
Muestra una substring… |
UPPER() |
Muestra la string original con todos los caracteres en mayúsculas. |
Funciones comodín de tablas | |
---|---|
TABLE_DATE_RANGE() |
Consulta múltiples tablas diarias que abarcan un período. |
TABLE_DATE_RANGE_STRICT() |
Consulta varias tablas diarias que abarcan un período, sin fechas faltantes. |
TABLE_QUERY() |
Consulta tablas cuyos nombres coinciden con un predicado especificado. |
Funciones de URL | |
---|---|
HOST() |
Dada una URL, muestra el nombre de host como una string. |
DOMAIN() |
Dada una URL, muestra el dominio como una string. |
TLD() |
Dada una URL, muestra el dominio de nivel superior y cualquier dominio de país en la URL. |
Funciones analíticas | |
---|---|
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM() |
La misma operación que las funciones agregadas correspondientes, pero se calculan en una ventana definida por la cláusula OVER. |
CUME_DIST() |
Muestra un doble que indica la distribución acumulativa de un valor en un grupo de valores… |
DENSE_RANK() |
Muestra el rango de números enteros de un valor en un grupo de valores. |
FIRST_VALUE() |
Muestra el primer valor del campo especificado en la ventana. |
LAG() |
Te permite leer datos de una fila anterior dentro de una ventana. |
LAST_VALUE() |
Muestra el último valor del campo especificado en la ventana. |
LEAD() |
Te permite leer datos de una fila siguiente dentro de una ventana. |
NTH_VALUE() |
Muestra el valor de <expr> en la posición <n> del marco de la ventana… |
NTILE() |
Divide la ventana en el número especificado de depósitos. |
PERCENT_RANK() |
Muestra el rango de la fila actual, en relación con las otras filas de la partición. |
PERCENTILE_CONT() |
Muestra un valor interpolado que se asignaría al argumento de percentil con respecto a la ventana… |
PERCENTILE_DISC() |
Muestra el valor más cercano al percentil del argumento en la ventana. |
RANK() |
Muestra el rango de números enteros de un valor en un grupo de valores. |
RATIO_TO_REPORT() |
Muestra la proporción de cada valor en la suma de los valores. |
ROW_NUMBER() |
Muestra el número de fila actual del resultado de la consulta en la ventana. |
Otras funciones | |
---|---|
CASE WHEN ... THEN |
Usa CASE para elegir entre dos o más expresiones alternativas en tu consulta. |
CURRENT_USER() |
Muestra la dirección de correo electrónico del usuario que ejecuta la consulta. |
EVERY() |
Muestra true si el argumento es verdadero para todas sus entradas. |
FROM_BASE64() |
Convierte la string de entrada codificada en base 64 en formato BYTES. |
HASH() |
Calcula y muestra un valor hash firmado de 64 bits… |
FARM_FINGERPRINT() |
Calcula y muestra un valor de huella digital firmada de 64 bits… |
IF() |
Si el primer argumento es verdadero, muestra el segundo argumento; de lo contrario, muestra el tercero. |
POSITION() |
Muestra la posición secuencial basada en uno del argumento. |
SHA1() |
Muestra un hash SHA1 en formato BYTES. |
SOME() |
Muestra true si el argumento es verdadero para al menos una de sus entradas. |
TO_BASE64() |
Convierte el argumento BYTES en una string codificada en base 64. |
Funciones de agregación
Las funciones agregadas muestran valores que representan resúmenes de conjuntos de datos más grandes, lo que hace que estas funciones sean muy útiles para analizar análisis de registros. Una función agregada opera contra una colección de valores y muestra un solo valor por tabla, grupo o alcance:
- Agregación de tablas
Usa una función agregada para resumir todas las filas que califican en la tabla. Por ejemplo:
SELECT COUNT(f1) FROM ds.Table;
- Agregación de grupo
Usa una función agregada y una cláusula
GROUP BY
que especifica un campo no agregado para resumir las filas por grupo. Por ejemplo:SELECT COUNT(f1) FROM ds.Table GROUP BY b1;
La función TOP representa un caso especializado de agregación de grupo.
- Agregación con alcance
Esta función solo se aplica a las tablas que tienen campos anidados.
Usa una función agregada y la palabra claveWITHIN
para agregar valores repetidos dentro de un alcance definido. Por ejemplo:SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;
El alcance puede ser
RECORD
, que corresponde a una fila completa, o un nodo (campo repetido en una fila). Las funciones de agregación operan sobre los valores dentro del alcance y muestran resultados agregados para cada registro o nodo.
Puedes aplicar una restricción a una función agregada con una de las siguientes opciones:
-
Un alias en una consulta de subselección. La restricción se especifica en la cláusula
WHERE
externa.#legacySQL SELECT corpus, count_corpus_words FROM (SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus) AS sub_shakespeare WHERE count_corpus_words > 4000
-
Un alias en una cláusula HAVING.
#legacySQL SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus HAVING count_corpus_words > 4000;
También puedes hacer referencia a un alias en las cláusulas GROUP BY
o ORDER BY
.
Sintaxis
Funciones de agregación | |
---|---|
AVG() |
Muestra el promedio de los valores para un grupo de filas… |
BIT_AND() |
Muestra el resultado de una operación AND bit a bit… |
BIT_OR() |
Muestra el resultado de una operación OR bit a bit… |
BIT_XOR() |
Muestra el resultado de una operación XOR bit a bit… |
CORR() |
Muestra el coeficiente de correlación de Pearson de un conjunto de pares de números. |
COUNT() |
Muestra la cantidad total de valores… |
COUNT([DISTINCT]) |
Muestra el número total de valores no nulos… |
COVAR_POP() |
Calcula la covarianza de la población de los valores… |
COVAR_SAMP() |
Calcula la covarianza muestral de los valores… |
EXACT_COUNT_DISTINCT() |
Muestra el número exacto de valores no nulos distintos para el campo especificado. |
FIRST() |
Muestra el primer valor secuencial dentro del alcance de la función. |
GROUP_CONCAT() |
Concatena múltiples strings en una sola… |
GROUP_CONCAT_UNQUOTED() |
Concatena múltiples strings en una sola… no agregará comillas dobles… |
LAST() |
Muestra el último valor secuencial… |
MAX() |
Muestra el valor máximo… |
MIN() |
Muestra el valor mínimo… |
NEST() |
Agrega todos los valores dentro del alcance de agregación actual a un campo repetido. |
NTH() |
Muestra el enésimo valor secuencial… |
QUANTILES() |
Calcula mínimos, máximos y cuantiles aproximados… |
STDDEV() |
Muestra la desviación estándar… |
STDDEV_POP() |
Calcula la desviación estándar de la población… |
STDDEV_SAMP() |
Calcula la desviación estándar de la muestra… |
SUM() |
Muestra la suma total de los valores… |
TOP() ... COUNT(*) |
Muestra los registros principales de max_records según la frecuencia. |
UNIQUE() |
Muestra el conjunto de valores no nulos únicos… |
VARIANCE() |
Calcula la varianza de los valores… |
VAR_POP() |
Calcula la varianza poblacional de los valores… |
VAR_SAMP() |
Calcula la varianza muestral de los valores… |
AVG(numeric_expr)
- Muestra el promedio de los valores de un grupo de filas calculadas por
numeric_expr
. Las filas con un valor NULL no se incluyen en el cálculo. BIT_AND(numeric_expr)
- Muestra el resultado de una operación
AND
a nivel de bits entre cada instancia denumeric_expr
en todas las filas. Los valoresNULL
se ignoran. Esta función muestraNULL
si todas las instancias denumeric_expr
se evalúan comoNULL
. BIT_OR(numeric_expr)
- Muestra el resultado de una operación
OR
a nivel de bits entre cada instancia denumeric_expr
en todas las filas. Los valoresNULL
se ignoran. Esta función muestraNULL
si todas las instancias denumeric_expr
se evalúan comoNULL
. BIT_XOR(numeric_expr)
- Muestra el resultado de una operación
XOR
a nivel de bits entre cada instancia denumeric_expr
en todas las filas. Los valoresNULL
se ignoran. Esta función muestraNULL
si todas las instancias denumeric_expr
se evalúan comoNULL
. CORR(numeric_expr, numeric_expr)
- Muestra el coeficiente de correlación de Pearson de un conjunto de pares de números.
COUNT(*)
- Muestra la cantidad total de valores (NULO y no NULO) en el alcance de la función. A menos que uses
COUNT(*)
con la funciónTOP
, es mejor especificar explícitamente el campo que se va a contar. COUNT([DISTINCT] field [, n])
- Muestra la cantidad total de valores no NULO en el alcance de la función.
Si usas la palabra clave
DISTINCT
, la función muestra la cantidad de valores distintos para el campo especificado. Ten en cuenta que el valor mostrado paraDISTINCT
es una aproximación estadística y puede que no sea exacto.Usa
EXACT_COUNT_DISTINCT()
para obtener una respuesta exacta.Si necesitas mayor precisión de
, puedes especificar un segundo parámetro,COUNT(DISTINCT)
n
, que proporciona el umbral por debajo del cual se garantizan los resultados exactos. De forma predeterminada,n
es 1,000, pero si le asignas unn
mayor, obtendrás resultados exactos paraCOUNT(DISTINCT)
hasta ese valor den
. Sin embargo, proporcionar valores más grandes den
reducirá la escalabilidad de este operador y puede aumentar considerablemente el tiempo de ejecución de la consulta o hacer que falle.Para calcular la cantidad exacta de valores distintos, usa EXACT_COUNT_DISTINCT. Para un enfoque más escalable, considera usar
GROUP EACH BY
en los campos relevantes y, luego, aplicarCOUNT(*)
. El enfoque deGROUP EACH BY
es más escalable, pero puede incurrir en una leve penalización de rendimiento inicial. COVAR_POP(numeric_expr1, numeric_expr2)
- Calcula la covarianza de la población de los valores que calculan
numeric_expr1
ynumeric_expr2
. COVAR_SAMP(numeric_expr1, numeric_expr2)
- alcula la covarianza de muestra de los valores que calculan
numeric_expr1
ynumeric_expr2
. EXACT_COUNT_DISTINCT(field)
- Muestra el número exacto de valores no NULOS distintos para el campo especificado. Para mejorar la escalabilidad y el rendimiento, usa COUNT(DISTINCT field).
FIRST(expr)
- Muestra el primer valor secuencial dentro del alcance de la función.
GROUP_CONCAT('str' [, separator])
-
Concatena varias strings en una sola, en la que cada valor está separado por el parámetro opcional
separator
. Si se omiteseparator
, BigQuery muestra una string separada por comas.Si una string en los datos de origen contiene un carácter de comillas dobles,
GROUP_CONCAT
muestra la string con comillas dobles agregadas. Por ejemplo, la stringa"b
se mostraría como"a""b"
. UsaGROUP_CONCAT_UNQUOTED
si prefieres que estas strings no se muestren con comillas dobles agregadas.Ejemplo:
#legacySQL SELECT GROUP_CONCAT(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])
-
Concatena varias strings en una sola, en la que cada valor está separado por el parámetro opcional
separator
. Si se omiteseparator
, BigQuery muestra una string separada por comas.A diferencia de
GROUP_CONCAT
, esta función no agregará comillas dobles a los valores mostrados que incluyan un carácter de comillas dobles. Por ejemplo, la stringa"b
se mostraría comoa"b
.Ejemplo:
#legacySQL SELECT GROUP_CONCAT_UNQUOTED(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
LAST(field)
- Muestra el último valor secuencial en el alcance de la función.
MAX(field)
- Muestra el valor máximo en el alcance de la función.
MIN(field)
- Muestra el valor mínimo en el alcance de la función.
NEST(expr)
-
Agrega todos los valores dentro del alcance de agregación actual a un campo repetido. Por ejemplo, la consulta
"SELECT x, NEST(y) FROM ... GROUP BY x"
muestra un registro de salida para cada valorx
distinto y contiene un campo repetido para todos los valoresy
sincronizados conx
en la entrada de consulta. La funciónNEST
requiere una cláusulaGROUP BY
.BigQuery aplana de forma automática los resultados de la consulta, por lo que si usas la función
NEST
en la consulta de nivel superior, los resultados no contendrán campos repetidos. Usa la funciónNEST
cuando uses una subselección que produce resultados intermedios para el uso inmediato de la misma consulta. NTH(n, field)
- Muestra el valor secuencial
n
th en el alcance de la función, donden
es una constante. La funciónNTH
comienza a contar desde 1, por lo que no hay ningún término cero. Si el alcance de la función tiene menos den
valores, la función mostraráNULL
. QUANTILES(expr[, buckets])
-
Calcula el mínimo, el máximo y los cuantiles aproximados para la expresión de entrada. Los valores de entrada
NULL
se ignoran. Las entradas vacías o exclusivamenteNULL
dan como resultado una salidaNULL
. La cantidad de cuantiles calculados se controla con el parámetrobuckets
opcional, que incluye el mínimo y el máximo en el recuento. Para calcular los N-tiles aproximados, usabuckets
N+1. El valor predeterminado debuckets
es 100. (Nota: El valor predeterminado de 100 no estima los percentiles. Para estimar percentiles, usa 101buckets
como mínimo). Si se especifica de manera explícita, el valor debuckets
debe ser 2 como mínimo.El error fraccional por cuantil es épsilon = 1/
buckets
, lo que significa que el error disminuye a medida que aumenta la cantidad de depósitos. Por ejemplo:QUANTILES(<expr>, 2) # computes min and max with 50% error. QUANTILES(<expr>, 3) # computes min, median, and max with 33% error. QUANTILES(<expr>, 5) # computes quartiles with 25% error. QUANTILES(<expr>, 11) # computes deciles with 10% error. QUANTILES(<expr>, 21) # computes vigintiles with 5% error. QUANTILES(<expr>, 101) # computes percentiles with 1% error.
La función
NTH
se puede usar para seleccionar un cuantil particular, pero recuerda queNTH
se basa en 1 y queQUANTILES
muestra el mínimo (cuantil “0”) en la primera posición y el máximo (percentil “100” o “Nth” N-tile) en la última posición. Por ejemplo,NTH(11, QUANTILES(expr, 21))
estima la mediana deexpr
, mientras queNTH(20, QUANTILES(expr, 21))
estima el vigintil 19 (percentil 95) deexpr
. Ambas estimaciones tienen un margen de error del 5%.Para mejorar la precisión, usa más depósitos. Por ejemplo, a fin de reducir el margen de error para los cálculos anteriores del 5% al 0.1%, usa 1,001 depósitos en lugar de 21 y ajusta el argumento a la función
NTH
según corresponda. A fin de calcular la mediana con un error del 0.1%, usaNTH(501, QUANTILES(expr, 1001))
. Para el percentil 95 con un error del 0.1%, usaNTH(951, QUANTILES(expr, 1001))
. STDDEV(numeric_expr)
- Muestra la desviación estándar de los valores que calcula
numeric_expr
. Las filas con un valor NULO no se incluyen en el cálculo. La funciónSTDDEV
es un alias paraSTDDEV_SAMP
. STDDEV_POP(numeric_expr)
- Calcula la desviación estándar de la población del valor que calcula
numeric_expr
. UsaSTDDEV_POP()
para calcular la desviación estándar de un conjunto de datos que abarque toda la población de interés. UsaSTDDEV_SAMP()
si tu conjunto de datos comprende solo una muestra representativa de la población. Para obtener más información sobre la desviación estándar de muestra frente a la de población, consulta Desviación estándar en Wikipedia. STDDEV_SAMP(numeric_expr)
- Calcula la desviación estándar de muestra del valor que calcula
numeric_expr
. UsaSTDDEV_SAMP()
para calcular la desviación estándar de una población completa según una muestra representativa de la población. UsaSTDDEV_POP()
si tu conjunto de datos abarca toda la población. Para obtener más información sobre la desviación estándar de muestra frente a la de población, consulta Desviación estándar en Wikipedia. SUM(field)
- Muestra la suma total de los valores en el alcance de la función. Solo para usarse con tipos de datos numéricos.
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
- Muestra los registros principales max_records según la frecuencia. Consulta la descripción de TOP a continuación para obtener más detalles.
UNIQUE(expr)
- Muestra el conjunto de valores únicos no NULO en el alcance de la función en un orden indefinido. Similar a una cláusula
GROUP BY
grande sin la palabra claveEACH
, la consulta fallará y mostrará el error “Resources Exceeded” si hay demasiados valores distintos. Sin embargo, a diferencia deGROUP BY
, la funciónUNIQUE
se puede aplicar con agregación con alcance, lo que permite una operación eficiente en campos anidados con una cantidad limitada de valores. VARIANCE(numeric_expr)
- Calcula la varianza de los valores que calcula
numeric_expr
. Las filas con un valor NULO no se incluyen en el cálculo. La funciónVARIANCE
es un alias paraVAR_SAMP
. VAR_POP(numeric_expr)
- Calcula la varianza de población de los valores que calcula
numeric_expr
. Para obtener más información sobre la desviación estándar de muestra frente a la de población, consulta Desviación estándar en Wikipedia. VAR_SAMP(numeric_expr)
- Calcula la varianza de muestra de los valores que calcula
numeric_expr
. Para obtener más información sobre la desviación estándar de muestra frente a la de población, consulta Desviación estándar en Wikipedia.
Función TOP()
TOP es una función alternativa a la cláusula GROUP BY. Se usa como sintaxis simplificada para GROUP BY ... ORDER BY ... LIMIT ...
. Por lo general, la función TOP opera más rápido que la consulta ... GROUP BY ... ORDER BY ... LIMIT ...
completa, pero es posible que solo muestre resultados aproximados. La siguiente es la sintaxis de la función TOP:
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
Cuando uses TOP en una cláusula SELECT
, debes incluir COUNT(*)
como uno de los campos.
Una consulta que usa la función TOP() puede mostrar solo dos campos: el campo TOP y el valor COUNT(*).
field|alias
- Es el campo o alias que se mostrará.
max_values
- Es la cantidad máxima de resultados que se mostrarán (opcional). El valor predeterminado es 20.
multiplier
- Es un número entero positivo que aumenta los valores que muestra
COUNT(*)
por el múltiplo especificado.
Ejemplos de TOP()
-
Consultas de ejemplo básicas que usan
TOP()
Las siguientes consultas usan
TOP()
para mostrar 10 filas.Ejemplo 1:
#legacySQL SELECT TOP(word, 10) as word, COUNT(*) as cnt FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th";
Ejemplo 2:
#legacySQL SELECT word, left(word, 3) FROM (SELECT TOP(word, 10) AS word, COUNT(*) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th");
-
Compara
TOP()
conGROUP BY...ORDER BY...LIMIT
La consulta muestra, en orden, las 10 palabras usadas con mayor frecuencia que contienen “th” y la cantidad de documentos en los que se usaron las palabras. La consulta
TOP
se ejecutará con mayor rapidez:Ejemplo sin
TOP()
:#legacySQL SELECT word, COUNT(*) AS cnt FROM ds.Table WHERE word CONTAINS 'th' GROUP BY word ORDER BY cnt DESC LIMIT 10;
Ejemplo con
TOP()
:#legacySQL SELECT TOP(word, 10), COUNT(*) FROM ds.Table WHERE word contains 'th';
-
Usa el parámetro
multiplier
.Las siguientes consultas muestran cómo el parámetro
multiplier
afecta el resultado de la consulta. La primera consulta muestra la cantidad de nacimientos por mes en Wyoming. La segunda consulta usa el parámetromultiplier
para multiplicar los valores decnt
por 100.Ejemplo sin el parámetro
multiplier
:#legacySQL SELECT TOP(month,3) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
Muestra:
+-------+-------+ | month | cnt | +-------+-------+ | 7 | 19594 | | 5 | 19038 | | 8 | 19030 | +-------+-------+
Ejemplo con el parámetro
multiplier
:#legacySQL SELECT TOP(month,3,100) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
Muestra:
+-------+---------+ | month | cnt | +-------+---------+ | 7 | 1959400 | | 5 | 1903800 | | 8 | 1903000 | +-------+---------+
Nota: Debes incluir COUNT(*)
en la cláusula SELECT
para usar TOP
.
Ejemplos avanzados
-
Desviación promedio y estándar agrupadas por condición
La siguiente consulta muestra la desviación promedio y estándar del peso de los bebés al momento del nacimiento en Ohio en 2003, agrupados por madres que fuman y no fuman.
Ejemplo:
#legacySQL SELECT cigarette_use, /* Finds average and standard deviation */ AVG(weight_pounds) baby_weight, STDDEV(weight_pounds) baby_weight_stdev, AVG(mother_age) mother_age FROM [bigquery-public-data:samples.natality] WHERE year=2003 AND state='OH' /* Group the result values by those */ /* who smoked and those who didn't. */ GROUP BY cigarette_use;
-
Filtra los resultados de la consulta con un valor agregado
Para filtrar los resultados de la consulta mediante un valor agregado (por ejemplo, con el valor de una
SUM
), usa la funciónHAVING
.HAVING
compara un valor con un resultado determinado por una función de agregación, a diferencia deWHERE
, que opera en cada fila antes de la agregación.Ejemplo:
#legacySQL SELECT state, /* If 'is_male' is True, return 'Male', */ /* otherwise return 'Female' */ IF (is_male, 'Male', 'Female') AS sex, /* The count value is aliased as 'cnt' */ /* and used in the HAVING clause below. */ COUNT(*) AS cnt FROM [bigquery-public-data:samples.natality] WHERE state != '' GROUP BY state, sex HAVING cnt > 3000000 ORDER BY cnt DESC
Muestra:
+-------+--------+---------+ | state | sex | cnt | +-------+--------+---------+ | CA | Male | 7060826 | | CA | Female | 6733288 | | TX | Male | 5107542 | | TX | Female | 4879247 | | NY | Male | 4442246 | | NY | Female | 4227891 | | IL | Male | 3089555 | +-------+--------+---------+
Operadores aritméticos
Los operadores aritméticos toman argumentos numéricos y muestran un resultado numérico. Cada argumento puede ser un literal o un valor numérico mostrado por una consulta. Si la operación aritmética se evalúa como un resultado indefinido, la operación muestra NULL
.
Sintaxis
Operador | Descripción | Ejemplo |
---|---|---|
+ | Suma |
Muestra: 10 |
- | Resta |
Muestra: 1 |
* | Multiplicación |
Muestra: 24 |
/ | División |
Muestra: 1.5 |
% | Modulo |
Muestra: 2 |
Funciones bit a bit
Las funciones bit a bit operan a nivel de bits individuales y requieren argumentos numéricos. Para obtener más información acerca de las funciones bit a bit, consulta Operación bit a bit.
En funciones agregadas, se documentan tres funciones adicionales a nivel de bits, BIT_AND
, BIT_OR
y BIT_XOR
.
Sintaxis
Operador | Descripción | Ejemplo |
---|---|---|
& | AND bit a bit |
Muestra: 0 |
| | OR bit a bit |
Muestra: 28 |
^ | XOR bit a bit |
Muestra: 1 |
<< | Desplaza bits hacia la izquierda |
Muestra: 16 |
>> | Desplaza bits hacia la derecha |
Muestra: 2 |
~ | NOT bit a bit |
Muestra: -3 |
BIT_COUNT(<numeric_expr>) |
Muestra la cantidad de bits configurados en |
Muestra: 4 |
Funciones de conversión
Las funciones de conversión cambian el tipo de datos de una expresión numérica. Las funciones de conversión son muy útiles para garantizar que los argumentos en una función de comparación tengan el mismo tipo de datos.
Sintaxis
Funciones de conversión | |
---|---|
BOOLEAN() |
Conversión a booleano. |
BYTES() |
Conversión a bytes. |
CAST(expr AS type) |
Convierte expr en una variable de tipo type . |
FLOAT() |
Conversión al doble. |
HEX_STRING() |
Conversión a la string hexadecimal. |
INTEGER() |
Conversión a número entero. |
STRING() |
Conversión a una string. |
BOOLEAN(<numeric_expr>)
-
- Muestra
true
si<numeric_expr>
no es 0 y no es NULO. - Muestra
false
si<numeric_expr>
es 0. - Muestra
NULL
si<numeric_expr>
es NULO.
- Muestra
BYTES(string_expr)
- Muestra
string_expr
como un valor de tipobytes
. CAST(expr AS type)
- Convierte
expr
en una variable de tipotype
. FLOAT(expr)
- Muestra
expr
como un doble.expr
puede ser una string como'45.78'
, pero la función muestraNULL
para los valores no numéricos. HEX_STRING(numeric_expr)
- Muestra
numeric_expr
como una string hexadecimal. INTEGER(expr)
- Convierte
expr
en un número entero de 64 bits.- Muestra NULO si
expr
es una string que no corresponde a un número entero. - Muestra la cantidad de microsegundos desde el ciclo de entrenamiento unix si
expr
es una marca de tiempo.
- Muestra NULO si
STRING(numeric_expr)
- Muestra
numeric_expr
como una string.
Funciones de comparación
Las funciones de comparación muestran true
o false
, según los siguientes tipos de comparaciones:
- Una comparación de dos expresiones
- Una comparación de una expresión o conjunto de expresiones con criterios específicos, como estar en una lista específica, ser NULO o ser un valor opcional no predeterminado
Algunas de las funciones detalladas a continuación muestran valores que no son true
ni false
, pero los valores que muestran se basan en operaciones de comparación.
Puedes usar expresiones numéricas o de strings como argumentos para las funciones de comparación. (Las constantes de string deben estar entre comillas simples o dobles). Las expresiones pueden ser literales o valores recuperados por una consulta. Las funciones de comparación se usan con más frecuencia como condiciones de filtrado en las cláusulas WHERE
, pero se pueden usar en otras cláusulas.
Sintaxis
Funciones de comparación | |
---|---|
expr1 = expr2 |
Muestra true si las expresiones son iguales. |
expr1 != expr2 expr1 <> expr2
|
Muestra true si las expresiones no son iguales. |
expr1 > expr2 |
Muestra true si expr1 es mayor que expr2 . |
expr1 < expr2 |
Muestra true si expr1 es menor que expr2 . |
expr1 >= expr2 |
Muestra true si expr1 es mayor o igual que expr2 . |
expr1 <= expr2 |
Muestra true si expr1 es menor o igual que expr2 . |
expr1 BETWEEN expr2 AND expr3 |
Muestra true si el valor de expr1 está entre expr2 y expr3 , inclusive. |
expr IS NULL |
Muestra true si expr es NULO. |
expr IN() |
Muestra true si expr coincide con expr1 , expr2 o cualquier valor entre paréntesis. |
COALESCE() |
Muestra el primer argumento que no es NULO. |
GREATEST() |
Muestra el parámetro numeric_expr más grande. |
IFNULL() |
Muestra el argumento si no es nulo. |
IS_INF() |
Muestra true si es infinito positivo o negativo. |
IS_NAN() |
Muestra true si el argumento es NaN . |
IS_EXPLICITLY_DEFINED() |
obsoleta: Usa expr IS NOT NULL en su lugar. |
LEAST() |
Muestra el parámetro numeric_expr más pequeño del argumento. |
NVL() |
Si expr no es nulo, muestra expr ; de lo contrario, muestra null_default . |
expr1 = expr2
- Muestra
true
si las expresiones son iguales. expr1 != expr2
expr1 <> expr2
- Muestra
true
si las expresiones no son iguales. expr1 > expr2
- Muestra
true
siexpr1
es mayor queexpr2
. expr1 < expr2
- Muestra
true
siexpr1
es menor queexpr2
. expr1 >= expr2
- Muestra
true
siexpr1
es mayor o igual queexpr2
. expr1 <= expr2
- Muestra
true
siexpr1
es menor o igual queexpr2
. expr1 BETWEEN expr2 AND expr3
-
Muestra
true
si el valor deexpr1
es mayor o igual queexpr2
y menor o igual queexpr3
. expr IS NULL
- Muestra
true
siexpr
es NULO. expr IN(expr1, expr2, ...)
- Muestra
true
siexpr
coincide conexpr1
,expr2
o cualquier valor entre paréntesis. La palabra claveIN
es una abreviatura eficiente de(expr = expr1 || expr = expr2 || ...)
. Las expresiones utilizadas con la palabra claveIN
deben ser constantes y deben coincidir con el tipo de datos deexpr
. También se puede usar la cláusulaIN
para crear semiuniones y antiuniones. Para obtener más información, consulta Semiunión y antiunión. COALESCE(<expr1>, <expr2>, ...)
- Muestra el primer argumento que no es NULO.
GREATEST(numeric_expr1, numeric_expr2, ...)
-
Muestra el parámetro
numeric_expr
más grande. Todos los parámetros deben ser numéricos y del mismo tipo. Si algún parámetro esNULL
, esta función muestraNULL
.Para ignorar los valores
NULL
, usa la funciónIFNULL
a fin de cambiar los valoresNULL
por uno que no afecte la comparación. En el siguiente código de ejemplo, se usa la funciónIFNULL
para cambiar los valoresNULL
a-1
, que no afecta la comparación entre números positivos.SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(expr, null_default)
- Si
expr
no es nulo, muestraexpr
; de lo contrario, muestranull_default
. IS_INF(numeric_expr)
- Muestra
true
sinumeric_expr
es infinito positivo o negativo. IS_NAN(numeric_expr)
- Muestra
true
sinumeric_expr
es el valor numérico especialNaN
. IS_EXPLICITLY_DEFINED(expr)
-
Esta función es obsoleta. Utiliza
expr IS NOT NULL
en lugar de esta función. LEAST(numeric_expr1, numeric_expr2, ...)
-
Muestra el parámetro
numeric_expr
más pequeño. Todos los parámetros deben ser numéricos y del mismo tipo. Si algún parámetro esNULL
, esta función muestraNULL
. NVL(expr, null_default)
- Si
expr
no es nulo, muestraexpr
; de lo contrario, muestranull_default
. La funciónNVL
es un alias paraIFNULL
.
Funciones de fecha y hora
Las siguientes funciones permiten la manipulación de fecha y hora para las strings de fecha, los tipos de datos TIMESTAMP y las marcas de tiempo de UNIX. Para obtener más información sobre cómo trabajar con el tipo de datos TIMESTAMP, consulta el Uso de TIMESTAMP.
Las funciones de fecha y hora que funcionan con las marcas de tiempo de UNIX operan en la hora de UNIX. Las funciones de fecha y hora muestran valores basados en la zona horaria UTC.
Sintaxis
Funciones de fecha y hora | |
---|---|
CURRENT_DATE() |
Muestra la fecha actual en el formato %Y-%m-%d . |
CURRENT_TIME() |
Muestra la hora actual del servidor en el formato %H:%M:%S . |
CURRENT_TIMESTAMP() |
Muestra la hora actual del servidor en el formato %Y-%m-%d %H:%M:%S . |
DATE() |
Muestra la fecha con el formato %Y-%m-%d . |
DATE_ADD() |
Agrega el intervalo especificado a un tipo de datos TIMESTAMP. |
DATEDIFF() |
Muestra el número de días entre dos tipos de datos TIMESTAMP. |
DAY() |
Muestra el día del mes como un número entero entre 1 y 31. |
DAYOFWEEK() |
Muestra el día de la semana como un número entero entre 1 (domingo) y 7 (sábado). |
DAYOFYEAR() |
Muestra el día del año como un número entero entre 1 y 366. |
FORMAT_UTC_USEC() |
Muestra una marca de tiempo UNIX en el formato YYYY-MM-DD HH:MM:SS.uuuuuu . |
HOUR() |
Muestra la hora de TIMESTAMP como un número entero entre 0 y 23. |
MINUTE() |
Muestra los minutos de una TIMESTAMP como un número entero entre 0 y 59. |
MONTH() |
Muestra el mes de una TIMESTAMP como un número entero entre 1 y 12. |
MSEC_TO_TIMESTAMP() |
Convierte una marca de tiempo UNIX en milisegundos en una TIMESTAMP. |
NOW() |
Muestra la marca de tiempo de UNIX actual en microsegundos. |
PARSE_UTC_USEC() |
Convierte una string de fecha en una marca de tiempo UNIX en microsegundos. |
QUARTER() |
Muestra el trimestre del año de una TIMESTAMP como un número entero entre 1 y 4. |
SEC_TO_TIMESTAMP() |
Convierte una marca de tiempo UNIX en segundos en una TIMESTAMP. |
SECOND() |
Muestra los segundos de una TIMESTAMP como un número entero entre 0 y 59. |
STRFTIME_UTC_USEC() |
Muestra una string de fecha en el formato date_format_str. |
TIME() |
Muestra un TIMESTAMP con el formato %H:%M:%S . |
TIMESTAMP() |
Convierte una string de fecha en un TIMESTAMP. |
TIMESTAMP_TO_MSEC() |
Convierte una TIMESTAMP en una marca de tiempo UNIX en milisegundos. |
TIMESTAMP_TO_SEC() |
Convierte una TIMESTAMP en una marca de tiempo UNIX en segundos. |
TIMESTAMP_TO_USEC() |
Convierte una TIMESTAMP en una marca de tiempo UNIX en microsegundos. |
USEC_TO_TIMESTAMP() |
Convierte una marca de tiempo UNIX en microsegundos en una TIMESTAMP. |
UTC_USEC_TO_DAY() |
Desplaza una marca de tiempo UNIX en microsegundos al comienzo del día en que se produce. |
UTC_USEC_TO_HOUR() |
Desplaza una marca de tiempo UNIX en microsegundos al comienzo de la hora en que se produce. |
UTC_USEC_TO_MONTH() |
Desplaza una marca de tiempo UNIX en microsegundos al comienzo del mes en que se produce. |
UTC_USEC_TO_WEEK() |
Muestra una marca de tiempo UNIX en microsegundos que representa un día de la semana. |
UTC_USEC_TO_YEAR() |
Muestra una marca de tiempo UNIX en microsegundos que representa el año. |
WEEK() |
Muestra la semana de una TIMESTAMP como un número entero entre 1 y 53. |
YEAR() |
Muestra el año de un TIMESTAMP. |
CURRENT_DATE()
Muestra una string legible de la fecha actual en el formato
%Y-%m-%d
.Ejemplo:
SELECT CURRENT_DATE();
Muestra: 2013-02-01
CURRENT_TIME()
Muestra una string legible de la hora actual del servidor en el formato
%H:%M:%S
.Ejemplo:
SELECT CURRENT_TIME();
Muestra: 01:32:56
CURRENT_TIMESTAMP()
Muestra un tipo de datos TIMESTAMP de la hora actual del servidor en el formato
%Y-%m-%d %H:%M:%S
.Ejemplo:
SELECT CURRENT_TIMESTAMP();
Muestra: 2013-02-01 01:33:35 UTC
DATE(<timestamp>)
Muestra una string legible de un tipo de datos TIMESTAMP en el formato
%Y-%m-%d
.Ejemplo:
SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));
Muestra: 2012-10-01
DATE_ADD(<timestamp>,<interval>,
<interval_units>)Agrega el intervalo especificado a un tipo de datos TIMESTAMP. Los valores posibles son
interval_units
,YEAR
,MONTH
,DAY
,HOUR
,MINUTE
ySECOND
. Siinterval
es un número negativo, el intervalo se resta del tipo de datos TIMESTAMP.Ejemplo:
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");
Muestra: 2017-10-01 02:03:04 UTC
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");
Muestra: 2007-10-01 02:03:04 UTC
DATEDIFF(<timestamp1>,<timestamp2>)
Muestra el número de días entre dos tipos de datos TIMESTAMP. El resultado es positivo si el primer tipo de datos TIMESTAMP viene después del segundo tipo de datos TIMESTAMP; de lo contrario, el resultado es negativo.
Ejemplo:
SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));
Muestra: 466
Ejemplo:
SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));
Muestra: -466
DAY(<timestamp>)
Muestra el día del mes de un tipo de datos TIMESTAMP como un número entero entre 1 y 31, inclusive.
Ejemplo:
SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));
Muestra: 2
DAYOFWEEK(<timestamp>)
Muestra el día de la semana de un tipo de datos TIMESTAMP como un número entero entre 1 (domingo) y 7 (sábado), inclusive.
Ejemplo:
SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));
Muestra: 2
DAYOFYEAR(<timestamp>)
Muestra el día del año de un tipo de datos TIMESTAMP como un número entero entre 1 y 366, inclusive. El número entero 1 se refiere al 1 de enero.
Ejemplo:
SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));
Muestra: 275
FORMAT_UTC_USEC(<unix_timestamp>)
Muestra una representación de string legible de una marca de tiempo UNIX en el formato
YYYY-MM-DD HH:MM:SS.uuuuuu
.Ejemplo:
SELECT FORMAT_UTC_USEC(1274259481071200);
Muestra: 2010-05-19 08:58:01.071200
HOUR(<timestamp>)
Muestra la hora de un tipo de datos TIMESTAMP como un número entero entre 0 y 23, inclusive.
Ejemplo:
SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));
Muestra: 5
MINUTE(<timestamp>)
Muestra los minutos de un tipo de datos TIMESTAMP como un número entero entre 0 y 59, inclusive.
Ejemplo:
SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));
Muestra: 23
MONTH(<timestamp>)
Muestra el mes de un tipo de datos TIMESTAMP como un número entero entre 1 y 12, inclusive.
Ejemplo:
SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));
Muestra: 10
MSEC_TO_TIMESTAMP(<expr>)
- Convierte una marca de tiempo UNIX en milisegundos en un tipo de datos TIMESTAMP.
Ejemplo:
SELECT MSEC_TO_TIMESTAMP(1349053323000);
Muestra: 2012-10-01 01:02:03 UTC
SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)
Muestra: 2012-10-01 01:02:04 UTC
NOW()
Muestra la marca de tiempo UNIX actual en microsegundos.
Ejemplo:
SELECT NOW();
Muestra: 1359685811687920
PARSE_UTC_USEC(<date_string>)
-
Convierte una string de fecha en una marca de tiempo UNIX en microsegundos.
date_string
debe tener el formatoYYYY-MM-DD HH:MM:SS[.uuuuuu]
. La parte fraccionaria de la segunda puede tener hasta 6 dígitos o puede omitirse.TIMESTAMP_TO_USEC es una función equivalente que convierte un argumento de tipo de datos TIMESTAMP en lugar de una string de fecha.
Ejemplo:
SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");
Muestra: 1349056984000000
QUARTER(<timestamp>)
Muestra el trimestre del año de un tipo de datos TIMESTAMP como un número entero entre 1 y 4, inclusive.
Ejemplo:
SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));
Muestra: 4
SEC_TO_TIMESTAMP(<expr>)
Convierte una marca de tiempo de UNIX en segundos a un tipo de datos TIMESTAMP.
Ejemplo:
SELECT SEC_TO_TIMESTAMP(1355968987);
Muestra: 2012-12-20 02:03:07 UTC
SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));
Muestra: 2012-12-20 02:03:07 UTC
SECOND(<timestamp>)
-
Muestra los segundos de un tipo de datos TIMESTAMP como un número entero entre 0 y 59, inclusive.
Durante un segundo bisiesto, el rango de números enteros está entre 0 y 60, inclusive.
Ejemplo:
SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));
Muestra: 48
STRFTIME_UTC_USEC(<unix_timestamp>,
<date_format_str>)-
Muestra una string de fecha legible en el formato date_format_str. En date_format_str, se pueden incluir caracteres de puntuación relacionados con la fecha (como date_format_str y date_format_str) y caracteres especiales aceptados por la función strftime en C++ (como date_format_str para el día del mes).
Usa las funciones
UTC_USEC_TO_<function_name>
si planeas agrupar datos de consulta por intervalos de tiempo, como obtener todos los datos de un mes determinado, porque las funciones son más eficientes.Ejemplo:
SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");
Muestra: 2010-05-19
TIME(<timestamp>)
Muestra una string legible de un tipo de datos TIMESTAMP en el formato
%H:%M:%S
.Ejemplo:
SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));
Muestra: 02:03:04
TIMESTAMP(<date_string>)
Convierte una string de fecha en un tipo de datos TIMESTAMP.
Ejemplo:
SELECT TIMESTAMP("2012-10-01 01:02:03");
Muestra: 2012-10-01 01:02:03 UTC
TIMESTAMP_TO_MSEC(<timestamp>)
Convierte un tipo de datos TIMESTAMP en una marca de tiempo de UNIX en milisegundos.
Ejemplo:
SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));
Muestra: 1349053323000
TIMESTAMP_TO_SEC(<timestamp>)
- Convierte un tipo de datos TIMESTAMP en una marca de tiempo UNIX en segundos.
Ejemplo:
SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));
Muestra: 1349053323
TIMESTAMP_TO_USEC(<timestamp>)
-
Convierte un tipo de datos TIMESTAMP en una marca de tiempo de UNIX en microsegundos.
PARSE_UTC_USEC es una función equivalente que convierte un argumento de string de datos en lugar de un tipo de datos TIMESTAMP.
Ejemplo:
SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));
Muestra: 1349053323000000
USEC_TO_TIMESTAMP(<expr>)
Convierte una marca de tiempo de UNIX en microsegundos en un tipo de datos TIMESTAMP.
Ejemplo:
SELECT USEC_TO_TIMESTAMP(1349053323000000);
Muestra: 2012-10-01 01:02:03 UTC
SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)
Muestra: 2012-10-01 01:02:04 UTC
UTC_USEC_TO_DAY(<unix_timestamp>)
-
Desplaza una marca de tiempo UNIX en microsegundos al comienzo del día en que se produce.
Por ejemplo, si
unix_timestamp
ocurre el 19 de mayo a las 08:58, esta función muestra una marca de tiempo UNIX para el 19 de mayo a las 00:00 (medianoche).Ejemplo:
SELECT UTC_USEC_TO_DAY(1274259481071200);
Muestra: 1274227200000000
UTC_USEC_TO_HOUR(<unix_timestamp>)
-
Desplaza una marca de tiempo UNIX en microsegundos al comienzo de la hora en que se produce.
Por ejemplo, si
unix_timestamp
aparece a las 08:58, esta función muestra una marca de tiempo UNIX para las 08:00 del mismo día.Ejemplo:
SELECT UTC_USEC_TO_HOUR(1274259481071200);
Muestra: 1274256000000000
UTC_USEC_TO_MONTH(<unix_timestamp>)
-
Desplaza una marca de tiempo UNIX en microsegundos al comienzo del mes en que se produce.
Por ejemplo, si
unix_timestamp
ocurre el 19 de marzo, esta función muestra una marca de tiempo UNIX para el 1 de marzo del mismo año.Ejemplo:
SELECT UTC_USEC_TO_MONTH(1274259481071200);
Muestra: 1272672000000000
UTC_USEC_TO_WEEK(<unix_timestamp>,
<day_of_week>)-
Muestra una marca de tiempo UNIX en microsegundos que representa un día en la semana del argumento
unix_timestamp
. Esta función toma dos argumentos: una marca de tiempo UNIX en microsegundos y un día de la semana de 0 (domingo) a 6 (sábado).Por ejemplo, si
unix_timestamp
aparece el viernes 11 de abril de 2008 y establecesday_of_week
en 2 (martes), la función muestra una marca de tiempo UNIX del martes 8 de abril de 2008.Ejemplo:
SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;
Muestra: 1207612800000000
UTC_USEC_TO_YEAR(<unix_timestamp>)
-
Muestra una marca de tiempo UNIX en microsegundos que representa el año del argumento
unix_timestamp
.Por ejemplo, si
unix_timestamp
ocurre en 2010, la función muestra1274259481071200
, la representación de microsegundos de2010-01-01 00:00
.Ejemplo:
SELECT UTC_USEC_TO_YEAR(1274259481071200);
Muestra: 1262304000000000
WEEK(<timestamp>)
Muestra la semana de un tipo de datos TIMESTAMP como un número entero entre 1 y 53, inclusive.
Las semanas comienzan el domingo, así que si el 1 de enero no es un domingo, la semana 1 tiene menos de 7 días y el primer domingo del año es el primer día de la semana 2.
Ejemplo:
SELECT WEEK(TIMESTAMP('2014-12-31'));
Muestra: 53
YEAR(<timestamp>)
- Muestra el año de un tipo de datos TIMESTAMP.
Ejemplo:
SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));
Muestra: 2012
Ejemplos avanzados
-
Conversión de los resultados de la marca de tiempo de número entero en un formato legible
La siguiente consulta encuentra los 5 mejores momentos en el tiempo en que se realizaron más revisiones de Wikipedia. Para mostrar los resultados en un formato legible, usa la función
FORMAT_UTC_USEC()
de BigQuery, que toma una marca de tiempo, en microsegundos, como entrada. Esta consulta multiplica las marcas de tiempo del formato POSIX de Wikipedia (en segundos) por 1,000,000 para convertir el valor en microsegundos.Ejemplo:
#legacySQL SELECT /* Multiply timestamp by 1000000 and convert */ /* into a more human-readable format. */ TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5) AS top_revision_time, COUNT (*) AS revision_count FROM [bigquery-public-data:samples.wikipedia];
Muestra:
+----------------------------+----------------+ | top_revision_time | revision_count | +----------------------------+----------------+ | 2002-02-25 15:51:15.000000 | 20976 | | 2002-02-25 15:43:11.000000 | 15974 | | 2010-02-02 03:34:51.000000 | 3 | | 2010-02-02 01:04:59.000000 | 3 | | 2010-02-01 23:55:05.000000 | 3 | +----------------------------+----------------+
-
Agrupamiento de resultados por marca de tiempo
Usar las funciones de fecha y hora para agrupar los resultados de las consultas en depósitos que corresponden a años, meses o días particulares puede resultar de utilidad. En el siguiente ejemplo, se usa la función
UTC_USEC_TO_MONTH()
para mostrar la cantidad de caracteres que cada colaborador de Wikipedia usa en sus comentarios de revisión por mes.Ejemplo:
#legacySQL SELECT contributor_username, /* Return the timestamp shifted to the * start of the month, formatted in * a human-readable format. Uses the * 'LEFT()' string function to return only * the first 7 characters of the formatted timestamp. */ LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_MONTH(timestamp * 1000000)),7) AS month, SUM(LENGTH(comment)) as total_chars_used FROM [bigquery-public-data:samples.wikipedia] WHERE (contributor_username != '' AND contributor_username IS NOT NULL) AND timestamp > 1133395200 AND timestamp < 1157068800 GROUP BY contributor_username, month ORDER BY total_chars_used DESC;
Muestra (truncada):
+--------------------------------+---------+-----------------------+ | contributor_username | month | total_chars_used | +--------------------------------+---------+-----------------------+ | Kingbotk | 2006-08 | 18015066 | | SmackBot | 2006-03 | 7838365 | | SmackBot | 2006-05 | 5148863 | | Tawkerbot2 | 2006-05 | 4434348 | | Cydebot | 2006-06 | 3380577 | etc ...
Funciones de IP
Las funciones de IP convierten las direcciones IP en formatos de fácil lectura y desde ellos.
Sintaxis
Funciones de IP | |
---|---|
FORMAT_IP() |
Convierte 32 bits menos significativos de integer_value en una string de dirección IPv4 legible. |
PARSE_IP() |
Convierte una string que representa la dirección IPv4 en un valor entero sin firma. |
FORMAT_PACKED_IP() |
Muestra una dirección IP legible con el formato 10.1.5.23 o 2620:0:1009:1:216:36ff:feef:3f . |
PARSE_PACKED_IP() |
Muestra una dirección IP en BYTES. |
FORMAT_IP(integer_value)
- Convierte 32 bits menos significativos de
integer_value
en una string de dirección IPv4 legible. Por ejemplo,FORMAT_IP(1)
mostrará la string'0.0.0.1'
. PARSE_IP(readable_ip)
- Convierte una string que representa la dirección IPv4 en un número entero sin firma. Por ejemplo,
PARSE_IP('0.0.0.1')
mostrará1
. Si la string no es una dirección IPv4 válida,PARSE_IP
mostraráNULL
.
BigQuery admite la escritura de direcciones IPv4 e IPv6 en strings empaquetadas, como datos binarios de 4 o 16 bytes en orden de bytes de red. Las funciones que se describen a continuación son compatibles con el análisis de las direcciones en formatos de fácil lectura y desde ellos. Estas solo funcionan en campos de string con direcciones IP.
Sintaxis
FORMAT_PACKED_IP(packed_ip)
Muestra una dirección IP legible con el formato
10.1.5.23
o2620:0:1009:1:216:36ff:feef:3f
. Ejemplos:FORMAT_PACKED_IP('0123456789@ABCDE')
muestra'3031:3233:3435:3637:3839:4041:4243:4445'
FORMAT_PACKED_IP('0123')
muestra'48.49.50.51'
PARSE_PACKED_IP(readable_ip)
Muestra una dirección IP en BYTES. Si la string de entrada no es una dirección IPv4 o IPv6 válida,
PARSE_PACKED_IP
mostraráNULL
. Ejemplos:PARSE_PACKED_IP('48.49.50.51')
muestra'MDEyMw=='
PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445')
muestra'MDEyMzQ1Njc4OUBBQkNERQ=='
Funciones JSON
Las funciones JSON de BigQuery te permiten encontrar valores dentro de tus datos JSON almacenados mediante expresiones similares a JSONPath.
El almacenamiento de datos JSON puede ser más flexible que la declaración de todos los campos individuales en el esquema de tu tabla, pero puede incurrir en costos más altos. Cuando seleccionas datos de una string JSON, se te cobra por analizar toda la string, esto es más costoso que si cada campo se encuentra en una columna distinta. La consulta también es más lenta, ya que debe analizarse la string completa al momento de la consulta. Pero para esquemas ad-hoc o que cambian con rapidez, la flexibilidad de JSON puede valer el costo adicional.
Usa las funciones JSON en lugar de las funciones de expresión regular de BigQuery si trabajas con datos estructurados, ya que las funciones JSON son más fáciles de usar.
Sintaxis
Funciones JSON | |
---|---|
JSON_EXTRACT() |
Selecciona un valor de acuerdo con la expresión JSONPath y muestra una string JSON. |
JSON_EXTRACT_SCALAR() |
Selecciona un valor de acuerdo con la expresión JSONPath y muestra un escalar JSON. |
JSON_EXTRACT(json, json_path)
-
Selecciona un valor en
json
según la expresión JSONPathjson_path
.json_path
debe ser una constante de string. Muestra el valor en formato de string JSON. JSON_EXTRACT_SCALAR(json, json_path)
-
Selecciona un valor en
json
según la expresión JSONPathjson_path
.json_path
debe ser una constante de string. Muestra un valor escalar JSON.
Operadores lógicos
Los operadores lógicos realizan lógica binaria o ternaria en las expresiones. La lógica binaria muestra true
o false
. La lógica ternaria admite valores NULL
y muestra true
, false
o NULL
.
Sintaxis
Operadores lógicos | |
---|---|
expr AND expr |
Muestra true si ambas expresiones son verdaderas. |
expr OR expr |
Muestra true si una o ambas expresiones son verdaderas. |
NOT expr |
Muestra true si la expresión es falsa. |
expr AND expr
- Muestra
true
si ambas expresiones son verdaderas. - Muestra
false
si una o ambas expresiones son falsas. - Muestra
NULL
si ambas expresiones son NULO o si una expresión es verdadera y la otra es NULO.
- Muestra
expr OR expr
- Muestra
true
si una o ambas expresiones son verdaderas. - Muestra
false
si ambas expresiones son falsas. - Muestra
NULL
si ambas expresiones son NULO o si una expresión es falsa y la otra es NULO.
- Muestra
NOT expr
- Muestra
true
si la expresión es falsa. - Muestra
false
si la expresión es verdadera. - Muestra
NULL
si la expresión es NULO.
Puedes usar
NOT
con otras funciones como un operador de negación. Por ejemplo,NOT IN(expr1, expr2)
oIS NOT NULL
.- Muestra
Funciones matemáticas
Las funciones matemáticas toman argumentos numéricos y muestran un resultado numérico. Cada argumento puede ser un literal o un valor numérico mostrado por una consulta. Si la función matemática se evalúa como un resultado indefinido, la operación muestra NULL
.
Sintaxis
Funciones matemáticas | |
---|---|
ABS() |
Muestra el valor absoluto del argumento. |
ACOS() |
Muestra el arcocoseno del argumento. |
ACOSH() |
Muestra el arcocoseno hiperbólico del argumento. |
ASIN() |
Muestra el arcoseno del argumento. |
ASINH() |
Muestra el arcoseno hiperbólico del argumento. |
ATAN() |
Muestra la arcotangente del argumento. |
ATANH() |
Muestra la arcotangente hiperbólica del argumento. |
ATAN2() |
Muestra la arcotangente de los dos argumentos. |
CEIL() |
Redondea el argumento al siguiente número entero más cercano y muestra el valor redondeado. |
COS() |
Muestra el coseno del argumento. |
COSH() |
Muestra el coseno hiperbólico del argumento. |
DEGREES() |
Convierte radianes en grados. |
EXP() |
Muestra e a la potencia del argumento. |
FLOOR() |
Redondea el argumento al número entero anterior más cercano. |
LN() LOG()
|
Muestra el logaritmo natural del argumento. |
LOG2() |
Muestra el logaritmo en base 2 del argumento. |
LOG10() |
Muestra el logaritmo en base 10 del argumento. |
PI() |
Muestra la constante π. |
POW() |
Muestra el primer argumento a la potencia del segundo argumento. |
RADIANS() |
Convierte grados en radianes. |
RAND() |
Muestra un valor flotante aleatorio en el rango 0.0 <= valor < 1.0. |
ROUND() |
Redondea el argumento hacia arriba o hacia abajo al número entero más cercano. |
SIN() |
Muestra el seno del argumento. |
SINH() |
Muestra el seno hiperbólico del argumento. |
SQRT() |
Muestra la raíz cuadrada de la expresión. |
TAN() |
Muestra la tangente del argumento. |
TANH() |
Muestra la tangente hiperbólica del argumento. |
ABS(numeric_expr)
- Muestra el valor absoluto del argumento.
ACOS(numeric_expr)
- Muestra el arcocoseno del argumento.
ACOSH(numeric_expr)
- Muestra el arcocoseno hiperbólico del argumento.
ASIN(numeric_expr)
- Muestra el arcoseno del argumento.
ASINH(numeric_expr)
- Muestra el arcoseno hiperbólico del argumento.
ATAN(numeric_expr)
- Muestra la arcotangente del argumento.
ATANH(numeric_expr)
- Muestra la arcotangente hiperbólica del argumento.
ATAN2(numeric_expr1, numeric_expr2)
- Muestra la arcotangente de los dos argumentos.
CEIL(numeric_expr)
- Redondea el argumento al siguiente número entero más cercano y muestra el valor redondeado.
COS(numeric_expr)
- Muestra el coseno del argumento.
COSH(numeric_expr)
- Muestra el coseno hiperbólico del argumento.
DEGREES(numeric_expr)
- Muestra
numeric_expr
, convertida de radianes en grados. EXP(numeric_expr)
- Muestra el resultado de elevar la constante “e”, la base del logaritmo natural, a la potencia de numeric_expr.
FLOOR(numeric_expr)
- Redondea el argumento hacia abajo al número entero más cercano y muestra el valor redondeado.
LN(numeric_expr)
LOG(numeric_expr)
- Muestra el logaritmo natural del argumento.
LOG2(numeric_expr)
- Muestra el logaritmo en base 2 del argumento.
LOG10(numeric_expr)
- Muestra el logaritmo en base 10 del argumento.
PI()
- Muestra la constante π. La función
PI()
requiere paréntesis para indicar que es una función, pero no toma argumentos entre esos paréntesis. Puedes usarPI()
como una constante con funciones matemáticas y aritméticas. POW(numeric_expr1, numeric_expr2)
- Muestra el resultado de subir
numeric_expr1
a la potencia denumeric_expr2
. RADIANS(numeric_expr)
- Muestra
numeric_expr
, convertido de grados en radianes. (Ten en cuenta que π radianes equivale a 180 grados). RAND([int32_seed])
- Muestra un valor de número de punto flotante aleatorio en el rango 0.0 <= valor < 1.0. Cada valor
int32_seed
genera siempre la misma secuencia de números aleatorios dentro de una consulta determinada, mientras no se use una cláusulaLIMIT
. Si no se especificaint32_seed
, BigQuery usa la marca de tiempo actual como valor inicial. ROUND(numeric_expr [, digits])
- Redondea el argumento hacia arriba o hacia abajo al número entero más cercano (o, si se especifica, al número de dígitos especificado) y muestra el valor redondeado.
SIN(numeric_expr)
- Muestra el seno del argumento.
SINH(numeric_expr)
- Muestra el seno hiperbólico del argumento.
SQRT(numeric_expr)
- Muestra la raíz cuadrada de la expresión.
TAN(numeric_expr)
- Muestra la tangente del argumento.
TANH(numeric_expr)
- Muestra la tangente hiperbólica del argumento.
Ejemplos avanzados
-
Consulta de cuadro de límite
La siguiente consulta muestra una colección de puntos dentro de un cuadro de límite rectangular centrado alrededor de San Francisco (37.46, -122.50).
Ejemplo:
#legacySQL SELECT year, month, AVG(mean_temp) avg_temp, MIN(min_temperature) min_temp, MAX(max_temperature) max_temp FROM [weather_geo.table] WHERE /* Return values between a pair of */ /* latitude and longitude coordinates */ lat / 1000 > 37.46 AND lat / 1000 < 37.65 AND long / 1000 > -122.50 AND long / 1000 < -122.30 GROUP BY year, month ORDER BY year, month ASC;
-
Consulta aproximada del círculo de límite
Muestra una colección de hasta 100 puntos dentro de un círculo aproximado determinado por el uso de la Ley esférica de los cosenos, centrada en Denver, Colorado (39.73, -104.98). Esta consulta usa las funciones matemáticas y trigonométricas de BigQuery, como
PI()
,SIN()
yCOS()
.Debido a que la Tierra no es una esfera absoluta y la longitud más la latitud convergen en los polos, esta consulta muestra una aproximación que puede ser útil para muchos tipos de datos.
Ejemplo:
#legacySQL SELECT distance, lat, long, temp FROM (SELECT ((ACOS(SIN(39.73756700 * PI() / 180) * SIN((lat/1000) * PI() / 180) + COS(39.73756700 * PI() / 180) * COS((lat/1000) * PI() / 180) * COS((-104.98471790 - (long/1000)) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance, AVG(mean_temp) AS temp, AVG(lat/1000) lat, AVG(long/1000) long FROM [weather_geo.table] WHERE month=1 GROUP BY distance) WHERE distance < 100 ORDER BY distance ASC LIMIT 100;
Funciones de expresión regular
BigQuery proporciona compatibilidad con expresiones regulares mediante la biblioteca re2. Consulta esa documentación para obtener información sobre su sintaxis de expresión regular.
Ten en cuenta que las expresiones regulares son coincidencias globales; para comenzar la coincidencia al principio de una palabra, debes usar el carácter ^.
Sintaxis
Funciones de expresión regular | |
---|---|
REGEXP_MATCH() |
Muestra true si el argumento coincide con la expresión regular. |
REGEXP_EXTRACT() |
Muestra la parte del argumento que coincide con el grupo de captura dentro de la expresión regular. |
REGEXP_REPLACE() |
Reemplaza una substring que coincide con una expresión regular. |
REGEXP_MATCH('str', 'reg_exp')
Muestra true si str coincide con la expresión regular. Para la coincidencia de strings sin expresiones regulares, usa CONTAINS en lugar de REGEXP_MATCH.
Ejemplo:
#legacySQL SELECT word, COUNT(word) AS count FROM [bigquery-public-data:samples.shakespeare] WHERE (REGEXP_MATCH(word,r'\w\w\'\w\w')) GROUP BY word ORDER BY count DESC LIMIT 3;
Muestra:
+-------+-------+ | word | count | +-------+-------+ | ne'er | 42 | | we'll | 35 | | We'll | 33 | +-------+-------+
REGEXP_EXTRACT('str', 'reg_exp')
Muestra la parte de str que coincide con el grupo de captura dentro de la expresión regular.
Ejemplo:
#legacySQL SELECT REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment FROM [bigquery-public-data:samples.shakespeare] GROUP BY fragment ORDER BY fragment LIMIT 3;
Muestra:
+----------+ | fragment | +----------+ | NULL | | Al'ce | | As'es | +----------+
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')
Muestra una string en la que cualquier substring de orig_str que coincida con orig_str se reemplaza con orig_str. Por ejemplo, REGEXP_REPLACE (“Hello”, “lo”, “p”) muestra Help.
Ejemplo:
#legacySQL SELECT REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word FROM [bigquery-public-data:samples.shakespeare] WHERE REGEXP_MATCH(word, r'ne\'er') GROUP BY expanded_word ORDER BY expanded_word LIMIT 5;
Muestra:
+---------------+ | expanded_word | +---------------+ | Whenever | | never | | nevertheless | | whenever | +---------------+
Ejemplos avanzados
-
Filtrada el conjunto de resultados por coincidencia de expresiones regulares
Las funciones de expresión regular de BigQuery se pueden usar a fin de filtrar los resultados en una cláusula
WHERE
y también para mostrar los resultados enSELECT
. El siguiente ejemplo combina ambos casos prácticos de expresiones regulares en una sola consulta.Ejemplo:
#legacySQL SELECT /* Replace white spaces in the title with underscores. */ REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions FROM (SELECT title, COUNT(revision_id) as revisions FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace=0 /* Match titles that start with 'G', end with * 'e', and contain at least two 'o's. */ AND REGEXP_MATCH(title, r'^G.*o.*o.*e$') GROUP BY title ORDER BY revisions DESC LIMIT 100);
-
Usa expresiones regulares en datos enteros o de número de punto flotante
Si bien las funciones de expresión regular de BigQuery solo funcionan para datos de string, es posible usar la función
STRING()
a fin de convertir datos enteros o de número de punto flotante en formato de string. En este ejemplo,STRING()
se usa para transmitir el valor de número enterocorpus_date
a una string, que luego es modificada porREGEXP_REPLACE
.Ejemplo:
#legacySQL SELECT corpus_date, /* Cast the corpus_date to a string value */ REGEXP_REPLACE(STRING(corpus_date), '^16', 'Written in the sixteen hundreds, in the year \'' ) AS date_string FROM [bigquery-public-data:samples.shakespeare] /* Cast the corpus_date to string, */ /* match values that begin with '16' */ WHERE REGEXP_MATCH(STRING(corpus_date), '^16') GROUP BY corpus_date, date_string ORDER BY date_string DESC LIMIT 5;
Funciones de string
Las funciones de string operan en datos de string. Las constantes de string deben ir entre comillas simples o dobles. Las funciones de string distinguen entre mayúsculas y minúsculas de forma predeterminada.
Puedes agregar IGNORE CASE
al final de una consulta para habilitar la coincidencia que no distingue entre mayúsculas y minúsculas. IGNORE CASE
funciona solo en caracteres ASCII y solo en el nivel superior de la consulta.
Los comodines no son compatibles con estas funciones; para la funcionalidad de expresión regular, usa funciones de expresión regular.
Sintaxis
Funciones de string | |
---|---|
CONCAT() |
Muestra la concatenación de dos o más strings, o NULO si alguno de los valores es NULO. |
expr CONTAINS 'str' |
Muestra true si expr contiene el argumento de string especificado. |
INSTR() |
Muestra el índice basado en uno del primer caso de una string. |
LEFT() |
Muestra los caracteres más a la izquierda de una string. |
LENGTH() |
Muestra la longitud de la string. |
LOWER() |
Muestra la string original con todos los caracteres en minúscula. |
LPAD() |
Inserta caracteres a la izquierda de una string. |
LTRIM() |
Quita caracteres del lado izquierdo de una string. |
REPLACE() |
Reemplaza todos los casos de una substring. |
RIGHT() |
Muestra los caracteres más a la derecha de una string. |
RPAD() |
Inserta caracteres a la derecha de una string. |
RTRIM() |
Quita los caracteres finales del lado derecho de una string. |
SPLIT() |
Divide una string en substrings repetidas. |
SUBSTR() |
Muestra una substring… |
UPPER() |
Muestra la string original con todos los caracteres en mayúsculas. |
CONCAT('str1', 'str2', '...')
str1 + str2 + ...- Muestra la concatenación de dos o más strings, o NULO si alguno de los valores es NULO. Ejemplo: si
str1
esJava
ystr2
esScript
,CONCAT
muestraJavaScript
. expr CONTAINS 'str'
- Muestra
true
siexpr
contiene el argumento de string especificado. Esta es una comparación que distingue entre mayúsculas y minúsculas. INSTR('str1', 'str2')
- Muestra el índice basado en uno de la primera aparición de str2 en str2, o muestra 0 si str2 no ocurre en str2.
LEFT('str', numeric_expr)
- Muestra los caracteres numeric_expr más a la izquierda de
str
. Si el número es más largo que str, se mostrará la string completa. Ejemplo:LEFT('seattle', 3)
muestrasea
. LENGTH('str')
- Muestra un valor numérico para la longitud de la string. Ejemplo: si
str
es'123456'
,LENGTH
muestra6
. LOWER('str')
- Muestra la string original con todos los caracteres en minúscula.
LPAD('str1', numeric_expr, 'str2')
- Rellena
str1
a la izquierda constr2
, y repitestr2
hasta que la string de resultado sea exactamentenumeric_expr
caracteres. Ejemplo:LPAD('1', 7, '?')
muestra??????1
. LTRIM('str1' [, str2])
-
Quita los caracteres del lado izquierdo de str1. Si se omite str2,
LTRIM
quita los espacios del lado izquierdo de str2. De lo contrario,LTRIM
quita cualquier carácter en str2 del lado izquierdo de str2 (distingue entre mayúsculas y minúsculas).Ejemplos:
SELECT LTRIM("Say hello", "yaS")
muestra" hello"
.SELECT LTRIM("Say hello", " ySa")
muestra"hello"
. REPLACE('str1', 'str2', 'str3')
-
Reemplaza todas las instancias de str2 dentro de str2 con str2.
RIGHT('str', numeric_expr)
- Muestra los caracteres numeric_expr más a la derecha de
str
. Si el número es más largo que la string, mostrará toda la cadena. Ejemplo:RIGHT('kirkland', 4)
muestraland
. RPAD('str1', numeric_expr, 'str2')
- Rellena
str1
a la derecha constr2
y repitestr2
hasta que la string de resultados tenga exactamente caracteresnumeric_expr
. Ejemplo:RPAD('1', 7, '?')
muestra1??????
. RTRIM('str1' [, str2])
-
Quita los caracteres finales del lado derecho de str1. Si se omite str2,
RTRIM
quita los espacios finales de str2. De lo contrario,RTRIM
quita cualquier carácter en str2 del lado derecho de str2 (distingue entre mayúsculas y minúsculas).Ejemplos:
SELECT RTRIM("Say hello", "leo")
muestra"Say h"
.SELECT RTRIM("Say hello ", " hloe")
muestra"Say"
. SPLIT('str' [, 'delimiter'])
- Divide una string en substrings repetidas. Si se especifica
delimiter
, la funciónSPLIT
dividestr
en substrings, condelimiter
como delimitador. SUBSTR('str', index [, max_len])
- Muestra una substring de
str
, a partir deindex
. Si se utiliza el parámetro opcionalmax_len
, la string que se muestra tiene un máximo demax_len
caracteres. El conteo comienza en 1, por lo que el primer carácter de la string está en la posición 1 (no en cero). Siindex
es5
, la substring comienza con el carácter 5 desde la izquierda enstr
. Siindex
es-4
, la substring comienza con el cuarto carácter de la derecha enstr
. Ejemplo:SUBSTR('awesome', -4, 4)
muestra la substringsome
. UPPER('str')
- Muestra la string original con todos los caracteres en mayúsculas.
Escapa caracteres especiales en strings
Para escapar caracteres especiales, usa uno de los siguientes métodos:
- Usa la notación
'\xDD'
, donde'\x'
va seguida de la representación hexadecimal de dos dígitos del carácter. - Usa una barra de escape delante de barras, comillas simples y comillas dobles.
- Usa secuencias estilo C (
'\a', '\b', '\f', '\n', '\r', '\t',
y'\v'
) para otros caracteres.
A continuación, se muestran algunos ejemplos de escape:
'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported
Funciones comodín de tablas
Las funciones comodín de tablas son una forma conveniente de consultar datos de un conjunto específico de tablas. Una función comodín de tablas es equivalente a una unión separada por comas de todas las tablas que coinciden con la función comodín. Cuando usas una función comodín de tablas, BigQuery solo accede y te cobra por las tablas que coinciden con el comodín. Las funciones comodín de tablas se especifican en la cláusula FROM de la consulta.
Si usas funciones comodín de tablas en una consulta, no es necesario que las funciones estén entre paréntesis. Por ejemplo, algunos de los siguientes ejemplos usan paréntesis, mientras que otros no.
Los resultados almacenados en caché no son compatibles con las consultas de varias tablas con la función comodín (incluso si está marcada la opción Usar los resultados almacenados en caché). Si ejecutas la misma consulta con comodín varias veces, se te facturará por cada consulta.
Sintaxis
Funciones comodín de tablas | |
---|---|
TABLE_DATE_RANGE() |
Consulta múltiples tablas diarias que abarcan un período. |
TABLE_DATE_RANGE_STRICT() |
Consulta varias tablas diarias que abarcan un período, sin fechas faltantes. |
TABLE_QUERY() |
Consulta tablas cuyos nombres coinciden con un predicado especificado. |
TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)
-
Consulta tablas diarias que se superponen con el intervalo de tiempo entre
<timestamp1>
y<timestamp2>
.Los nombres de tabla deben tener el siguiente formato:
<prefix><day>
, donde<day>
tiene el formatoYYYYMMDD
.Puedes usar las funciones de fecha y hora para generar los parámetros de marca de tiempo. Por ejemplo:
TIMESTAMP('2012-10-01 02:03:04')
DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')
Ejemplo: obtener tablas entre dos días
En este ejemplo, se supone que existen las siguientes tablas:
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
#legacySQL SELECT name FROM TABLE_DATE_RANGE([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27')) WHERE age >= 35
Coincide con las siguientes tablas:
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
Ejemplo: obtener tablas en un rango de dos días hasta “hoy”
En este ejemplo, se supone que existen las siguientes tablas en un proyecto llamado
myproject-1234
:- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
#legacySQL SELECT name FROM (TABLE_DATE_RANGE([myproject-1234:mydata.people], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), CURRENT_TIMESTAMP())) WHERE age >= 35
Coincide con las siguientes tablas:
- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)
-
Esta función es equivalente a
TABLE_DATE_RANGE
. La única diferencia es que si falta una tabla diaria en la secuencia,TABLE_DATE_RANGE_STRICT
falla y muestra un errorNot Found: Table <table_name>
.Ejemplo: error en la tabla faltante
En este ejemplo, se supone que existen las siguientes tablas:
- people20140325
- people20140327
#legacySQL SELECT name FROM (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27'))) WHERE age >= 35
El ejemplo anterior muestra el error “No encontrado” para la tabla “people20140326”.
TABLE_QUERY(dataset, expr)
-
Las tablas de consultas cuyos nombres coinciden con el parámetro
expr
suministrado. El parámetroexpr
debe representarse como una string y contener una expresión para evaluar. Por ejemplo,'length(table_id) < 3'
.Ejemplo: coincidencia de tablas cuyos nombres contienen “oo” y tienen una longitud mayor que 4
En este ejemplo, se supone que existen las siguientes tablas:
- mydata.boo
- mydata.fork
- mydata.ooze
- mydata.spoon
#legacySQL SELECT speed FROM (TABLE_QUERY([myproject-1234:mydata], 'table_id CONTAINS "oo" AND length(table_id) >= 4'))
Coincide con las siguientes tablas:
- mydata.ooze
- mydata.spoon
Ejemplo: coincidencias de tablas cuyos nombres comienzan con “boo”, seguidos de entre 3 y 5 dígitos numéricos
En este ejemplo, se supone que existen las siguientes tablas en un proyecto llamado
myproject-1234
:- mydata.book4
- mydata.book418
- mydata.boom12345
- mydata.boom123456789
- mydata.taboo999
#legacySQL SELECT speed FROM TABLE_QUERY([myproject-1234:mydata], 'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')
Coincide con las siguientes tablas:
- mydata.book418
- mydata.boom12345
Funciones de URL
Sintaxis
Funciones de URL | |
---|---|
HOST() |
Dada una URL, muestra el nombre de host como una string. |
DOMAIN() |
Dada una URL, muestra el dominio como una string. |
TLD() |
Dada una URL, muestra el dominio de nivel superior y cualquier dominio de país en la URL. |
HOST('url_str')
- Dada una URL, muestra el nombre de host como una string. Ejemplo: HOST('http://www.google.com:80/index.html') muestra 'www.google.com'
DOMAIN('url_str')
- Dada una URL, muestra el dominio como una string. Ejemplo: DOMAIN('http://www.google.com:80/index.html') muestra 'google.com'.
TLD('url_str')
- Dada una URL, muestra el dominio de nivel superior y cualquier dominio de país en la URL. character: TLD('http://www.google.com:80/index.html') muestra '.com'. TLD('http://www.google.co.uk:80/index.html') muestra '.co.uk'.
Notas:
- Estas funciones no realizan una búsqueda de DNS inversa, por lo que si llamas a estas funciones con una dirección IP, las funciones mostrarán segmentos de la dirección IP en lugar de segmentos del nombre del host.
- Todas las funciones de análisis de URL llevan caracteres en minúsculas. Los caracteres en mayúscula en la URL tendrán un resultado incorrecto o NULL. Considera pasar la entrada a esta función a través de LOWER() si sus datos tienen una combinación de mayúsculas y minúsculas.
Ejemplo avanzado
Análisis de nombres de dominio a partir de datos de URL
Esta consulta usa la función DOMAIN()
para mostrar los dominios más populares que aparecen como páginas principales del repositorio en GitHub. Ten en cuenta el uso de HAVING para filtrar registros mediante el resultado de la función DOMAIN()
. Esta función es útil para determinar la información del referente a partir de datos de URL.
Ejemplos:
#legacySQL SELECT DOMAIN(repository_homepage) AS user_domain, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_domain HAVING user_domain IS NOT NULL AND user_domain != '' ORDER BY activity_count DESC LIMIT 5;
Muestra:
+-----------------+----------------+ | user_domain | activity_count | +-----------------+----------------+ | github.com | 281879 | | google.com | 34769 | | khanacademy.org | 17316 | | sourceforge.net | 15103 | | mozilla.org | 14091 | +-----------------+----------------+
Para ver específicamente la información del TLD, usa la función TLD()
. Este ejemplo muestra los TLD principales que no están en una lista de ejemplos comunes.
#legacySQL SELECT TLD(repository_homepage) AS user_tld, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_tld HAVING /* Only consider TLDs that are NOT NULL */ /* or in our list of common TLDs */ user_tld IS NOT NULL AND NOT user_tld IN ('','.com','.net','.org','.info','.edu') ORDER BY activity_count DESC LIMIT 5;
Muestra:
+----------+----------------+ | user_tld | activity_count | +----------+----------------+ | .de | 22934 | | .io | 17528 | | .me | 13652 | | .fr | 12895 | | .co.uk | 9135 | +----------+----------------+
Funciones analíticas
Las funciones analíticas permiten realizar cálculos en un subconjunto específico, o “ventana”, de un conjunto de resultados. Las funciones analíticas facilitan la creación de informes que incluyen estadísticas complejas, como medias finales y totales.
Cada función analítica requiere una cláusula OVER
que especifica la parte inferior y superior del subconjunto. Los tres componentes de la cláusula OVER
(partición, orden y encuadre) proporcionan un control adicional sobre el subconjunto. La partición te permite dividir los datos de entrada en grupos lógicos que tienen una característica común. El orden te permite ordenar los resultados dentro de una partición. El enmarcado te permite crear un marco de ventana deslizante dentro de una partición que se mueve en relación con la fila actual. Puedes configurar el tamaño del marco de la ventana deslizante en función de un número de filas o un rango de valores, como un intervalo de tiempo.
#legacySQL SELECT <window_function> OVER ( [PARTITION BY <expr>] [ORDER BY <expr> [ASC | DESC]] [<window-frame-clause>] )
PARTITION BY
- Define la partición base sobre la que opera esta función.
Especifica uno o más nombres de columna separados por comas. Se creará una partición para cada conjunto distinto de valores de estas columnas, similar a una cláusula
GROUP BY
. Si se omitePARTITION BY
, la partición base es todas las filas en la entrada de la función analítica. - La cláusula
PARTITION BY
también permite que las funciones analíticas particionen datos y paralelicen la ejecución. Si deseas usar una función de ventana conallowLargeResults
, o si pretendes aplicar más uniones o agregaciones al resultado de tu función analítica, usaPARTITION BY
para paralelizar la ejecución. - No se pueden usar las cláusulas
JOIN EACH
yGROUP EACH BY
en el resultado de las funciones analíticas. Para generar resultados de consultas grandes cuando se usan funciones analíticas, debes usarPARTITION BY
. ORDER BY
- Ordena la partición. Si
ORDER BY
está ausente, no hay garantía de ningún orden de clasificación predeterminado. La clasificación se produce en el nivel de partición, antes de que se aplique cualquier cláusula de marco de ventana. Si especificas una ventanaRANGE
, debes agregar una cláusulaORDER BY
. El orden predeterminado esASC
. ORDER BY
es opcional en algunos casos, pero ciertas funciones analíticas, como rank() o dense_rank(), requieren la cláusula.- Si usas
ORDER BY
sin especificarROWS
oRANGE
,ORDER BY
implica que la ventana se extiende desde el principio de la partición hasta la fila actual. En ausencia de una cláusulaORDER BY
, la ventana es la partición completa. <window-frame-clause>
-
{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}
- Un subconjunto de la partición sobre la que se opera. Este puede ser del mismo tamaño que la partición o más pequeño. Si usas
ORDER BY
sin unwindow-frame-clause
, el marco de ventana predeterminado esRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
. Si omitesORDER BY
ywindow-frame-clause
, el marco de ventana predeterminado es la partición completa.ROWS
: Define una ventana en términos de la posición de la fila, en relación con la fila actual. Por ejemplo, para agregar una columna que muestre la suma de las 5 filas anteriores de valores de sueldo, deberías consultarSUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
. Por lo general, el conjunto de filas incluye la fila actual, pero no es necesario.RANGE
: define una ventana en términos de un rango de valores en una columna determinada en relación con el valor de esa columna en la fila actual. Solo funciona con números y fechas, en los que los valores de fecha son números enteros simples (microsegundos desde el ciclo de entrenamiento). Las filas vecinas con el mismo valor se denominan filas del mismo nivel. Las filas de pares deCURRENT ROW
se incluyen en un marco de ventana que especificaCURRENT ROW
. Por ejemplo, si especificas que el final de la ventana esCURRENT ROW
y la siguiente fila en la ventana tiene el mismo valor, se incluirá en el cálculo de la función.BETWEEN <start> AND <end>
: Un rango que incluye las filas inicial y final. El rango no necesita incluir la fila actual, pero<start>
debe ser anterior o igual a<end>
.<start>
: Especifica el desplazamiento de inicio para esta ventana, en relación con la fila actual. Se admiten las siguientes opciones: donde{UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
<expr>
es un número entero positivo,PRECEDING
indica un número de fila o un valor de intervalo precedente yFOLLOWING
indica un número de fila o un valor de intervalo.UNBOUNDED PRECEDING
significa la primera fila de la partición. Si el inicio precede a la ventana, se establecerá en la primera fila de la partición.<end>
: Especifica el desplazamiento final para esta ventana, en relación con la fila actual. Se admiten las siguientes opciones: donde{UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
<expr>
es un número entero positivo,PRECEDING
indica un número de fila o un valor de intervalo precedente yFOLLOWING
indica un número de fila o un valor de intervalo.UNBOUNDED FOLLOWING
significa la última fila de la partición. Si el final está más allá del final de la ventana, se establecerá en la última fila de la partición.
A diferencia de las funciones de agregación, que colapsan muchas filas de entrada en una fila de salida, las funciones analíticas muestran una fila de salida por cada fila de entrada.
Esta función facilita la creación de consultas que calculan totales y medias móviles. Por ejemplo, la siguiente consulta muestra un total para un pequeño conjunto de datos de cinco filas definidas por declaraciones SELECT
:
#legacySQL SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
Valor de muestra:
+------+-------+--------------+ | name | value | RunningTotal | +------+-------+--------------+ | a | 0 | 0 | | b | 1 | 1 | | c | 2 | 3 | | d | 3 | 6 | | e | 4 | 10 | +------+-------+--------------+
El siguiente ejemplo calcula una media móvil de los valores en la fila actual y la que la precede. El marco de la ventana consta de dos filas que se mueven con la fila actual.
#legacySQL SELECT name, value, AVG(value) OVER (ORDER BY value ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MovingAverage FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
Valor de muestra:
+------+-------+---------------+ | name | value | MovingAverage | +------+-------+---------------+ | a | 0 | 0.0 | | b | 1 | 0.5 | | c | 2 | 1.5 | | d | 3 | 2.5 | | e | 4 | 3.5 | +------+-------+---------------+
Sintaxis
Funciones analíticas | |
---|---|
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM() |
La misma operación que las funciones agregadas correspondientes, pero se calculan en una ventana definida por la cláusula OVER. |
CUME_DIST() |
Muestra un doble que indica la distribución acumulativa de un valor en un grupo de valores… |
DENSE_RANK() |
Muestra el rango de números enteros de un valor en un grupo de valores. |
FIRST_VALUE() |
Muestra el primer valor del campo especificado en la ventana. |
LAG() |
Te permite leer datos de una fila anterior dentro de una ventana. |
LAST_VALUE() |
Muestra el último valor del campo especificado en la ventana. |
LEAD() |
Te permite leer datos de una fila siguiente dentro de una ventana. |
NTH_VALUE() |
Muestra el valor de <expr> en la posición <n> del marco de la ventana… |
NTILE() |
Divide la ventana en el número especificado de depósitos. |
PERCENT_RANK() |
Muestra el rango de la fila actual, en relación con las otras filas de la partición. |
PERCENTILE_CONT() |
Muestra un valor interpolado que se asignaría al argumento de percentil con respecto a la ventana… |
PERCENTILE_DISC() |
Muestra el valor más cercano al percentil del argumento en la ventana. |
RANK() |
Muestra el rango de números enteros de un valor en un grupo de valores. |
RATIO_TO_REPORT() |
Muestra la proporción de cada valor en la suma de los valores. |
ROW_NUMBER() |
Muestra el número de fila actual del resultado de la consulta en la ventana. |
-
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
- Estas funciones analíticas realizan la misma operación que las funciones agregadas correspondientes, pero se calculan sobre una ventana definida por la cláusula OVER.
Otra diferencia significativa es que la función
COUNT([DISTINCT] field)
produce resultados exactos cuando se usa como una función analítica, con un comportamiento similar a la función agregadaEXACT_COUNT_DISTINCT()
.En la consulta de ejemplo, la cláusula
ORDER BY
hace que la ventana se calcule desde el inicio de la partición hasta la fila actual, lo que genera una suma acumulativa para ese año.#legacySQL SELECT corpus_date, corpus, word_count, SUM(word_count) OVER ( PARTITION BY corpus_date ORDER BY word_count) annual_total FROM [bigquery-public-data:samples.shakespeare] WHERE word='love' ORDER BY corpus_date, word_count
Muestra:
corpus_date corpus word_count annual_total 0 various 37 37 0 sonnets 157 194 1,590 2kinghenryvi 18 18 1,590 1kinghenryvi 24 42 1,590 3kinghenryvi 40 82 CUME_DIST()
-
Muestra un doble que indica la distribución acumulativa de un valor en un grupo de valores, calculado mediante la fórmula
<number of rows preceding or tied with the current row> / <total rows>
. Los valores empatados muestran el mismo valor de distribución acumulativa.Esta función analítica requiere
ORDER BY
en la cláusulaOVER
.#legacySQL SELECT word, word_count, CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Muestra:
word word_count cume_dist pañuelo 29 0.2 satisfacción 5 0.4 desagrado 4 0.8 instrumentos 4 0.8 circunstancia 3 1.0 DENSE_RANK()
-
Muestra el rango de números enteros de un valor en un grupo de valores. El rango se calcula en función de comparaciones con otros valores del grupo.
Los valores empatados se muestran como el mismo rango. El rango del siguiente valor se incrementa en 1. Por ejemplo, si dos valores empatan para el rango 2, el siguiente valor clasificado es 3. Si prefieres un espacio en la lista de clasificación, usa rank().
Esta función analítica requiere
ORDER BY
en la cláusulaOVER
. Devuelve:#legacySQL SELECT word, word_count, DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count dense_rank pañuelo 29 1 satisfacción 5 2 desagrado 4 3 instrumentos 4 3 circunstancia 3 4 FIRST_VALUE(<field_name>)
-
Muestra el primer valor de
<field_name>
en la ventana. Devuelve:#legacySQL SELECT word, word_count, FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
word word_count fv imperfectly 1 imperfectly LAG(<expr>[, <offset>[, <default_value>]])
-
Te permite leer datos de una fila anterior dentro de una ventana. Específicamente,
LAG()
muestra el valor de<expr>
para la fila ubicada<offset>
filas antes de la fila actual. Si la fila no existe, se muestra<default_value>
.#legacySQL SELECT word, word_count, LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
Muestra:
word word_count lag pañuelo 29 nulo satisfacción 5 pañuelo desagrado 4 satisfacción instrumentos 4 desagrado circunstancia 3 instrumentos LAST_VALUE(<field_name>)
-
Muestra el último valor de
<field_name>
en la ventana.#legacySQL SELECT word, word_count, LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
Muestra:
word word_count lv imperfectly 1 imperfectly LEAD(<expr>[, <offset>[, <default_value>]])
-
Te permite leer datos de una fila siguiente dentro de una ventana. Específicamente,
LEAD()
muestra el valor de<expr>
para la fila ubicada<offset>
filas después de la fila actual. Si la fila no existe, se muestra<default_value>
. Devuelve:#legacySQL SELECT word, word_count, LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count lead pañuelo 29 satisfacción satisfacción 5 desagrado desagrado 4 instrumentos instrumentos 4 circunstancia circunstancia 3 nulo NTH_VALUE(<expr>, <n>)
-
Muestra el valor de
<expr>
en la posición<n>
del marco de la ventana, donde<n>
es un índice basado en uno. NTILE(<num_buckets>)
-
Divide una secuencia de filas en depósitos
<num_buckets>
y asigna una cantidad de depósitos correspondiente, como un número entero, a cada fila. La funciónntile()
asigna los números de bucket de la misma manera posible y muestra un valor de 1 a<num_buckets>
para cada fila. Devuelve:#legacySQL SELECT word, word_count, NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count ntile pañuelo 29 1 satisfacción 5 1 desagrado 4 1 instrumentos 4 2 circunstancia 3 2 PERCENT_RANK()
-
Muestra el rango de la fila actual en relación con las otras filas en la partición. Los valores mostrados oscilan entre 0 y 1, inclusive. El primer valor mostrado es 0.0.
Esta función analítica requiere
ORDER BY
en la cláusulaOVER
. Devuelve:#legacySQL SELECT word, word_count, PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_rank pañuelo 29 0.0 satisfacción 5 0.25 desagrado 4 0.5 instrumentos 4 0.5 circunstancia 3 1.0 PERCENTILE_CONT(<percentile>)
-
Muestra un valor interpolado que se asignaría al argumento del percentil con respecto a la ventana, después de ordenarlos según la cláusula
ORDER BY
.<percentile>
debe estar comprendido entre 0 y 1.Esta función analítica requiere
ORDER BY
en la cláusulaOVER
. Devuelve:#legacySQL SELECT word, word_count, PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_cont pañuelo 29 4 satisfacción 5 4 desagrado 4 4 instrumentos 4 4 circunstancia 3 4 PERCENTILE_DISC(<percentile>)
-
Muestra el valor más cercano al percentil del argumento en la ventana.
<percentile>
debe estar comprendido entre 0 y 1.Esta función analítica requiere
ORDER BY
en la cláusulaOVER
. Devuelve:#legacySQL SELECT word, word_count, PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_disc pañuelo 29 4 satisfacción 5 4 desagrado 4 4 instrumentos 4 4 circunstancia 3 4 RANK()
-
Muestra el rango de números enteros de un valor en un grupo de valores. El rango se calcula en función de comparaciones con otros valores del grupo.
Los valores empatados se muestran como el mismo rango. El rango del siguiente valor se incrementa de acuerdo con la cantidad de valores empatados que ocurrieron antes. Por ejemplo, si dos valores empatan para el rango 2, el siguiente valor clasificado es 4, no 3. Si prefieres que no haya espacios en la lista de clasificación, usa dense_rank().
Esta función analítica requiere
ORDER BY
en la cláusulaOVER
. Devuelve:#legacySQL SELECT word, word_count, RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count rank pañuelo 29 1 satisfacción 5 2 desagrado 4 3 instrumentos 4 3 circunstancia 3 5 RATIO_TO_REPORT(<column>)
-
Muestra la proporción de cada valor en la suma de los valores, como un doble entre 0 y 1.
Devuelve:#legacySQL SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count r_to_r pañuelo 29 0.6444444444444445 satisfacción 5 0.1111111111111111 desagrado 4 0.08888888888888889 instrumentos 4 0.08888888888888889 circunstancia 3 0.06666666666666667 ROW_NUMBER()
-
Muestra el número de fila actual del resultado de la consulta en la ventana, comenzando con 1.
Devuelve:#legacySQL SELECT word, word_count, ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count row_num pañuelo 29 1 satisfacción 5 2 desagrado 4 3 instrumentos 4 4 circunstancia 3 5
Otras funciones
Sintaxis
Otras funciones | |
---|---|
CASE WHEN ... THEN |
Usa CASE para elegir entre dos o más expresiones alternativas en tu consulta. |
CURRENT_USER() |
Muestra la dirección de correo electrónico del usuario que ejecuta la consulta. |
EVERY() |
Muestra true si el argumento es verdadero para todas sus entradas. |
FROM_BASE64() |
Convierte la string de entrada codificada en base 64 en formato BYTES. |
HASH() |
Calcula y muestra un valor hash firmado de 64 bits… |
FARM_FINGERPRINT() |
Calcula y muestra un valor de huella digital firmada de 64 bits… |
IF() |
Si el primer argumento es verdadero, muestra el segundo argumento; de lo contrario, muestra el tercero. |
POSITION() |
Muestra la posición secuencial basada en uno del argumento. |
SHA1() |
Muestra un hash SHA1 en formato BYTES. |
SOME() |
Muestra true si el argumento es verdadero para al menos una de sus entradas. |
TO_BASE64() |
Convierte el argumento BYTES en una string codificada en base 64. |
CASE WHEN when_expr1 THEN then_expr1
WHEN when_expr2 THEN then_expr2 ...
ELSE else_expr END- Usa CASE para elegir entre dos o más expresiones alternativas en tu consulta. Las expresiones WHEN deben ser booleanas y todas las expresiones en las cláusulas THEN y ELSE deben ser tipos compatibles.
CURRENT_USER()
- Muestra la dirección de correo electrónico del usuario que ejecuta la consulta.
EVERY(<condition>)
- Muestra
true
sicondition
es verdadero para todas sus entradas. Cuando se usa con la cláusulaOMIT IF
, esta función es útil para consultas que involucran campos repetidos. FROM_BASE64(<str>)
- Convierte la string de entrada codificada en base64
str
en formato BYTES. Para convertir BYTES a una string codificada en base64, usa TO_BASE64(). HASH(expr)
- Calcula y muestra un valor de hash firmado de 64 bits de los bytes de
expr
según lo definido por la biblioteca CityHash (versión 1.0.3). Se admite cualquier string o expresión de número entero, y la función respetaIGNORE CASE
para las strings y muestra valores que no varían. FARM_FINGERPRINT(expr)
- Calcula y muestra un valor de huella digital firmada de 64 bits de la entrada de
STRING
oBYTES
mediante la funciónFingerprint64
de la biblioteca de FarmHash de código abierto. El resultado de esta función para una entrada particular nunca cambiará y coincidirá con el resultado de la funciónFARM_FINGERPRINT
cuando usasGoogleSQL. RespetaIGNORE CASE
para las strings y muestra valores que no varían. IF(condition, true_return, false_return)
- Muestra
true_return
ofalse_return
, según sicondition
es verdadero o falso. Los valores que se muestran pueden ser literales o valores derivados de campos, pero deben ser del mismo tipo de datos. No es necesario incluir los valores derivados de campos en la cláusulaSELECT
. POSITION(field)
- Muestra la posición secuencial basada en uno del campo dentro de un conjunto de campos repetidos.
SHA1(<str>)
- Muestra un hash SHA1, en formato BYTES, de la string de entrada
str
. Puedes convertir el resultado en base64 con TO_BASE64(). Por ejemplo:#legacySQL SELECT TO_BASE64(SHA1(corpus)) FROM [bigquery-public-data:samples.shakespeare] LIMIT 100;
SOME(<condition>)
- Muestra
true
sicondition
es verdadero para al menos una de sus entradas. Cuando se usa con la cláusulaOMIT IF
, esta función es útil para consultas que involucran campos repetidos. TO_BASE64(<bin_data>)
- Convierte la entrada
bin_data
BYTES a una string codificada en base64. Por ejemplo: Para convertir una cadena codificada en base64 a BYTES, usa FROM_BASE64().#legacySQL SELECT TO_BASE64(SHA1(title)) FROM [bigquery-public-data:samples.wikipedia] LIMIT 100;
Ejemplos avanzados
-
Agrupa resultados en categorías mediante condicionales
La siguiente consulta utiliza un bloque
CASE/WHEN
para agrupar los resultados en categorías de “región” según una lista de estados. Si el estado no aparece como una opción en una de las declaracionesWHEN
, el valor del estado se establecerá de forma predeterminada en “Ninguno”.Ejemplo:
#legacySQL SELECT CASE WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID', 'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM') THEN 'West' WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL', 'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV', 'MD', 'DC', 'DE') THEN 'South' WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA', 'MO', 'WI', 'IL', 'IN', 'MI', 'OH') THEN 'Midwest' WHEN state IN ('NY', 'PA', 'NJ', 'CT', 'RI', 'MA', 'VT', 'NH', 'ME') THEN 'Northeast' ELSE 'None' END as region, average_mother_age, average_father_age, state, year FROM (SELECT year, state, SUM(mother_age)/COUNT(mother_age) as average_mother_age, SUM(father_age)/COUNT(father_age) as average_father_age FROM [bigquery-public-data:samples.natality] WHERE father_age < 99 GROUP BY year, state) ORDER BY year LIMIT 5;
Muestra:
+--------+--------------------+--------------------+-------+------+ | region | average_mother_age | average_father_age | state | year | +--------+--------------------+--------------------+-------+------+ | South | 24.342600163532296 | 27.683769419460344 | AR | 1969 | | West | 25.185041908446163 | 28.268214055448098 | AK | 1969 | | West | 24.780776677578217 | 27.831181063905248 | CA | 1969 | | West | 25.005834769924412 | 27.942978384829598 | AZ | 1969 | | South | 24.541730952905738 | 27.686430093306885 | AL | 1969 | +--------+--------------------+--------------------+-------+------+
-
Simulacro de una tabla dinámica
Usa declaraciones condicionales para organizar los resultados de una consulta de subselección en filas y columnas. En el siguiente ejemplo, los resultados de una búsqueda de los artículos más consultados de Wikipedia que comienzan con el valor “Google” se organizan en columnas en las que se muestran los recuentos de consultas si cumplen con varios criterios.
Ejemplo:
#legacySQL SELECT page_title, /* Populate these columns as True or False, */ /* depending on the condition */ IF (page_title CONTAINS 'search', INTEGER(total), 0) AS search, IF (page_title CONTAINS 'Earth' OR page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo, FROM /* Subselect to return top revised Wikipedia articles */ /* containing 'Google', followed by additional text. */ (SELECT TOP (title, 5) as page_title, COUNT (*) as total FROM [bigquery-public-data:samples.wikipedia] WHERE REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0 );
Muestra:
+---------------+--------+------+ | page_title | search | geo | +---------------+--------+------+ | Google search | 4261 | 0 | | Google Earth | 0 | 3874 | | Google Chrome | 0 | 0 | | Google Maps | 0 | 2617 | | Google bomb | 0 | 0 | +---------------+--------+------+
-
Usa HASH para seleccionar una muestra aleatoria de tus datos
Algunas consultas pueden proporcionar un resultado útil mediante un submuestreo aleatorio del conjunto de resultados. A fin de recuperar un muestreo aleatorio de valores, usa la función
HASH
para mostrar los resultados en los que el módulo “n” del hash sea igual a cero.Por ejemplo, la siguiente consulta encontrará el
HASH()
del valor "título" y, luego, verificará si ese módulo "2" es cero. Esto debería dar como resultado que cerca del 50% de los valores se etiqueten como "muestreados". Para realizar muestras de menos valores, aumenta el valor de la operación de módulo de "2" a algo más grande. La consulta usa la funciónABS
en combinación conHASH
, porqueHASH
puede mostrar valores negativos, y el operador de módulo en un valor negativo produce un valor negativo.Ejemplo:
#legacySQL SELECT title, HASH(title) AS hash_value, IF(ABS(HASH(title)) % 2 == 1, 'True', 'False') AS included_in_sample FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace = 0 LIMIT 5;