Funciones, operadores y condicionales

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

Este tema es una compilación de funciones, operadores y expresiones condicionales.

Para obtener más información sobre cómo llamar a funciones, reglas de llamada a funciones, el prefijo SAFE y tipos especiales de argumentos, consultaLlamadas a la función.


OPERADORES Y CONDICIONALES

Operadores

SQL estándar de Google para BigQuery es compatible con los operadores. Los operadores se representan mediante caracteres especiales o palabras clave; no se usa la sintaxis de llamada a funciones. Un operador manipula cualquier cantidad de entradas de datos, también llamados operandos, y muestra un resultado.

Convenciones comunes:

  • A menos que se especifique lo contrario, todos los operadores muestran NULL cuando uno de los operandos es NULL.
  • Todos los operadores mostrarán un error si el cálculo arroja demasiados resultados.
  • Para todas las operaciones de punto flotante, +/-inf y NaN solo se pueden mostrar si uno de los operandos es +/-inf o NaN. En otros casos, se muestra un error.

Prioridad del operador

En la siguiente tabla se enumeran todos los operadores de SQL estándar de Google de mayor a menor prioridad, es decir, el orden en el que se evaluarán en una declaración.

Orden de prioridad Operador Tipos de datos de entrada Nombre Aridad del operador
1 Operador de acceso de campos JSON
STRUCT
Operador de acceso de campos Binario
  Operador subíndice de array ARRAY Posición del arreglo. Se debe usar con OFFSET o con ORDINAL; consulta Funciones de array. Binaria
  Operador subíndice JSON JSON Posición del nombre o del array del campo en JSON. Binaria
2 + Todos los tipos numéricos Más unario Unario
  - Todos los tipos numéricos Menos unario Unario
  ~ Número entero o BYTES NOT a nivel de bits Unario
3 * Todos los tipos numéricos Multiplicación Binario
  / Todos los tipos numéricos División Binario
  || STRING, BYTES o ARRAY <T> Operador de concatenación Binario
4 + Todos los tipos numéricos, DATE con INT64, INTERVAL Suma Binario
  - Todos los tipos numéricos, DATE con INT64, INTERVAL Resta Binario
5 << Número entero o BYTES Desplazamiento de bits a la izquierda Binario
  >> Número entero o BYTES Desplazamiento de bits a la derecha Binario
6 & Número entero o BYTES AND a nivel de bits Binario
7 ^ Número entero o BYTES XOR a nivel de bits Binario
8 | Número entero o BYTES OR a nivel de bits Binario
9 (Operadores de comparación) = Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Igual Binario
  < Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Menor que Binario
  > Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Mayor que Binario
  <= Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Menor o igual que Binario
  >= Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. Mayor o igual que Binario
  !=, <> Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. No igual Binario
  [NOT] LIKE STRING y byte El valor [no] coincide con el patrón especificado Binario
  [NOT] BETWEEN Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. El valor [no] está dentro del rango especificado Binario
  [NOT] IN Cualquier tipo comparable. Consulta Tipos de datos para ver una lista completa. El valor [no] está en el conjunto de valores especificado Binario
  IS [NOT] NULL Todos El valor [no] es NULL Unario
  IS [NOT] TRUE BOOL El valor [no] es VERDADERO Unario
  IS [NOT] FALSE BOOL El valor [no] es FALSO Unario
10 NOT BOOL NOT lógico Unario
11 AND BOOL AND lógico Binario
12 OR BOOL OR lógico Binario

Los operadores con la misma prioridad son asociativos a la izquierda. Esto significa que esos operadores se agrupan juntos de izquierda a derecha. Por ejemplo, la expresión:

x AND y AND z

se interpreta como

( ( x AND y ) AND z )

La expresión:

x * y / z

se interpreta como:

( ( x * y ) / z )

Todos los operadores de comparación tienen la misma prioridad, pero no son asociativos. Por lo tanto, se requieren paréntesis para resolver la ambigüedad. Por ejemplo:

(x < y) IS FALSE

Operador de acceso de campos

expression.fieldname[. ...]

Descripción

Obtiene el valor de un campo. También se conoce como el operador de punto. Se puede usar para acceder a campos anidados. Por ejemplo, expression.fieldname1.fieldname2.

Tipos de entrada

  • STRUCT
  • JSON

Tipo de datos que se muestra

  • Para STRUCT: El tipo de datos de SQL de fieldname. Si no se encuentra un campo en el struct, se genera un error.
  • Para JSON: JSON. Si no se encuentra un campo en un valor JSON, se muestra NULL de SQL.

Ejemplo

En el siguiente ejemplo, la expresión es t.customer y las operaciones de acceso de campo son .address y .country. Una operación es una aplicación de un operador (.) a operandos específicos (en este caso, address y country o, más específicamente, t.customer y address, para la primera operación, y t.customer.address y country para la segunda operación).

WITH orders AS (
  SELECT STRUCT(STRUCT('Yonge Street' AS street, 'Canada' AS country) AS address) AS customer
)
SELECT t.customer.address.country FROM orders AS t;

+---------+
| country |
+---------+
| Canada  |
+---------+

Operador subíndice de array

array_expression[array_subscript_specifier]

array_subscript_specifier:
  position_keyword(index)

position_keyword:
  { OFFSET | SAFE_OFFSET | ORDINAL | SAFE_ORDINAL }

Descripción

Obtiene un valor de un array en una ubicación específica.

Tipos de entrada

  • array_expression: El array de entrada
  • position_keyword: El inicio del índice del array y cómo se manejan los índices fuera de rango. Las opciones son:
    • OFFSET: El índice comienza en cero. Se produce un error si el índice está fuera de rango.
    • SAFE_OFFSET: El índice comienza en cero. Muestra NULL si el índice está fuera de rango.
    • ORDINAL: El índice comienza en uno. Se produce un error si el índice está fuera de rango.
    • SAFE_ORDINAL: El índice comienza en uno. Muestra NULL si el índice está fuera de rango.
  • index: un número entero que representa una posición específica en el array.

Tipo de datos que se muestra

En el ejemplo anterior, Ten el que array_expression es ARRAY<T>:

Ejemplos

En este ejemplo, el operador de subíndice del array se usa para mostrar valores en ubicaciones específicas en item_array. En este ejemplo, también se muestra lo que sucede cuando haces referencia a un índice (6) en un array que está fuera de rango. Si se incluye el prefijo SAFE, se muestra NULL; de lo contrario, se genera un error.

WITH Items AS (SELECT ["coffee", "tea", "milk"] AS item_array)
SELECT
  item_array,
  item_array[OFFSET(1)] AS item_offset,
  item_array[ORDINAL(1)] AS item_ordinal,
  item_array[SAFE_OFFSET(6)] AS item_safe_offset,
FROM Items

+----------------------------------+--------------+--------------+------------------+
| item_array                       | item_offset  | item_ordinal | item_safe_offset |
+----------------------------------+--------------+--------------+------------------+
| [coffee, tea, milk]              | tea          | coffee       | NULL             |
+----------------------------------+--------------+--------------+------------------+

En el siguiente ejemplo, cuando se hace referencia a un índice en un array que está fuera de rango y no se incluye el prefijo SAFE, se produce un error.

WITH Items AS (SELECT ["coffee", "tea", "milk"] AS item_array)
SELECT
  item_array[OFFSET(6)] AS item_offset
FROM Items

-- Error. OFFSET(6) is out of range.

Operador subíndice JSON

json_expression[array_element_id]
json_expression[field_name]

Descripción

Obtiene un valor de un campo o elemento del array en una expresión JSON. Se puede usar para acceder a datos anidados.

Tipos de entrada

  • JSON expression: La expresión JSON que contiene un elemento de array o campo que se mostrará.
  • [array_element_id]: Es una expresión INT64 que representa un índice basado en cero en el array. Si se ingresa un valor negativo, o el valor es mayor o igual que el tamaño del array, o la expresión JSON no representa un array JSON, se muestra un NULL de SQL.
  • [field_name]: Es una expresión STRING que representa el nombre de un campo en JSON. Si no se encuentra el nombre del campo o la expresión JSON no es un objeto JSON, se muestra NULL de SQL.

Tipo de datos que se muestra

JSON

Ejemplo

En el siguiente ejemplo:

  • json_value es una expresión JSON.
  • .class es un acceso de campo JSON.
  • .students es un acceso de campo JSON.
  • [0] es una expresión de subíndice JSON con un desplazamiento de elemento que accede al elemento cero de un array en el valor JSON.
  • ['name'] es una expresión de subíndice JSON con un nombre de campo que accede a un campo.
SELECT json_value.class.students[0]['name'] AS first_student
FROM
  UNNEST(
    [
      JSON '{"class" : {"students" : [{"name" : "Jane"}]}}',
      JSON '{"class" : {"students" : []}}',
      JSON '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'])
    AS json_value;

+-----------------+
| first_student   |
+-----------------+
| "Jane"          |
| NULL            |
| "John"          |
+-----------------+

Operadores aritméticos

Todos los operadores aritméticos aceptan la entrada del tipo numérico T, y el tipo de resultado tiene el tipo T, a menos que se indique lo contrario en la siguiente descripción:

Nombre Sintaxis
Suma X + Y
Resta X - Y
Multiplicación X * Y
División X / Y
Más unario + X
Menos unario - X

NOTA: Las operaciones de dividir por cero muestran un error. Para que se muestre un resultado diferente considera usar las funciones IEEE_DIVIDE o SAFE_DIVIDE.

Tipos de resultados para suma, resta y multiplicación:

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64INT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

Tipos de resultados para división:

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64FLOAT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

Tipos de resultados para Unary Plus:

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTINT64NUMERICBIGNUMERICFLOAT64

Tipos de resultados para menos unario:

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTINT64NUMERICBIGNUMERICFLOAT64

Operadores aritméticos de fecha

Los operadores “+” y “-” se pueden usar para las operaciones aritméticas con fechas.

date_expression + int64_expression
int64_expression + date_expression
date_expression - int64_expression

Descripción

Agrega días int64_expression a date_expression o se los resta. Esto es equivalente a las funciones DATE_ADD o DATE_SUB, cuando el intervalo se expresa en días.

Tipo de datos mostrados

DATE

Ejemplo

SELECT DATE "2020-09-22" + 1 AS day_later, DATE "2020-09-22" - 7 AS week_ago

+------------+------------+
| day_later  | week_ago   |
+------------+------------+
| 2020-09-23 | 2020-09-15 |
+------------+------------+

Resta de fecha y hora

date_expression - date_expression
timestamp_expression - timestamp_expression
datetime_expression - datetime_expression

Descripción

Calcula la diferencia entre dos valores de fecha y hora como un intervalo.

Tipo de datos mostrados

INTERVAL

Ejemplo

SELECT
  DATE "2021-05-20" - DATE "2020-04-19" AS date_diff,
  TIMESTAMP "2021-06-01 12:34:56.789" - TIMESTAMP "2021-05-31 00:00:00" AS time_diff

+-------------------+------------------------+
| date_diff         | time_diff              |
+-------------------+------------------------+
| 0-0 396 0:0:0     | 0-0 0 36:34:56.789     |
+-------------------+------------------------+

Operadores aritméticos de intervalo

Suma y resta

date_expression + interval_expression = DATETIME
date_expression - interval_expression = DATETIME
timestamp_expression + interval_expression = TIMESTAMP
timestamp_expression - interval_expression = TIMESTAMP
datetime_expression + interval_expression = DATETIME
datetime_expression - interval_expression = DATETIME

Descripción

Agrega un intervalo a un valor de fecha y hora o resta un intervalo de un valor de fecha y hora. Ejemplo

SELECT
  DATE "2021-04-20" + INTERVAL 25 HOUR AS date_plus,
  TIMESTAMP "2021-05-02 00:01:02.345" - INTERVAL 10 SECOND AS time_minus;

+-------------------------+--------------------------------+
| date_plus               | time_minus                     |
+-------------------------+--------------------------------+
| 2021-04-21 01:00:00     | 2021-05-02 00:00:52.345+00     |
+-------------------------+--------------------------------+

Multiplicación y división

interval_expression * integer_expression = INTERVAL
interval_expression / integer_expression = INTERVAL

Descripción

Multiplica o divide un valor de intervalo por un número entero.

Ejemplo

SELECT
  INTERVAL '1:2:3' HOUR TO SECOND * 10 AS mul1,
  INTERVAL 35 SECOND * 4 AS mul2,
  INTERVAL 10 YEAR / 3 AS div1,
  INTERVAL 1 MONTH / 12 AS div2

+----------------+--------------+-------------+--------------+
| mul1           | mul2         | div1        | div2         |
+----------------+--------------+-------------+--------------+
| 0-0 0 10:20:30 | 0-0 0 0:2:20 | 3-4 0 0:0:0 | 0-0 2 12:0:0 |
+----------------+--------------+-------------+--------------+

Operadores a nivel de bits

Todos los operadores a nivel de bits muestran el mismo tipo y la misma longitud que el primer operando.

Nombre Sintaxis Tipo de datos de entrada Descripción
NOT a nivel de bits ~ X Número entero o BYTES Realiza una negación lógica en cada bit y forma el complemento a uno del valor binario dado.
OR a nivel de bits X | Y X: número entero o BYTES
Y: mismo tipo que X
Toma dos patrones de bits de igual longitud y realiza la operación OR inclusiva lógica en cada par de los bits correspondientes. Este operador muestra un error si Y y X son BYTES de diferentes longitudes.
XOR a nivel de bits X ^ Y X: número entero o BYTES
Y: mismo tipo que X
Toma dos patrones de bits de igual longitud y realiza la operación OR exclusiva lógica en cada par de los bits correspondientes. Este operador muestra un error si Y y X son BYTES de diferentes longitudes.
AND a nivel de bits X & Y X: número entero o BYTES
Y: mismo tipo que X
Toma dos patrones de bits de igual longitud y realiza la operación lógica AND en cada par de los bits correspondientes. Este operador muestra un error si Y y X son BYTES de diferentes longitudes.
Desplazamiento a la izquierda X << Y X: Número entero o BYTES
Y: INT64
Desplaza el primer operando X a la izquierda. Este operador muestra 0 o una secuencia de bytes de b'\x00' si el segundo operando Y es igual o mayor que el largo de bits del primer operando X (por ejemplo, 64 si X tiene el tipo INT64). Este operador muestra un error si Y es negativo.
Desplazamiento a la derecha X >> Y X: Número entero o BYTES
Y: INT64
Desplaza el primer operando X a la derecha. Este operador no realiza la extensión de signo con un tipo de signo (es decir, llena los bits vacantes de la izquierda con 0). Este operador muestra 0 o una secuencia de bytes de b'\x00' si el segundo operando Y es igual o mayor que el largo de bits del primer operando X (por ejemplo, 64 si X tiene el tipo INT64). Este operador muestra un error si Y es negativo.

Operadores lógicos

SQL estándar de Google es compatible con los operadores lógicos AND, OR y NOT. Los operadores lógicos solo permiten la entrada BOOL o NULL y usan la lógica trivalente para producir un resultado. El resultado puede ser TRUE, FALSE o NULL:

x y x AND y x OR y
VERDADERO VERDADERO VERDADERO VERDADERO
VERDADERO FALSO FALSO VERDADERO
VERDADERO NULL NULL VERDADERO
FALSO VERDADERO FALSO VERDADERO
FALSO FALSO FALSO FALSO
FALSO NULL FALSO NULL
NULL VERDADERO NULL VERDADERO
NULL FALSO FALSO NULL
NULL NULL NULL NULL
x NOT X
VERDADERO FALSO
FALSO VERDADERO
NULL NULL

Ejemplos

En los ejemplos de esta sección, se hace referencia a una tabla llamada entry_table:

+-------+
| entry |
+-------+
| a     |
| b     |
| c     |
| NULL  |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'

-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL

+-------+
| entry |
+-------+
| a     |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')

-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL

+-------+
| entry |
+-------+
| b     |
| c     |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL

-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE

+-------+
| entry |
+-------+
| NULL  |
+-------+

Operadores de comparación

Las comparaciones siempre muestran BOOL. Por lo general, las comparaciones requieren que ambos operandos sean del mismo tipo. Si los operandos son de tipos diferentes y si SQL estándar de Google puede convertir los valores de esos tipos en un tipo común sin que se pierda la precisión, el SQL estándar de Google, por lo general, los coerciona a ese tipo común para comparación. Por lo general, el SQL estándar de Google coerciona los literales al tipo de los no literales, cuando están presentes. Los tipos de datos comparables se definen en Tipos de datos.

Los STRUCT solo admiten 4 operadores de comparación: igual (=), no igual (!= Y <>) y también IN.

Las siguientes reglas se aplican cuando se comparan estos tipos de datos:

  • FLOAT64: todas las comparaciones con NaN muestran FALSO, excepto != y <>, que muestran VERDADERO.
  • BOOL: FALSO es menor que VERDADERO.
  • STRING: las strings se comparan punto de código por punto de código, lo que significa que solo se garantiza que las strings canónicamente equivalentes se comparen como iguales si se normalizaron primero.
  • NULL: aquí se mantiene la convención; cualquier operación con una entrada NULL mostrará el valor NULL.
Nombre Sintaxis Descripción
Menor que X < Y Muestra TRUE si X es menor que Y. Este operador admite la especificación de intercalación.
Menor o igual que X <= Y Muestra TRUE si X es menor o igual que Y. Este operador admite la especificación de intercalación.
Mayor que X > Y Muestra TRUE si X es mayor que Y. Este operador admite la especificación de intercalación.
Mayor o igual que X >= Y Muestra TRUE si X es mayor o igual que Y. Este operador admite la especificación de intercalación.
Igual X = Y Muestra TRUE si X es igual que Y. Este operador admite la especificación de intercalación.
No igual X != Y
X <> Y
Muestra TRUE si X no es igual que Y. Este operador admite la especificación de intercalación.
BETWEEN X [NOT] BETWEEN Y AND Z

Muestra TRUE si X [no] está dentro del rango especificado. El resultado de "X BETWEEN Y AND Z" es equivalente a "Y <= X AND X <= Z", pero X se evalúa solo una vez en el primer caso. Este operador admite la especificación de intercalación.

LIKE X [NOT] LIKE Y Comprueba si la STRING del primer operando X coincide con un patrón especificado por el segundo operando Y. Las expresiones pueden contener estos caracteres:
  • Un signo de porcentaje "%" coincide con cualquier número de caracteres o bytes
  • Un guion bajo "_" coincide con un solo carácter o byte
  • Puedes marcar "\", "_" o "%" con dos barras invertidas. Por ejemplo, "\\%". Si usas strings sin procesar, se requiere una sola barra invertida. Por ejemplo, r"\%".
IN Múltiple: ver a continuación Muestra FALSE si el operando derecho está vacío. Muestra NULL si el operando izquierdo es NULL. Muestra TRUE o NULL, nunca FALSE, si el operando derecho contiene NULL. Los argumentos a ambos lados de IN son expresiones generales. No se requiere que ninguno de los operandos sea literal, aunque lo más común es usar un literal del lado derecho. X se evalúa una sola vez. Por lo general, este operador admite la especificación de intercalación.

Cuando se evalúan valores que tienen un tipo de datos STRUCT para determinar la igualdad, es posible que uno o más campos sea NULL. En esos casos:

  • Si todos los valores que no son NULL son iguales, la comparación muestra NULL.
  • Si el valor de algún campo no NULL no es igual, la comparación muestra FALSE.

En la siguiente tabla, se muestra cómo los tipos de datos STRUCT se comparan cuando tienen campos con valores NULL.

Struct1 Struct2 Struct1 = Struct2
STRUCT(1, NULL) STRUCT(1, NULL) NULL
STRUCT(1, NULL) STRUCT(2, NULL) FALSE
STRUCT(1,2) STRUCT(1, NULL) NULL

Operador EXISTS

EXISTS ( subquery )

Descripción

El resultado es TRUE si la subconsulta produce una o más filas. Muestra FALSE si la subconsulta produce cero filas. Nunca muestra NULL. Para obtener más información sobre cómo puedes usar una subconsulta con EXISTS, visita Subconsultas EXISTS.

Ejemplos

En este ejemplo, el operador EXISTS muestra FALSE porque no hay filas en Words en las que la dirección sea south:

WITH Words AS (
  SELECT 'Intend' as value, 'east' as direction UNION ALL
  SELECT 'Secure', 'north' UNION ALL
  SELECT 'Clarity', 'west'
 )
SELECT EXISTS ( SELECT value FROM Words WHERE direction = 'south' ) as result;

+--------+
| result |
+--------+
| FALSE  |
+--------+

Operador IN

El operador IN es compatible con la siguiente sintaxis:

search_value [NOT] IN value_set

value_set:
  {
    (expression[, ...])
    | (subquery)
    | UNNEST(array_expression)
  }

Descripción

Comprueba si hay un valor igual en un conjunto de valores. Se aplican reglas semánticas, pero, por lo general, IN muestra TRUE si se encuentra un valor igual, FALSE si se excluye un valor igual; de lo contrario, NULL, NOT IN muestra FALSE si se encuentra un valor igual, TRUE si se excluye un valor igual; de lo contrario, es NULL.

  • search_value: Es la expresión que se compara con un conjunto de valores.
  • value_set: Representa uno o más valores para comparar con un valor de búsqueda.

    • (expression[, ...]): Es una lista de expresiones.
    • (subquery): Es una subconsulta que muestra una sola columna. Los valores de esa columna son el conjunto de valores. Si no se generan filas, el conjunto de valores está vacío.
    • UNNEST(array_expression): Es un operador UNNEST que muestra una columna de valores de una expresión de array. Esto equivale a lo siguiente:

      IN (SELECT element FROM UNNEST(array_expression) AS element)
      

Reglas semánticas

Cuando se usa el operador IN, se aplica la siguiente semántica en este orden:

  • Muestra FALSE si value_set está vacío.
  • Muestra NULL si search_value es NULL.
  • Muestra TRUE si value_set contiene un valor igual a search_value.
  • Muestra NULL si value_set contiene un NULL.
  • Muestra FALSE.

Cuando se usa el operador NOT IN, se aplica la siguiente semántica en este orden:

  • Muestra TRUE si value_set está vacío.
  • Muestra NULL si search_value es NULL.
  • Muestra FALSE si value_set contiene un valor igual a search_value.
  • Muestra NULL si value_set contiene un NULL.
  • Muestra TRUE.

Por lo general, este operador admite la intercalación, sin embargo, no se admite x [NOT] IN UNNEST.

La semántica de

x IN (y, z, ...)

se define como equivalente a

(x = y) OR (x = z) OR ...

y las formas de subconsulta y array se definen de forma similar.

x NOT IN ...

es equivalente a lo siguiente:

NOT(x IN ...)

El formulario UNNEST trata un análisis de array como UNNEST en la cláusula FROM:

x [NOT] IN UNNEST(<array expression>)

Este formulario se suele usar con parámetros ARRAY. Por ejemplo:

x IN UNNEST(@array_parameter)

Consulta el tema Arrays para obtener información sobre cómo usar esta sintaxis.

IN se puede usar con claves de varias partes mediante la sintaxis del constructor de struct. Por ejemplo:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

Consulta Tipo Struct para obtener más información.

Tipo de datos mostrados

BOOL

Ejemplos

Puedes usar estas cláusulas WITH a fin de emular tablas temporales para Words y Items en los siguientes ejemplos:

WITH Words AS (
  SELECT 'Intend' as value UNION ALL
  SELECT 'Secure' UNION ALL
  SELECT 'Clarity' UNION ALL
  SELECT 'Peace' UNION ALL
  SELECT 'Intend'
 )
SELECT * FROM Words;

+----------+
| value    |
+----------+
| Intend   |
| Secure   |
| Clarity  |
| Peace    |
| Intend   |
+----------+
WITH
  Items AS (
    SELECT STRUCT('blue' AS color, 'round' AS shape) AS info UNION ALL
    SELECT STRUCT('blue', 'square') UNION ALL
    SELECT STRUCT('red', 'round')
  )
SELECT * FROM Items;

+----------------------------+
| info                       |
+----------------------------+
| {blue color, round shape}  |
| {blue color, square shape} |
| {red color, round shape}   |
+----------------------------+

Ejemplo con IN y una expresión:

SELECT * FROM Words WHERE value IN ('Intend', 'Secure');

+----------+
| value    |
+----------+
| Intend   |
| Secure   |
| Intend   |
+----------+

Ejemplo con NOT IN y una expresión:

SELECT * FROM Words WHERE value NOT IN ('Intend');

+----------+
| value    |
+----------+
| Secure   |
| Clarity  |
| Peace    |
+----------+

Ejemplo con IN, una subconsulta escalar y una expresión:

SELECT * FROM Words WHERE value IN ((SELECT 'Intend'), 'Clarity');

+----------+
| value    |
+----------+
| Intend   |
| Clarity  |
| Intend   |
+----------+

Ejemplo con IN y una operación UNNEST:

SELECT * FROM Words WHERE value IN UNNEST(['Secure', 'Clarity']);

+----------+
| value    |
+----------+
| Secure   |
| Clarity  |
+----------+

Ejemplo con IN y STRUCT:

SELECT
  (SELECT AS STRUCT Items.info) as item
FROM
  Items
WHERE (info.shape, info.color) IN (('round', 'blue'));

+------------------------------------+
| item                               |
+------------------------------------+
| { {blue color, round shape} info } |
+------------------------------------+

Operadores IS

Los operadores IS muestran TRUE o FALSE para la condición que prueban. Nunca muestran NULL, ni siquiera para entradas NULL, a diferencia de la función IS_INF y la función IS_NAN definida en Funciones matemáticas. Si NOT está presente, el valor BOOL de salida se invierte.

Sintaxis de la función Tipo de datos de entrada Tipo de datos del resultado Descripción

X IS TRUE
BOOL BOOL Se evalúa como TRUE si X se evalúa como TRUE. De lo contrario, se evalúa como FALSE.

X IS NOT TRUE
BOOL BOOL Se evalúa como FALSE si X se evalúa como TRUE. De lo contrario, se evalúa como TRUE.

X IS FALSE
BOOL BOOL Se evalúa como TRUE si X se evalúa como FALSE. De lo contrario, se evalúa como FALSE.

X IS NOT FALSE
BOOL BOOL Se evalúa como FALSE si X se evalúa como FALSE. De lo contrario, se evalúa como TRUE.

X IS NULL
Cualquier tipo de valor BOOL Se evalúa como TRUE si X se evalúa como NULL. De lo contrario, se evalúa como FALSE.

X IS NOT NULL
Cualquier tipo de valor BOOL Se evalúa como FALSE si X se evalúa como NULL. De lo contrario, se evalúa como TRUE.

X IS UNKNOWN
BOOL BOOL Se evalúa como TRUE si X se evalúa como NULL. De lo contrario, se evalúa como FALSE.

X IS NOT UNKNOWN
BOOL BOOL Se evalúa como FALSE si X se evalúa como NULL. De lo contrario, se evalúa como TRUE.

IS DISTINCT FROM operador

expression_1 IS [NOT] DISTINCT FROM expression_2

Descripción

IS DISTINCT FROM muestra TRUE si las cláusulas DISTINCT y GROUP BY consideran que los valores de entrada son distintos entre sí. De lo contrario, muestra FALSE.

a IS DISTINCT FROM b es TRUE equivale a lo siguiente:

  • SELECT COUNT(DISTINCT x) FROM UNNEST([a,b]) x muestra 2.
  • SELECT * FROM UNNEST([a,b]) x GROUP BY x muestra 2 filas.

a IS DISTINCT FROM b es equivalente a NOT (a = b), excepto en los siguientes casos:

  • Este operador nunca muestra NULL, por lo que los valores NULL se consideran distintos de los valores que no son NULL, no de otros valores NULL.
  • Los valores NaN se consideran distintos de los valores no NaN, pero no de otros valores NaN.

Puedes usar esta operación con campos en un tipo de datos complejo, pero no en los tipos de datos complejos. Estos tipos de datos complejos no se pueden comparar directamente:

  • STRUCT
  • ARRAY

Tipos de entrada

  • expression_1: El primer valor que se compara. Puede ser un tipo de datos que se pueda agrupar, NULL o NaN.
  • expression_2: El segundo valor que se compara. Puede ser un tipo de datos que se pueda agrupar, NULL o NaN.
  • NOT: si está presente, el valor BOOL de salida se invierte.

Tipo de datos que se muestra

BOOL

Ejemplos

Estos muestran TRUE:

SELECT 1 IS DISTINCT FROM 2
SELECT 1 IS DISTINCT FROM NULL
SELECT 1 IS NOT DISTINCT FROM 1
SELECT NULL IS NOT DISTINCT FROM NULL

Estos muestran FALSE:

SELECT NULL IS DISTINCT FROM NULL
SELECT 1 IS DISTINCT FROM 1
SELECT 1 IS NOT DISTINCT FROM 2
SELECT 1 IS NOT DISTINCT FROM NULL

Operador de concatenación

El operador de concatenación combina varios valores en uno.

Sintaxis de la función Tipo de datos de entrada Tipo de datos del resultado

STRING || STRING [ || ... ]
STRING STRING

BYTES || BYTES [ || ... ]
BYTES STRING

ARRAY<T> || ARRAY<T> [ || ... ]
ARRAY<T> ARRAY<T>

Expresiones condicionales

SQL estándar de Google para BigQuery es compatible con expresiones condicionales. Las expresiones condicionales imponen restricciones en el orden de evaluación de sus entradas. En síntesis, se evalúan de izquierda a derecha, con cortocircuito, y solo evalúan el valor de resultado elegido. Por el contrario, todas las entradas a las funciones normales se evalúan antes de llamar a la función. El cortocircuito en expresiones condicionales puede explotarse para el manejo de errores o el ajuste del rendimiento.

CASE expr

CASE expr
  WHEN expr_to_match THEN result
  [ ... ]
  [ ELSE else_result ]
  END

Descripción

Compara expr con el valor expr_to_match de cada cláusula WHEN sucesiva y muestra el primer resultado en el que esta comparación se evalúe como TRUE. Las cláusulas WHEN restantes y else_result no se evalúan.

Si la comparación expr = expr_to_match se evalúa como FALSE o NULL para todas las cláusulas WHEN, muestra la evaluación de else_result si está presente; si else_result no está presente, muestra NULL.

De acuerdo con las comparaciones de igualdad en otros lugares, si expr y expr_to_match son NULL, expr = expr_to_match se evalúa como NULL, que muestra lo siguiente else_result Si una declaración CASE necesita distinguir un valor NULL, se debe usar la sintaxis alternativa CASE.

expr y expr_to_match pueden ser de cualquier tipo. Deben ser coercibles de forma implícita a un supertipo común; las comparaciones de igualdad se realizan en valores coercionados. Puede haber varios tipos de result. Las expresiones result y else_result deben ser coercibles a un supertipo común.

Esta expresión admite la especificación de la intercalación.

Tipo de datos mostrados

Supertipo de result [, ...] y else_result

Ejemplo

WITH Numbers AS (
  SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 8 UNION ALL
  SELECT 60, 6 UNION ALL
  SELECT 50, 10
)
SELECT
  A,
  B,
  CASE A
    WHEN 90 THEN 'red'
    WHEN 50 THEN 'blue'
    ELSE 'green'
    END
    AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 8  | blue   |
| 60 | 6  | green  |
| 50 | 10 | blue   |
+------------------+

CASE

CASE
  WHEN condition THEN result
  [ ... ]
  [ ELSE else_result ]
  END

Descripción

Evalúa la condición de cada cláusula WHEN sucesiva y muestra el primer resultado en el que la condición se evalúe como TRUE; las cláusulas WHEN restantes y else_result no se evalúan.

Si todas las condiciones se evalúan como FALSE o NULL, se muestra la evaluación de else_result si está presente; si else_result no está presente, muestra NULL.

Para obtener reglas adicionales sobre cómo se evalúan los valores, consulta la tabla de lógica de tres valores en Operadores lógicos.

condition debe ser una expresión booleana. Puede haber varios tipos de result. Las expresiones result y else_result deben ser coercibles de manera implícita a un supertipo común.

Esta expresión admite la especificación de la intercalación.

Tipo de datos mostrados

Supertipo de result [, ...] y else_result

Ejemplo

WITH Numbers AS (
  SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 6 UNION ALL
  SELECT 20, 10
)
SELECT
  A,
  B,
  CASE
    WHEN A > 60 THEN 'red'
    WHEN B = 6 THEN 'blue'
    ELSE 'green'
    END
    AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 6  | blue   |
| 20 | 10 | green  |
+------------------+

COALESCE

COALESCE(expr[, ...])

Descripción

Muestra el valor de la primera expresión no-NULL. Las expresiones restantes no se evalúan. Una expresión de entrada puede ser de cualquier tipo. Puede haber varios tipos de expresión de entrada. Todas las expresiones de entrada deben ser coercibles de forma implícita a un supertipo común.

Tipo de datos mostrados

Supertipo de expr [, …].

Ejemplos

SELECT COALESCE('A', 'B', 'C') as result

+--------+
| result |
+--------+
| A      |
+--------+
SELECT COALESCE(NULL, 'B', 'C') as result

+--------+
| result |
+--------+
| B      |
+--------+

IF

IF(expr, true_result, else_result)

Descripción

Si expr se evalúa como TRUE, muestra true_result. De lo contrario, muestra la evaluación para else_result. else_result no se evalúa si expr se evalúa como TRUE. true_result no se evalúa si expr se evalúa como FALSE o NULL.

expr debe ser una expresión booleana. true_result y else_result deben ser coercibles a un supertipo común.

Tipo de datos mostrados

Supertipo de true_result y else_result

Ejemplo

WITH Numbers AS (
  SELECT 10 as A, 20 as B UNION ALL
  SELECT 50, 30 UNION ALL
  SELECT 60, 60
)
SELECT
  A,
  B,
  IF(A < B, 'true', 'false') AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 10 | 20 | true   |
| 50 | 30 | false  |
| 60 | 60 | false  |
+------------------+

IFNULL

IFNULL(expr, null_result)

Descripción

Si expr se evalúa como NULL, muestra null_result. De lo contrario, muestra expr. Si expr no se evalúa como NULL, null_result no se evalúa.

expr y null_result pueden ser de cualquier tipo y deben ser coercibles de forma implícita a un supertipo común. Sinónimo de COALESCE(expr, null_result).

Tipo de datos mostrados

Supertipo de expr y null_result.

Ejemplos

SELECT IFNULL(NULL, 0) as result

+--------+
| result |
+--------+
| 0      |
+--------+
SELECT IFNULL(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

NULLIF

NULLIF(expr, expr_to_match)

Descripción

Muestra NULL si expr = expr_to_match se evalúa como TRUE; de lo contrario, muestra expr.

expr y expr_to_match deben ser coercibles de manera implícita a un supertipo común y deben ser comparables.

Esta expresión admite la especificación de la intercalación.

Tipo de datos mostrados

Supertipo de expr y expr_to_match

Ejemplo

SELECT NULLIF(0, 0) as result

+--------+
| result |
+--------+
| NULL   |
+--------+
SELECT NULLIF(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

FUNCIONES

Funciones de agregación

SQL estándar de Google para BigQuery es compatible con las siguientes funciones de agregación generales. Para obtener información sobre la sintaxis de llamadas a funciones agregadas, consulta Llamadas a funciones agregadas.

ANY_VALUE

ANY_VALUE(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra expression para algunas filas que se eligieron del grupo. La elección de la fila no es determinista ni aleatoria. Muestra NULL cuando la entrada no produce filas. Muestra NULL cuando expression es NULL para todas las filas del grupo.

ANY_VALUE se comporta como si se especificara RESPECT NULLS, se consideran y pueden seleccionarse las filas en las que expression es NULL.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Tipos de argumentos admitidos

Cualquiera

Tipos de datos mostrados

Coincide con el tipo de datos de entrada.

Ejemplos

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+
SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+--------+-----------+
| fruit  | any_value |
+--------+-----------+
| pear   | pear      |
| apple  | pear      |
| banana | apple     |
+--------+-----------+

ARRAY_AGG

ARRAY_AGG(
  [ DISTINCT ]
  expression
  [ { IGNORE | RESPECT } NULLS ]
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra un ARRAY de valores expression.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Se genera un error si un arreglo en el resultado final de la consulta contiene un elemento NULL.

Tipos de argumentos admitidos

Todos los tipos de datos excepto ARRAY.

Tipos de datos mostrados

ARRAY

Si hay cero filas de entrada, esta función muestra NULL.

Ejemplos

SELECT ARRAY_AGG(x) AS array_agg FROM UNNEST([2, 1,-2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
SELECT ARRAY_AGG(x ORDER BY ABS(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT ARRAY_AGG(x LIMIT 5) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
WITH vals AS
  (
    SELECT 1 x UNION ALL
    SELECT -2 x UNION ALL
    SELECT 3 x UNION ALL
    SELECT -2 x UNION ALL
    SELECT 1 x
  )
SELECT ARRAY_AGG(DISTINCT x ORDER BY x) as array_agg
FROM vals;

+------------+
| array_agg  |
+------------+
| [-2, 1, 3] |
+------------+
WITH vals AS
  (
    SELECT 1 x, 'a' y UNION ALL
    SELECT 1 x, 'b' y UNION ALL
    SELECT 2 x, 'a' y UNION ALL
    SELECT 2 x, 'c' y
  )
SELECT x, ARRAY_AGG(y) as array_agg
FROM vals
GROUP BY x;

+---------------+
| x | array_agg |
+---------------+
| 1 | [a, b]    |
| 2 | [a, c]    |
+---------------+
SELECT
  x,
  ARRAY_AGG(x) OVER (ORDER BY ABS(x)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+----+-------------------------+
| x  | array_agg               |
+----+-------------------------+
| 1  | [1, 1]                  |
| 1  | [1, 1]                  |
| 2  | [1, 1, 2, -2, -2, 2]    |
| -2 | [1, 1, 2, -2, -2, 2]    |
| -2 | [1, 1, 2, -2, -2, 2]    |
| 2  | [1, 1, 2, -2, -2, 2]    |
| 3  | [1, 1, 2, -2, -2, 2, 3] |
+----+-------------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(
  expression
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)

Descripción

Concatena elementos de expression de tipo ARRAY y muestra un solo array como resultado.

Esta función ignora los arreglos de entrada NULL, pero respeta los elementos NULL en arrays de entrada que no son NULL. Sin embargo, se genera un error si un array en el resultado final de la consulta contiene un elemento NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Tipos de argumentos admitidos

ARRAY

Tipos de datos mostrados

ARRAY

Ejemplos

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x)) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4]       |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+--------------------------+
| array_concat_agg         |
+--------------------------+
| [1, 2, 3, 4, 5, 6]       |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

AVG(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra el promedio de valores de entrada que no son NULL, o muestra NaN si la entrada contiene un NaN.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Tipos de argumentos admitidos

Cualquier tipo de entrada numérico, como INT64. Ten en cuenta que para los tipos de entrada de punto flotante el resultado mostrado es no determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Tipos de datos mostrados

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

Ejemplos

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+
SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+------+
| x    | avg  |
+------+------+
| NULL | NULL |
| 0    | 0    |
| 2    | 1    |
| 4    | 3    |
| 4    | 4    |
| 5    | 4.5  |
+------+------+

BIT_AND

BIT_AND(
  expression
)

Descripción

Realiza una operación AND a nivel de bits en expression y muestra el resultado.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Tipos de argumentos admitidos

  • INT64

Tipos de datos mostrados

INT64

Ejemplos

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR(
  expression
)

Descripción

Realiza una operación OR a nivel de bits en expression y muestra el resultado.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Tipos de argumentos admitidos

  • INT64

Tipos de datos mostrados

INT64

Ejemplos

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR(
  [ DISTINCT ]
  expression
)

Descripción

Realiza una operación XOR a nivel de bits en expression y muestra el resultado.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Tipos de argumentos admitidos

  • INT64

Tipos de datos mostrados

INT64

Ejemplos

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1.

COUNT(*)
[OVER over_clause]

2.

COUNT(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

  1. Muestra la cantidad de filas en la entrada.
  2. Muestra el número de filas con expression evaluado como cualquier valor distinto de NULL.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Esta función con DISTINCT admite la especificación de intercalación.

Tipos de argumentos admitidos

expression puede ser cualquier tipo de datos. Si DISTINCT está presente, expression solo puede ser un tipo de datos agrupable.

Tipos de datos mostrados

INT64

Ejemplos

Puedes usar la función COUNT para mostrar la cantidad de filas en una tabla o la cantidad de valores distintos de una expresión. Por ejemplo:

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------+------------+--------------+
| x    | count_star | count_dist_x |
+------+------------+--------------+
| 1    | 3          | 2            |
| 4    | 3          | 2            |
| 4    | 3          | 2            |
| 5    | 1          | 1            |
+------+------------+--------------+
SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------+------------+---------+
| x    | count_star | count_x |
+------+------------+---------+
| NULL | 1          | 0       |
| 1    | 3          | 3       |
| 4    | 3          | 3       |
| 4    | 3          | 3       |
| 5    | 1          | 1       |
+------+------------+---------+

Si deseas contar la cantidad de valores distintos de una expresión para los que se cumple una condición determinada, esto es algo que puedes usar:

COUNT(DISTINCT IF(condition, expression, NULL))

Aquí, IF mostrará el valor de expression si condition es TRUE o, de lo contrario, NULL. El COUNT(DISTINCT ...) circundante ignorará los valores NULL, por lo que solo contará los valores distintos de expression para los que condition es TRUE.

Por ejemplo, para contar la cantidad de valores positivos distintos de x, haz lo siguiente:

SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;

+-------------------+
| distinct_positive |
+-------------------+
| 3                 |
+-------------------+

O bien, para contar la cantidad de fechas distintas en las que ocurrió un tipo de evento determinado, haz lo siguiente:

WITH Events AS (
  SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
  COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
    AS distinct_dates_with_failures
FROM Events;

+------------------------------+
| distinct_dates_with_failures |
+------------------------------+
| 2                            |
+------------------------------+

COUNTIF

COUNTIF(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra el recuento de valores TRUE para expression. Muestra 0 si hay cero filas de entrada o si la expression se evalúa como FALSE o NULL para todas las filas.

Dado que expression debe ser BOOL, el formulario COUNTIF(DISTINCT ...) no es compatible. Esto no sería útil: solo hay un valor distinto de TRUE. Por lo general, cuando alguien quiere combinar COUNTIF y DISTINCT, quiere contar la cantidad de valores distintos de una expresión para la que se cumple una condición determinada. Una receta para lograrlo es la siguiente:

COUNT(DISTINCT IF(condition, expression, NULL))

Ten en cuenta que esto usa COUNT, no COUNTIF; la parte IF se movió dentro. Para obtener más información, consulta los ejemplos de COUNT.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Tipos de argumentos admitidos

BOOL

Tipos de datos mostrados

INT64

Ejemplos

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+
SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

LOGICAL_AND

LOGICAL_AND(
  expression
)

Descripción

Muestra el AND lógico para todas las expresiones que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Tipos de argumentos admitidos

BOOL

Tipos de datos mostrados

BOOL

Ejemplos

LOGICAL_AND muestra FALSE porque no todos los valores del arreglo son menores que 3.

SELECT LOGICAL_AND(x < 3) AS logical_and FROM UNNEST([1, 2, 4]) AS x;

+-------------+
| logical_and |
+-------------+
| FALSE       |
+-------------+

LOGICAL_OR

LOGICAL_OR(
  expression
)

Descripción

Muestra el OR lógico para todas las expresiones que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Tipos de argumentos admitidos

BOOL

Tipos de datos mostrados

BOOL

Ejemplos

LOGICAL_OR muestra TRUE porque al menos uno de los valores en el array es menor que 3.

SELECT LOGICAL_OR(x < 3) AS logical_or FROM UNNEST([1, 2, 4]) AS x;

+------------+
| logical_or |
+------------+
| TRUE       |
+------------+

MAX

MAX(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra el valor máximo de las expresiones que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas. Muestra NaN si la entrada contiene un NaN.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Esta función admite la especificación de la intercalación.

Tipos de argumentos admitidos

Cualquier tipo de datos que se pueda ordenar, excepto ARRAY.

Tipos de datos mostrados

Igual al tipo de datos usado como valores de entrada.

Ejemplos

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | max  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 8    |
| 4    | 8    |
| 37   | 55   |
| 55   | 55   |
+------+------+

MIN

MIN(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra el valor mínimo de las expresiones que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas. Muestra NaN si la entrada contiene un NaN.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Esta función admite la especificación de la intercalación.

Tipos de argumentos admitidos

Cualquier tipo de datos que se pueda ordenar, excepto ARRAY.

Tipos de datos mostrados

Igual al tipo de datos usado como valores de entrada.

Ejemplos

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | min  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 4    |
| 4    | 4    |
| 37   | 37   |
| 55   | 37   |
+------+------+

STRING_AGG

STRING_AGG(
  [ DISTINCT ]
  expression [, delimiter]
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra un valor (ya sea STRING o BYTES) que se obtiene cuando se concatenan valores que no son NULL. Muestra NULL si hay cero filas de entrada o si expression se evalúa como NULL para todas las filas.

Si especificas un delimiter, los valores concatenados se separan con ese delimitador; de lo contrario, se usa una coma.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Tipos de argumentos admitidos

STRING o BYTES

Tipos de datos mostrados

STRING o BYTES

Ejemplos

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| pear & pear & apple & banana |
+------------------------------+
SELECT STRING_AGG(fruit, " & " LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+--------------+
| string_agg   |
+--------------+
| apple & pear |
+--------------+
SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+---------------+
| string_agg    |
+---------------+
| pear & banana |
+---------------+
SELECT
  fruit,
  STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+--------+------------------------------+
| fruit  | string_agg                   |
+--------+------------------------------+
| NULL   | NULL                         |
| pear   | pear & pear                  |
| pear   | pear & pear                  |
| apple  | pear & pear & apple          |
| banana | pear & pear & apple & banana |
+--------+------------------------------+

SUM

SUM(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra la suma de valores no nulos.

Si la expresión es un valor de punto flotante, la suma es no determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Tipos de argumentos admitidos

Cualquier tipo de datos numéricos admitido e INTERVAL

Tipos de datos mostrados

INPUTINT64NUMERICBIGNUMERICFLOAT64INTERVAL
OUTPUTINT64NUMERICBIGNUMERICFLOAT64INTERVAL

Casos especiales:

Muestra NULL si la entrada solo contiene NULL.

Muestra NULL si la entrada no contiene filas.

Muestra Inf si la entrada contiene Inf.

Muestra -Inf si la entrada contiene -Inf.

Muestra NaN si la entrada contiene un NaN.

Muestra NaN si la entrada contiene una combinación de Inf y -Inf.

Ejemplos

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 6   |
| 3 | 6   |
| 1 | 10  |
| 4 | 10  |
| 4 | 10  |
| 1 | 10  |
| 2 | 9   |
| 5 | 9   |
| 2 | 9   |
+---+-----+
SELECT
  x,
  SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 3   |
| 3 | 3   |
| 1 | 5   |
| 4 | 5   |
| 4 | 5   |
| 1 | 5   |
| 2 | 7   |
| 5 | 7   |
| 2 | 7   |
+---+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

Funciones de agregación estadística

SQL estándar de Google para BigQuery es compatible con las funciones de agregación estadística. Para obtener información sobre la sintaxis de llamadas a funciones agregadas, consulta Llamadas a funciones agregadas.

CORR

CORR(
  X1, X2
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra el coeficiente de correlación de Pearson de un conjunto de pares de números. Para cada par de números, el primero es la variable dependiente y el segundo es la variable independiente. El resultado que se muestra está entre -1 y 1. Un resultado de 0 indica que no hay correlación.

Se admiten todos los tipos numéricos. Si la entrada es NUMERIC o BIGNUMERIC, la agregación interna es estable con el resultado final convertido en un FLOAT64. De lo contrario, la entrada se convierte en un FLOAT64 antes de la agregación, lo que genera un resultado potencialmente inestable.

Esta función ignora los pares de entrada que contengan uno o más valores NULL. Si hay menos de dos pares de entrada sin valores NULL, la función muestra NULL.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Tipo de datos mostrados

FLOAT64

COVAR_POP

COVAR_POP(
  X1, X2
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra la covarianza poblacional de un conjunto de pares de números. El primer número es la variable dependiente; el segundo es la variable independiente. El resultado que se muestra está entre -Inf y +Inf.

Se admiten todos los tipos numéricos. Si la entrada es NUMERIC o BIGNUMERIC, la agregación interna es estable con el resultado final convertido en un FLOAT64. De lo contrario, la entrada se convierte en un FLOAT64 antes de la agregación, lo que genera un resultado potencialmente inestable.

Esta función ignora los pares de entrada que contengan uno o más valores NULL. Si no hay ningún par de entrada sin valores NULL, esta función muestra NULL. Si hay un par de entrada sin valores NULL, esta función muestra 0.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Tipo de datos mostrados

FLOAT64

COVAR_SAMP

COVAR_SAMP(
  X1, X2
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

Descripción

Muestra la covarianza de ejemplo de un conjunto de pares de números. El primer número es la variable dependiente; el segundo es la variable independiente. El resultado que se muestra está entre -Inf y +Inf.

Se admiten todos los tipos numéricos. Si la entrada es NUMERIC o BIGNUMERIC, la agregación interna es estable con el resultado final convertido en un FLOAT64. De lo contrario, la entrada se convierte en un FLOAT64 antes de la agregación, lo que genera un resultado potencialmente inestable.

Esta función ignora los pares de entrada que contengan uno o más valores NULL. Si hay menos de dos pares de entrada sin valores NULL, la función muestra NULL.

Para obtener más información sobre los argumentos opcionales en esta función y cómo usarlos, consulta Llamadas a funciones agregadas.

Para obtener más información sobre la cláusula OVER y cómo usarla, consulta Llamadas a funciones analíticas.

Tipo de datos mostrados

FLOAT64

STDDEV_POP

STDDEV_POP(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_fram