Sintaxis, funciones y operadores de SQL heredados

Este documento detalla la sintaxis, las funciones y los operadores de las consultas SQL heredadas. La sintaxis de consulta preferida para BigQuery es GoogleSQL. Para obtener información sobre GoogleSQL, consulte Sintaxis de consulta de GoogleSQL .

Sintaxis de consulta

Nota: Las palabras clave no distinguen entre mayúsculas y minúsculas. En este documento, palabras clave como SELECT se escriben con mayúscula inicial a modo de ejemplo.

cláusula SELECT

La cláusula SELECT especifica una lista de expresiones que se calcularán. Las expresiones de la cláusula SELECT pueden contener nombres de campo, literales y llamadas a funciones (incluidas funciones de agregado y de ventana ), así como combinaciones de las tres. La lista de expresiones está separada por comas.

Se puede asignar un alias a cada expresión añadiendo un espacio seguido de un identificador después de la expresión. La palabra clave opcional AS se puede añadir entre la expresión y el alias para mejorar la legibilidad. Los alias definidos en una cláusula SELECT se pueden referenciar en las cláusulas GROUP BY , HAVING y ORDER BY de la consulta, pero no en las cláusulas FROM , WHERE u OMIT RECORD IF ni en otras expresiones de la misma cláusula SELECT .

Notas:

• Si utiliza una función de agregación en su cláusula SELECT , debe usarla en todas las expresiones o su consulta debe tener una cláusula GROUP BY que incluya todos los campos no agregados de su 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. */

• Puede usar corchetes para escapar palabras reservadas y usarlas como nombre de campo y alias. Por ejemplo, si tiene 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 lo escape con corchetes:

  SELECT [partition] FROM ...

Ejemplo

Este ejemplo define alias en la cláusula SELECT y luego hace referencia a uno de ellos en la cláusula ORDER BY . Observe que la palabra " column" no se puede referenciar mediante el alias "word_alias" en la cláusula WHERE ; debe referenciarse por su nombre. El alias "len" tampoco es visible en la cláusula WHERE . Sería visible para 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 de agregación agregue valores repetidos dentro de cada registro. Por cada registro de entrada, se generará exactamente una salida agregada. Este tipo de agregación se denomina agregación con ámbito . Dado que la agregación con ámbito genera una salida para cada registro, se pueden seleccionar expresiones no agregadas junto con expresiones con ámbito agregado sin usar una cláusula GROUP BY .

Lo más común es usar el ámbito RECORD al usar la agregación con ámbito. Si tiene un esquema anidado y repetido muy complejo, podría necesitar realizar agregaciones dentro de ámbitos de subregistro. Esto se puede lograr reemplazando la palabra clave RECORD en la sintaxis anterior por el nombre del nodo del esquema donde desea que se realice la agregación. Para obtener más información sobre este comportamiento avanzado, consulte "Tratar con datos" .

Ejemplo

Este ejemplo realiza una agregación COUNT con alcance y luego filtra y ordena los registros por 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 mediante operadores específicos que se describen a continuación. Las combinaciones de estas fuentes de datos se pueden consultar mediante la coma , que es el operador UNION ALL en BigQuery.

Tablas de referencia

Al hacer referencia a una tabla, se deben especificar tanto datasetId como tableId ; project_name es opcional. Si no se especifica project_name , BigQuery usa el proyecto actual de forma predeterminada. Si el nombre del proyecto incluye un guion, debe escribir toda la referencia de la tabla entre corchetes.

Ejemplo

[my-dashed-project:dataset1.tableName]

Se puede asignar un alias a las tablas añadiendo un espacio seguido de un identificador después del nombre de la tabla. La palabra clave opcional AS se puede añadir entre el ID de la tabla y el alias para mejorar la legibilidad.

Al hacer referencia a columnas de una tabla, puede usar el nombre de columna simple o prefijar el nombre de columna con el alias, si lo especificó, o con datasetId y tableId, siempre que no se haya especificado project_name . Project_name no se puede incluir en el prefijo de columna porque no se permiten los dos puntos en los nombres de campo.

Ejemplos

Este ejemplo hace referencia a una columna sin prefijo de tabla.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare];

Este ejemplo antepone datasetId y tableId al nombre de la columna. Tenga en cuenta que no se puede incluir project_name en este ejemplo. Este método solo funcionará si el conjunto de datos está en su proyecto predeterminado actual.

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

Este ejemplo antepone al nombre de la columna un alias de tabla.

#legacySQL
SELECT
  t.word
FROM
  [bigquery-public-data:samples.shakespeare] AS t;

Tablas particionadas por rango de enteros

El SQL heredado admite el uso de decoradores de tabla para direccionar una partición específica en una tabla particionada por rango de enteros. La clave para direccionar 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;

Tenga en cuenta que no puede usar SQL heredado para realizar consultas en una tabla particionada completa por rango de enteros. En su lugar, la consulta devuelve un error como el siguiente:

Uso de subconsultas

Una subconsulta es una sentencia SELECT anidada entre paréntesis. Las expresiones calculadas en la cláusula SELECT de la subconsulta están disponibles para la consulta externa, igual que las columnas de una tabla .

Las subconsultas se pueden usar para calcular agregaciones y otras expresiones. Todos los operadores SQL están disponibles en la subconsulta. Esto significa que una subconsulta puede contener otras subconsultas, realizar uniones y agrupaciones, etc.

Coma como UNION ALL

A diferencia de GoogleSQL, el SQL heredado usa la coma como operador UNION ALL en lugar de CROSS JOIN . Este comportamiento heredado evolucionó porque, históricamente, BigQuery no era compatible con CROSS JOIN y sus usuarios necesitaban escribir consultas UNION ALL con regularidad. En GoogleSQL, las consultas que realizan uniones son particularmente detalladas. Usar la coma como operador de unión permite escribir estas consultas de forma mucho más eficiente. Por ejemplo, esta consulta puede utilizarse para ejecutar una sola consulta sobre 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;

Las consultas que unen un gran número de tablas suelen ser más lentas que las que procesan la misma cantidad de datos de una sola tabla. La diferencia de rendimiento puede ser de hasta 50 ms por cada tabla adicional. Una sola consulta puede unir un máximo de 1000 tablas.

Funciones comodín de tabla

El término "función comodín de tabla" se refiere a un tipo especial de función exclusivo de BigQuery. Estas funciones se utilizan en la cláusula FROM para buscar coincidencias en un conjunto de nombres de tabla mediante uno de varios tipos de filtros. Por ejemplo, la función TABLE_DATE_RANGE permite consultar únicamente un conjunto específico de tablas diarias. Para obtener más información sobre estas funciones, consulte "Funciones comodín de tabla" .

Operador FLATTEN

(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened))
(FLATTEN((subquery), field_to_be_flattened))

A diferencia de los sistemas de procesamiento SQL tradicionales, BigQuery está diseñado para gestionar datos repetidos. Por ello, los usuarios de BigQuery a veces necesitan escribir consultas que manipulen la estructura de registros repetidos. Una forma de hacerlo es mediante el operador FLATTEN .

FLATTEN convierte un nodo del esquema de repetido a opcional. Dado un registro con uno o más valores para un campo repetido, FLATTEN creará varios registros, uno para cada valor del campo repetido. Todos los demás campos seleccionados del registro se duplican en cada nuevo registro de salida. FLATTEN puede aplicarse repetidamente para eliminar múltiples niveles de repetición.

Para obtener más información y ejemplos, consulte Tratamiento de datos .

Operador JOIN

BigQuery admite varios operadores JOIN en cada cláusula FROM . Las operaciones JOIN posteriores utilizan los resultados de la operación JOIN anterior como entrada JOIN izquierda. Los campos de cualquier entrada JOIN anterior se pueden usar como claves en las cláusulas ON de los operadores JOIN posteriores.

Tipos de UNIÓN

BigQuery admite las operaciones INNER , [FULL|RIGHT|LEFT] OUTER y CROSS JOIN . Si no se especifica, el valor predeterminado es INNER .

Las operaciones CROSS JOIN no admiten cláusulas ON . CROSS JOIN puede devolver una gran cantidad de datos y podría resultar en una consulta lenta e ineficiente o en una consulta que exceda el máximo de recursos permitidos por consulta. Estas consultas fallarán con un error. Siempre que sea posible, opte por consultas que no utilicen CROSS JOIN . Por ejemplo, CROSS JOIN se suele usar donde las funciones de ventana serían más eficientes.

CADA modificador

El modificador EACH es una indicación que indica a BigQuery que ejecute la JOIN usando múltiples particiones. Esto es especialmente útil cuando se sabe que ambos lados de la JOIN son grandes. El modificador EACH no se puede usar en cláusulas CROSS JOIN .

Antes se recomendaba usar EACH en muchos casos, pero ya no es así. Siempre que sea posible, use JOIN sin el modificador EACH para un mejor rendimiento. Use JOIN EACH cuando su consulta falle con un mensaje de error de recursos excedidos.

Semi-unión y anti-unión

Además de admitir 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; la antiunión, mediante las palabras clave NOT IN .

Ejemplos

La siguiente consulta utiliza una semiunión para encontrar ngramas donde la primera palabra del ngrama también es la segunda palabra de otro ngrama que tiene "AND" como la tercera palabra del ngrama.

#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 utiliza una semiunión para devolver el número de mujeres mayores de 50 años que dieron a luz en los 10 estados con más 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 cifras de los otros 40 estados, puede usar una anti-unión. La siguiente consulta es casi idéntica al ejemplo anterior, pero usa NOT IN en lugar de IN para devolver el número 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 admite uniones semi- ni anti-semi-uniones correlacionadas. La subconsulta no puede hacer referencia a ningún campo de la consulta externa.
• La subconsulta utilizada en una unión semi o anti-semi-unión debe seleccionar exactamente un campo.
• Los tipos del campo seleccionado y del campo utilizado en la consulta externa en la cláusula WHERE deben coincidir exactamente. BigQuery no realizará ninguna conversión de tipos para uniones semi o antisemi.

cláusula WHERE

La cláusula WHERE , a veces denominada predicado, filtra los registros generados 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 de una cláusula WHERE en la cláusula SELECT correspondiente, y la expresión de la cláusula WHERE no puede hacer referencia a expresiones calculadas en la cláusula SELECT de la consulta a la que pertenece WHERE cláusula.

Nota: No se pueden usar funciones de agregación en la cláusula WHERE . Utilice una cláusula HAVING y una consulta externa si necesita filtrar la salida de una función de agregación.

Ejemplo

El siguiente ejemplo utiliza una disyunción de expresiones booleanas en la cláusula WHERE (dos expresiones unidas mediante el operador OR . Un registro de entrada pasará por el filtro WHERE si alguna de las expresiones devuelve 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 especialmente útil para gestionar esquemas anidados y repetidos. Es similar a una cláusula WHERE , pero se diferencia en dos aspectos importantes. En primer lugar, utiliza una condición de exclusión, lo que significa que los registros se omiten si la expresión devuelve true , pero se conservan si la expresión devuelve false o null . En segundo lugar, la cláusula OMIT RECORD IF puede (y suele hacerlo) utilizar funciones de agregación 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. Esto se logra usando el nombre de un nodo no hoja en el esquema, en lugar de RECORD en la cláusula OMIT...IF . Los usuarios de BigQuery rara vez usan esta funcionalidad. Puede encontrar más documentación sobre este comportamiento avanzado en el enlace a la documentación WITHIN mencionada anteriormente.

Si usa OMIT...IF para excluir una parte de un registro en un campo repetitivo 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 ve el error Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, le recomendamos cambiar a GoogleSQL. Para obtener información sobre la migración de las instrucciones OMIT...IF a GoogleSQL, consulte Migración a GoogleSQL .

Ejemplo

Volviendo al ejemplo utilizado para el modificador WITHIN , OMIT RECORD IF se puede utilizar para lograr lo mismo que se utilizaba WITHIN y HAVING en ese ejemplo.

#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 permite agrupar filas con los mismos valores para un campo o conjunto de campos, lo que permite calcular agregaciones de campos relacionados. La agrupación se produce después del filtrado realizado en la cláusula WHERE , pero antes de calcular las expresiones de la cláusula SELECT . Los resultados de las expresiones 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 , muestra cómo se pueden usar índices posicionales en lugar de 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 realizada mediante 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.

El modificador EACH

El modificador EACH es una indicación que indica a BigQuery que ejecute GROUP BY usando múltiples particiones. Esto es especialmente útil cuando se sabe que el conjunto de datos contiene una gran cantidad de valores distintos para las claves de grupo.

Antes se recomendaba usar EACH en muchos casos, pero ya no es así. Usar GROUP BY sin el modificador EACH suele ofrecer un mejor rendimiento. Use GROUP EACH BY cuando su consulta falle con un mensaje de error de recursos excedidos.

La función ROLLUP

Al usar la función ROLLUP , BigQuery agrega filas adicionales al resultado de la consulta que representan agregaciones acumuladas . Todos los campos enumerados después de ROLLUP deben estar entre paréntesis. En las filas agregadas mediante la función ROLLUP , NULL indica las columnas para las que se acumula la agregación.

Ejemplo

Esta consulta genera recuentos anuales de nacimientos masculinos y femeninos a partir del conjunto de datos de natalidad de muestra.

#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. Observe que hay filas donde una o ambas claves de grupo son NULL . Estas filas son las filas de resumen .

+------+---------+----------+
| 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 |
+------+---------+----------+

Al utilizar la función ROLLUP , puede utilizar la función GROUPING para distinguir entre las filas que se agregaron debido a la función ROLLUP y las filas que en realidad 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 devuelve 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 estar incluidos 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 utilizar 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, porque la función de igualdad para esos tipos no está bien definida.
• Dado que el sistema es interactivo, las consultas que generan una gran cantidad de grupos podrían fallar. El uso de la función TOP en lugar de GROUP BY podría solucionar algunos problemas de escalado.

Cláusula HAVING

La cláusula HAVING se comporta exactamente igual que la cláusula WHERE , salvo que se evalúa después de la cláusula SELECT , por lo que los resultados de todas las expresiones calculadas son visibles para la cláusula HAVING . La cláusula HAVING solo puede hacer referencia a las salidas de la cláusula SELECT correspondiente.

Ejemplo

Esta consulta calcula las primeras palabras más comunes en el conjunto de datos de muestra de ngramas 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 utilizando uno o más campos clave. Para ordenar por varios campos o alias, introdúzcalos como una lista separada por comas. Los resultados se ordenan por los campos en el orden en que aparecen. Utilice DESC (descendente) o ASC (ascendente) para especificar la dirección de ordenación. ASC es el valor predeterminado. Se puede especificar una dirección de ordenación diferente para cada clave de ordenación.

La cláusula ORDER BY se evalúa después de la cláusula SELECT , por lo que puede referenciar la salida de cualquier expresión calculada en SELECT . Si se asigna un alias a un campo en la cláusula SELECT , este debe usarse en la cláusula ORDER BY .

cláusula LIMIT

La cláusula LIMIT limita el número de filas en el conjunto de resultados devuelto. Dado que las consultas de BigQuery suelen operar sobre un gran número de filas, LIMIT es una buena manera de evitar consultas de larga duración al procesar solo un subconjunto de las filas.

Notas:

• La cláusula LIMIT detendrá el procesamiento y devolverá los resultados cuando cumpla con sus requisitos. Esto puede reducir el tiempo de procesamiento de algunas consultas, pero al especificar funciones de agregación como las cláusulas COUNT u ORDER BY , se debe procesar todo el conjunto de resultados antes de devolver los resultados. La cláusula LIMIT es la última en evaluarse.
• Una consulta con una cláusula LIMIT puede seguir siendo no determinista si no hay ningún operador que garantice el orden del conjunto de resultados. Esto se debe a que BigQuery se ejecuta con una gran cantidad de trabajadores paralelos. El orden de retorno de los trabajos paralelos no está garantizado.
• La cláusula LIMIT no puede contener ninguna función; solo acepta una constante numérica.
• Cuando se utiliza la cláusula LIMIT , el total de bytes procesados ​​y los bytes facturados pueden variar para la misma consulta.

Gramática de consultas

Las cláusulas individuales de las sentencias SELECT de BigQuery se describen en detalle más arriba . Aquí presentamos la gramática completa de las sentencias SELECT de forma resumida, con enlaces 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 dentro de corchetes "[, ... ]" indica que el elemento anterior puede repetirse en una lista con el separador especificado.
• Los paréntesis "()" indican paréntesis literales.

Funciones y operadores compatibles

La mayoría de las cláusulas SELECT admiten funciones. Los campos referenciados en una función no necesitan estar incluidos 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 agregadas

Las funciones de agregación devuelven valores que representan resúmenes de conjuntos de datos más grandes, lo que las hace especialmente útiles para analizar registros. Una función de agregación opera sobre una colección de valores y devuelve un único valor por tabla, grupo o ámbito:

• Agregación de tablas

  Utiliza una función de agregación para resumir todas las filas que cumplen los requisitos de la tabla. Por ejemplo:

  SELECT COUNT(f1) FROM ds.Table;

• Agregación de grupos

  Utiliza una función de agregación 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 grupos.

• Agregación con alcance

  Esta característica se aplica únicamente a tablas que tienen campos anidados .
  Utiliza una función de agregación y la palabra clave WITHIN para agregar valores repetidos dentro de un ámbito 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 devuelven resultados agregados para cada registro o nodo.

Puede aplicar una restricción a una función de agregación utilizando 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

AVG( numeric_expr )

Devuelve el promedio de los valores de un grupo de filas calculado por numeric_expr . Las filas con un valor nulo no se incluyen en el cálculo.

BIT_AND( numeric_expr )

Devuelve el resultado de una operación AND bit a bit entre cada instancia de numeric_expr en todas las filas. Se ignoran los valores NULL . Esta función devuelve NULL si todas las instancias de numeric_expr se evalúan como NULL .

BIT_OR( numeric_expr )

Devuelve el resultado de una operación OR bit a bit entre cada instancia de numeric_expr en todas las filas. Se ignoran los valores NULL . Esta función devuelve NULL si todas las instancias de numeric_expr se evalúan como NULL .

BIT_XOR( numeric_expr )

Devuelve el resultado de una operación XOR bit a bit entre cada instancia de numeric_expr en todas las filas. Los valores NULL se ignoran. Esta función devuelve NULL si todas las instancias de numeric_expr se evalúan como NULL .

CORR( numeric_expr , numeric_expr )

Devuelve el coeficiente de correlación de Pearson de un conjunto de pares de números.

COUNT(*)

Devuelve el número total de valores (NULL y no NULL) dentro del alcance de la función. A menos que utilice COUNT(*) con la función TOP , es mejor especificar explícitamente el campo que se va a contar.

COUNT([DISTINCT] field [, n ])

Devuelve el número total de valores distintos de NULL en el ámbito de la función.

Si utiliza la palabra clave DISTINCT , la función devuelve el número de valores distintos para el campo especificado. Tenga en cuenta que el valor devuelto por DISTINCT es una aproximación estadística y no se garantiza su exactitud.

Utilice EXACT_COUNT_DISTINCT() para obtener una respuesta exacta.

Si necesita una mayor precisión del COUNT(DISTINCT) , puede especificar un segundo parámetro, n , que da el umbral por debajo del cual se garantizan los resultados exactos. Por defecto, n es 1000, pero si da una n más grande, obtendrá resultados exactos para COUNT(DISTINCT) hasta ese valor de n . Sin embargo, dar valores más grandes de n reducirá la escalabilidad de este operador y puede aumentar sustancialmente el tiempo de ejecución de la consulta o hacer que la consulta falle.

Para calcular el número exacto de valores distintos, use Exact_Count_Distast . O, para un enfoque más escalable, considere usar GROUP EACH BY en los campos relevantes y luego aplicar COUNT(*) . El GROUP EACH BY enfoque es más escalable, pero puede incurrir en una ligera penalización por rendimiento por adelantado.

COVAR_POP( numeric_expr1 , numeric_expr2 )

Calcula la covarianza de la población de los valores calculados por numeric_expr1 y numeric_expr2 .

COVAR_SAMP( numeric_expr1 , numeric_expr2 )

Calcula la covarianza de muestra de los valores calculados por numeric_expr1 y numeric_expr2 .

EXACT_COUNT_DISTINCT( field )

Devuelve el número exacto de valores distintos no nulos para el campo especificado. Para una mejor escalabilidad y rendimiento, use recuento ( campo distinto) .

FIRST( expr )

Devuelve el primer valor secuencial en el alcance de la función.

GROUP_CONCAT( 'str' [, separator ])

Concatena múltiples cadenas en una sola cadena, donde cada valor está separado por el parámetro separator opcional. Si se omite separator , BigQuery devuelve una cadena separada por comas.

Si una cadena en los datos de origen contiene un carácter de doble cita, GROUP_CONCAT devuelve la cadena con cotizaciones dobles agregadas. Por ejemplo, la cadena a"b regresaría como "a""b" . Use GROUP_CONCAT_UNQUOTED si prefiere que estas cadenas no regresen con cotizaciones dobles agregadas.

Ejemplo:

#legacySQL
SELECT
  GROUP_CONCAT(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);

GROUP_CONCAT_UNQUOTED( 'str' [, separator ])

Concatena múltiples cadenas en una sola cadena, donde cada valor está separado por el parámetro separator opcional. Si se omite separator , BigQuery devuelve una cadena separada por comas.

A diferencia de GROUP_CONCAT , esta función no agregará cotizaciones dobles a los valores devueltos que incluyen un carácter de doble cita. Por ejemplo, la cadena a"b regresaría como a"b

Ejemplo:

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);

LAST( field )

Devuelve el último valor secuencial en el alcance de la función.

MAX( field )

Devuelve el valor máximo en el alcance de la función.

MIN( field )

Devuelve el valor mínimo en el alcance de la función.

NEST( expr )

Agregue todos los valores en el alcance de agregación actual en un campo repetido. Por ejemplo, la consulta "SELECT x, NEST(y) FROM ... GROUP BY x" Devuelve un registro de salida para cada valor x distintivo, y contiene un campo repetido para todos los valores y combinados con x en la entrada de consulta. La función NEST requiere un GROUP BY cláusula.

BigQuery aplana automáticamente los resultados de la consulta, por lo que si usa la función NEST en la consulta de nivel superior, los resultados no contienen campos repetidos. Use la función NEST cuando se use una subselección que produce resultados intermedios para el uso inmediato por la misma consulta.

NTH( n , field )

Devuelve el valor secuencial n en el alcance de la función, donde n es una constante. La NTH función comienza a contar en 1, por lo que no hay un término Zeroth. Si el alcance de la función tiene menos de n valores, la función devuelve NULL .

QUANTILES( expr [, buckets ])

Calcula el mínimo, máximo y cuantiles aproximados para la expresión de entrada. Se ignoran los valores de entrada NULL . La entrada vacía o exclusivamente NULL da como resultado una salida NULL . El número 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 fichas N aproximadas, use buckets N+1. El valor predeterminado de buckets es 100. (Nota: El valor predeterminado de 100 no estimula los percentiles. Para estimar los percentiles, use 101 buckets como mínimo). Si se especifica explícitamente, buckets deben ser al menos 2.

El error fraccional por cuantil es Epsilon = 1 / buckets , lo que significa que el error disminuye a medida que aumenta el número de cubos. 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 NTH función se puede usar para elegir un cuantil en particular, pero recuerde que NTH está basado en 1, y que QUANTILES devuelven el mínimo ("0 °" cuantil) en la primera posición, y el máximo (percentil "100 °" o "nth" n-filo) 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 19º Vigintil (percentil 95) de expr . Ambas estimaciones tienen un margen de error del 5%.

Para mejorar la precisión, use más cubos. Por ejemplo, para reducir el margen de error para los cálculos anteriores del 5% al ​​0.1%, use 1001 cubos en lugar de 21, y ajuste el argumento a la NTH función en consecuencia. Para calcular la mediana con un error del 0.1%, use NTH(501, QUANTILES(expr, 1001)) ; Para el percentil 95 con un error del 0.1%, use NTH(951, QUANTILES(expr, 1001)) .

STDDEV( numeric_expr )

Devuelve la desviación estándar de los valores calculados por numeric_expr . Las filas con un valor nulo no están incluidas 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 calculado por numeric_expr . Use STDDEV_POP() para calcular la desviación estándar de un conjunto de datos que abarca toda la población de interés. Si su conjunto de datos comprende solo una muestra representativa de la población, use STDDEV_SAMP() en su lugar. Para obtener más información sobre la población versus la desviación estándar de la muestra, consulte la desviación estándar sobre Wikipedia .

STDDEV_SAMP( numeric_expr )

Calcula la muestra estándar de desviación del valor calculado por numeric_expr . Use STDDEV_SAMP() para calcular la desviación estándar de una población completa basada en una muestra representativa de la población. Si su conjunto de datos comprende toda la población, use STDDEV_POP() en su lugar. Para obtener más información sobre la población versus la desviación estándar de la muestra, consulte la desviación estándar sobre Wikipedia .

SUM( field )

Devuelve la suma total de los valores en el alcance de la función. Para usar solo con tipos de datos numéricos.

TOP( field | alias [, max_values ][, multiplier ]) ... COUNT(*)

Devuelve los registros Top Max_records por frecuencia. Consulte la descripción superior a continuación para más detalles.

UNIQUE( expr )

Devuelve el conjunto de valores únicos y no nulos en el alcance de la función en un orden indefinido. Similar a un GROUP BY cláusula sin EACH palabra clave, la consulta fallará con un error de "recursos excedidos" si hay demasiados valores distintos. Sin embargo, a diferencia GROUP BY , la función UNIQUE se puede aplicar con agregación alcanzada, lo que permite una operación eficiente en campos anidados con un número limitado de valores.

VARIANCE( numeric_expr )

Calcula la varianza de los valores calculados por numeric_expr . Las filas con un valor nulo no están incluidas 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 calculados por numeric_expr . Para obtener más información sobre la población versus la desviación estándar de la muestra, consulte la desviación estándar sobre Wikipedia .

VAR_SAMP( numeric_expr )

Calcula la varianza de muestra de los valores calculados por numeric_expr . Para obtener más información sobre la población versus la desviación estándar de la muestra, consulte la desviación estándar sobre Wikipedia .

Función top ()

TOP es una función que es una alternativa al grupo por cláusula. Se usa como sintaxis simplificada para GROUP BY ... ORDER BY ... LIMIT ... En general, la función superior funciona más rápido que el ... GROUP BY ... ORDER BY ... LIMIT ... consulta, pero solo puede devolver resultados aproximados. La siguiente es la sintaxis para la función superior:

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

Al usar la parte superior en una cláusula SELECT , debe incluir COUNT(*) como uno de los campos.

Una consulta que usa la función superior () puede devolver solo dos campos: el campo superior y el valor de recuento (*).

field | alias

El campo o alias para regresar.

max_values

[ Opcional ] El número máximo de resultados para devolver. El valor predeterminado es 20.

multiplier

Un entero positivo que aumenta el valor (s) devuelto por COUNT(*) por el múltiplo especificado.

Ejemplos de TOP()

• Consultas de ejemplo básicas que usan TOP()

  Las siguientes consultas usan TOP() para devolver 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");

• Compare TOP() con GROUP BY...ORDER BY...LIMIT

  La consulta devuelve, en orden, las 10 palabras más utilizadas con más frecuencia que contienen "th", y el número de documentos en los que se usaron las palabras. La consulta TOP se ejecutará mucho más rápido:

  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';

• Usando el parámetro multiplier .

  Las siguientes consultas muestran cómo el parámetro multiplier afecta el resultado de la consulta. La primera consulta devuelve el número de nacimientos por mes en Wyoming. La segunda consulta usa para multiplier del parámetro para multiplicar los valores 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";

  Devoluciones:

  +-------+-------+
  | 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";

  Devoluciones:

  +-------+---------+
  | month |   cnt   |
  +-------+---------+
  |   7   | 1959400 |
  |   5   | 1903800 |
  |   8   | 1903000 |
  +-------+---------+
  

Nota: debe incluir COUNT(*) en la cláusula SELECT para usar TOP .

Ejemplos avanzados

• Desviación promedio y estándar agrupada por condición

  La siguiente consulta devuelve la desviación promedio y estándar de los pesos del nacer en Ohio en 2003, agrupada por madres que sí 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;

• Resultados de la consulta de filtro utilizando un valor agregado

  Para filtrar los resultados de la consulta utilizando un valor agregado (por ejemplo, filtrarse por el valor de una SUM ), use 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

  Devoluciones:

  +-------+--------+---------+
  | 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 devuelven un resultado numérico. Cada argumento puede ser un valor numérico literal o un valor numérico devuelto por una consulta. Si la operación aritmética se evalúa con un resultado indefinido, la operación devuelve NULL .

Sintaxis

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 sobre las funciones bit a bit, consulte la operación bitwise .

Tres funciones adicionales de bitwise, BIT_AND , BIT_OR y BIT_XOR , se documentan en funciones agregadas .

Sintaxis

Funciones de casting

Las funciones de fundición cambian el tipo de datos de una expresión numérica. Las funciones de fundición son particularmente útiles para garantizar que los argumentos en una función de comparación tengan el mismo tipo de datos.

Sintaxis

BOOLEAN( <numeric_expr> )

• Devuelve true si <numeric_expr> no es 0 y no es nulo.
• Devuelve false si <numeric_expr> es 0.
• Devuelve NULL IF <numeric_expr> es NULL.

BYTES( string_expr )

Devuelve string_expr como un valor de tipo bytes .

CAST( expr AS type )

Convierte expr en una variable de type .

FLOAT( expr )

Devuelve expr como un doble. El expr puede ser una cadena como '45.78' , pero la función devuelve NULL para valores no numéricos.

HEX_STRING( numeric_expr )

Devuelve numeric_expr como una cadena hexadecimal.

INTEGER( expr )

Cierre expr a un entero de 64 bits.

• Devuelve nulo si expr es una cadena que no corresponde a un valor entero.
• Devuelve el número de microsegundos ya que la época Unix si expr es una marca de tiempo.

STRING( numeric_expr )

Devuelve numeric_expr como una cadena.

Funciones de comparación

Las funciones de comparación devuelven true o false , basado en los siguientes tipos de comparaciones:

• Una comparación de dos expresiones.
• Una comparación de una expresión o conjunto de expresiones con un criterio específico, como estar en una lista especificada, ser nula o ser un valor opcional no deformento.

Algunas de las funciones que se enumeran a continuación valores de retorno distintos de true o false , pero los valores que devuelven se basan en las operaciones de comparación.

Puede usar expresiones numéricas o de cadena como argumentos para funciones de comparación. (Las constantes de cadena deben estar encerradas en cotizaciones simples o dobles.) Las expresiones pueden ser literales o valores obtenidos por una consulta. Las funciones de comparación se usan con mayor frecuencia como condiciones de filtrado en WHERE cláusulas, pero se pueden usar en otras cláusulas.

Sintaxis

expr1 = expr2

Devuelve true si las expresiones son iguales.

expr1 != expr2
expr1 <> expr2

Devuelve true si las expresiones no son iguales.

expr1 > expr2

Devuelve true si expr1 es mayor que expr2 .

expr1 < expr2

Devuelve true si expr1 es menor que expr2 .

expr1 >= expr2

Devuelve true si expr1 es mayor o igual a expr2 .

expr1 <= expr2

Devuelve true si expr1 es menor o igual a expr2 .

expr1 BETWEEN expr2 AND expr3

Devuelve true si el valor de expr1 es mayor o igual a expr2 , y menor o igual a expr3 .

expr IS NULL

Devuelve true si expr es nulo.

expr IN( expr1 , expr2, ...)

Devuelve true si expr coincide expr1 , expr2 o cualquier valor en los paréntesis. La palabra clave IN es una taquigrafía eficiente para (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 . La cláusula IN también se puede usar para crear semi uniones y anti-uniones. Para obtener más información, consulte Semi-un y anti-unión .

COALESCE( <expr1> , <expr2> , ...)

Devuelve el primer argumento que no es nulo.

GREATEST( numeric_expr1 , numeric_expr2 , ...)

Devuelve el parámetro numeric_expr más grande. Todos los parámetros deben ser numéricos, y todos los parámetros deben ser del mismo tipo. Si algún parámetro es NULL , esta función devuelve NULL .

Para ignorar los valores NULL , use la función IFNULL para cambiar los valores NULL a un valor que no afecta la comparación. En el siguiente ejemplo del código, la función IFNULL se usa para cambiar los valores NULL a -1 , lo 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, devuelve expr , de lo contrario devuelve null_default .

IS_INF( numeric_expr )

Devuelve true si numeric_expr es un infinito positivo o negativo.

IS_NAN( numeric_expr )

Devuelve true si numeric_expr es el valor numérico NaN especial.

IS_EXPLICITLY_DEFINED( expr )

Esta función está en desuso. Usar expr IS NOT NULL en su lugar.

LEAST( numeric_expr1 , numeric_expr2 , ...)

Devuelve el parámetro numeric_expr más pequeño. Todos los parámetros deben ser numéricos, y todos los parámetros deben ser del mismo tipo. Si algún parámetro es NULL , esta función devuelve NULL

NVL( expr , null_default )

Si expr no es nulo, devuelve expr , de lo contrario devuelve 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 marcas de tiempo UNIX, las cadenas de fecha y los tipos de datos de la marca de tiempo. Para obtener más información sobre el trabajo con el tipo de datos de la marca de tiempo, consulte Uso de TimeStamp .

Las funciones de fecha y hora que funcionan con las marcas de tiempo UNIX funcionan en Tiempo UNIX . Funciones de fecha y hora Los valores de devolución basados ​​en la zona horaria de UTC.

Sintaxis

CURRENT_DATE()

Devuelve una cadena legible por humanos de la fecha actual en el formato %Y-%m-%d .

Ejemplo:

SELECT CURRENT_DATE();

Devoluciones: 2013-02-01

CURRENT_TIME()

Devuelve una cadena legible por humanos de la hora actual del servidor en el formato %H:%M:%S .

Ejemplo:

SELECT CURRENT_TIME();

Devuelve: 01:32:56

CURRENT_TIMESTAMP()

Devuelve un tipo de datos de marca de tiempo del tiempo actual del servidor en el formato %Y-%m-%d %H:%M:%S .

Ejemplo:

SELECT CURRENT_TIMESTAMP();

Devoluciones: 2013-02-01 01:33:35 UTC

DATE( <timestamp> )

Devuelve una cadena legible por humanos de un tipo de datos de marca de tiempo en el formato %Y-%m-%d .

Ejemplo:

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

Devoluciones: 2012-10-01

DATE_ADD( <timestamp> , <interval> ,
<interval_units> )

Agrega el intervalo especificado a un tipo de datos de marca de tiempo. Los posibles valores de interval_units incluyen YEAR , MONTH , DAY , HOUR , MINUTE y SECOND . Si interval es un número negativo, el intervalo se resta del tipo de datos de marca de tiempo.

Ejemplo:

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");

Devoluciones: 2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

Devoluciones: 2007-10-01 02:03:04 UTC

DATEDIFF( <timestamp1> , <timestamp2> )

Devuelve el número de días entre dos tipos de datos de marca de tiempo. El resultado es positivo si el primer tipo de datos de la marca de tiempo se produce después del segundo tipo de datos de marca de tiempo, y de lo contrario el resultado es negativo.

Ejemplo:

SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));

Devoluciones: 466

Ejemplo:

SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));

Devoluciones: -466

DAY( <timestamp> )

Devuelve el día del mes de un tipo de datos de marca de tiempo como entero entre 1 y 31 años, inclusivamente.

Ejemplo:

SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));

Devoluciones: 2

DAYOFWEEK( <timestamp> )

Devuelve el día de la semana de un tipo de datos de marca de tiempo como entero entre 1 (domingo) y 7 (sábado), inclusivamente.

Ejemplo:

SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));

Devoluciones: 2

DAYOFYEAR( <timestamp> )

Devuelve el día del año de un tipo de datos de marca de tiempo como entero entre 1 y 366, inclusivamente. El entero 1 se refiere al 1 de enero.

Ejemplo:

SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));

Devoluciones: 275

FORMAT_UTC_USEC( <unix_timestamp> )

Devuelve una representación de cadena legible por humanos de una marca de tiempo UNIX en el formato YYYY-MM-DD HH:MM:SS.uuuuuu .

Ejemplo:

SELECT FORMAT_UTC_USEC(1274259481071200);

Devoluciones: 2010-05-19 08: 58: 01.071200

HOUR( <timestamp> )

Devuelve la hora de un tipo de datos de marca de tiempo como entero entre 0 y 23, inclusivamente.

Ejemplo:

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

Devoluciones: 5

MINUTE( <timestamp> )

Devuelve las actas de un tipo de datos de marca de tiempo como entero entre 0 y 59, inclusivamente.

Ejemplo:

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

Devoluciones: 23

MONTH( <timestamp> )

Devuelve el mes de un tipo de datos de marca de tiempo como entero entre 1 y 12, inclusivamente.

Ejemplo:

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

Devoluciones: 10

MSEC_TO_TIMESTAMP( <expr> )

Convierte una marca de tiempo UNIX en milisegundos a un tipo de datos de marca de tiempo.

Ejemplo:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

Devoluciones: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Devoluciones: 2012-10-01 01:02:04 UTC

NOW()

Devuelve la marca de tiempo UNIX actual en microsegundos.

Ejemplo:

SELECT NOW();

Devoluciones: 1359685811687920

PARSE_UTC_USEC( <date_string> )

Convierte una cadena 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 fraccional del segundo puede tener hasta 6 dígitos de largo o puede omitirse.

TimestAMP_TO_USEC es una función equivalente que convierte un argumento de tipo de datos de marca de tiempo en lugar de una cadena de fecha.

Ejemplo:

SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");

Devoluciones: 1349056984000000

QUARTER( <timestamp> )

Devuelve el trimestre del año de un tipo de datos de marca de tiempo como entero entre 1 y 4, inclusivamente.

Ejemplo:

SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));

Devoluciones: 4

SEC_TO_TIMESTAMP( <expr> )

Convierte una marca de tiempo UNIX en segundos a un tipo de datos de marca de tiempo.

Ejemplo:

SELECT SEC_TO_TIMESTAMP(1355968987);

Devoluciones: 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

Devoluciones: 2012-12-20 02:03:07 UTC

SECOND( <timestamp> )

Devuelve los segundos de un tipo de datos de marca de tiempo como entero entre 0 y 59, inclusivamente.

Durante un salto segundo , el rango entero es de entre 0 y 60, inclusivamente.

Ejemplo:

SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));

Devoluciones: 48

STRFTIME_UTC_USEC( <unix_timestamp> ,
<date_format_str> )

Devuelve una cadena de fecha legible para humanos en el formato date_format_str . date_format_str puede incluir caracteres de puntuación relacionados con la fecha (como / y - ) y caracteres especiales aceptados por la función Strftime en C ++ (como %D para el día del mes).

Utilice las funciones UTC_USEC_TO_ <function_name> Si planea agrupar los datos de consulta mediante intervalos de tiempo, como obtener todos los datos durante un cierto mes, porque las funciones son más eficientes.

Ejemplo:

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

Devoluciones: 2010-05-19

TIME( <timestamp> )

Devuelve una cadena legible por humanos de un tipo de datos de marca de tiempo, en el formato %H:%M:%S .

Ejemplo:

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

Devuelve: 02:03:04

TIMESTAMP( <date_string> )

Convierta una cadena de fecha en un tipo de datos de marca de tiempo.

Ejemplo:

SELECT TIMESTAMP("2012-10-01 01:02:03");

Devoluciones: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC( <timestamp> )

Convierte un tipo de datos de marca de tiempo en una marca de tiempo UNIX en milisegundos.

Ejemplo:

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));

Devoluciones: 1349053323000

TIMESTAMP_TO_SEC( <timestamp> )

Convierte un tipo de datos de marca de tiempo en una marca de tiempo UNIX en segundos.

Ejemplo:

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

Devoluciones: 1349053323

TIMESTAMP_TO_USEC( <timestamp> )

Convierte un tipo de datos TIMESTAMP en una marca de tiempo UNIX en microsegundos.

Parse_utc_usec es una función equivalente que convierte un argumento de cadena de datos en lugar de un tipo de datos de marca de tiempo.

Ejemplo:

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

Devoluciones: 1349053323000000

USEC_TO_TIMESTAMP( <expr> )

Convierte una marca de tiempo UNIX en microsegundos a un tipo de datos de marca de tiempo.

Ejemplo:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

Devoluciones: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

Devoluciones: 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY( <unix_timestamp> )

Cambia una marca de tiempo UNIX en microsegundos al comienzo del día en que ocurre.

Por ejemplo, si unix_timestamp ocurre el 19 de mayo a las 08:58, esta función devuelve una marca de tiempo UNIX para el 19 de mayo a las 00:00 (medianoche).

Ejemplo:

SELECT UTC_USEC_TO_DAY(1274259481071200);

Devoluciones: 1274227200000000

UTC_USEC_TO_HOUR( <unix_timestamp> )

Cambia una marca de tiempo Unix en microsegundos al comienzo de la hora en la que ocurre.

Por ejemplo, si unix_timestamp ocurre a las 08:58, esta función devuelve una marca de tiempo UNIX para 08:00 el mismo día.

Ejemplo:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

Devoluciones: 1274256000000000

UTC_USEC_TO_MONTH( <unix_timestamp> )

Cambia una marca de tiempo UNIX en microsegundos al comienzo del mes en el que ocurre.

Por ejemplo, si unix_timestamp ocurre el 19 de marzo, esta función devuelve una marca de tiempo UNIX para el 1 de marzo del mismo año.

Ejemplo:

SELECT UTC_USEC_TO_MONTH(1274259481071200);

Devoluciones: 1272672000000000

UTC_USEC_TO_WEEK( <unix_timestamp> ,
<day_of_week> )

Devuelve 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 desde 0 (domingo) hasta 6 (sábado).

Por ejemplo, si unix_timestamp ocurre el viernes 2008-04-11, y establece day_of_week a 2 (martes), la función devuelve una marca de tiempo UNIX para el martes 2008-04-08.

Ejemplo:

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

Devoluciones: 1207612800000000

UTC_USEC_TO_YEAR( <unix_timestamp> )

Devuelve 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 devuelve 1274259481071200 , la representación de microsegundos de 2010-01-01 00:00 .

Ejemplo:

SELECT UTC_USEC_TO_YEAR(1274259481071200);

Devoluciones: 1262304000000000

WEEK( <timestamp> )

Devuelve la semana de un tipo de datos de marca de tiempo como entero entre 1 y 53, inclusivamente.

Las semanas comienzan el domingo, por lo que si el 1 de enero está en un día distinto del 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'));

Devoluciones: 53

YEAR( <timestamp> )

Devuelve el año de un tipo de datos de marca de tiempo.

Ejemplo:

SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

Devoluciones: 2012

Ejemplos avanzados

• Convertir los resultados de la marca de tiempo entero en formato legible por humanos

  La siguiente consulta encuentra los 5 momentos principales en el tiempo en los que tuvieron lugar la mayoría de las revisiones de Wikipedia. Para mostrar los resultados en un formato legible por humanos, use la función FORMAT_UTC_USEC() de BigQuery, que toma una marca de tiempo, en microsegundos, como una entrada. Esta consulta multiplica las marcas de tiempo del formato Wikipedia Posix (en segundos) en 1000000 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];

  Devoluciones:

  +----------------------------+----------------+
  |     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 |
  +----------------------------+----------------+
  

• RESULTADOS DE CONJUNTO POR TIMESTAMP

  Es útil usar funciones de fecha y hora para los resultados de consulta grupal en cubos correspondientes a años, meses o días particulares. El siguiente ejemplo utiliza la función UTC_USEC_TO_MONTH() para mostrar cuántos caracteres usa cada contribuyente de Wikipedia 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;

  Devuelve (truncado):

  +--------------------------------+---------+-----------------------+
  |      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 IP

Las funciones IP convierten las direcciones IP hacia y desde la forma legible por humanos.

Sintaxis

FORMAT_IP( integer_value )

Convierte 32 bits menos significativos de integer_value en cadena de dirección IPv4 legible por humanos. Por ejemplo, FORMAT_IP(1) devolverá la cadena '0.0.0.1' .

PARSE_IP( readable_ip )

Convierte una cadena que representa la dirección IPv4 a un valor entero sin firmar. Por ejemplo, PARSE_IP('0.0.0.1') volverá 1 Si la cadena no es una dirección IPv4 válida, PARSE_IP devolverá NULL .

BigQuery admite escribir direcciones IPv4 e IPv6 en cadenas empaquetadas, como datos binarios de 4 o 16 bytes en el orden de bytes de red. Las funciones descritas a continuación admiten las direcciones hacia y desde la forma legible humana. Estas funciones solo funcionan en campos de cadenas con IPS.

Sintaxis

FORMAT_PACKED_IP( packed_ip )

Devuelve una dirección IP legible por humanos, en el Formulario 10.1.5.23 o 2620:0:1009:1:216:36ff:feef:3f . Ejemplos:

• FORMAT_PACKED_IP('0123456789@ABCDE') Devuelve '3031:3233:3435:3637:3839:4041:4243:4445'
• FORMAT_PACKED_IP('0123') Devuelve '48.49.50.51'

PARSE_PACKED_IP( readable_ip )

Devuelve una dirección IP en bytes . Si la cadena de entrada no es una dirección IPv4 o IPv6 válida, PARSE_PACKED_IP devolverá NULL . Ejemplos:

• PARSE_PACKED_IP('48.49.50.51') Devuelve 'MDEyMw=='
• PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') Devuelve 'MDEyMzQ1Njc4OUBBQkNERQ=='

Funciones JSON

Las funciones JSON de BigQuery le brindan la capacidad de encontrar valores dentro de sus datos JSON almacenados, utilizando expresiones similares a JSONPath .

El almacenamiento de datos JSON puede ser más flexible que declarar todos sus campos individuales en el esquema de su tabla, pero puede conducir a mayores costos. Cuando selecciona datos de una cadena JSON, se le cobra por escanear toda la cadena, que es más caro que si cada campo está en una columna separada. La consulta también es más lenta ya que toda la cadena debe analizarse en el momento de la consulta. Pero para los esquemas ad-hoc o que cambian rápidamente, la flexibilidad de JSON puede valer la pena el costo adicional.

Use funciones JSON en lugar de las funciones de expresión regulares de BigQuery si trabaja con datos estructurados, ya que las funciones JSON son más fáciles de usar.

Sintaxis

JSON_EXTRACT( json , json_path )

Selecciona un valor en json de acuerdo con la expresión JSONPath json_path . json_path debe ser una constante de cadena. Devuelve el valor en formato de cadena JSON.

JSON_EXTRACT_SCALAR( json , json_path )

Selecciona un valor en json de acuerdo con la expresión JSONPath json_path . json_path debe ser una constante de cadena. Devuelve un valor escalar JSON.

Operadores lógicos

Los operadores lógicos realizan una lógica binaria o ternaria sobre expresiones. La lógica binaria devuelve true o false . La lógica ternaria acomoda los valores NULL y devuelve true , false o NULL .

Sintaxis

expr AND expr

• Devuelve true si ambas expresiones son verdaderas.
• Devuelve false si una o ambas expresiones son falsas.
• Devuelve NULL si ambas expresiones son nulas o una expresión es verdadera y la otra es nula.

expr OR expr

• Devuelve true si una o ambas expresiones son verdaderas.
• Devuelve false si ambas expresiones son falsas.
• Devuelve NULL si ambas expresiones son nulas o una expresión es falsa y la otra es nula.

NOT expr

• Devuelve true si la expresión es falsa.
• Devuelve false si la expresión si es verdadera.
• Devuelve NULL si la expresión es nula.

NOT puede usar con otras funciones como 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 devuelven un resultado numérico. Cada argumento puede ser un valor numérico literal o un valor numérico devuelto por una consulta. Si la función matemática se evalúa como un resultado indefinido, la operación devuelve NULL .

Sintaxis

ABS( numeric_expr )

Devuelve el valor absoluto del argumento.

ACOS( numeric_expr )

Devuelve el arco coseno del argumento.

ACOSH( numeric_expr )

Devuelve el coseno hiperbólico del arco del argumento.

ASIN( numeric_expr )

Devuelve el arco seno del argumento.

ASINH( numeric_expr )

Devuelve el seno hiperbólico del arco del argumento.

ATAN( numeric_expr )

Devuelve el arco tangente del argumento.

ATANH( numeric_expr )

Devuelve la tangente hiperbólica del arco del argumento.

ATAN2( numeric_expr1 , numeric_expr2 )

Devuelve la tangente de arco de los dos argumentos.

CEIL( numeric_expr )

Redonde el argumento hasta el número entero más cercano y devuelve el valor redondeado.

COS( numeric_expr )

Devuelve el coseno del argumento.

COSH( numeric_expr )

Devuelve el coseno hiperbólico del argumento.

DEGREES( numeric_expr )

Devuelve numeric_expr , convertido de radianes a grados.

EXP( numeric_expr )

Devuelve el resultado de elevar la constante "E", la base del logaritmo natural, al poder de Numeric_Expr .

FLOOR( numeric_expr )

Redondea el argumento al número entero más cercano y devuelve el valor redondeado.

LN( numeric_expr )
LOG( numeric_expr )

Devuelve el logaritmo natural del argumento.

LOG2( numeric_expr )

Devuelve el logaritmo base-2 del argumento.

LOG10( numeric_expr )

Devuelve el logaritmo base-10 del argumento.

PI()

Devuelve la constante π. La función PI() requiere paréntesis para indicar que es una función, pero no toma argumentos entre esos paréntesis. Puede usar PI() como una constante con funciones matemáticas y aritméticas.

POW( numeric_expr1 , numeric_expr2 )

Devuelve el resultado de elevar numeric_expr1 al poder de numeric_expr2 .

RADIANS( numeric_expr )

Devuelve numeric_expr , convertido de grados a radianes. (Tenga en cuenta que los radianos π equivalen a 180 grados).

RAND([ int32_seed ])

Devuelve un valor flotante aleatorio en el rango 0.0 <= valor <1.0. Cada valor int32_seed siempre genera la misma secuencia de números aleatorios dentro de una consulta dada, siempre que no use una cláusula LIMIT . Si no se especifica int32_seed , BigQuery usa la marca de tiempo actual como valor de semilla.

ROUND( numeric_expr [, digits ])

Redonde el argumento hacia arriba o hacia abajo al número entero más cercano (o si se especifica, al número especificado de dígitos) y devuelve el valor redondeado.

SIN( numeric_expr )

Devuelve el seno del argumento.

SINH( numeric_expr )

Devuelve el seno hiperbólico del argumento.

SQRT( numeric_expr )

Devuelve la raíz cuadrada de la expresión.

TAN( numeric_expr )

Devuelve la tangente del argumento.

TANH( numeric_expr )

Devuelve la tangente hiperbólica del argumento.

Ejemplos avanzados

• Consulta de caja delimitador

  La siguiente consulta devuelve una colección de puntos dentro de una caja delimitadora rectangular centrada 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 de círculo delimitador aproximado

  Devuelva 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 , centrados en Denver Colorado (39.73, -104.98). Esta consulta utiliza 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+latitud converge en los polos, esta consulta devuelve 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 soporte de expresión regular utilizando la biblioteca RE2 ; Vea esa documentación para su sintaxis de expresión regular .

Tenga en cuenta que las expresiones regulares son coincidencias globales; Para comenzar a coincidir al comienzo de una palabra, debe usar el carácter ^.

Sintaxis

REGEXP_MATCH(' str ', 'reg_exp' )

Devuelve verdadero si STR coincide con la expresión regular. Para la coincidencia de cadenas sin expresiones regulares, use contiene 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;

Devoluciones:

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+

REGEXP_EXTRACT(' str ', ' reg_exp ')

Devuelve 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;

Devoluciones:

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+

REGEXP_REPLACE(' orig_str ', ' reg_exp ', 'replace_str')

Devuelve una cadena donde cualquier subcadena de orig_str que coincida con Reg_EXP se reemplaza con reemplazar_str . Por ejemplo, regexp_replace ('hola', 'lo', 'p') devuelve ayuda.

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;

Devoluciones:

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+

Ejemplos avanzados

• Filtro de resultados establecidos por coincidencia de expresión regular

  Las funciones de expresión regulares de BigQuery se pueden usar para filtrar los resultados en una cláusula WHERE , así como para mostrar los resultados en la SELECT . El siguiente ejemplo combina estos dos casos de uso de expresión regular 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);

• Uso de expresiones regulares en datos enteros o flotantes

  Si bien las funciones de expresión regulares de BigQuery solo funcionan para los datos de cadena, es posible usar la función STRING() para fundir datos enteros o flotantes en el formato de cadena. En este ejemplo, STRING() se usa para lanzar el valor entero corpus_date a una cadena, que luego se altera 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 cadena

Las funciones de cadena operan con datos de cadena. Las constantes de cadena deben estar entre comillas simples o dobles. Las funciones de cadena distinguen entre mayúsculas y minúsculas de forma predeterminada. Puede añadir IGNORE CASE al final de una consulta para habilitar la coincidencia sin distinción entre mayúsculas y minúsculas. IGNORE CASE solo funciona con 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, use funciones de expresión regulares .

Sintaxis

CONCAT(' str1 ', ' str2 ', '...')
str1 + str2 + ...

Returns the concatenation of two or more strings, or NULL if any of the values are NULL. Example: if str1 is Java and str2 is Script , CONCAT returns JavaScript .

expr CONTAINS ' str '

Returns true if expr contains the specified string argument. This is a case-sensitive comparison.

INSTR(' str1 ', ' str2 ')

Returns the one-based index of the first occurrence of str2 in str1 , or returns 0 if str2 does not occur in str1 .

LEFT(' str ', numeric_expr )

Returns the leftmost numeric_expr characters of str . If the number is longer than str , the full string will be returned. Example: LEFT('seattle', 3) returns sea .

LENGTH(' str ')

Returns a numerical value for the length of the string. Example: if str is '123456' , LENGTH returns 6 .

LOWER(' str ')

Returns the original string with all characters in lower case.

LPAD(' str1 ', numeric_expr , ' str2 ')

Pads str1 on the left with str2 , repeating str2 until the result string is exactly numeric_expr characters. Example: LPAD('1', 7, '?') returns ??????1 .

LTRIM(' str1 ' [, str2 ])

Removes characters from the left side of str1 . If str2 is omitted, LTRIM removes spaces from the left side of str1 . Otherwise, LTRIM removes any characters in str2 from the left side of str1 (case-sensitive).

Ejemplos:

SELECT LTRIM("Say hello", "yaS") returns " hello" .

SELECT LTRIM("Say hello", " ySa") returns "hello" .

REPLACE(' str1 ', ' str2 ', ' str3 ')

Replaces all instances of str2 within str1 with str3 .

RIGHT(' str ', numeric_expr )

Returns the rightmost numeric_expr characters of str . If the number is longer than the string, it will return the whole string. Example: RIGHT('kirkland', 4) returns land .

RPAD(' str1 ', numeric_expr , ' str2 ')

RTRIM(' str1 ' [, str2 ])

Removes trailing characters from the right side of str1 . If str2 is omitted, RTRIM removes trailing spaces from str1 . Otherwise, RTRIM removes any characters in str2 from the right side of str1 (case-sensitive).

Ejemplos:

SELECT RTRIM("Say hello", "leo") returns "Say h" .

SELECT RTRIM("Say hello ", " hloe") returns "Say" .

SPLIT(' str ' [, 'delimiter'])

Splits a string into repeated substrings. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.

SUBSTR(' str ', index [, max_len ])

Returns a substring of str , starting at index . If the optional max_len parameter is used, the returned string is a maximum of max_len characters long. Counting starts at 1, so the first character in the string is in position 1 (not zero). If index is 5 , the substring begins with the 5th character from the left in str . If index is -4 , the substring begins with the 4th character from the right in str . Example: SUBSTR(' awesome ', -4 , 4 ) returns the substring some .

UPPER(' str ')

Returns the original string with all characters in upper case.

'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 tabla

Table wildcard functions are a convenient way to query data from a specific set of tables. A table wildcard function is equivalent to a comma-separated union of all the tables matched by the wildcard function. When you use a table wildcard function, BigQuery only accesses and charges you for tables that match the wildcard. Table wildcard functions are specified in the query's FROM clause .

If you use table wildcard functions in a query, the functions no longer need to be contained in parentheses. For example, some of the following examples use parentheses, whereas others don't.

Cached results are not supported for queries against multiple tables using a wildcard function (even if the Use Cached Results option is checked). If you run the same wildcard query multiple times, you are billed for each query.

Sintaxis

TABLE_DATE_RANGE( prefix , timestamp1 , timestamp2 )

Queries daily tables that overlap with the time range between <timestamp1> and <timestamp2> .

Table names must have the following format: <prefix><day> , where <day> is in the format YYYYMMDD .

• TIMESTAMP('2012-10-01 02:03:04')
• DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

Example: get tables between two days

This example assumes the following tables exist:

• 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

Matches the following tables:

• mydata.people20140325
• mydata.people20140326
• mydata.people20140327

Example: get tables in a two-day range up to "now"

This example assumes the following tables exist in a project named 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

Matches the following tables:

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

TABLE_DATE_RANGE_STRICT( prefix , timestamp1 , timestamp2 )

This function is equivalent to TABLE_DATE_RANGE . The only difference is that if any daily table is missing in the sequence, TABLE_DATE_RANGE_STRICT fails and returns a Not Found: Table <table_name> error.

Example: error on missing table

This example assumes the following tables exist:

• 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

The above example returns an error "Not Found" for the table "people20140326".

TABLE_QUERY( dataset , expr )

Queries tables whose names match the supplied expr . The expr parameter must be represented as a string and must contain an expression to evaluate. For example, 'length(table_id) < 3' .

Example: match tables whose names contain "oo" and have a length greater than 4

This example assumes the following tables exist:

• 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'))

Matches the following tables:

• mydata.ooze
• mydata.spoon

Example: match tables whose names start with "boo", followed by 3-5 numeric digits

This example assumes the following tables exist in a project named 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}")')

Matches the following tables:

• mydata.book418
• mydata.boom12345

Funciones de URL

Sintaxis

HOST(' url_str ')

Given a URL, returns the host name as a string. Example: HOST('http://www.google.com:80/index.html') returns 'www.google.com'

DOMAIN(' url_str ')

Given a URL, returns the domain as a string. Example: DOMAIN('http://www.google.com:80/index.html') returns 'google.com'.

TLD(' url_str ')

Given a URL, returns the top level domain plus any country domain in the URL. Example: TLD('http://www.google.com:80/index.html') returns '.com'. TLD('http://www.google.co.uk:80/index.html') returns '.co.uk'.

Notas:

• These functions don't perform reverse DNS lookup, so if you call these functions using an IP address the functions will return segments of the IP address rather than segments of the host name.
• All of the URL parsing functions expect lower-case characters. Upper-case characters in the URL will result in a NULL or otherwise incorrect result. Consider passing input to this function through LOWER() if your data has mixed casing.

Ejemplo avanzado

Parse domain names from URL data

This query uses the DOMAIN() function to return the most popular domains listed as repository homepages on GitHub. Note the use of HAVING to filter records using the result of the DOMAIN() function. This is a useful function to determine referrer information from URL data.

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;

Devoluciones:

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

To look specifically at TLD information, use the TLD() function. This example displays the top TLDs that are not in a list of common examples.

#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;

Devoluciones:

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+

Funciones de ventana

Window functions, also known as analytic functions, enable calculations on a specific subset, or "window", of a result set. Window functions make it easier to create reports that include complex analytics such as trailing averages and running totals.

Each window function requires an OVER clause that specifies the window top and bottom. The three components of the OVER clause (partitioning, ordering, and framing) provide additional control over the window. Partitioning enables you to divide the input data into logical groups that have a common characteristic. Ordering enables you to order the results within a partition. Framing enables you to create a sliding window frame within a partition that moves relative to the current row. You can configure the size of the moving window frame based on a number of rows or a range of values, such as a time interval.

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )

PARTITION BY

Defines the base partition over which this function operates. Specify one or more comma-separated column names; one partition will be created for each distinct set of values for these columns, similar to a GROUP BY clause. If PARTITION BY is omitted, the base partition is all rows in the input to the window function.

The PARTITION BY clause also allows window functions to partition data and parallelize execution. If you wish to use a window function with allowLargeResults , or if you intend to apply further joins or aggregations to the output of your window function, use PARTITION BY to parallelize execution.

JOIN EACH and GROUP EACH BY clauses can't be used on the output of window functions. To generate large query results when using window functions, you must use PARTITION BY .

ORDER BY

Sorts the partition. If ORDER BY is absent, there is no guarantee of any default sorting order. Sorting occurs at the partition level, before any window frame clause is applied. If you specify a RANGE window, you should add an ORDER BY clause. Default order is ASC .

ORDER BY is optional in some cases, but certain window functions, such as rank() or dense_rank() , require the clause.

If you use ORDER BY without specifying ROWS or RANGE , ORDER BY implies that the window extends from the beginning of the partition to the current row. In the absence of an ORDER BY clause, the window is the entire partition.

<window-frame-clause>

{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}

A subset of the partition over which to operate. This can be the same size as the partition or smaller. If you use ORDER BY without a window-frame-clause , the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . If you omit both ORDER BY and the window-frame-clause , the default window frame is the entire partition.

• ROWS - Defines a window in terms of row position, relative to the current row. For example, to add a column showing the sum of the preceding 5 rows of salary values, you would query SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) . The set of rows typically includes the current row, but that is not required.
• RANGE - Defines a window in terms of a range of values in a given column, relative to that column's value in the current row. Only operates on numbers and dates, where date values are simple integers (microseconds since the epoch). Neighboring rows with the same value are called peer rows. Peer rows of the CURRENT ROW are included in a window frame that specifies CURRENT ROW . For example, if you specify the window end to be CURRENT ROW and the following row in the window has the same value, it will be included in the function calculation.
• BETWEEN <start> AND <end> - A range, inclusive of the start and end rows. The range need not include the current row, but <start> must precede or equal <end> .
• <start> - Specifies the start offset for this window, relative to the current row. The following options are supported:

  {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  where <expr> is a positive integer, PRECEDING indicates a preceding row number or range value, and FOLLOWING indicates a following row number or range value. UNBOUNDED PRECEDING means the first row of the partition. If the start precedes the window, it will be set to the first row of the partition.
• <end> - Specifies the end offset for this window, relative to the current row. The following options are supported:

  {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  where <expr> is a positive integer, PRECEDING indicates a preceding row number or range value, and FOLLOWING indicates a following row number or range value. UNBOUNDED FOLLOWING means the last row of the partition. If end is beyond the end of the window, it will be set to the last row of the partition.

Unlike aggregation functions, which collapse many input rows into one output row, window functions return one row of output for each row of input. This feature makes it easier to create queries that calculate running totals and moving averages. For example, the following query returns a running total for a small dataset of five rows defined by SELECT statements:

#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);