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áusula GROUP BY que incluya todos los campos no agregados en la cláusula SELECT 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áusula GROUP 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áusula GROUP 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 de GROUP 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 o ORDER BY, el conjunto de resultados completo todavía debe procesarse antes de mostrar los resultados. La cláusula LIMIT 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 clave WITHIN 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 de numeric_expr en todas las filas. Los valores NULL se ignoran. Esta función muestra NULL si todas las instancias de numeric_expr se evalúan como NULL.
BIT_OR(numeric_expr)
Muestra el resultado de una operación OR a nivel de bits entre cada instancia de numeric_expr en todas las filas. Los valores NULL se ignoran. Esta función muestra NULL si todas las instancias de numeric_expr se evalúan como NULL.
BIT_XOR(numeric_expr)
Muestra el resultado de una operación XOR a nivel de bits entre cada instancia de numeric_expr en todas las filas. Los valores NULL se ignoran. Esta función muestra NULL si todas las instancias de numeric_expr se evalúan como NULL.
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ón TOP, 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 para DISTINCT 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 COUNT(DISTINCT), puedes especificar un segundo parámetro, 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 un n mayor, obtendrás resultados exactos para COUNT(DISTINCT) hasta ese valor de n. Sin embargo, proporcionar valores más grandes de n 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, aplicar COUNT(*). El enfoque de GROUP 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 y numeric_expr2.
COVAR_SAMP(numeric_expr1, numeric_expr2)
alcula la covarianza de muestra de los valores que calculan numeric_expr1 y numeric_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 omite separator, 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 string a"b se mostraría como "a""b". Usa GROUP_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 omite separator, 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 string a"b se mostraría como a"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 valor x distinto y contiene un campo repetido para todos los valores y sincronizados con x en la entrada de consulta. La función NEST requiere una cláusula GROUP 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ón NEST cuando uses una subselección que produce resultados intermedios para el uso inmediato de la misma consulta.

NTH(n, field)
Muestra el valor secuencial nth en el alcance de la función, donde n es una constante. La función NTH comienza a contar desde 1, por lo que no hay ningún término cero. Si el alcance de la función tiene menos de n 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 exclusivamente NULL dan como resultado una salida NULL. La cantidad de cuantiles calculados se controla con el parámetro buckets opcional, que incluye el mínimo y el máximo en el recuento. Para calcular los N-tiles aproximados, usa buckets N+1. El valor predeterminado de buckets es 100. (Nota: El valor predeterminado de 100 no estima los percentiles. Para estimar percentiles, usa 101 buckets como mínimo). Si se especifica de manera explícita, el valor de buckets 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 que NTH se basa en 1 y que QUANTILES 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 de expr, mientras que NTH(20, QUANTILES(expr, 21)) estima el vigintil 19 (percentil 95) de expr. 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%, usa NTH(501, QUANTILES(expr, 1001)). Para el percentil 95 con un error del 0.1%, usa NTH(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ón STDDEV es un alias para STDDEV_SAMP.
STDDEV_POP(numeric_expr)
Calcula la desviación estándar de la población del valor que calcula numeric_expr. Usa STDDEV_POP() para calcular la desviación estándar de un conjunto de datos que abarque toda la población de interés. Usa STDDEV_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. Usa STDDEV_SAMP() para calcular la desviación estándar de una población completa según una muestra representativa de la población. Usa STDDEV_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 clave EACH, la consulta fallará y mostrará el error “Resources Exceeded” si hay demasiados valores distintos. Sin embargo, a diferencia de GROUP BY, la función UNIQUE 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ón VARIANCE es un alias para VAR_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() con GROUP 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ámetro multiplier para multiplicar los valores de cnt 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ón HAVING. HAVING compara un valor con un resultado determinado por una función de agregación, a diferencia de WHERE, 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

SELECT 6 + (5 - 1);

Muestra: 10
- Resta

SELECT 6 - (4 + 1);

Muestra: 1
* Multiplicación

SELECT 6 * (5 - 1);

Muestra: 24
/ División

SELECT 6 / (2 + 2);

Muestra: 1.5
% Modulo

SELECT 6 % (2 + 2);

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

SELECT (1 + 3) & 1

Muestra: 0
| OR bit a bit

SELECT 24 | 12

Muestra: 28
^ XOR bit a bit

SELECT 1 ^ 0

Muestra: 1
<< Desplaza bits hacia la izquierda

SELECT 1 << (2 + 2)

Muestra: 16
>> Desplaza bits hacia la derecha

SELECT (6 + 2) >> 2

Muestra: 2
~ NOT bit a bit

SELECT ~2

Muestra: -3
BIT_COUNT(<numeric_expr>)

Muestra la cantidad de bits configurados en <numeric_expr>.

SELECT BIT_COUNT(29);

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.
BYTES(string_expr)
Muestra string_expr como un valor de tipo bytes.
CAST(expr AS type)
Convierte expr en una variable de tipo type.
FLOAT(expr)
Muestra expr como un doble. expr puede ser una string como '45.78', pero la función muestra NULL 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.
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 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 es mayor o igual que expr2 y menor o igual que expr3.

expr IS NULL
Muestra true si expr es NULO.
expr IN(expr1, expr2, ...)
Muestra true si expr coincide con expr1, expr2 o cualquier valor entre paréntesis. La palabra clave IN es una abreviatura eficiente de (expr = expr1 || expr = expr2 || ...). Las expresiones utilizadas con la palabra clave IN deben ser constantes y deben coincidir con el tipo de datos de expr. También se puede usar la cláusula IN 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 es NULL, esta función muestra NULL.

Para ignorar los valores NULL, usa la función IFNULL a fin de cambiar los valores NULL por uno que no afecte la comparación. En el siguiente código de ejemplo, se usa la función IFNULL para cambiar los valores NULL 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, muestra expr; de lo contrario, muestra null_default.
IS_INF(numeric_expr)
Muestra true si numeric_expr es infinito positivo o negativo.
IS_NAN(numeric_expr)
Muestra true si numeric_expr es el valor numérico especial NaN.
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 es NULL, esta función muestra NULL.

NVL(expr, null_default)
Si expr no es nulo, muestra expr; de lo contrario, muestra null_default. La función NVL es un alias para IFNULL.

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 y SECOND. Si interval 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 formato YYYY-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 estableces day_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 muestra 1274259481071200, la representación de microsegundos de 2010-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 o 2620: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 JSONPath json_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 JSONPath json_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.
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.
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) o IS NOT NULL.

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 usar PI() 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 de numeric_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áusula LIMIT. Si no se especifica int32_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() y COS().

    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 en SELECT. 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 entero corpus_date a una string, que luego es modificada por REGEXP_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 es Java y str2 es Script, CONCAT muestra JavaScript.
expr CONTAINS 'str'
Muestra true si expr 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) muestra sea.
LENGTH('str')
Muestra un valor numérico para la longitud de la string. Ejemplo: si str es '123456', LENGTH muestra 6.
LOWER('str')
Muestra la string original con todos los caracteres en minúscula.
LPAD('str1', numeric_expr, 'str2')
Rellena str1 a la izquierda con str2, y repite str2 hasta que la string de resultado sea exactamente numeric_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.

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) muestra land.
RPAD('str1', numeric_expr, 'str2')
Rellena str1 a la derecha con str2 y repite str2 hasta que la string de resultados tenga exactamente caracteres numeric_expr. Ejemplo: RPAD('1', 7, '?') muestra 1??????.
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ón SPLIT divide str en substrings, con delimiter como delimitador.
SUBSTR('str', index [, max_len])
Muestra una substring de str, a partir de index. Si se utiliza el parámetro opcional max_len, la string que se muestra tiene un máximo de max_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). Si index es 5, la substring comienza con el carácter 5 desde la izquierda en str. Si index es -4, la substring comienza con el cuarto carácter de la derecha en str. Ejemplo: SUBSTR('awesome', -4, 4) muestra la substring some.
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 formato YYYYMMDD.

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 error Not 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ámetro expr 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 omite PARTITION 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 con allowLargeResults, o si pretendes aplicar más uniones o agregaciones al resultado de tu función analítica, usa PARTITION BY para paralelizar la ejecución.
No se pueden usar las cláusulas JOIN EACH y GROUP EACH BY en el resultado de las funciones analíticas. Para generar resultados de consultas grandes cuando se usan funciones analíticas, debes usar PARTITION 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 ventana RANGE, debes agregar una cláusula ORDER BY. El orden predeterminado es ASC.
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 especificar ROWS o RANGE, ORDER BY implica que la ventana se extiende desde el principio de la partición hasta la fila actual. En ausencia de una cláusula ORDER 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 un window-frame-clause, el marco de ventana predeterminado es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Si omites ORDER BY y window-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 consultar SUM(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 de CURRENT ROW se incluyen en un marco de ventana que especifica CURRENT ROW. Por ejemplo, si especificas que el final de la ventana es CURRENT 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: 
    {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
     donde <expr> es un número entero positivo, PRECEDING indica un número de fila o un valor de intervalo precedente y FOLLOWING 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: 
    {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}
     donde <expr> es un número entero positivo, PRECEDING indica un número de fila o un valor de intervalo precedente y FOLLOWING 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 agregada EXACT_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áusula OVER.

#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áusula OVER.

#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
Devuelve:
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.

#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
Devuelve:
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>.

#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
Devuelve:
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ón ntile() asigna los números de bucket de la misma manera posible y muestra un valor de 1 a <num_buckets> para cada fila.

#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
Devuelve:
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áusula OVER.

#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
Devuelve:
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áusula OVER.

#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
Devuelve:
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áusula OVER.

#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
Devuelve:
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áusula OVER.

#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
Devuelve:
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.

#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
Devuelve:
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.

#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
Devuelve:
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 si condition es verdadero para todas sus entradas. Cuando se usa con la cláusula OMIT 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 respeta IGNORE 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 o BYTES mediante la función Fingerprint64 de la biblioteca de FarmHash de código abierto. El resultado de esta función para una entrada particular nunca cambiará y coincidirá con el resultado de la función FARM_FINGERPRINT cuando usasGoogleSQL. Respeta IGNORE CASE para las strings y muestra valores que no varían.
IF(condition, true_return, false_return)
Muestra true_return o false_return, según si condition 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áusula SELECT.
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 si condition es verdadero para al menos una de sus entradas. Cuando se usa con la cláusula OMIT 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: 
#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;
 Para convertir una cadena codificada en base64 a BYTES, usa FROM_BASE64().

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 declaraciones WHEN, 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ón ABS en combinación con HASH, porque HASH 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;