Funciones de agregación

Una función de agregación es una función que resume las filas de un grupo en un solo valor. COUNT, MIN y MAX son ejemplos de funciones agregadas.

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

Cuando se usa junto con una cláusula GROUP BY, los grupos resumidos suelen tener al menos una fila. Cuando la SELECT asociada no tiene una cláusula GROUP BY o cuando ciertos modificadores de funciones de agregación filtran filas del grupo que se resumirá, es posible que la función necesite resumir un grupo vacío. En este caso, las funciones COUNT y COUNTIF muestran 0, mientras que todas las otras funciones de agregación muestran NULL.

En las siguientes secciones, se describen las funciones agregadas que admite SQL de Cloud Spanner.

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

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 IGNORE NULLS; las filas para las que expression es NULL no se tienen en cuenta y no se seleccionarán.

Tipos de argumentos admitidos

Cualquiera

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

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

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

Descripción

Muestra un ARRAY de valores expression.

Tipos de argumentos admitidos

Todos los tipos de datos excepto ARRAY.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. IGNORE NULLS o RESPECT NULLS: si se especifica IGNORE NULLS, los valores NULL se excluyen del resultado. Si se especifica RESPECT NULLS o si no se especifica ninguno, los valores NULL se incluyen en el resultado.
  3. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Orden del elemento de salida

El orden de los elementos en la salida no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

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

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

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 arreglos de entrada que no son NULL.

Tipos de argumentos admitidos

ARRAY

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Orden del elemento de salida

El orden de los elementos en la salida no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Tipos de datos mostrados

ARRAY

Muestra NULL si no hay filas de entrada o si expression se evalúa como NULL en todas las filas.

Ejemplos

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

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

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

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.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

  • FLOAT64

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

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

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

Tipos de argumentos admitidos

  • INT64

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

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([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

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

Tipos de argumentos admitidos

  • INT64

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

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 [HAVING {MAX | MIN} expression2])

Descripción

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

Tipos de argumentos admitidos

  • INT64

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

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(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descripción

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

Tipos de argumentos admitidos

expression puede ser cualquier tipo de datos.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

INT64

Ejemplos

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 COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

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.

Tipos de argumentos admitidos

BOOL

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

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

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

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.

Tipos de argumentos admitidos

BOOL

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

BOOL

Ejemplos

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

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.

Tipos de argumentos admitidos

BOOL

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

BOOL

Ejemplos

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

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.

Tipos de argumentos admitidos

Cualquier tipo de datos excepto: ARRAY STRUCT

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

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

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

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.

Tipos de argumentos admitidos

Cualquier tipo de datos excepto: ARRAY STRUCT

Cláusula opcional

HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega por un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

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

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

Descripción

Muestra un valor (ya sea STRING o BYTES) obtenido mediante la concatenación de valores no nulos.

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

Tipos de argumentos admitidos

STRING BYTES

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Orden del elemento de salida

El orden de los elementos en la salida no es determinista, lo que significa que podrías recibir un resultado diferente cada vez que uses esta función.

Tipos de datos mostrados

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

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

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.

Tipos de argumentos admitidos

Cualquier tipo de datos numéricos admitido.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. HAVING MAX o HAVING MIN: Restringe el conjunto de filas que la función agrega según un valor máximo o mínimo. Consulta Cláusulas HAVING MAX y HAVING MIN para obtener más detalles.

Tipos de datos mostrados

  • Muestra INT64 si la entrada es un número entero.
  • Muestra FLOAT64 si la entrada es un valor de punto flotante.

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 SUM(x) AS sum
FROM UNNEST([]) AS x;

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

Cláusulas comunes

Cláusulas HAVING MAX y HAVING MIN

La mayoría de las funciones de agregación admiten dos cláusulas opcionales llamadas HAVING MAX y HAVING MIN, que restringen el conjunto de filas que una función agrega a las filas que tienen un valor máximo o mínimo en una columna en particular. La sintaxis suele verse así:

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX restringe el conjunto de filas que la función agrega a las que tienen un valor de expression2 igual que el valor máximo de expression2 dentro del grupo. El valor máximo es igual que el resultado de MAX(expression2).
  • HAVING MIN restringe el conjunto de filas que la función agrega a las que tienen un valor de expression2 igual que el valor mínimo de expression2 dentro del grupo. El valor mínimo es igual que el resultado de MIN(expression2).

Estas cláusulas ignoran los valores NULL cuando se calcula el valor máximo o el mínimo, a menos que expression2 se evalúe como NULL para todas las filas.

Estas cláusulas no admiten los siguientes tipos de datos: ARRAY STRUCT

Ejemplo

En este ejemplo, se muestra el promedio de precipitaciones del año más reciente, 2001.

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

Primero, la consulta obtiene las filas con el valor máximo en la columna year. Hay dos filas:

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

Por último, la consulta promedia los valores de la columna inches (9 y 1) con este resultado:

+---------+
| average |
+---------+
| 5       |
+---------+