Organízate con las colecciones Guarda y clasifica el contenido según tus preferencias.

Operadores y funciones de SQL heredado

En este documento, se detallan los operadores y las funciones de SQL heredado. La sintaxis de consulta preferida para BigQuery es SQL estándar de Google. Para obtener información sobre SQL estándar de Google, consulta Funciones y operadores de SQL estándar de Google.

Operadores y funciones compatibles

La mayoría de las cláusulas de instrucción SELECT son compatibles con funciones. Los campos a los que se hace referencia en una función no necesitan enumerarse en ninguna cláusula SELECT. Por lo tanto, la siguiente consulta es válida, aunque el campo clicks no se muestre directamente:

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;
Funciones de agregación
AVG() Muestra el promedio de los valores para un grupo de filas…
BIT_AND() Muestra el resultado de una operación AND bit a bit…
BIT_OR() Muestra el resultado de una operación OR bit a bit…
BIT_XOR() Muestra el resultado de una operación XOR bit a bit…
CORR() Muestra el coeficiente de correlación de Pearson de un conjunto de pares de números.
COUNT() Muestra la cantidad total de valores…
COUNT([DISTINCT]) Muestra el número total de valores no nulos…
COVAR_POP() Calcula la covarianza de la población de los valores…
COVAR_SAMP() Calcula la covarianza muestral de los valores…
EXACT_COUNT_DISTINCT() Muestra el número exacto de valores no nulos distintos para el campo especificado.
FIRST() Muestra el primer valor secuencial dentro del alcance de la función.
GROUP_CONCAT() Concatena múltiples strings en una sola…
GROUP_CONCAT_UNQUOTED() Concatena múltiples strings en una sola… no agregará comillas dobles…
LAST() Muestra el último valor secuencial…
MAX() Muestra el valor máximo…
MIN() Muestra el valor mínimo…
NEST() Agrega todos los valores dentro del alcance de agregación actual a un campo repetido.
NTH() Muestra el enésimo valor secuencial…
QUANTILES() Calcula mínimos, máximos y cuantiles aproximados…
STDDEV() Muestra la desviación estándar…
STDDEV_POP() Calcula la desviación estándar de la población…
STDDEV_SAMP() Calcula la desviación estándar de la muestra…
SUM() Muestra la suma total de los valores…
TOP() ... COUNT(*) Muestra los registros principales de max_records según la frecuencia.
UNIQUE() Muestra el conjunto de valores no nulos únicos…
VARIANCE() Calcula la varianza de los valores…
VAR_POP() Calcula la varianza poblacional de los valores…
VAR_SAMP() Calcula la varianza muestral de los valores…
Operadores aritméticos
+ Suma
- Resta
* Multiplicación
/ División
% Módulo
Funciones bit a bit
& AND bit a bit
| OR bit a bit
^ XOR bit a bit
<< Desplaza bits hacia la izquierda
>> Desplaza bits hacia la derecha
~ NOT bit a bit
BIT_COUNT() Muestra el número de bits…
Funciones de conversión
BOOLEAN() Conversión a booleano.
BYTES() Conversión a bytes.
CAST(expr AS type) Convierte expr en una variable de tipo type.
FLOAT() Conversión al doble.
HEX_STRING() Conversión a la string hexadecimal.
INTEGER() Conversión a número entero.
STRING() Conversión a una string.
Funciones de comparación
expr1 = expr2 Muestra true si las expresiones son iguales.
expr1 != expr2
expr1 <> expr2
Muestra true si las expresiones no son iguales.
expr1 > expr2 Muestra true si expr1 es mayor que expr2.
expr1 < expr2 Muestra true si expr1 es menor que expr2.
expr1 >= expr2 Muestra true si expr1 es mayor o igual que expr2.
expr1 <= expr2 Muestra true si expr1 es menor o igual que expr2.
expr1 BETWEEN expr2 AND expr3 Muestra true si el valor de expr1 está entre expr2 y expr3, inclusive.
expr IS NULL Muestra true si expr es NULO.
expr IN() Muestra true si expr coincide con expr1, expr2 o cualquier valor entre paréntesis.
COALESCE() Muestra el primer argumento que no es NULO.
GREATEST() Muestra el parámetro numeric_expr más grande.
IFNULL() Muestra el argumento si no es nulo.
IS_INF() Muestra true si es infinito positivo o negativo.
IS_NAN() Muestra true si el argumento es NaN.
IS_EXPLICITLY_DEFINED() obsoleta: Usa expr IS NOT NULL en su lugar.
LEAST() Muestra el parámetro numeric_expr más pequeño del argumento.
NVL() Si expr no es nulo, muestra expr; de lo contrario, muestra null_default.
Funciones de fecha y hora
CURRENT_DATE() Muestra la fecha actual en el formato %Y-%m-%d.
CURRENT_TIME() Muestra la hora actual del servidor en el formato %H:%M:%S.
CURRENT_TIMESTAMP() Muestra la hora actual del servidor en el formato %Y-%m-%d %H:%M:%S.
DATE() Muestra la fecha con el formato %Y-%m-%d.
DATE_ADD() Agrega el intervalo especificado a un tipo de datos TIMESTAMP.
DATEDIFF() Muestra el número de días entre dos tipos de datos TIMESTAMP.
DAY() Muestra el día del mes como un número entero entre 1 y 31.
DAYOFWEEK() Muestra el día de la semana como un número entero entre 1 (domingo) y 7 (sábado).
DAYOFYEAR() Muestra el día del año como un número entero entre 1 y 366.
FORMAT_UTC_USEC() Muestra una marca de tiempo UNIX en el formato YYYY-MM-DD HH:MM:SS.uuuuuu.
HOUR() Muestra la hora de TIMESTAMP como un número entero entre 0 y 23.
MINUTE() Muestra los minutos de una TIMESTAMP como un número entero entre 0 y 59.
MONTH() Muestra el mes de una TIMESTAMP como un número entero entre 1 y 12.
MSEC_TO_TIMESTAMP() Convierte una marca de tiempo UNIX en milisegundos en una TIMESTAMP.
NOW() Muestra la marca de tiempo de UNIX actual en microsegundos.
PARSE_UTC_USEC() Convierte una string de fecha en una marca de tiempo UNIX en microsegundos.
QUARTER() Muestra el trimestre del año de una TIMESTAMP como un número entero entre 1 y 4.
SEC_TO_TIMESTAMP() Convierte una marca de tiempo UNIX en segundos en una TIMESTAMP.
SECOND() Muestra los segundos de una TIMESTAMP como un número entero entre 0 y 59.
STRFTIME_UTC_USEC() Muestra una string de fecha en el formato date_format_str.
TIME() Muestra un TIMESTAMP con el formato %H:%M:%S.
TIMESTAMP() Convierte una string de fecha en un TIMESTAMP.
TIMESTAMP_TO_MSEC() Convierte una TIMESTAMP en una marca de tiempo UNIX en milisegundos.
TIMESTAMP_TO_SEC() Convierte una TIMESTAMP en una marca de tiempo UNIX en segundos.
TIMESTAMP_TO_USEC() Convierte una TIMESTAMP en una marca de tiempo UNIX en microsegundos.
USEC_TO_TIMESTAMP() Convierte una marca de tiempo UNIX en microsegundos en una TIMESTAMP.
UTC_USEC_TO_DAY() Desplaza una marca de tiempo UNIX en microsegundos al comienzo del día en que se produce.
UTC_USEC_TO_HOUR() Desplaza una marca de tiempo UNIX en microsegundos al comienzo de la hora en que se produce.
UTC_USEC_TO_MONTH() Desplaza una marca de tiempo UNIX en microsegundos al comienzo del mes en que se produce.
UTC_USEC_TO_WEEK() Muestra una marca de tiempo UNIX en microsegundos que representa un día de la semana.
UTC_USEC_TO_YEAR() Muestra una marca de tiempo UNIX en microsegundos que representa el año.
WEEK() Muestra la semana de una TIMESTAMP como un número entero entre 1 y 53.
YEAR() Muestra el año de una TIMESTAMP.
Funciones de IP
FORMAT_IP() Convierte 32 bits menos significativos de integer_value en una string de dirección IPv4 legible.
PARSE_IP() Convierte una string que representa la dirección IPv4 en un valor entero sin firma.
FORMAT_PACKED_IP() Muestra una dirección IP legible con el formato 10.1.5.23 o 2620:0:1009:1:216:36ff:feef:3f.
PARSE_PACKED_IP() Muestra una dirección IP en BYTES.
Funciones JSON
JSON_EXTRACT() Selecciona un valor de acuerdo con la expresión JSONPath y muestra una string JSON.
JSON_EXTRACT_SCALAR() Selecciona un valor de acuerdo con la expresión JSONPath y muestra un escalar JSON.
Operadores lógicos
expr AND expr Muestra true si ambas expresiones son verdaderas.
expr OR expr Muestra true si una o ambas expresiones son verdaderas.
NOT expr Muestra true si la expresión es falsa.
Funciones matemáticas
ABS() Muestra el valor absoluto del argumento.
ACOS() Muestra el arcocoseno del argumento.
ACOSH() Muestra el arcocoseno hiperbólico del argumento.
ASIN() Muestra el arcoseno del argumento.
ASINH() Muestra el arcoseno hiperbólico del argumento.
ATAN() Muestra la arcotangente del argumento.
ATANH() Muestra la arcotangente hiperbólica del argumento.
ATAN2() Muestra la arcotangente de los dos argumentos.
CEIL() Redondea el argumento al siguiente número entero más cercano y muestra el valor redondeado.
COS() Muestra el coseno del argumento.
COSH() Muestra el coseno hiperbólico del argumento.
DEGREES() Convierte radianes en grados.
EXP() Muestra e a la potencia del argumento.
FLOOR() Redondea el argumento al número entero anterior más cercano.
LN()
LOG()
Muestra el logaritmo natural del argumento.
LOG2() Muestra el logaritmo en base 2 del argumento.
LOG10() Muestra el logaritmo en base 10 del argumento.
PI() Muestra la constante π.
POW() Muestra el primer argumento a la potencia del segundo argumento.
RADIANS() Convierte grados en radianes.
RAND() Muestra un valor flotante aleatorio en el rango 0.0 <= valor < 1.0.
ROUND() Redondea el argumento hacia arriba o hacia abajo al número entero más cercano.
SIN() Muestra el seno del argumento.
SINH() Muestra el seno hiperbólico del argumento.
SQRT() Muestra la raíz cuadrada de la expresión.
TAN() Muestra la tangente del argumento.
TANH() Muestra la tangente hiperbólica del argumento.
Funciones de expresión regular
REGEXP_MATCH() Muestra true si el argumento coincide con la expresión regular.
REGEXP_EXTRACT() Muestra la parte del argumento que coincide con el grupo de captura dentro de la expresión regular.
REGEXP_REPLACE() Reemplaza una substring que coincide con una expresión regular.
Funciones de string
CONCAT() Muestra la concatenación de dos o más strings, o NULO si alguno de los valores es NULO.
expr CONTAINS 'str' Muestra true si expr contiene el argumento de string especificado.
INSTR() Muestra el índice basado en uno del primer caso de una string.
LEFT() Muestra los caracteres más a la izquierda de una string.
LENGTH() Muestra la longitud de la string.
LOWER() Muestra la string original con todos los caracteres en minúscula.
LPAD() Inserta caracteres a la izquierda de una string.
LTRIM() Quita caracteres del lado izquierdo de una string.
REPLACE() Reemplaza todos los casos de una substring.
RIGHT() Muestra los caracteres más a la derecha de una string.
RPAD() Inserta caracteres a la derecha de una string.
RTRIM() Quita los caracteres finales del lado derecho de una string.
SPLIT() Divide una string en substrings repetidas.
SUBSTR() Muestra una substring…
UPPER() Muestra la string original con todos los caracteres en mayúsculas.
Funciones comodín de tablas
TABLE_DATE_RANGE() Consulta múltiples tablas diarias que abarcan un período.
TABLE_DATE_RANGE_STRICT() Consulta varias tablas diarias que abarcan un período, sin fechas faltantes.
TABLE_QUERY() Consulta tablas cuyos nombres coinciden con un predicado especificado.
Funciones de URL
HOST() Dada una URL, muestra el nombre de host como una string.
DOMAIN() Dada una URL, muestra el dominio como una string.
TLD() Dada una URL, muestra el dominio de nivel superior y cualquier dominio de país en la URL.
Funciones analíticas
AVG()
COUNT(*)
COUNT([DISTINCT])
MAX()
MIN()
STDDEV()
SUM()
La misma operación que las funciones agregadas correspondientes, pero se calculan en una ventana definida por la cláusula OVER.
CUME_DIST() Muestra un doble que indica la distribución acumulativa de un valor en un grupo de valores…
DENSE_RANK() Muestra el rango de números enteros de un valor en un grupo de valores.
FIRST_VALUE() Muestra el primer valor del campo especificado en la ventana.
LAG() Te permite leer datos de una fila anterior dentro de una ventana.
LAST_VALUE() Muestra el último valor del campo especificado en la ventana.
LEAD() Te permite leer datos de una fila siguiente dentro de una ventana.
NTH_VALUE() Muestra el valor de <expr> en la posición <n> del marco de la ventana…
NTILE() Divide la ventana en el número especificado de depósitos.
PERCENT_RANK() Muestra el rango de la fila actual, en relación con las otras filas de la partición.
PERCENTILE_CONT() Muestra un valor interpolado que se asignaría al argumento de percentil con respecto a la ventana…
PERCENTILE_DISC() Muestra el valor más cercano al percentil del argumento en la ventana.
RANK() Muestra el rango de números enteros de un valor en un grupo de valores.
RATIO_TO_REPORT() Muestra la proporción de cada valor en la suma de los valores.
ROW_NUMBER() Muestra el número de fila actual del resultado de la consulta en la ventana.
Otras funciones
CASE WHEN ... THEN Usa CASE para elegir entre dos o más expresiones alternativas en tu consulta.
CURRENT_USER() Muestra la dirección de correo electrónico del usuario que ejecuta la consulta.
EVERY() Muestra true si el argumento es verdadero para todas sus entradas.
FROM_BASE64() Convierte la string de entrada codificada en base 64 en formato BYTES.
HASH() Calcula y muestra un valor hash firmado de 64 bits…
FARM_FINGERPRINT() Calcula y muestra un valor de huella digital firmada de 64 bits…
IF() Si el primer argumento es verdadero, muestra el segundo argumento; de lo contrario, muestra el tercero.
POSITION() Muestra la posición secuencial basada en uno del argumento.
SHA1() Muestra un hash SHA1 en formato BYTES.
SOME() Muestra true si el argumento es verdadero para al menos una de sus entradas.
TO_BASE64() Convierte el argumento BYTES en una string codificada en base 64.

Sintaxis de las consultas

Nota: Las palabras clave no distinguen entre mayúsculas y minúsculas. En este documento, las palabras clave como SELECT se escriben en mayúsculas por fines ilustrativos.

Cláusula SELECT

La cláusula SELECT especifica una lista de expresiones que deben ser calculadas. Las expresiones de la cláusula SELECT pueden contener nombres de campo, literales y llamadas a funciones (incluidas las funciones agregadas y las funciones analíticas), así como combinaciones de las tres. La lista de expresiones está separada por comas.

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

Notas:

  • Si usas una función agregada en la cláusula SELECT, debes usar una función agregada en todas las expresiones o tu consulta debe tener una cláusula GROUP BY que incluya todos los campos no agregados en la cláusula SELECT como claves de agrupación. Por ejemplo:
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word)
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word,
      corpus; /* Succeeds because all non-aggregated fields are group keys. */
    
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word)
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word; /* Fails because corpus is not aggregated nor is it a group key. */
    
  • Puedes usar corchetes para escapar palabras reservadas, así podrás usarlas como nombre de campo y alias. Por ejemplo, si tienes una columna llamada “partición”, que es una palabra reservada en la sintaxis de BigQuery, las consultas que hacen referencia a ese campo fallan con mensajes de error poco claros, a menos que la escapes con corchetes:
    SELECT [partition] FROM ...
Ejemplo

Este ejemplo define los alias en la cláusula SELECT y, luego, hace referencia a una de ellas en la cláusula ORDER BY. Ten en cuenta que no se puede hacer referencia a la columna palabra mediante word_alias en la cláusula WHERE; se le debe hacer referencia con su nombre. El alias len tampoco es visible en la cláusula WHERE. Sería visible a una cláusula HAVING.

#legacySQL
SELECT
  word AS word_alias,
  LENGTH(word) AS len
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS 'th'
ORDER BY
  len;

Modificador WITHIN para funciones agregadas

aggregate_function WITHIN RECORD [ [ AS ] alias ]

La palabra clave WITHIN hace que la función agregada se agregue a valores repetidos dentro de cada registro. Para cada registro de entrada, se producirá exactamente un resultado agregado. Este tipo de agregación se conoce como agregación con alcance. Como la agregación con alcance produce resultados para cada registro, las expresiones no agregadas se pueden seleccionar junto con expresiones agregadas con alcance sin usar una cláusula GROUP BY.

Por lo general, usarás el alcance RECORD cuando uses la agregación con alcance. Si tienes un esquema anidado repetido muy complejo, quizás necesites realizar agregaciones dentro de los alcances de subregistro. Esto se puede hacer mediante el reemplazo de la palabra clave RECORD en la sintaxis anterior con el nombre del nodo del esquema en el que deseas que se realice la agregación. Para obtener más información sobre ese comportamiento avanzado, consulta la sección sobre cómo trabajar con datos.

Ejemplo

En este ejemplo, se realiza una agregación COUNT con alcance y, luego, se filtran y ordenan los registros según el valor agregado.

#legacySQL
SELECT
  repository.url,
  COUNT(payload.pages.page_name) WITHIN RECORD AS page_count
FROM
  [bigquery-public-data:samples.github_nested]
HAVING
  page_count > 80
ORDER BY
  page_count DESC;

Cláusula FROM

FROM
  [project_name:]datasetId.tableId [ [ AS ] alias ] |
  (subquery) [ [ AS ] alias ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

La cláusula FROM especifica los datos de origen que se consultarán. Las consultas de BigQuery pueden ejecutarse directamente sobre tablas, subconsultas, tablas unidas y tablas modificadas por operadores de objetivos especiales que se describen a continuación. Las combinaciones de estas fuentes de datos se pueden consultar con la coma, que es el operador UNION ALL en BigQuery.

Referencia tablas

Cuando se hace referencia a una tabla, se deben especificar datasetId y tableId; project_name es opcional. Si no se especifica project_name, BigQuery usa de forma predeterminada el proyecto actual. Si el nombre de tu proyecto incluye un guion, debes rodear toda la referencia de la tabla con corchetes.

Ejemplo
[my-dashed-project:dataset1.tableName]

Se puede otorgar un alias a las tablas si se agrega un espacio seguido de un identificador después del nombre de la tabla. Se puede agregar la palabra clave AS opcional entre tableId y el alias para mejorar la legibilidad.

Cuando haces referencia a columnas de una tabla, puedes usar el nombre simple de la columna o prefijar el nombre de la columna con el alias, si especificaste uno, o con el datasetId y el tableId, siempre y cuando no hayas especificado project_name. No se puede incluir project_name en el prefijo de la columna porque los caracteres de dos puntos no están permitidos en los nombres de campo.

Ejemplos

En este ejemplo, se hace referencia a una columna sin prefijo de tabla.

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

En este ejemplo, se prefija el nombre de la columna con el datasetId y el tableId. Ten en cuenta que no se puede incluir project_name en este ejemplo. Este método solo funcionará si el conjunto de datos está en tu proyecto predeterminado actual.

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

En este ejemplo, se prefija el nombre de la columna con un alias de tabla.

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

Usa subconsultas

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

Las subconsultas pueden usarse para calcular agregaciones y otras expresiones. La gama completa de operadores SQL está disponible en la subconsulta. Esto significa que una subconsulta puede contener otras subconsultas, las subconsultas pueden realizar uniones y agregaciones de agrupamiento, etcétera.

Coma como UNION ALL

A diferencia de SQL estándar de Google, SQL heredado usa la coma como un operador UNION ALL en lugar de un operador CROSS JOIN. Este es un comportamiento heredado que evolucionó porque, históricamente, BigQuery no era compatible con CROSS JOIN y los usuarios de BigQuery necesitaban escribir consultas UNION ALL. En el SQL estándar de Google, las consultas que realizan uniones son muy detalladas. El uso de la coma como operador de la unión permite escribir las consultas de manera más eficiente. Por ejemplo, se puede usar esta consulta para ejecutar una sola consulta en los registros de varios días.

#legacySQL
SELECT
  FORMAT_UTC_USEC(event.timestamp_in_usec) AS time,
  request_url
FROM
  [applogs.events_20120501],
  [applogs.events_20120502],
  [applogs.events_20120503]
WHERE
  event.username = 'root' AND
  NOT event.source_ip.is_internal;

Por lo general, las consultas que unen una gran cantidad de tablas se ejecutan con mayor lentitud que las que procesan la misma cantidad de datos desde una sola tabla. La diferencia de rendimiento puede ser de hasta 50 ms por tabla adicional. Una sola consulta puede unir como máximo 1,000 tablas.

Funciones comodín de tablas

El término función comodín de tablas hace referencia a un tipo especial de función exclusiva de BigQuery. Estas funciones se usan en la cláusula FROM para hacer coincidir una colección de nombres de tablas mediante uno de varios tipos de filtros. Por ejemplo, se puede usar la función TABLE_DATE_RANGE para consultar solo un conjunto específico de tablas diarias. Para obtener más información sobre estas funciones, consulta Funciones comodín de tablas.

Operador FLATTEN

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

A diferencia de los sistemas de procesamiento de SQL típicos, BigQuery está diseñado para administrar datos repetidos. Debido a esto, a veces los usuarios de BigQuery necesitan escribir consultas que manipulan la estructura de registros repetidos. Una forma de hacerlo es con el operador FLATTEN.

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

Para obtener más información y ejemplos, consulta la sección sobre cómo trabajar con datos.

Operador JOIN

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

Tipos de JOIN

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

Las operaciones CROSS JOIN no permiten cláusulas ON. CROSS JOIN puede mostrar una gran cantidad de datos y podría dar como resultado una consulta ineficiente y lenta, o una consulta que exceda el máximo permitido por recursos de consulta. Esas consultas fallarán con un error. Cuando sea posible, usa las consultas que no usen CROSS JOIN. Por ejemplo, CROSS JOIN suele usarse en lugares donde las funciones analíticas serían más eficientes.

Modificador EACH

El modificador EACH es una sugerencia que le dice a BigQuery que ejecute JOIN con múltiples particiones. Esto es muy útil cuando sabes que ambos lados de la JOIN son grandes. El modificador EACH no se puede usar en las cláusulas CROSS JOIN.

Se solía recomendar el uso de EACH en muchos casos, pero ya no es así. Cuando sea posible, usa JOIN sin el modificador EACH para obtener un mejor rendimiento. Usa JOIN EACH cuando tu consulta falle con un mensaje de error de recursos excedidos.

Semiunión y antiunión

Además de ser compatible con JOIN en la cláusula FROM, BigQuery también admite dos tipos de uniones en la cláusula WHERE: semiunión y antisemiunión. Una semiunión se especifica mediante la palabra clave IN con una subconsulta; antiunión, con las palabras clave NOT IN.

Ejemplos

La siguiente consulta usa una semiunión para encontrar ngrams en los que la primera palabra es también la segunda palabra de otro ngram que tiene “AND” como tercera palabra.

#legacySQL
SELECT
  ngram
FROM
  [bigquery-public-data:samples.trigrams]
WHERE
  first IN (SELECT
              second
            FROM
              [bigquery-public-data:samples.trigrams]
            WHERE
              third = "AND")
LIMIT 10;

La siguiente consulta usa una semiunión para mostrar la cantidad de mujeres mayores de 50 años que dieron a luz en los 10 estados con más cantidad de nacimientos.

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

Para ver las cantidades de los otros 40 estados, puedes usar una antiunión. La siguiente consulta es casi idéntica al ejemplo anterior, pero usa NOT IN en lugar de IN para mostrar la cantidad de mujeres mayores de 50 años que dieron a luz en los 40 estados con menos nacimientos.

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state NOT IN (SELECT
                  state
                FROM
                  (SELECT
                     state,
                     COUNT(state) total
                   FROM
                     [bigquery-public-data:samples.natality]
                   GROUP BY
                     state
                   ORDER BY
                     total DESC
                   LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

Notas:

  • BigQuery no es compatible con semi o antisemiuniones correlacionadas. La subconsulta no puede hacer referencia a ningún campo de la consulta externa.
  • La subconsulta usada en una semiunión o una antisemiunión debe seleccionar solo un campo.
  • Los tipos del campo seleccionado y el campo que se utiliza desde la consulta externa en la cláusula WHERE deben coincidir exactamente. BigQuery no realizará ningún tipo de coerción para las semiuniones o antisemiuniones.

Cláusula WHERE

La cláusula WHERE, a veces llamada predicado, filtra los registros producidos por la cláusula FROM mediante una expresión booleana. Se pueden unir varias condiciones mediante cláusulas booleanas AND y OR, opcionalmente entre paréntesis () para agruparlas. No es necesario seleccionar los campos enumerados en una cláusula WHERE en la cláusula SELECT correspondiente. Además, la expresión de la cláusula WHERE no puede hacer referencia a las expresiones calculadas en la cláusula SELECT de la consulta a la que pertenece la cláusula WHERE.

Nota: Las funciones agregadas no pueden usarse en la cláusula WHERE. Usa una cláusula HAVING y una consulta externa si necesitas filtrar los resultados de una función agregada.

Ejemplo

En el siguiente ejemplo, se utiliza una disyunción de expresiones booleanas en la cláusula WHERE: Las dos expresiones unidas por un operador OR. Un registro de entrada pasará por el filtro WHERE si alguna de las expresiones muestra true.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  (word CONTAINS 'prais' AND word CONTAINS 'ing') OR
  (word CONTAINS 'laugh' AND word CONTAINS 'ed');

Cláusula OMIT RECORD IF

La cláusula OMIT RECORD IF es una construcción exclusiva de BigQuery. Resulta muy útil cuando se trabaja con esquemas anidados y repetidos. Es similar a una cláusula WHERE , pero diferente en dos aspectos importantes. Primero, usa una condición de exclusión, lo que significa que los registros se omiten si la expresión muestra true, pero se mantiene si la expresión muestra false o null. En segundo lugar, la cláusula OMIT RECORD IF puede (y generalmente lo hace) usar funciones agregadas con alcance en su condición.

Además de filtrar registros completos, OMIT...IF puede especificar un alcance más limitado para filtrar solo partes de un registro. Para ello, utiliza el nombre de un nodo que no sea de hoja en tu esquema en lugar de RECORD en tu cláusula OMIT...IF. Los usuarios de BigQuery no suelen usar esta funcionalidad. Puedes encontrar más documentación sobre este comportamiento avanzado en la documentación de WITHIN arriba.

Si usas OMIT...IF para excluir una parte de un registro en un campo repetido y la consulta también selecciona otros campos que se repiten de forma independiente, BigQuery omite una parte de los demás registros repetidos en la consulta. Si ves el error Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>,, te recomendamos cambiar a SQL estándar de Google. Para obtener información sobre cómo migrar declaraciones OMIT...IF a SQL estándar de Google, consulta la sección sobre cómo migrar a SQL estándar de Google.

Ejemplo

En referencia al ejemplo usado para el modificador WITHIN, OMIT RECORD IF se puede usar a fin de lograr lo mismo que WITHIN y HAVING.

#legacySQL
SELECT
  repository.url
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

Cláusula GROUP BY

La cláusula GROUP BY te permite agrupar filas que tienen los mismos valores para un campo o conjunto de campos determinado a fin de que puedas calcular agregaciones de campos relacionados. La agrupación se produce después del filtrado que se realizó en la cláusula WHERE, pero antes de que se calculen las expresiones en la cláusula SELECT. Los resultados de la expresión no se pueden usar como claves de grupo en la cláusula GROUP BY.

Ejemplo

Esta consulta encuentra las diez primeras palabras más comunes en el conjunto de datos de muestra de trigramas. Además de demostrar el uso de la cláusula GROUP BY, demuestra cómo se pueden usar los índices posicionales en lugar de los nombres de campo en las cláusulas GROUP BY y ORDER BY.

#legacySQL
SELECT
  first,
  COUNT(ngram)
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10;

La agregación que se realiza con una cláusula GROUP BY se denomina agregación agrupada. A diferencia de la agregación con alcance, la agregación agrupada es común en la mayoría de los sistemas de procesamiento de SQL.

Modificador EACH

El modificador EACH es una sugerencia que le dice a BigQuery que ejecute GROUP BY con múltiples particiones. Esto es muy útil cuando sabes que tu conjunto de datos contiene una gran cantidad de valores distintos para las claves de grupo.

Se solía recomendar el uso de EACH en muchos casos, pero ya no es así. Por lo general, el uso de GROUP BY sin el modificador EACH proporciona un mejor rendimiento. Usa GROUP EACH BY cuando tu consulta falle con un mensaje de error de Resources exceeded.

Función ROLLUP

Cuando se usa la función ROLLUP, BigQuery agrega filas adicionales al resultado de la consulta que representan las agregaciones completas. Todos los campos enumerados después de ROLLUP deben estar dentro de un único conjunto de paréntesis. En las filas agregadas debido a la función ROLLUP, NULL indica las columnas para las cuales se acumula la agregación.

Ejemplo

Esta consulta genera recuentos por año de nacimientos niños y niñas a partir del conjunto de datos de muestra de natalidad.

#legacySQL
SELECT
  year,
  is_male,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

Estos son los resultados de la consulta. Ten en cuenta que hay filas en las que una o ambas claves de grupo son NULL. Estas filas son las filas rollup.

+------+---------+----------+
| year | is_male |  count   |
+------+---------+----------+
| NULL |    NULL | 12122730 |
| 2000 |    NULL |  4063823 |
| 2000 |   false |  1984255 |
| 2000 |    true |  2079568 |
| 2001 |    NULL |  4031531 |
| 2001 |   false |  1970770 |
| 2001 |    true |  2060761 |
| 2002 |    NULL |  4027376 |
| 2002 |   false |  1966519 |
| 2002 |    true |  2060857 |
+------+---------+----------+

Cuando se usa la función ROLLUP, puedes usar la función GROUPING para distinguir entre las filas que se agregaron debido a la función ROLLUP y las que de verdad tienen un valor NULL para la clave de grupo.

Ejemplo

Esta consulta agrega la función GROUPING al ejemplo anterior para identificar mejor las filas agregadas debido a la función ROLLUP.

#legacySQL
SELECT
  year,
  GROUPING(year) as rollup_year,
  is_male,
  GROUPING(is_male) as rollup_gender,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

Estos son los resultados que muestra la nueva consulta.

+------+-------------+---------+---------------+----------+
| year | rollup_year | is_male | rollup_gender |  count   |
+------+-------------+---------+---------------+----------+
| NULL |           1 |    NULL |             1 | 12122730 |
| 2000 |           0 |    NULL |             1 |  4063823 |
| 2000 |           0 |   false |             0 |  1984255 |
| 2000 |           0 |    true |             0 |  2079568 |
| 2001 |           0 |    NULL |             1 |  4031531 |
| 2001 |           0 |   false |             0 |  1970770 |
| 2001 |           0 |    true |             0 |  2060761 |
| 2002 |           0 |    NULL |             1 |  4027376 |
| 2002 |           0 |   false |             0 |  1966519 |
| 2002 |           0 |    true |             0 |  2060857 |
+------+-------------+---------+---------------+----------+

Notas:

  • Los campos no agregados en la cláusula SELECT deben enumerarse en la cláusula GROUP BY.
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word)
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word,
      corpus; /* Succeeds because all non-aggregated fields are group keys. */
    
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word)
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word;  /* Fails because corpus is not aggregated nor is it a group key. */
    
  • Las expresiones calculadas en la cláusula SELECT no se pueden usar en la cláusula GROUP BY correspondiente.
    #legacySQL
    SELECT
      word,
      corpus,
      COUNT(word) word_count
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th"
    GROUP BY
      word,
      corpus,
      word_count;  /* Fails because word_count is not visible to this GROUP BY clause. */
    
  • No se admite la agrupación por valores flotantes y dobles, ya que la función de igualdad para esos tipos no está bien definida.
  • Debido a que el sistema es interactivo, las consultas que producen una gran cantidad de grupos pueden fallar. El uso de la función TOP en lugar de GROUP BY podría resolver algunos problemas de escalamiento.

Cláusula HAVING

La cláusula HAVING se comporta exactamente igual que la cláusula WHERE, excepto que se evalúa después de la cláusula SELECT a fin de que los resultados de todas las expresiones calculadas sean visibles para la cláusula HAVING. La cláusula HAVING solo puede referirse a los resultados de la cláusula SELECT correspondiente.

Ejemplo

Esta consulta calcula las primeras palabras más comunes en el conjunto de datos de muestra de ngram que contienen la letra a y aparecen como máximo 10,000 veces.

#legacySQL
SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

Cláusula ORDER BY

La cláusula ORDER BY ordena los resultados de una consulta en orden ascendente o descendente con uno o más campos clave. Para ordenar por múltiples campos o alias, ingrésalos como una lista separada por comas. Los resultados se ordenan en los campos en el orden en que se enumeran. Usa DESC (descendente) o ASC (ascendente) para especificar la dirección de clasificación. ASC es la configuración predeterminada. Se puede especificar una dirección de clasificación diferente para cada clave de clasificación.

La cláusula ORDER BY se evalúa después de la cláusula SELECT para que pueda hacer referencia al resultado de cualquier expresión calculada en SELECT. Si a un campo se le asigna un alias en la cláusula SELECT, el alias se debe usar en la cláusula ORDER BY.

Cláusula LIMIT

La cláusula LIMIT limita el número de filas en el conjunto de resultados que se muestra. Dado que las consultas de BigQuery operan habitualmente en un gran número de filas, LIMIT es una buena manera de procesar solo un subconjunto de las filas para evitar consultas de ejecución larga.

Notas:

  • La cláusula LIMIT detendrá el procesamiento y mostrará los resultados cuando satisfaga tus requisitos. Esto puede reducir el tiempo de procesamiento de algunas consultas, pero cuando especificas funciones agregadas como las cláusulas COUNT o ORDER BY, el conjunto de resultados completo todavía debe procesarse antes de mostrar los resultados. La cláusula LIMIT es la última que se evalúa.
  • Una consulta con una cláusula LIMIT puede no ser determinista si no hay un operador en la consulta que garantice el orden del conjunto de resultados de salida. Esto se debe a que BigQuery se ejecuta mediante una gran cantidad de trabajadores paralelos. El orden en el que se muestran los trabajos paralelos no está garantizado.
  • La cláusula LIMIT no puede contener funciones; solo admite una constante numérica.

Gramática de consulta

Las cláusulas individuales de las declaraciones SELECT de BigQuery se describen en detalle más arriba. Aquí, presentamos la gramática completa de las declaraciones SELECT de forma compacta con vínculos a las secciones individuales.

query:
    SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ]
    [ FROM from_body
      [ WHERE bool_expression ]
      [ OMIT RECORD IF bool_expression]
      [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ]
      [ HAVING bool_expression ]
      [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ]
      [ LIMIT n ]
    ];

from_body:
    {
      from_item [, ...] |  # Warning: Comma means UNION ALL here
      from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] |
      (FLATTEN({ table_name | (query) }, field_name_or_alias)) |
      table_wildcard_function
    }

from_item:
    { table_name | (query) } [ [ AS ] alias ]

join_type:
    { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS }

join_predicate:
    field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...]

expression:
    {
      literal_value |
      field_name_or_alias |
      function_call
    }

bool_expression:
    {
      expression_which_results_in_a_boolean_value |
      bool_expression AND bool_expression |
      bool_expression OR bool_expression |
      NOT bool_expression
    }

Notación:

  • Los corchetes “[ ]” indican cláusulas opcionales.
  • Las llaves “{ }” encierran un conjunto de opciones.
  • La barra vertical “|” indica un OR lógico.
  • Una coma o palabra clave seguida de puntos suspensivos entre corchetes “[, ...]” indica que el elemento anterior puede repetirse en una lista con el separador especificado.
  • Los paréntesis “( )” indican paréntesis literales.

Funciones de agregación

Las funciones agregadas muestran valores que representan resúmenes de conjuntos de datos más grandes, lo que hace que estas funciones sean muy útiles para analizar análisis de registros. Una función agregada opera contra una colección de valores y muestra un solo valor por tabla, grupo o alcance:

  • Agregación de tablas

    Usa una función agregada para resumir todas las filas que califican en la tabla. Por ejemplo:

    SELECT COUNT(f1) FROM ds.Table;

  • Agregación de grupo

    Usa una función agregada y una cláusula GROUP BY que especifica un campo no agregado para resumir las filas por grupo. Por ejemplo:

    SELECT COUNT(f1) FROM ds.Table GROUP BY b1;

    La función TOP representa un caso especializado de agregación de grupo.

  • Agregación con alcance

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

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

    El alcance puede ser RECORD, que corresponde a una fila completa, o un nodo (campo repetido en una fila). Las funciones de agregación operan sobre los valores dentro del alcance y muestran resultados agregados para cada registro o nodo.

Puedes aplicar una restricción a una función agregada con una de las siguientes opciones:

  • Un alias en una consulta de subselección. La restricción se especifica en la cláusula WHERE externa.

    #legacySQL
    SELECT corpus, count_corpus_words
    FROM
      (SELECT corpus, count(word) AS count_corpus_words
      FROM [bigquery-public-data:samples.shakespeare]
      GROUP BY corpus) AS sub_shakespeare
    WHERE count_corpus_words > 4000
    
  • Un alias en una cláusula HAVING.

    #legacySQL
    SELECT corpus, count(word) AS count_corpus_words
    FROM [bigquery-public-data:samples.shakespeare]
    GROUP BY corpus
    HAVING count_corpus_words > 4000;
    

También puedes hacer referencia a un alias en las cláusulas GROUP BY o ORDER BY.

Sintaxis

Funciones de agregación
AVG() Muestra el promedio de los valores para un grupo de filas…
BIT_AND() Muestra el resultado de una operación AND bit a bit…
BIT_OR() Muestra el resultado de una operación OR bit a bit…
BIT_XOR() Muestra el resultado de una operación XOR bit a bit…
CORR() Muestra el coeficiente de correlación de Pearson de un conjunto de pares de números.
COUNT() Muestra la cantidad total de valores…
COUNT([DISTINCT]) Muestra el número total de valores no nulos…
COVAR_POP() Calcula la covarianza de la población de los valores…
COVAR_SAMP() Calcula la covarianza muestral de los valores…
EXACT_COUNT_DISTINCT() Muestra el número exacto de valores no nulos distintos para el campo especificado.
FIRST() Muestra el primer valor secuencial dentro del alcance de la función.
GROUP_CONCAT() Concatena múltiples strings en una sola…
GROUP_CONCAT_UNQUOTED() Concatena múltiples strings en una sola… no agregará comillas dobles…
LAST() Muestra el último valor secuencial…
MAX() Muestra el valor máximo…
MIN() Muestra el valor mínimo…
NEST() Agrega todos los valores dentro del alcance de agregación actual a un campo repetido.
NTH() Muestra el enésimo valor secuencial…
QUANTILES() Calcula mínimos, máximos y cuantiles aproximados…
STDDEV() Muestra la desviación estándar…
STDDEV_POP() Calcula la desviación estándar de la población…
STDDEV_SAMP() Calcula la desviación estándar de la muestra…
SUM() Muestra la suma total de los valores…
TOP() ... COUNT(*) Muestra los registros principales de max_records según la frecuencia.
UNIQUE() Muestra el conjunto de valores no nulos únicos…
VARIANCE() Calcula la varianza de los valores…
VAR_POP() Calcula la varianza poblacional de los valores…
VAR_SAMP() Calcula la varianza muestral de los valores…
AVG(numeric_expr)
Muestra el promedio de los valores de un grupo de filas calculadas por numeric_expr. Las filas con un valor NULL no se incluyen en el cálculo.
BIT_AND(numeric_expr)
Muestra el resultado de una operación AND a nivel de bits entre cada instancia de numeric_expr en todas las filas. Los valores NULL se ignoran. Esta función muestra NULL si todas las instancias de numeric_expr se evalúan como NULL.
BIT_OR(numeric_expr)
Muestra el resultado de una operación OR a nivel de bits entre cada instancia de numeric_expr en todas las filas. Los valores NULL se ignoran. Esta función muestra NULL si todas las instancias de numeric_expr se evalúan como NULL.
BIT_XOR(numeric_expr)
Muestra el resultado de una operación XOR a nivel de bits entre cada instancia de numeric_expr en todas las filas. Los valores NULL se ignoran. Esta función muestra NULL si todas las instancias de numeric_expr se evalúan como NULL.
CORR(numeric_expr, numeric_expr)
Muestra el coeficiente de correlación de Pearson de un conjunto de pares de números.
COUNT(*)
Muestra la cantidad total de valores (NULO y no NULO) en el alcance de la función. A menos que uses COUNT(*) con la función TOP, es mejor especificar explícitamente el campo que se va a contar.
COUNT([DISTINCT] field [, n])
Muestra la cantidad total de valores no NULO en el alcance de la función.

Si usas la palabra clave DISTINCT, la función muestra la cantidad de valores distintos para el campo especificado. Ten en cuenta que el valor mostrado para DISTINCT es una aproximación estadística y puede que no sea exacto.

Usa EXACT_COUNT_DISTINCT() para obtener una respuesta exacta.

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

Para calcular la cantidad exacta de valores distintos, usa EXACT_COUNT_DISTINCT. Para un enfoque más escalable, considera usar GROUP EACH BY en los campos relevantes y, luego, aplicar COUNT(*). El enfoque de GROUP EACH BY es más escalable, pero puede incurrir en una leve penalización de rendimiento inicial.

COVAR_POP(numeric_expr1, numeric_expr2)
Calcula la covarianza de la población de los valores que calculan numeric_expr1 y numeric_expr2.
COVAR_SAMP(numeric_expr1, numeric_expr2)
alcula la covarianza de muestra de los valores que calculan numeric_expr1 y numeric_expr2.
EXACT_COUNT_DISTINCT(field)
Muestra el número exacto de valores no NULOS distintos para el campo especificado. Para mejorar la escalabilidad y el rendimiento, usa COUNT(DISTINCT field).
FIRST(expr)
Muestra el primer valor secuencial dentro del alcance de la función.
GROUP_CONCAT('str' [, separator])

Concatena varias strings en una sola, en la que cada valor está separado por el parámetro opcional separator. Si se omite separator, BigQuery muestra una string separada por comas.

Si una string en los datos de origen contiene un carácter de comillas dobles, GROUP_CONCAT muestra la string con comillas dobles agregadas. Por ejemplo, la string a"b se mostraría como "a""b". Usa GROUP_CONCAT_UNQUOTED si prefieres que estas strings no se muestren con comillas dobles agregadas.

Ejemplo:

#legacySQL
SELECT
  GROUP_CONCAT(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])

Concatena varias strings en una sola, en la que cada valor está separado por el parámetro opcional separator. Si se omite separator, BigQuery muestra una string separada por comas.

A diferencia de GROUP_CONCAT, esta función no agregará comillas dobles a los valores mostrados que incluyan un carácter de comillas dobles. Por ejemplo, la string a"b se mostraría como a"b.

Ejemplo:

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);
LAST(field)
Muestra el último valor secuencial en el alcance de la función.
MAX(field)
Muestra el valor máximo en el alcance de la función.
MIN(field)
Muestra el valor mínimo en el alcance de la función.
NEST(expr)

Agrega todos los valores dentro del alcance de agregación actual a un campo repetido. Por ejemplo, la consulta "SELECT x, NEST(y) FROM ... GROUP BY x" muestra un registro de salida para cada valor x distinto y contiene un campo repetido para todos los valores y sincronizados con x en la entrada de consulta. La función NEST requiere una cláusula GROUP BY.

BigQuery aplana de forma automática los resultados de la consulta, por lo que si usas la función NEST en la consulta de nivel superior, los resultados no contendrán campos repetidos. Usa la función NEST cuando uses una subselección que produce resultados intermedios para el uso inmediato de la misma consulta.

NTH(n, field)
Muestra el valor secuencial nth en el alcance de la función, donde n es una constante. La función NTH comienza a contar desde 1, por lo que no hay ningún término cero. Si el alcance de la función tiene menos de n valores, la función mostrará NULL.
QUANTILES(expr[, buckets])

Calcula el mínimo, el máximo y los cuantiles aproximados para la expresión de entrada. Los valores de entrada NULL se ignoran. Las entradas vacías o exclusivamente NULL dan como resultado una salida NULL. La cantidad de cuantiles calculados se controla con el parámetro buckets opcional, que incluye el mínimo y el máximo en el recuento. Para calcular los N-tiles aproximados, usa buckets N+1. El valor predeterminado de buckets es 100. (Nota: El valor predeterminado de 100 no estima los percentiles. Para estimar percentiles, usa 101 buckets como mínimo). Si se especifica de manera explícita, el valor de buckets debe ser 2 como mínimo.

El error fraccional por cuantil es épsilon = 1/buckets, lo que significa que el error disminuye a medida que aumenta la cantidad de depósitos. Por ejemplo:

QUANTILES(<expr>, 2) # computes min and max with 50% error.
QUANTILES(<expr>, 3) # computes min, median, and max with 33% error.
QUANTILES(<expr>, 5) # computes quartiles with 25% error.
QUANTILES(<expr>, 11) # computes deciles with 10% error.
QUANTILES(<expr>, 21) # computes vigintiles with 5% error.
QUANTILES(<expr>, 101) # computes percentiles with 1% error.

La función NTH se puede usar para seleccionar un cuantil particular, pero recuerda que NTH se basa en 1 y que QUANTILES muestra el mínimo (cuantil “0”) en la primera posición y el máximo (percentil “100” o “Nth” N-tile) en la última posición. Por ejemplo, NTH(11, QUANTILES(expr, 21)) estima la mediana de expr, mientras que NTH(20, QUANTILES(expr, 21)) estima el vigintil 19 (percentil 95) de expr. Ambas estimaciones tienen un margen de error del 5%.

Para mejorar la precisión, usa más depósitos. Por ejemplo, a fin de reducir el margen de error para los cálculos anteriores del 5% al 0.1%, usa 1,001 depósitos en lugar de 21 y ajusta el argumento a la función NTH según corresponda. A fin de calcular la mediana con un error del 0.1%, usa NTH(501, QUANTILES(expr, 1001)). Para el percentil 95 con un error del 0.1%, usa NTH(951, QUANTILES(expr, 1001)).

STDDEV(numeric_expr)
Muestra la desviación estándar de los valores que calcula numeric_expr. Las filas con un valor NULO no se incluyen en el cálculo. La función STDDEV es un alias para STDDEV_SAMP.
STDDEV_POP(numeric_expr)
Calcula la desviación estándar de la población del valor que calcula numeric_expr. Usa STDDEV_POP() para calcular la desviación estándar de un conjunto de datos que abarque toda la población de interés. Usa STDDEV_SAMP() si tu conjunto de datos comprende solo una muestra representativa de la población. Para obtener más información sobre la desviación estándar de muestra frente a la de población, consulta Desviación estándar en Wikipedia.
STDDEV_SAMP(numeric_expr)
Calcula la desviación estándar de muestra del valor que calcula numeric_expr. Usa STDDEV_SAMP() para calcular la desviación estándar de una población completa según una muestra representativa de la población. Usa STDDEV_POP() si tu conjunto de datos abarca toda la población. Para obtener más información sobre la desviación estándar de muestra frente a la de población, consulta Desviación estándar en Wikipedia.
SUM(field)
Muestra la suma total de los valores en el alcance de la función. Solo para usarse con tipos de datos numéricos.
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
Muestra los registros principales max_records según la frecuencia. Consulta la descripción de TOP a continuación para obtener más detalles.
UNIQUE(expr)
Muestra el conjunto de valores únicos no NULO en el alcance de la función en un orden indefinido. Similar a una cláusula GROUP BY grande sin la palabra clave EACH, la consulta fallará y mostrará el error “Resources Exceeded” si hay demasiados valores distintos. Sin embargo, a diferencia de GROUP BY, la función UNIQUE se puede aplicar con agregación con alcance, lo que permite una operación eficiente en campos anidados con una cantidad limitada de valores.
VARIANCE(numeric_expr)
Calcula la varianza de los valores que calcula numeric_expr. Las filas con un valor NULO no se incluyen en el cálculo. La función VARIANCE es un alias para VAR_SAMP.
VAR_POP(numeric_expr)
Calcula la varianza de población de los valores que calcula numeric_expr. Para obtener más información sobre la desviación estándar de muestra frente a la de población, consulta Desviación estándar en Wikipedia.
VAR_SAMP(numeric_expr)
Calcula la varianza de muestra de los valores que calcula numeric_expr. Para obtener más información sobre la desviación estándar de muestra frente a la de población, consulta Desviación estándar en Wikipedia.

Función TOP()

TOP es una función alternativa a la cláusula GROUP BY. Se usa como sintaxis simplificada para GROUP BY ... ORDER BY ... LIMIT .... Por lo general, la función TOP opera más rápido que la consulta ... GROUP BY ... ORDER BY ... LIMIT ... completa, pero es posible que solo muestre resultados aproximados. La siguiente es la sintaxis de la función TOP:

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

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

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

field|alias
Es el campo o alias que se mostrará.
max_values
Es la cantidad máxima de resultados que se mostrarán (opcional). El valor predeterminado es 20.
multiplier
Es un número entero positivo que aumenta los valores que muestra COUNT(*) por el múltiplo especificado.

Ejemplos de TOP()

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

    Las siguientes consultas usan TOP() para mostrar 10 filas.

    Ejemplo 1:

    #legacySQL
    SELECT
      TOP(word, 10) as word, COUNT(*) as cnt
    FROM
      [bigquery-public-data:samples.shakespeare]
    WHERE
      word CONTAINS "th";
    

    Ejemplo 2:

    #legacySQL
    SELECT
      word, left(word, 3)
    FROM
      (SELECT TOP(word, 10) AS word, COUNT(*)
         FROM [bigquery-public-data:samples.shakespeare]
         WHERE word CONTAINS "th");
    
  • Compara TOP() con GROUP BY...ORDER BY...LIMIT

    La consulta muestra, en orden, las 10 palabras usadas con mayor frecuencia que contienen “th” y la cantidad de documentos en los que se usaron las palabras. La consulta TOP se ejecutará con mayor rapidez:

    Ejemplo sin TOP():

    #legacySQL
    SELECT
      word, COUNT(*) AS cnt
    FROM
      ds.Table
    WHERE
      word CONTAINS 'th'
    GROUP BY
      word
    ORDER BY
      cnt DESC LIMIT 10;
    

    Ejemplo con TOP():

    #legacySQL
    SELECT
      TOP(word, 10), COUNT(*)
    FROM
      ds.Table
    WHERE
      word contains 'th';
    
  • Usa el parámetro multiplier.

    Las siguientes consultas muestran cómo el parámetro multiplier afecta el resultado de la consulta. La primera consulta muestra la cantidad de nacimientos por mes en Wyoming. La segunda consulta usa el parámetro multiplier para multiplicar los valores de cnt por 100.

    Ejemplo sin el parámetro multiplier:

    #legacySQL
    SELECT
      TOP(month,3) as month, COUNT(*) as cnt
    FROM
      [bigquery-public-data:samples.natality]
    WHERE
      state = "WY";

    Muestra:

    +-------+-------+
    | month |  cnt  |
    +-------+-------+
    |   7   | 19594 |
    |   5   | 19038 |
    |   8   | 19030 |
    +-------+-------+
    

    Ejemplo con el parámetro multiplier:

    #legacySQL
    SELECT
      TOP(month,3,100) as month, COUNT(*) as cnt
    FROM
      [bigquery-public-data:samples.natality]
    WHERE
      state = "WY";

    Muestra:

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

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

Ejemplos avanzados

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

    La siguiente consulta muestra la desviación promedio y estándar del peso de los bebés al momento del nacimiento en Ohio en 2003, agrupados por madres que fuman y no fuman.

    Ejemplo:

    #legacySQL
    SELECT
      cigarette_use,
      /* Finds average and standard deviation */
      AVG(weight_pounds) baby_weight,
      STDDEV(weight_pounds) baby_weight_stdev,
      AVG(mother_age) mother_age
    FROM
      [bigquery-public-data:samples.natality]
    WHERE
      year=2003 AND state='OH'
    /* Group the result values by those */
    /* who smoked and those who didn't.  */
    GROUP BY
      cigarette_use;
    
  • Filtra los resultados de la consulta con un valor agregado

    Para filtrar los resultados de la consulta mediante un valor agregado (por ejemplo, con el valor de una SUM), usa la función HAVING. HAVING compara un valor con un resultado determinado por una función de agregación, a diferencia de WHERE, que opera en cada fila antes de la agregación.

    Ejemplo:

    #legacySQL
    SELECT
      state,
      /* If 'is_male' is True, return 'Male', */
      /* otherwise return 'Female' */
      IF (is_male, 'Male', 'Female') AS sex,
      /* The count value is aliased as 'cnt' */
      /* and used in the HAVING clause below. */
      COUNT(*) AS cnt
    FROM
      [bigquery-public-data:samples.natality]
    WHERE
      state != ''
    GROUP BY
      state, sex
    HAVING
      cnt > 3000000
    ORDER BY
      cnt DESC
    

    Muestra:

    +-------+--------+---------+
    | state |  sex   |   cnt   |
    +-------+--------+---------+
    | CA    | Male   | 7060826 |
    | CA    | Female | 6733288 |
    | TX    | Male   | 5107542 |
    | TX    | Female | 4879247 |
    | NY    | Male   | 4442246 |
    | NY    | Female | 4227891 |
    | IL    | Male   | 3089555 |
    +-------+--------+---------+
    

Operadores aritméticos

Los operadores aritméticos toman argumentos numéricos y muestran un resultado numérico. Cada argumento puede ser un literal o un valor numérico mostrado por una consulta. Si la operación aritmética se evalúa como un resultado indefinido, la operación muestra NULL.

Sintaxis

Operador Descripción Ejemplo
+ Suma

SELECT 6 + (5 - 1);

Muestra: 10

- Resta

SELECT 6 - (4 + 1);

Muestra: 1

* Multiplicación

SELECT 6 * (5 - 1);

Muestra: 24

/ División

SELECT 6 / (2 + 2);

Muestra: 1.5

% Módulo

SELECT 6 % (2 + 2);

Muestra: 2

Funciones bit a bit

Las funciones bit a bit operan a nivel de bits individuales y requieren argumentos numéricos. Para obtener más información acerca de las funciones bit a bit, consulta Operación bit a bit.

En funciones agregadas, se documentan tres funciones adicionales a nivel de bits, BIT_AND, BIT_OR y BIT_XOR.

Sintaxis

Operador Descripción Ejemplo
& AND bit a bit

SELECT (1 + 3) & 1

Muestra: 0

| OR bit a bit

SELECT 24 | 12

Muestra: 28

^ XOR bit a bit

SELECT 1 ^ 0

Muestra: 1

<< Desplaza bits hacia la izquierda

SELECT 1 << (2 + 2)

Muestra: 16

>> Desplaza bits hacia la derecha

SELECT (6 + 2) >> 2

Muestra: 2

~ NOT bit a bit

SELECT ~2

Muestra: -3

BIT_COUNT(<numeric_expr>)

Muestra la cantidad de bits configurados en <numeric_expr>.

SELECT BIT_COUNT(29);

Muestra: 4

Funciones de conversión

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

Sintaxis

Funciones de conversión
BOOLEAN() Conversión a booleano.
BYTES() Conversión a bytes.
CAST(expr AS type) Convierte expr en una variable de tipo type.
FLOAT() Conversión al doble.
HEX_STRING() Conversión a la string hexadecimal.
INTEGER() Conversión a número entero.
STRING() Conversión a una string.
BOOLEAN(<numeric_expr>)
  • Muestra true si <numeric_expr> no es 0 y no es NULO.
  • Muestra false si <numeric_expr> es 0.
  • Muestra NULL si <numeric_expr> es NULO.
BYTES(string_expr)
Muestra string_expr como un valor de tipo bytes.
CAST(expr AS type)
Convierte expr en una variable de tipo type.
FLOAT(expr)
Muestra expr como un doble. expr puede ser una string como '45.78', pero la función muestra NULL para los valores no numéricos.
HEX_STRING(numeric_expr)
Muestra numeric_expr como una string hexadecimal.
INTEGER(expr)
Convierte expr en un número entero de 64 bits.
  • Muestra NULO si expr es una string que no corresponde a un número entero.
  • Muestra la cantidad de microsegundos desde el ciclo de entrenamiento unix si expr es una marca de tiempo.
STRING(numeric_expr)
Muestra numeric_expr como una string.

Funciones de comparación

Las funciones de comparación muestran true o false, según los siguientes tipos de comparaciones:

  • Una comparación de dos expresiones
  • Una comparación de una expresión o conjunto de expresiones con criterios específicos, como estar en una lista específica, ser NULO o ser un valor opcional no predeterminado

Algunas de las funciones detalladas a continuación muestran valores que no son true ni false, pero los valores que muestran se basan en operaciones de comparación.

Puedes usar expresiones numéricas o de strings como argumentos para las funciones de comparación. (Las constantes de string deben estar entre comillas simples o dobles). Las expresiones pueden ser literales o valores recuperados por una consulta. Las funciones de comparación se usan con más frecuencia como condiciones de filtrado en las cláusulas WHERE, pero se pueden usar en otras cláusulas.

Sintaxis

Funciones de comparación
expr1 = expr2 Muestra true si las expresiones son iguales.
expr1 != expr2
expr1 <> expr2
Muestra true si las expresiones no son iguales.
expr1 > expr2 Muestra true si expr1 es mayor que expr2.
expr1 < expr2 Muestra true si expr1 es menor que expr2.
expr1 >= expr2 Muestra true si expr1 es mayor o igual que expr2.
expr1 <= expr2 Muestra true si expr1 es menor o igual que expr2.
expr1 BETWEEN expr2 AND expr3 Muestra true si el valor de expr1 está entre expr2 y expr3, inclusive.
expr IS NULL Muestra true si expr es NULO.
expr IN() Muestra true si expr coincide con expr1, expr2 o cualquier valor entre paréntesis.
COALESCE() Muestra el primer argumento que no es NULO.
GREATEST() Muestra el parámetro numeric_expr más grande.
IFNULL() Muestra el argumento si no es nulo.
IS_INF() Muestra true si es infinito positivo o negativo.
IS_NAN() Muestra true si el argumento es NaN.
IS_EXPLICITLY_DEFINED() obsoleta: Usa expr IS NOT NULL en su lugar.
LEAST() Muestra el