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 de agregación.
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:
DISTINCT
: cada valor distinto deexpression
se agrega solo una vez en el resultado.IGNORE NULLS
oRESPECT NULLS
: si se especificaIGNORE NULLS
, los valoresNULL
se excluyen del resultado. Si se especificaRESPECT NULLS
, los valoresNULL
se incluyen en el resultado. Si no se especifica ninguno de los dos valores, los valoresNULL
se incluyen en el resultado.HAVING MAX
oHAVING 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:
DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.HAVING MAX
oHAVING 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
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | 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:
DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.HAVING MAX
oHAVING 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:
DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.HAVING MAX
oHAVING 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:
DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.HAVING MAX
oHAVING 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
- Muestra la cantidad de filas en la entrada.
- Muestra la cantidad de filas con
expression
evaluado como cualquier valor distinto deNULL
.
Tipos de argumentos admitidos
expression
puede ser cualquier tipo de datos. Si DISTINCT
está presente, expression
solo puede ser un tipo de datos agrupable.
Cláusulas opcionales
Las cláusulas se aplican en el siguiente orden:
DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.HAVING MAX
oHAVING 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:
DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.HAVING MAX
oHAVING 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:
DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.HAVING MAX
oHAVING 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:
DISTINCT
: Cada valor distinto deexpression
se agrega solo una vez en el resultado.HAVING MAX
oHAVING 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
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | INT64 | NUMERIC | FLOAT64 |
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 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 deexpression2
igual que el valor máximo deexpression2
dentro del grupo. El valor máximo es igual que el resultado deMAX(expression2)
.HAVING MIN
restringe el conjunto de filas que la función agrega a las que tienen un valor deexpression2
igual que el valor mínimo deexpression2
dentro del grupo. El valor mínimo es igual que el resultado deMIN(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
.
Existen dos:
+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9 |
| 2001 | winter | 1 |
+------+--------+--------+
Por último, la consulta promedia los valores en la columna inches
(9 y 1) con este resultado:
+---------+
| average |
+---------+
| 5 |
+---------+