Sintaxis, funciones y operadores de SQL antiguo

En este documento se detalla la sintaxis, las funciones y los operadores de las consultas de SQL antiguo. La sintaxis de consulta preferida para BigQuery es GoogleSQL. Para obtener información sobre GoogleSQL, consulta la sintaxis de las consultas de GoogleSQL.

Sintaxis de consulta

Nota: En las palabras clave no se distingue entre mayúsculas y minúsculas. En este documento, las palabras clave, como SELECT, se escriben con mayúscula inicial con fines ilustrativos.

Cláusula SELECT

La cláusula SELECT especifica una lista de expresiones que se van a calcular. Las expresiones de la cláusula SELECT pueden contener nombres de campos, literales y llamadas a funciones (incluidas funciones de agregación y funciones de ventana), así como combinaciones de los 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 usar en las cláusulas GROUP BY, HAVING y ORDER BY de la consulta, pero no en las cláusulas FROM, WHERE ni OMIT RECORD IF, ni tampoco en otras expresiones de la misma cláusula SELECT.

Notas:

• Si usas una función de agregación en tu cláusula SELECT, debes usar una función de agregación en todas las expresiones o tu consulta debe tener una cláusula GROUP BY que incluya todos los campos no agregados de tu 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 de las palabras reservadas y usarlas como nombres de campo y alias. Por ejemplo, si tienes una columna llamada "partition", que es una palabra reservada en la sintaxis de BigQuery, las consultas que hagan referencia a ese campo fallarán y mostrarán mensajes de error confusos, a menos que la incluyas entre corchetes:

  SELECT [partition] FROM ...

Ejemplo

En este ejemplo se definen alias en la cláusula SELECT y, a continuación, se hace referencia a uno de ellos en la cláusula ORDER BY. Ten en cuenta que no se puede hacer referencia a la columna palabra mediante el alias_palabra en la cláusula WHERE. Se debe hacer referencia a ella por su nombre. El alias len tampoco se puede ver en la cláusula WHERE. Se mostraría en 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 de agregación

aggregate_function WITHIN RECORD [ [ AS ] alias ]

La palabra clave WITHIN hace que la función de agregación agregue los valores repetidos 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. Como la agregación por ámbito genera resultados para cada registro, se pueden seleccionar expresiones no agregadas junto con expresiones agregadas por ámbito sin usar una cláusula GROUP BY.

Lo más habitual es que uses el alcance RECORD cuando utilices la agregación por alcance. Si tienes un esquema anidado y repetido muy complejo, puede que necesites realizar agregaciones en ámbitos de subregistros. Para ello, sustituye la palabra clave RECORD de la sintaxis anterior por el nombre del nodo de tu esquema en el que quieras que se realice la agregación. Para obtener más información sobre este comportamiento avanzado, consulta el artículo Gestionar datos.

Ejemplo

En este ejemplo se realiza una agregación COUNT con ámbito y, a continuación, se filtran y ordenan 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 van a consultar. Las consultas de BigQuery se pueden ejecutar directamente en tablas, subconsultas, tablas combinadas y tablas modificadas por operadores especiales 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.

Hacer referencia a tablas

Cuando se hace referencia a una tabla, se deben especificar datasetId y tableId. project_name es opcional. Si no se especifica project_name, BigQuery utiliza el proyecto actual de forma predeterminada. Si el nombre del proyecto incluye un guion, debes poner entre corchetes toda la referencia de la tabla.

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 AS opcional se puede añadir entre tableId y el alias para mejorar la legibilidad.

Cuando haces referencia a columnas de una tabla, puedes usar el nombre de columna simple o añadirle el prefijo del alias (si has especificado uno) o del datasetId y el tableId, siempre que no se haya especificado ningún project_name. El project_name no se puede incluir en el prefijo de la columna porque el carácter de dos puntos no está permitido en los nombres de los campos.

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 añade el prefijo datasetId y tableId al nombre de la columna. Ten en cuenta que el project_name no se puede incluir 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 añade un prefijo al nombre de la columna con un alias de tabla.

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

Tablas con particiones de rangos de números enteros

El SQL antiguo permite usar decoradores de tabla para hacer referencia a una partición específica de una tabla con particiones de rangos de números enteros. La clave para dirigirse a una partición de rango es el inicio del rango.

En el siguiente ejemplo se consulta la partición de intervalo que empieza por 30:

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

Ten en cuenta que no puedes usar SQL antiguo para consultar toda una tabla con particiones de rangos de números enteros. En su lugar, la consulta devuelve un error como el siguiente:

Usar subconsultas

Una subconsulta es una instrucción SELECT anidada entre paréntesis. Las expresiones calculadas en la cláusula SELECT de la subconsulta están disponibles para la consulta externa del mismo modo que lo estarían las columnas de una tabla.

Las subconsultas se pueden usar para calcular agregaciones y otras expresiones. En la subconsulta se puede usar toda la gama de operadores de SQL. Esto significa que una subconsulta puede contener otras subconsultas, que pueden realizar combinaciones y agregaciones de agrupaciones, etc.

Coma como UNION ALL

A diferencia de GoogleSQL, el lenguaje SQL antiguo usa la coma como operador UNION ALL en lugar de como operador CROSS JOIN. Este es un comportamiento antiguo que ha evolucionado porque, históricamente, BigQuery no admitía CROSS JOIN y los usuarios de BigQuery necesitaban escribir consultas de UNION ALL con regularidad. En GoogleSQL, las consultas que realizan uniones son especialmente detalladas. Si se usa la coma como operador de unión, estas consultas se pueden escribir de forma mucho más eficiente. Por ejemplo, esta consulta se puede usar 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;

Las consultas que unen un gran número de tablas suelen ejecutarse más lentamente 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 de comodín de tabla

El término función comodín de tabla hace referencia a un tipo especial de función exclusivo de BigQuery. Estas funciones se usan en la cláusula FROM para buscar una colección de nombres de tabla con uno de los distintos tipos de filtros. Por ejemplo, la función TABLE_DATE_RANGE se puede usar para consultar solo un conjunto específico de tablas diarias. Para obtener más información sobre estas funciones, consulta 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 de SQL habituales, BigQuery se ha diseñado para gestionar datos repetidos. Por este motivo, los usuarios de BigQuery a veces tienen que escribir consultas que manipulen la estructura de los registros repetidos. Una forma de hacerlo es usar el operador FLATTEN.

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

Para obtener más información y ejemplos, consulta Gestionar datos.

Operador JOIN

BigQuery admite varios operadores JOIN en cada cláusula FROM. Las operaciones JOIN posteriores usan 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 JOIN

BigQuery admite 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 devolver una gran cantidad de datos y dar lugar a una consulta lenta e ineficiente o a una consulta que supere los recursos máximos permitidos por consulta. Estas consultas fallarán y devolverán un error. Cuando sea posible, se recomienda usar consultas que no utilicen CROSS JOIN. Por ejemplo, CROSS JOIN se suele usar en lugares donde las funciones de ventana serían más eficientes.

Modificador EACH

El modificador EACH es una sugerencia que indica a BigQuery que ejecute JOIN con varias particiones. Esto resulta especialmente útil cuando sabes 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í. Cuando sea posible, usa JOIN sin el modificador EACH para obtener un mejor rendimiento. Usa JOIN EACH cuando tu consulta haya fallado y se haya mostrado un mensaje de error que indica que se han superado los recursos.

Combinación semi-interna y anti-interna

Además de admitir JOIN en la cláusula FROM, BigQuery también admite dos tipos de combinaciones en la cláusula WHERE: combinaciones semi y anti-semi. Una semicombinación se especifica mediante la palabra clave IN con una subconsulta, mientras que una anticombinación se especifica mediante la palabra clave NOT IN.

Ejemplos

La siguiente consulta usa una semicombinación para buscar n-gramas en los que la primera palabra del n-grama también sea la segunda palabra de otro n-grama que tenga "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 unión parcial 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 los números de los otros 40 estados, puedes usar una anti-combinació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 las combinaciones semi o anti-semi correlacionadas. La subconsulta no puede hacer referencia a ningún campo de la consulta externa.
• La subconsulta utilizada en una combinación semi o anti-semi debe seleccionar exactamente un campo.
• Los tipos del campo seleccionado y del campo que se usa en la consulta externa en la cláusula WHERE deben coincidir exactamente. BigQuery no realizará ningún tipo de conversión para las combinaciones semi o anti-semi.

Cláusula WHERE

La cláusula WHERE, a veces llamada predicado, filtra los registros generados por la cláusula FROM mediante una expresión booleana. Se pueden combinar varias condiciones con cláusulas booleanas AND y OR, que se pueden agrupar entre paréntesis. Los campos que se enumeran en una cláusula WHERE no tienen por qué seleccionarse 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 la cláusula WHERE.

Nota: Las funciones de agregación no se pueden usar en la cláusula WHERE. Usa una cláusula HAVING y una consulta externa si necesitas filtrar el resultado de una función de agregado.

Ejemplo

En el siguiente ejemplo se usa 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 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 estructura exclusiva de BigQuery. Es especialmente útil para tratar con esquemas anidados y repetidos. Es similar a una cláusula WHERE , pero se diferencia en dos aspectos importantes. En primer lugar, usa 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) usar funciones de agregación con ámbito en su condición.

Además de filtrar registros completos, OMIT...IF puede especificar un ámbito más reducido para filtrar solo partes de un registro. Para ello, se usa el nombre de un nodo que no sea hoja en el esquema en lugar de RECORD en la cláusula OMIT...IF. Los usuarios de BigQuery rara vez utilizan esta función. Puedes consultar más documentación sobre este comportamiento avanzado en la documentación de WITHIN que aparece más arriba.

Si usa OMIT...IF para excluir una parte de un registro de un campo repetido y la consulta también selecciona otros campos repetidos de forma independiente, BigQuery omite una parte de los otros registros repetidos de la consulta. Si ves el error Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, te recomendamos que cambies a GoogleSQL. Para obtener información sobre cómo migrar instrucciones OMIT...IF a GoogleSQL, consulta Migrar a GoogleSQL.

Ejemplo

Volviendo al ejemplo del modificador WITHIN, se puede usar OMIT RECORD IF para conseguir lo mismo que 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 te permite agrupar las filas que tienen los mismos valores en un campo o conjunto de campos determinado para que puedas calcular agregaciones de campos relacionados. La agrupación se produce después del filtrado realizado en la cláusula WHERE, pero antes de que se calculen las expresiones de 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 busca las diez primeras palabras más comunes del conjunto de datos de ejemplo de trigramas. Además de mostrar el uso de la cláusula GROUP BY, se explica cómo se pueden usar los índices de posición en lugar de los nombres de los campos 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 por ámbito, la agregación agrupada es habitual en la mayoría de los sistemas de procesamiento de SQL.

El modificador EACH

El modificador EACH es una sugerencia que indica a BigQuery que ejecute GROUP BY con varias particiones. Esto es especialmente útil cuando sabes que tu conjunto de datos contiene un gran número de valores distintos para las claves de grupo.

Antes, se recomendaba usar EACH en muchos casos, pero ya no es así. Si usas GROUP BY sin el modificador EACH, suele obtenerse un mejor rendimiento. Usa GROUP EACH BY cuando tu consulta haya fallado y se haya mostrado un mensaje de error que indica que se han superado los recursos.

Función ROLLUP

Cuando se usa la función ROLLUP, BigQuery añade filas adicionales al resultado de la consulta que representan agregaciones acumuladas. Todos los campos que aparecen después de ROLLUP deben incluirse entre un único conjunto de paréntesis. En las filas añadidas debido a la función ROLLUP, NULL indica las columnas en las que se agrega la información.

Ejemplo

Esta consulta genera recuentos anuales de nacimientos de niños y niñas a partir del conjunto de datos de natalidad de ejemplo.

#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. Observa que hay filas en las que una o ambas claves de grupo son NULL. Estas filas son las filas de agregación.

+------+---------+----------+
| 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, se puede usar la función GROUPING para distinguir entre las filas que se han añadido debido a la función ROLLUP y las filas que realmente tienen un valor NULL para la clave de grupo.

Ejemplo

Esta consulta añade la función GROUPING al ejemplo anterior para identificar mejor las filas añadidas 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 de la cláusula SELECT deben incluirse 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 puede agrupar por valores de coma flotante y de doble precisión porque la función de igualdad de esos tipos no está bien definida.
• Como 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 puede solucionar algunos problemas de escalado.

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, 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 los resultados de la cláusula SELECT correspondiente.

Ejemplo

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

La cláusula ORDER BY se evalúa después de la cláusula SELECT, por lo que puede hacer referencia al resultado 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 del conjunto de resultados que se devuelve. Como las consultas de BigQuery suelen operar con un número muy elevado de filas, LIMIT es una buena forma de evitar que las consultas se prolonguen demasiado, ya que solo se procesa un subconjunto de las filas.

Notas:

• La cláusula LIMIT detendrá el procesamiento y devolverá los resultados cuando cumpla tus requisitos. Esto puede reducir el tiempo de procesamiento de algunas consultas, pero cuando especificas funciones de agregación, como COUNT o cláusulas ORDER BY, se debe procesar todo el conjunto de resultados antes de devolverlos. La cláusula LIMIT es la última que se evalúa.
• Una consulta con una cláusula LIMIT puede seguir siendo no determinista si no hay ningún operador en la consulta que garantice el orden del conjunto de resultados de salida. Esto se debe a que BigQuery se ejecuta con un gran número de trabajadores paralelos. No se garantiza el orden en el que se devuelven los trabajos paralelos.
• La cláusula LIMIT no puede contener ninguna función; solo acepta una constante numérica.
• Cuando se usa la cláusula LIMIT, los bytes totales procesados y los bytes facturados pueden variar en la misma consulta.

Gramática de las consultas

Las cláusulas individuales de las instrucciones SELECT de BigQuery se describen en detalle más arriba. Aquí se presenta la gramática completa de las instrucciones de SELECT de forma compacta con enlaces a las secciones correspondientes.

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 "{ }" contienen un conjunto de opciones.
• La barra vertical "|" indica un operador OR lógico.
• Una coma o una 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.

Funciones y operadores admitidos

La mayoría de las cláusulas de la instrucción SELECT admiten funciones. Los campos a los que se hace referencia en una función no tienen que aparecer 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

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

• Agregación de tabla

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

  SELECT COUNT(f1) FROM ds.Table;

• Agregación de grupo

  Usa 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 de ámbito

  Esta función solo se aplica a las tablas que tienen campos anidados.
  Usa una función de agregación y la palabra clave WITHIN para agregar valores repetidos en un ámbito definido. Por ejemplo:

  SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

  El ámbito puede ser RECORD, lo que corresponde a toda la fila, o bien un nodo (campo repetido de una fila). Las funciones de agregación operan en los valores del ámbito y devuelven resultados agregados para cada registro o nodo.

Puede aplicar una restricción a una función de agregado mediante una de las siguientes opciones:

• Un alias en una subconsulta. 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 la media de los valores de un grupo de filas calculada por numeric_expr. Las filas con un valor NULL 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 de NULL. Esta función devuelve NULL si todas las instancias de numeric_expr dan como resultado 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 de NULL. Esta función devuelve NULL si todas las instancias de numeric_expr dan como resultado 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. Se ignoran los valores de NULL. Esta función devuelve NULL si todas las instancias de numeric_expr dan como resultado 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) en el ámbito de la función. A menos que uses COUNT(*) con la función TOP, es mejor que especifiques explícitamente el campo que quieres contar.

COUNT([DISTINCT] field [, n])

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

Si usa la palabra clave DISTINCT, la función devuelve el número de valores distintos del campo especificado. Ten en cuenta que el valor devuelto de DISTINCT es una aproximación estadística y no se garantiza que sea exacto.

Usa EXACT_COUNT_DISTINCT() para obtener una respuesta exacta.

Si necesitas una mayor precisión de COUNT(DISTINCT), puedes especificar un segundo parámetro, n, que indica el umbral por debajo del cual se garantizan los resultados exactos. De forma predeterminada, n es 1000, pero si le asignas un valor más alto, obtendrás resultados exactos para COUNT(DISTINCT) hasta ese valor de n.n Sin embargo, si asignas valores más grandes a n, se reducirá la escalabilidad de este operador y puede que aumente considerablemente el tiempo de ejecución de la consulta o que la consulta falle.

Para calcular el número exacto de valores distintos, usa EXACT_COUNT_DISTINCT. Si quieres un enfoque más escalable, puedes usar GROUP EACH BY en los campos correspondientes y, a continuación, aplicar COUNT(*). El enfoque GROUP EACH BY es más escalable, pero puede conllevar una ligera penalización del rendimiento inicial.

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 NULL del campo especificado. Para mejorar la escalabilidad y el rendimiento, usa COUNT(DISTINCT field).

FIRST(expr)

Devuelve el primer valor secuencial del ámbito de la función.

GROUP_CONCAT('str' [, separator])

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

Si una cadena de los datos de origen contiene el carácter de comillas dobles, GROUP_CONCAT devuelve la cadena con comillas dobles añadidas. Por ejemplo, la cadena a"b se devolvería como "a""b". Usa GROUP_CONCAT_UNQUOTED si prefieres que estas cadenas no se devuelvan con comillas dobles añadidas.

Ejemplo:

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

GROUP_CONCAT_UNQUOTED('str' [, separator])

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

A diferencia de GROUP_CONCAT, esta función no añadirá comillas dobles a los valores devueltos que incluyan el carácter de comillas dobles. Por ejemplo, la cadena a"b se devolverí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 del ámbito de la función.

MAX(field)

Devuelve el valor máximo del ámbito de la función.

MIN(field)

Devuelve el valor mínimo del ámbito de la función.

NEST(expr)

Agrega todos los valores del ámbito 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 por cada valor de x distinto y contiene un campo repetido para todos los valores de y emparejados con x en la entrada de la consulta. La función NEST requiere una cláusula GROUP BY.

BigQuery aplana automáticamente los resultados de las consultas, 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 produzca resultados intermedios para que la misma consulta los use de inmediato.

NTH(n, field)

Devuelve el n-ésimo valor secuencial en el ámbito de la función, donde n es una constante. La función NTH empieza a contar en 1, por lo que no hay ningún término cero. Si el ámbito de la función tiene menos de n valores, la función devuelve NULL.

QUANTILES(expr[, buckets])

Calcula los valores mínimos, máximos y cuantiles aproximados de la expresión de entrada. Se ignoran los valores de entrada de NULL. Si la entrada está vacía o solo contiene NULL, la salida será NULL. El número de cuantiles calculados se controla con el parámetro opcional buckets, que incluye el mínimo y el máximo en el recuento. Para calcular los N-tiles aproximados, usa N+1 buckets. El valor predeterminado de buckets es 100. Nota: El valor predeterminado de 100 no estima percentiles. Para estimar percentiles, usa al menos 101 buckets. Si se especifica explícitamente, buckets debe ser al menos 2.

El error fraccionario por cuantil es épsilon = 1 / buckets, lo que significa que el error disminuye a medida que aumenta el número de contenedores. 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 elegir un cuantil concreto, pero recuerda que NTH se basa en 1 y que QUANTILES devuelve el mínimo (cuantil "0") en la primera posición y el máximo (percentil "100" o N-cuantil "N") 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 contenedores. Por ejemplo, para reducir el margen de error de los cálculos anteriores del 5% al 0,1%, utilice 1001 contenedores en lugar de 21 y ajuste el argumento de la función NTH en consecuencia. Para calcular la mediana con un error del 0,1 %, usa NTH(501, QUANTILES(expr, 1001)). Para calcular el percentil 95 con un error del 0,1 %, usa NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)

Devuelve la desviación estándar de los valores calculados por numeric_expr. Las filas con valor NULO no se incluyen en el cálculo. La función STDDEV es un alias de STDDEV_SAMP.

STDDEV_POP(numeric_expr)

Calcula la desviación estándar poblacional del valor calculado por numeric_expr. Usa 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 solo incluye una muestra representativa de la población, utilice STDDEV_SAMP(). Para obtener más información sobre la desviación estándar de la población y de la muestra, consulta el artículo Desviación estándar en Wikipedia.

STDDEV_SAMP(numeric_expr)

Calcula la desviación estándar muestral del valor calculado por numeric_expr. Usa STDDEV_SAMP() para calcular la desviación estándar de una población completa a partir de una muestra representativa de la población. Si el conjunto de datos incluye a toda la población, usa STDDEV_POP(). Para obtener más información sobre la desviación estándar de la población y de la muestra, consulta el artículo Desviación estándar en Wikipedia.

SUM(field)

Devuelve la suma total de los valores del ámbito de la función. Se usa únicamente con tipos de datos numéricos.

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

Devuelve los max_records registros principales por frecuencia. Consulta la descripción de TOP que aparece más abajo para obtener más información.

UNIQUE(expr)

Devuelve el conjunto de valores únicos no nulos del ámbito de la función en un orden indefinido. Al igual que una cláusula GROUP BY grande sin la palabra clave EACH, la consulta fallará con un error "Resources Exceeded" (Recursos superados) si hay demasiados valores distintos. Sin embargo, a diferencia de GROUP BY, la función UNIQUE se puede aplicar con una agregación acotada, lo que permite operar de forma 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 valor NULO no se incluyen en el cálculo. La función VARIANCE es un alias de VAR_SAMP.

VAR_POP(numeric_expr)

Calcula la varianza de la población de los valores calculados por numeric_expr. Para obtener más información sobre la desviación estándar de la población y de la muestra, consulta el artículo Desviación estándar en Wikipedia.

VAR_SAMP(numeric_expr)

Calcula la varianza de la muestra de los valores calculados por numeric_expr. Para obtener más información sobre la desviación estándar de la población y de la muestra, consulta el artículo 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 de GROUP BY ... ORDER BY ... LIMIT .... Por lo general, la función TOP se ejecuta más rápido que la consulta ... GROUP BY ... ORDER BY ... LIMIT ... completa, pero solo puede devolver resultados aproximados. A continuación, se indica la sintaxis de la función TOP:

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

Cuando se usa TOP en una cláusula SELECT, debe incluir COUNT(*) como uno de los campos.

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

field|alias

Campo o alias que se va a devolver.

max_values

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

multiplier

Número entero positivo que aumenta los valores devueltos por COUNT(*) en el múltiplo especificado.

Ejemplos de TOP()

• Ejemplos de consultas 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");

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

  La consulta devuelve, por orden, las 10 palabras más usadas que contienen "th" y el número de documentos en los que se han usado. 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';

• Usa el parámetro multiplier.

  En las siguientes consultas se muestra cómo afecta el parámetro multiplier al resultado de la consulta. La primera consulta devuelve el número de nacimientos al 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";

  Devuelve:

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

  Devuelve:

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

Nota: Para usar TOP, debes incluir COUNT(*) en la cláusula SELECT.

Ejemplos avanzados

• Media y desviación estándar agrupadas por condición

  La consulta siguiente devuelve el promedio y la desviación estándar de los pesos de recién nacidos en Ohio en 2003, agrupados según si las madres fuman o 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;

• Filtrar los resultados de una consulta con un valor agregado

  Para filtrar los resultados de una consulta mediante un valor agregado (por ejemplo, para filtrar por el valor de un 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

  Devuelve:

  +-------+--------+---------+
  | 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 literal numérico o un valor numérico devuelto por una consulta. Si la operación aritmética da como resultado un valor indefinido, la operación devuelve NULL.

Sintaxis

Funciones de 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, consulta Operación bit a bit.

En funciones de agregación se documentan tres funciones bit a bit adicionales: BIT_AND, BIT_OR y BIT_XOR.

Sintaxis

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 particularmente útiles para garantizar que los argumentos de una función de comparación tengan el mismo tipo de datos.

Sintaxis

BOOLEAN(<numeric_expr>)

• Devuelve true si <numeric_expr> no es 0 ni NULL.
• Devuelve false si <numeric_expr> es 0.
• Devuelve NULL si <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 tipo type.

FLOAT(expr)

Devuelve expr como un valor de doble precisión. 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)

Convierte expr en un entero de 64 bits.

• Devuelve NULL si expr es una cadena que no corresponde a un valor entero.
• Devuelve el número de microsegundos transcurridos desde el inicio del registro de tiempo 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 en función de los siguientes tipos de comparaciones:

• Comparación de dos expresiones.
• Comparación de una expresión o un conjunto de expresiones con un criterio específico, como estar en una lista determinada, ser NULL o ser un valor opcional no predeterminado.

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

Puedes usar expresiones numéricas o de cadena como argumentos para las funciones de comparación. (Las constantes de cadena deben ir entre comillas simples o dobles). Las expresiones pueden ser literales o valores recuperados por una consulta. Normalmente, las funciones de comparación se usan como condiciones de filtro en cláusulas WHERE, aunque también 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 que expr2.

expr1 <= expr2

devuelve true si expr1 es menor o igual que expr2.

expr1 BETWEEN expr2 AND expr3

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

expr IS NULL

devuelve true si expr es NULL.

expr IN(expr1, expr2, ...)

Devuelve 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 que se usan 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 combinaciones semi-joins y anti-joins. Para obtener más información, consulta Semi-join y Anti-join.

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

Devuelve el primer argumento que no es NULL.

GREATEST(numeric_expr1, numeric_expr2, ...)

Devuelve 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 devuelve NULL.

Para ignorar los valores NULL, usa la función IFNULL para cambiar los valores NULL por un valor que no afecte a la comparación. En el siguiente ejemplo de código, se usa la función IFNULL para cambiar los valores de NULL a -1, lo que no afecta a 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 infinito positivo o negativo.

IS_NAN(numeric_expr)

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

IS_EXPLICITLY_DEFINED(expr)

Esta función ya no está disponible. En su lugar, usa expr IS NOT NULL.

LEAST(numeric_expr1, numeric_expr2, ...)

Devuelve 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 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 de IFNULL.

Funciones de fecha y hora

Las siguientes funciones permiten manipular fechas y horas de marcas de tiempo UNIX, cadenas de fecha y tipos de datos TIMESTAMP. Para obtener más información sobre cómo trabajar con el tipo de datos TIMESTAMP, consulta Usar TIMESTAMP.

Las funciones de fecha y hora que funcionan con marcas de tiempo UNIX operan con hora UNIX. Las funciones de fecha y hora devuelven valores basados en la zona horaria UTC.

Sintaxis

CURRENT_DATE()

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

Ejemplo:

SELECT CURRENT_DATE();

Devuelve: 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();

Devoluciones: 01:32:56

CURRENT_TIMESTAMP()

Devuelve un tipo de datos TIMESTAMP de la hora 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 TIMESTAMP con el formato %Y-%m-%d.

Ejemplo:

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

Devuelve: 2012-10-01

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

Añade el intervalo especificado a un tipo de datos TIMESTAMP. Entre los valores posibles de interval_units se incluyen 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");

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

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

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

DATEDIFF(<timestamp1>,<timestamp2>)

Devuelve el número de días entre dos tipos de datos TIMESTAMP. El resultado es positivo si el primer tipo de datos TIMESTAMP es posterior al 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'));

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 TIMESTAMP como un número entero entre 1 y 31, ambos incluidos.

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 TIMESTAMP como un número entero entre 1 (domingo) y 7 (sábado), ambos incluidos.

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 TIMESTAMP como un número entero entre 1 y 366, ambos incluidos. El número entero 1 hace referencia 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 de UNIX con 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 TIMESTAMP como un número entero entre 0 y 23, ambos incluidos.

Ejemplo:

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

Devoluciones: 5

MINUTE(<timestamp>)

Devuelve los minutos de un tipo de datos TIMESTAMP como un número entero entre 0 y 59, ambos inclusive.

Ejemplo:

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

Devoluciones: 23

MONTH(<timestamp>)

Devuelve el mes de un tipo de datos TIMESTAMP como un número entero entre 1 y 12, ambos incluidos.

Ejemplo:

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

Devoluciones: 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);

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

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

Devuelve: 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 fraccionaria del segundo puede tener un máximo de seis dígitos de longitud o se puede omitir.

TIMESTAMP_TO_USEC es una función equivalente que convierte un argumento de tipo de datos TIMESTAMP 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 TIMESTAMP como un número entero entre 1 y 4, ambos incluidos.

Ejemplo:

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

Devoluciones: 4

SEC_TO_TIMESTAMP(<expr>)

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

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 TIMESTAMP como un número entero entre 0 y 59, ambos inclusive.

Durante un segundo bisiesto, el intervalo de números enteros es de 0 a 60, ambos incluidos.

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 por humanos con 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).

Usa las funciones UTC_USEC_TO_<function_name> si tienes previsto agrupar los datos de las consultas por intervalos de tiempo (por ejemplo, para obtener todos los datos de un mes determinado), ya que estas 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 TIMESTAMP con el formato %H:%M:%S.

Ejemplo:

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

Devoluciones: 02:03:04

TIMESTAMP(<date_string>)

Convierte una cadena de fecha en un tipo de datos TIMESTAMP.

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 TIMESTAMP 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 TIMESTAMP 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 TIMESTAMP.

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 en un tipo de datos TIMESTAMP.

Ejemplo:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

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

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

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

UTC_USEC_TO_DAY(<unix_timestamp>)

Cambia una marca de tiempo UNIX en microsegundos al principio del día en el que se produce.

Por ejemplo, si unix_timestamp se produce el 19 de mayo a las 08:58, esta función devuelve una marca de tiempo UNIX del 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 principio de la hora en la que se produce.

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

Ejemplo:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

Devolución: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

Desplaza una marca de tiempo UNIX en microsegundos al principio del mes en el que se produce.

Por ejemplo, si unix_timestamp se produce el 19 de marzo, esta función devuelve una marca de tiempo UNIX del 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 de la semana del argumento unix_timestamp. Esta función utiliza dos argumentos: una marca de tiempo de Unix en microsegundos y un día de la semana del 0 (domingo) al 6 (sábado).

Por ejemplo, si unix_timestamp se produce el viernes 11 de abril del 2008 y asigna el valor 2 (martes) a day_of_week, la función devuelve una marca de tiempo UNIX correspondiente al martes 8 de abril del 2008.

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 se produce en el 2010, la función devuelve 1274259481071200, la representación en 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 TIMESTAMP como un número entero entre 1 y 53, ambos incluidos.

Las semanas empiezan el domingo, por lo que, si el 1 de enero cae en un día que no sea domingo, la semana 1 tendrá menos de 7 días y el primer domingo del año será 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 TIMESTAMP.

Ejemplo:

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

Devoluciones: 2012

Ejemplos avanzados

• Convertir los resultados de marcas de tiempo de números enteros en un formato legible

  La siguiente consulta busca los cinco momentos en los que se han producido más revisiones en 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 en 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];

  Devuelve:

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

• Agrupar resultados por marca de tiempo

  Es útil usar funciones de fecha y hora para agrupar los resultados de las consultas en contenedores correspondientes a años, meses o días concretos. En el siguiente ejemplo se usa la función UTC_USEC_TO_MONTH() para mostrar cuántos caracteres usa cada colaborador de Wikipedia en sus comentarios de revisión al 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;

  Devoluciones (truncadas):

  +--------------------------------+---------+-----------------------+
  |      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 direcciones IP a un formato legible para los humanos y viceversa.

Sintaxis

FORMAT_IP(integer_value)

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

PARSE_IP(readable_ip)

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

BigQuery admite la escritura de direcciones IPv4 e IPv6 en cadenas empaquetadas, como datos binarios de 4 o 16 bytes en orden de bytes de red. Las funciones descritas a continuación permiten analizar las direcciones en formato legible para humanos y viceversa. Estas funciones solo funcionan en campos de cadena con IPs.

Sintaxis

FORMAT_PACKED_IP(packed_ip)

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

• FORMAT_PACKED_IP('0123456789@ABCDE') devoluciones '3031:3233:3435:3637:3839:4041:4243:4445'
• FORMAT_PACKED_IP('0123') devoluciones '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') devoluciones 'MDEyMw=='
• PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') devoluciones 'MDEyMzQ1Njc4OUBBQkNERQ=='

Funciones JSON

Las funciones JSON de BigQuery te permiten encontrar valores en los datos JSON almacenados mediante expresiones similares a JSONPath.

Almacenar datos JSON puede ser más flexible que declarar todos los campos individuales en el esquema de la tabla, pero puede conllevar costes más elevados. Cuando seleccionas datos de una cadena JSON, se te cobra por analizar toda la cadena, lo que resulta más caro que si cada campo estuviera en una columna independiente. La consulta también es más lenta, ya que toda la cadena debe analizarse en el momento de la consulta. Sin embargo, en el caso de los esquemas ad hoc o que cambian rápidamente, la flexibilidad de JSON puede merecer la pena a pesar del coste adicional.

Si trabajas con datos estructurados, usa funciones JSON en lugar de funciones de expresiones regulares de BigQuery, ya que las funciones JSON son más fáciles de usar.

Sintaxis

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 cadena. Devuelve el valor en formato de cadena 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 cadena. Devuelve un valor JSON escalar.

Operadores lógicos

Los operadores lógicos aplican la lógica binaria o ternaria en expresiones. La lógica binaria devuelve true o false. La lógica ternaria admite 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 si una expresión se cumple 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 si una expresión no se cumple y la otra es NULA.

NOT expr

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

Puedes usar NOT 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 usan argumentos numéricos y devuelven un resultado numérico. Cada argumento puede ser un literal numérico o un valor numérico devuelto por una consulta. Si la función matemática da como resultado un valor indefinido, la operación devuelve NULL.

Sintaxis

ABS(numeric_expr)

Devuelve el valor absoluto del argumento.

ACOS(numeric_expr)

Devuelve el arcocoseno del argumento.

ACOSH(numeric_expr)

Devuelve el coseno hiperbólico inverso del argumento.

ASIN(numeric_expr)

Devuelve el arcoseno del argumento.

ASINH(numeric_expr)

Devuelve el seno hiperbólico inverso del argumento.

ATAN(numeric_expr)

Devuelve la arcotangente del argumento.

ATANH(numeric_expr)

Devuelve la tangente hiperbólica inversa del argumento.

ATAN2(numeric_expr1, numeric_expr2)

Devuelve la arcotangente de los dos argumentos.

CEIL(numeric_expr)

Redondea el argumento al número entero más próximo 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) a la potencia de numeric_expr.

FLOOR(numeric_expr)

Redondea el argumento al número entero inferior más próximo y devuelve el valor redondeado.

LN(numeric_expr)
LOG(numeric_expr)

Devuelve el logaritmo natural del argumento.

LOG2(numeric_expr)

Devuelve el logaritmo en base 2 del argumento.

LOG10(numeric_expr)

Devuelve el logaritmo en base 10 del argumento.

PI()

Devuelve la constante π. La función PI() requiere paréntesis para indicar que es una función, pero no incluye ningún argumento entre ellos. Puedes usar PI() como una constante con funciones matemáticas y aritméticas.

POW(numeric_expr1, numeric_expr2)

Devuelve el resultado de elevar numeric_expr1 a la potencia de numeric_expr2.

RADIANS(numeric_expr)

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

RAND([int32_seed])

Devuelve un valor flotante aleatorio en el intervalo 0,0 <= valor < 1,0. Cada valor de int32_seed siempre genera la misma secuencia de números aleatorios en una consulta determinada, siempre que no uses 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])

Redondea el argumento hacia arriba o hacia abajo al número entero más próximo (o, si se especifica, al número de decimales indicado) 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 cuadro delimitador

  La siguiente consulta devuelve una colección de puntos dentro de un cuadro delimitador rectangular centrado en 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 envolvente aproximado

  Devuelve una colección de hasta 100 puntos dentro de un círculo aproximado determinado por la mediante la ley esférica del coseno, centrado en Denver, Colorado (39,73, -104,98). Esta consulta usa funciones matemáticas y trigonométricas de BigQuery, como PI(), SIN() y COS().

  Como la Tierra no es una esfera perfecta y la longitud y la latitud convergen 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 ofrece compatibilidad con expresiones regulares mediante la biblioteca re2. Consulta la documentación de la sintaxis de expresiones regulares.

Ten en cuenta que las expresiones regulares son coincidencias globales; para iniciar la coincidencia al principio de una palabra, usa el carácter ^.

Sintaxis

REGEXP_MATCH('str', 'reg_exp')

Devuelve "true" si str coincide con la expresión regular. Para buscar coincidencias de cadenas sin expresiones regulares, utilice 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;

Devuelve:

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

Devuelve:

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

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

Devuelve una cadena en la que las subcadenas de orig_str que coincidan con reg_exp se sustituyen por replace_str. Por ejemplo, REGEXP_REPLACE ('Hello', 'lo', 'p') devuelve "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;

Devuelve:

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

Ejemplos avanzados

• Filtrar el conjunto de resultados por coincidencia de expresiones regulares

  Las funciones de expresiones regulares de BigQuery se pueden usar para filtrar resultados en una cláusula WHERE, así como para mostrar resultados en la SELECT. En el siguiente ejemplo se combinan 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);

• Usar expresiones regulares en datos de tipo entero o flotante

  Aunque las funciones de expresiones regulares de BigQuery solo funcionan con datos de cadena, se puede usar la función STRING() para convertir datos de números enteros o de coma flotante en formato de cadena. En este ejemplo, STRING() se usa para convertir el valor entero corpus_date en una cadena, que luego se modifica con 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 ir entre comillas simples o dobles. Las funciones de cadena distinguen entre mayúsculas y minúsculas de forma predeterminada. Puedes añadir IGNORE CASE al final de una consulta para habilitar la coincidencia que no distingue entre mayúsculas y minúsculas. IGNORE CASE solo funciona con caracteres ASCII y solo en el nivel superior de la consulta.

Estas funciones no admiten comodines. Para usar expresiones regulares, consulta las funciones de expresiones regulares.

Sintaxis

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

Devuelve la concatenación de dos o más cadenas, o NULL si alguno de los valores es NULL. Ejemplo: si str1 es Java y str2 es Script, CONCAT devuelve JavaScript.

expr CONTAINS 'str'

devuelve true si expr contiene el argumento de cadena especificado. Esta comparación distingue entre mayúsculas y minúsculas.

INSTR('str1', 'str2')

Devuelve el índice basado en uno de la primera aparición de str2 en str1 o devuelve 0 si str2 no aparece en str1.

LEFT('str', numeric_expr)

Devuelve los numeric_expr caracteres situados más a la izquierda de str. Si el número es mayor que str, se devolverá la cadena completa. Ejemplo: LEFT('seattle', 3) devuelve sea.

LENGTH('str')

Devuelve un valor numérico de la longitud de la cadena. Ejemplo: si str es '123456', LENGTH devuelve 6.

LOWER('str')

Devuelve la cadena original con todos los caracteres en minúsculas.

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

Rellena

str1 por la izquierda con str2, repitiendo str2 hasta que la cadena resultante tenga exactamente numeric_expr caracteres. Ejemplo: LPAD('1', 7, '?') devuelve ??????1.

LTRIM('str1' [, str2])

Quita caracteres de la parte izquierda de str1. Si se omite str2, LTRIM elimina los espacios de la parte izquierda de str1. De lo contrario, LTRIM elimina cualquier carácter de str2 de la parte izquierda de str1 (distingue entre mayúsculas y minúsculas).

Ejemplos:

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

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

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

Sustituye todas las instancias de str2 en str1 por str3.

RIGHT('str', numeric_expr)

Devuelve los numeric_expr caracteres situados más a la derecha de str. Si el número es mayor que la cadena, se devuelve la cadena completa. Ejemplo: RIGHT('kirkland', 4) devuelve land.

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

Rellena str1 por la derecha con str2, repitiendo str2 hasta que la cadena resultante tenga exactamente numeric_expr caracteres.

Ejemplo: RPAD('1', 7, '?') devuelve 1??????.

RTRIM('str1' [, str2])

Quita los caracteres finales de la parte derecha de str1. Si se omite str2, RTRIM elimina los espacios finales de str1. De lo contrario, RTRIM elimina los caracteres de str2 de la parte derecha de str1 (distingue entre mayúsculas y minúsculas).

Ejemplos:

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

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

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

Divide una cadena en subcadenas repetidas. Si se especifica delimiter, la función SPLIT divide str en subcadenas usando delimiter como delimitador.

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

Devuelve una subcadena de str, que empieza en index. Si se usa el parámetro opcional max_len, la cadena devuelta tendrá una longitud máxima de max_len caracteres. El recuento empieza en uno, de manera que el primer carácter de la cadena está en la posición uno (no cero). Si index es 5, la subcadena empieza por el quinto carácter de str contando desde la izquierda. Si index es -4, la subcadena empieza por el cuarto carácter de str contando desde la derecha. Ejemplo: SUBSTR('awesome', -4, 4) devuelve la subcadena some.

UPPER('str')

Devuelve la cadena original con todos los caracteres en mayúsculas.

'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 de comodín de tabla

Las funciones comodín de tabla son una forma cómoda de consultar datos de un conjunto específico de tablas. Una función de comodín de tabla equivale a una unión separada por comas de todas las tablas que coinciden con la función de comodín. Cuando usas una función comodín de tabla, BigQuery solo accede a las tablas que coinciden con el comodín y te cobra por ellas. Las funciones de comodín de tabla se especifican en la cláusula FROM de la consulta.

Si usa funciones de comodín de tabla en una consulta, ya no es necesario que las incluya entre paréntesis. Por ejemplo, algunos de los siguientes ejemplos usan paréntesis, mientras que otros no.

Los resultados almacenados en caché no pueden utilizarse en las consultas de varias tablas con una función de comodín (aunque esté marcada la opción Utilizar los resultados almacenados en caché). Si realiza varias veces la misma consulta con comodín, se le facturará cada una de ellas.

Sintaxis

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

Consulta las tablas diarias que se solapan con el periodo comprendido entre <timestamp1> y <timestamp2>.

Los nombres de las tablas deben tener el siguiente formato: <prefix><day>, donde <day> tiene el formato YYYYMMDD.

Puedes usar 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 presupone 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 de un periodo de dos días hasta "ahora"

En este ejemplo, se presupone 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 equivale a TABLE_DATE_RANGE. La única diferencia es que, si falta alguna tabla diaria en la secuencia, TABLE_DATE_RANGE_STRICT falla y devuelve un error Not Found: Table <table_name>.

Ejemplo: error en una tabla que falta

En este ejemplo, se presupone 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

En el ejemplo anterior, se devuelve el error "Not Found" (No encontrado) para la tabla "people20140326".

TABLE_QUERY(dataset, expr)

Consulta las tablas cuyos nombres coincidan con el expr proporcionado. El parámetro expr debe representarse como una cadena y debe contener una expresión que se va a evaluar. Por ejemplo, 'length(table_id) < 3'.

Ejemplo: buscar tablas cuyos nombres contengan "oo" y tengan una longitud superior a 4

En este ejemplo, se presupone 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: busca tablas cuyos nombres empiecen por "boo" y tengan entre 3 y 5 dígitos numéricos

En este ejemplo, se presupone 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

HOST('url_str')

Dada una URL, devuelve el nombre de host como una cadena. Ejemplo: HOST('http://www.google.com:80/index.html') devuelve 'www.google.com'.

DOMAIN('url_str')

Dada una URL, devuelve el dominio como una cadena. Ejemplo: DOMAIN('http://www.google.com:80/index.html') devuelve 'google.com'.

TLD('url_str')

Dada una URL, devuelve el dominio de nivel superior más cualquier dominio de país de la URL. Ejemplo: TLD('http://www.google.com:80/index.html') devuelve '.com'. TLD('http://www.google.co.uk:80/index.html') devuelve ".co.uk".

Notas:

• Estas funciones no realizan búsquedas inversas de DNS, por lo que, si las llamas con una dirección IP, devolverán segmentos de la dirección IP en lugar de segmentos del nombre de host.
• Todas las funciones de análisis de URLs esperan caracteres en minúsculas. Si la URL contiene caracteres en mayúsculas, el resultado será NULL o incorrecto. Si tus datos tienen una combinación de mayúsculas y minúsculas, te recomendamos que pases la entrada a esta función a través de LOWER().

Ejemplo avanzado

Analizar nombres de dominio a partir de datos de URL

Esta consulta usa la función DOMAIN() para devolver los dominios más populares que aparecen como páginas principales de repositorios en GitHub. Observa 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 de la referente a partir de los datos de la 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;

Devuelve:

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

Para consultar información específica sobre TLDs, usa la función TLD(). En este ejemplo se muestran los TLDs 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;

Devuelve:

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

Funciones de ventana

Las funciones de ventana, también conocidas como funciones analíticas, permiten hacer cálculos en un subconjunto específico, o "ventana", de un conjunto de resultados. Las funciones de ventana facilitan la creación de informes que incluyen analíticas complejas, como medias móviles y totales acumulados.

Cada función de ventana requiere una cláusula OVER que especifique la parte superior e inferior de la ventana. Los tres componentes de la cláusula OVER (partición, orden y marco) proporcionan un control adicional sobre la ventana. 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 de una partición. El encuadre 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 móvil en función de un número de filas o de un intervalo 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 varios nombres de columna separados por comas. Se creará una partición por cada conjunto de valores distintos de estas columnas, de forma similar a una cláusula GROUP BY. Si se omite PARTITION BY, la partición base serán todas las filas de la entrada de la función de ventana.

La cláusula PARTITION BY también permite que las funciones de ventana particionen los datos y paralelice la ejecución. Si quieres usar una función de ventana con allowLargeResults o aplicar más combinaciones o agregaciones al resultado de tu función de ventana, usa PARTITION BY para paralelizar la ejecución.

Las cláusulas

JOIN EACH y GROUP EACH BY no se pueden usar en el resultado de las funciones de ventana. Para generar resultados de consultas grandes al usar funciones de ventana, debes usar PARTITION BY.

ORDER BY

Ordena la partición. Si no se incluye ORDER BY, no se garantiza ningún orden de clasificación predeterminado. La ordenación se produce a nivel de partición, antes de que se aplique cualquier cláusula de marco de ventana. Si especificas una ventana RANGE, debes añadir una cláusula ORDER BY. El orden predeterminado es ASC.

ORDER BY es opcional en algunos casos, pero ciertas funciones de ventana, como rank() o dense_rank(), requieren la cláusula.

Si usas ORDER BY sin especificar ROWS ni RANGE, ORDER BY implica que la ventana se extiende desde el principio de la partición hasta la fila actual. Si no hay ninguna cláusula ORDER BY, la ventana es toda la partición.

<window-frame-clause>

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

Un subconjunto de la partición sobre la que se va a operar. Puede ser del mismo tamaño que la partición o más pequeño. Si usas ORDER BY sin window-frame-clause, el marco de la ventana predeterminado es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Si omites tanto ORDER BY como window-frame-clause, el marco de ventana predeterminado será toda la partición.

• ROWS: define una ventana en términos de posición de la fila, en relación con la fila actual. Por ejemplo, para añadir una columna que muestre la suma de los 5 valores de salario anteriores, consultarías SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW). El conjunto de filas suele incluir la fila actual, pero no es obligatorio.
• RANGE: define una ventana en términos de un intervalo de valores de una columna determinada, en relación con el valor de esa columna en la fila actual. Solo funciona con números y fechas, donde los valores de fecha son números enteros simples (microsegundos desde el inicio del registro de tiempo). Las filas adyacentes con el mismo valor se denominan filas del mismo nivel. Las filas de elementos del mismo nivel de CURRENT ROW se incluyen en un marco de ventana que especifica CURRENT ROW. Por ejemplo, si especifica que el final de la ventana sea CURRENT ROW y la fila siguiente de la ventana tenga el mismo valor, se incluirá en el cálculo de la función.
• BETWEEN <start> AND <end>: un intervalo que incluye las filas inicial y final. El intervalo no tiene por qué incluir la fila actual, pero <start> debe ser anterior o igual a <end>.
• <start>: especifica el desplazamiento inicial de 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 entero positivo, PRECEDING indica un número de fila o un valor de intervalo anterior, y FOLLOWING indica un número de fila o un valor de intervalo posterior. UNBOUNDED PRECEDING significa la primera fila de la partición. Si el inicio precede a la ventana, se asignará a la primera fila de la partición.
• <end>: especifica el desplazamiento final de 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 entero positivo, PRECEDING indica un número de fila o un valor de intervalo anterior, y FOLLOWING indica un número de fila o un valor de intervalo posterior. UNBOUNDED FOLLOWING significa la última fila de la partición. Si end supera el final de la ventana, se asignará a la última fila de la partición.

A diferencia de las funciones de agregación, que combinan muchas filas de entrada en una fila de salida, las funciones de ventana devuelven una fila de salida por cada fila de entrada. Esta función facilita la creación de consultas que calculan totales acumulados y medias móviles. Por ejemplo, la siguiente consulta devuelve un total acumulado de un pequeño conjunto de datos de cinco filas definido por las instrucciones 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);