21st Jul 2022 Lectura de 13 minutos

¿Qué son las funciones agregadas en SQL y cómo se utilizan?

Los datos son su fuente de conocimiento. Y gracias a las funciones de agregación de SQL, puede extraer el conocimiento preciso que necesita de sus datos de forma eficiente. Lea a continuación para saber más.

Las principales funciones de agregación de SQL son las siguientes

COUNT(column_name | *) devuelve el número de filas de una tabla.
SUM(column_name) devuelve la suma de los valores de una columna numérica.
AVG(column_name) devuelve el valor medio de una columna numérica.
MIN(column_name) devuelve el valor mínimo de una columna seleccionada.
MAX(column_name) devuelve el valor máximo de una columna seleccionada.

En este artículo, hablaremos de cada uno de ellos con ejemplos. Descubrirá lo que ocurre con NULLs y los duplicados cuando se someten a las funciones de agregación. Además, explicaremos *, la palabra clave DISTINCT y la sentencia CASE.

Comencemos.

Cómo funcionan las funciones de agregación

Las funciones de agregación de SQL acumulan datos de varias filas en una única fila de resumen. El valor acumulado se basa en los valores de la columna pasada como argumento. Podemos agrupar las filas utilizando una cláusula GROUP BY y filtrarlas aún más utilizando una cláusula HAVING.

Un ejemplo estándar es encontrar el número de filas de una tabla. En este caso, agregamos todas las filas de la tabla Libros en una sola fila.

SELECT * FROM Books;

Id	Author	Title	Price
234	Anthony Molinaro	SQL Cookbook	20.00
235	Alan Beaulieu	Learning SQL	25.00
236	Donald Knuth	Things a Computer Scientist Rarely Talks About	25.00
237	Donald Knuth	The Art of Computer Programming	27.00

SELECT COUNT(*) AS NumberOfBooks
FROM Books;

NumberOfBooks
4

Fácil, ¿verdad? Usando COUNT(*), puede contar el número de todas las filas.

También puedes echar un vistazo a nuestros otros artículos sobre las funciones de agregación de SQL aquí y aquí.

El argumento * de las funciones agregadas

Si me preguntas qué significa * en SQL, mi respuesta es que significa todos. Se utiliza comúnmente con una sentencia SELECT cuando se consultan todas las columnas de una tabla determinada. Tomemos, por ejemplo, SELECT * FROM Books, como en el caso anterior.

El argumento * también puede utilizarse con la función de agregación COUNT(). Ésta cuenta todas las filas de una tabla. Tome, por ejemplo, SELECT COUNT(*) as NumberOfBooks FROM Books, como arriba.

Puede agrupar los datos por alguna columna o incluso por muchas columnas. Observe el siguiente ejemplo:

SELECT Author, COUNT(*) AS NumberOfBooks
FROM Books
GROUP BY Author;

Author	NumberOfBooks
Anthony Molinaro	1
Alan Beaulieu	1
Donald Knuth	2

Esto cuenta el número de libros por autor.

El argumento * sólo se aplica a la función de agregación COUNT(). Para otras funciones de agregación, se requiere una columna específica, o una combinación de columnas, como argumento.

La palabra clave DISTINCT

La palabra clave DISTINCT indica a la base de datos que no queremos considerar los valores duplicados.

Por ejemplo, COUNT(Author) nos da el número de todos los autores presentes en una tabla. Pero si el mismo autor aparece muchas veces en una columna, el autor se cuenta muchas veces. Echa un vistazo a esto:

SELECT COUNT(Author) AS NumberOfAuthors
FROM books;

NumberOfAuthors
4

¿Ves? Cuenta cuatro autores porque Donald Knuth se cuenta dos veces.

¿Qué ocurre si añadimos la palabra clave DISTINCT?

SELECT COUNT(DISTINCT Author) AS NumberOfAuthors
FROM Books;

NumberOfAuthors
3

Esta vez, utilizamos la palabra clave DISTINCT. Ahora, Donald Knuth se cuenta sólo una vez. Cuando se utiliza la palabra clave DISTINCT, COUNT() debe tomar una columna específica como argumento. Devuelve el número de valores únicos almacenados en esa columna.

Del mismo modo, podemos utilizar la palabra clave DISTINCT con argumentos de las funciones agregadas SUM() y AVG(). A continuación, comparamos los resultados de la ejecución de la función SUM() con y sin la palabra clave DISTINCT.

SELECT SUM(DISTINCT Price)
AS TotalDistinctPrice
FROM Books;

TotalDistinctPrice
72

	SELECT SUM(Price)
	AS TotalPrice
	FROM Books;

TotalPrice
97

Como puede ver, cuando se utiliza la palabra clave DISTINCT, los libros con el mismo precio se consideran sólo una vez en SUM(). En este caso, tiene más sentido utilizar la función SUM() sin la palabra clave DISTINCT.

Del mismo modo, al calcular un precio medio, es mejor no utilizar la palabra clave DISTINCT; debemos considerar cada precio tantas veces como aparezca en la columna. Vea lo que ocurre con AVG():

SELECT AVG(DISTINCT Price)
AS TotalDistinctAvg
FROM Books;

TotalDistinctAvg
24

	SELECT AVG(Price)
	AS TotalAvg
	FROM Books;

TotalAvg
24.25

Para las funciones agregadas MIN() y MAX(), la palabra clave DISTINCT no supone ninguna diferencia. Pero tampoco provoca ningún error. ¿Por qué? Consideremos un conjunto de números {1, 2, 2, 3, 4, 5, 5, 6}. Sus valores máximo y mínimo son 6 y 1, respectivamente. Con la palabra clave DISTINCT, este conjunto se convierte en {1, 2, 3, 4, 5, 6}, por lo que los valores máximo y mínimo siguen siendo los mismos.

La sentencia CASE

La sentencia CASE categoriza y filtra los datos. Es como un guardián para el argumento de una función agregada, decidiendo qué valores dejar entrar. Veamos algunos ejemplos para ilustrar este concepto.

En la siguiente consulta, utilizamos una sentencia CASE como argumento para la función COUNT(). Cuenta sólo los libros cuyo precio es superior a 20,00 dólares.

SELECT COUNT(CASE WHEN Price > 20 THEN Price END)
AS NumberOfExpensiveBooks
FROM Books;

NumberOfExpensiveBooks
3

Una sentencia CASE también puede utilizarse como argumento para otras funciones agregadas. En la consulta de abajo, sumamos los precios de los libros que cuestan exactamente $25.00. La sentencia CASE dentro de la función SUM() permite que sólo se incluyan en la suma los libros con un precio de $25.00.

SELECT SUM(CASE WHEN Price = 25 THEN Price END)
AS BooksSum
FROM Books;

BooksSum
50

Ahora, promediamos los precios de los libros que cuestan menos de $26.00 en la siguiente consulta. La sentencia CASE dentro de la función AVG() permite que sólo los libros con un precio inferior a 26,00 dólares se incluyan en la media. La función AVG() es un argumento de la función ROUND(), por lo que la salida de la función AVG() se redondea a dos decimales.

SELECT ROUND(AVG(CASE WHEN Price < 26 THEN Price END), 2)
AS BooksAvg
FROM Books;

BooksAvg
23.33

En la siguiente consulta, encontramos el precio mínimo de los libros sobre SQL que cuestan menos de $26.00. La sentencia CASE dentro de la función MIN() permite que sólo se incluyan en el conjunto los libros con un precio inferior a 26,00 dólares.

SELECT MIN(CASE WHEN Price < 26 AND Title LIKE '%SQL%' THEN Price END)
AS BooksMin
FROM Books;

BooksMin
20

A continuación, encontramos el precio máximo de los libros que cuestan menos de 25,00 dólares. La sentencia CASE dentro de la función MAX() permite que sólo los libros con un precio inferior a $25.00 sean incluidos en el conjunto.

SELECT MAX(CASE WHEN Price < 25 THEN Price END)
AS BooksMax
FROM Books;

BooksMax
20

Estoy seguro de que ya puede imaginarse el resultado de estas consultas.

¿Qué ocurre con los NULL?

La respuesta es sencilla. Las funciones agregadas de SQL ignoran los valores de NULL.

Consideremos una tabla Books actualizada. Esta vez tenemos un precio de NULL.

SELECT * FROM Books;

Id	Author	Title	Price
234	Anthony Molinaro	SQL Cookbook	20.00
235	Alan Beaulieu	Learning SQL	25.00
236	Donald Knuth	Things a Computer Scientist Rarely Talks About	25.00
237	Donald Knuth	The Art of Computer Programming	NULL

COUNT(Price) ahora devuelve 3, no 4, y SUM(Price) devuelve 70.00. NULLs se ignora en ambos casos.

¡También puede utilizar funciones de agregación con JOINs! Eche un vistazo a nuestro artículo sobre el uso de funciones agregadas SQL con JO INs para aprender más.

El papel de HAVING y GROUP BY con las funciones agregadas

Es sencillo entender lo que hace una cláusula HAVING si está familiarizado con la cláusula WHERE. Una cláusula HAVING filtra los valores de salida de las funciones agregadas. Una cláusula GROUP BY le permite dividir sus datos en grupos y encontrar un valor agregado para cada grupo.

Veamos un ejemplo.

SELECT Author, AVG(Price) AS AvgBookPrice
FROM Books
GROUP BY Author
HAVING AVG(Price) > 20;

Author	AvgBookPrice
Alan Beaulieu	25
Donald Knuth	26

Agrupamos los datos por la columna Autor utilizando una cláusula GROUP BY. Luego, restringimos los valores de AVG(Price) para que sean mayores a 20 utilizando una cláusula HAVING.

Podemos intentar utilizar las cláusulas WHERE y HAVING juntas para ver la diferencia entre ellas.

SELECT Author, AVG(Price) AS AvgBookPrice
FROM Books
WHERE Author LIKE 'A%'
GROUP BY Author
HAVING AVG(Price) > 20;

Author	AvgBookPrice
Alan Beaulieu	25

La cláusula HAVING se confunde a menudo con la cláusula WHERE. Recuerda que no puedes usar funciones agregadas en una cláusula WHERE. Asegúrate de practicar lo suficiente y revisa nuestro Tutorial de HAVING en SQL. También, revise nuestro artículo sobre el uso de GROUP BY en SQL para obtener aún más información sobre la cláusula GROUP BY.

¿Funciones agregadas en SQL o funciones de ventana en SQL? ¿O tal vez ambas? Consulta nuestro artículo sobre las similitudes y diferencias entre ambas.

¡Practiquemos!

No basta con leer. SQL requiere una gran cantidad de práctica. ¡Comencemos con algunos ejemplos aquí para que puedas continuar por tu cuenta!

Antes de pasar a los ejemplos, asegúrate de que tienes claros todos los Principios básicos de SQL y Funciones estándar de SQL¡!

Ejemplo de base de datos

Abajo está el plano de la base de datos. Usaremos esta base de datos en nuestros ejemplos a continuación.

Analicemos el plano de la base de datos, empezando por la izquierda.

La tabla Customers almacena los datos de los clientes. Su clave primaria es la columna CustomerId. Las columnas Customers y Orders están vinculadas mediante la columna CustomerId. La tabla Pedidos almacena la fecha del pedido y el ID del cliente que realizó el pedido. Su clave primaria es la columna OrderId. El enlace entre las tablas Customers y Orders define la relación entre ellas. Un cliente puede tener cero o más pedidos, pero un pedido sólo puede asignarse a un cliente.

Las tablas Orders y OrderDetails están vinculadas mediante la columna OrderId. Las tablas Products y OrderDetails están vinculadas mediante la columna ProductId. La clave primaria de la tabla OrderDetails está formada por las columnas OrderId y ProductId.

Un pedido puede constar de uno o varios productos. Por lo tanto, una fila de la tabla Orders puede estar relacionada con una o más filas de la tabla OrderDetails tabla. Además, un producto puede estar en cero o más pedidos. Por lo tanto, una fila de la tabla Products tabla puede estar relacionada con cero o más filas de la OrderDetails tabla.

Ahora, vamos a insertar algunos datos en nuestras tablas.

La tabla Customers tabla:

CustomerId	FirstName	LastName	Street	HouseNo	City	Country	Email	PhoneNo
1	Rachel	Hartley	Bicetown Road	602	New York	USA	rh@email.com	0123456789
2	Caitlyn	Ray	Friedrichstrasse	44	Berlin	Germany	cr@email.com	0987654321
3	Andrew	Duncan	Lairg Road	38	London	NULL	ad@email.com	0567432678
4	Taylor	Jenkins	Park Row	106	Edinburgh	UK	NULL	0876345123
5	Ben	Holland	Williams Avenue	252	Los Angeles	USA	bh@email.com	0987456789

La tabla Orders tabla (la columna de la fecha está en el formato DD-MM-AAAA):

OrderId	CustomerId	OrderDate
45	1	10-10-2021
46	2	11-12-2020
47	3	05-05-2021
48	4	09-08-2021
49	5	NULL
50	1	02-06-2021
51	2	07-07-2021

El OrderDetails tabla:

OrderId	ProductId	Quantity
45	100	2
45	101	3
46	100	1
47	102	4
48	101	3
48	103	5
49	104	2
50	100	3
51	101	1

La Products tabla:

ProductId	Name	UnitPrice	AvailableInStock
100	Keyboard	30.00	300
101	USB Drive	20.00	450
102	Mouse	20.00	500
103	Screen	100.00	450
104	Laptop	600.00	200

Ahora estamos listos para comenzar con los ejemplos.

Ejemplos con COUNT()

Empezamos con la Customers tabla. Vamos a averiguar cuántos clientes hay por país.

SELECT Country, COUNT(CustomerId) AS NumberOfCustomers
FROM Customers
GROUP BY Country;

Country	NumberOfCustomers
NULL	1
Germany	1
UK	1
USA	2

Hemos seleccionado todos los valores distintos de la columna Country, incluyendo el valor NULL. La columna NumberOfCustomers almacena el número de clientes para cada valor de la columna Country.

¿Qué ocurre si utilizamos la columna Email como argumento de la función COUNT()?

SELECT Country, COUNT(Email) AS NumberOfCustomers
FROM Customers
GROUP BY Country;

Country	NumberOfCustomers
NULL	1
Germany	1
UK	0
USA	2

El valor de la columna NumberOfCustomers para el país "UK" se convierte en cero. Esto se debe a que la columna Email de la tabla Customers tabla es NULL para este cliente.

Ahora, veamos un ejemplo que utiliza las cláusulas GROUP BY y HAVING.

SELECT Country, COUNT(Email) AS NumberOfCustomersWithEmail
FROM Customers
WHERE Country IS NOT NULL
GROUP BY Country
HAVING COUNT(Email) > 1;

Country	NumberOfCustomersWithEmail
USA	2

Como antes, seleccionamos los valores de la columna País y obtenemos el recuento de clientes con correos electrónicos por país. En la cláusula WHERE, indicamos que no consideramos los valores de NULL para la columna Country. A continuación, agrupamos nuestros datos por Country. Por último, restringimos los valores de la columna NumberOfCustomersWithEmail para que sean mayores que 1 con una cláusula HAVING.

Ejemplos con SUM()

Vamos a comprobar cuánto valen todos los productos disponibles.

SELECT SUM(UnitPrice * AvailableInStock) AS AllProductsValue
FROM Products;

AllProductsValue
193000

Aquí, la función de agregación SUM() crea un valor de UnitPrice * AvailableInStock para cada fila y luego suma todos esos valores.

Digamos que cada pedido de valor superior a 100,00 dólares tiene derecho a un descuento. Queremos saber qué pedidos tienen derecho al descuento.

SELECT OrderId,
       CASE WHEN OrderValue > 100 THEN 1 ELSE 0 END
       AS QualifiesForDiscount
FROM
     ( SELECT aod.OrderId AS OrderId,
              SUM(aod.Quantity * ap.UnitPrice) AS OrderValue
       FROM Products ap
       JOIN OrderDetails aod
       ON ap.ProductId = aod.ProductId
       GROUP BY aod.OrderId );

OrderId	QualifiesForDiscount
45	1
46	0
47	0
48	1
49	1
50	0
51	0

La consulta interna selecciona todos los valores de la columna OrderId y calcula el valor de cada pedido utilizando la función SUM(). La consulta externa utiliza una sentencia CASE para decidir si el pedido tiene derecho a un descuento (1) o no (0).

Ahora, digamos que definimos que todos los productos con un precio unitario superior a 90,00 dólares son caros. Averigüemos el valor total de todos los productos caros en stock.

SELECT
    SUM(CASE WHEN UnitPrice > 90 THEN UnitPrice * AvailableInStock END)
    AS ExpensiveProductsValue
FROM Products;

ExpensiveProductsValue
165000

Hemos pasado una sentencia CASE como argumento a la función SUM(). Este argumento asegura que sólo se consideren las filas con el valor UnitPrice mayor a $90.00. Aparte de esto, este ejemplo es bastante similar al primero de esta sección.

Ejemplos con AVG()

Vamos a comprobar cuál es el precio medio de un pedido.

SELECT AVG(OrderValue) AS AvgOrderValue
FROM
     ( SELECT aod.OrderId AS OrderId,
              SUM(aod.Quantity * ap.UnitPrice) AS OrderValue
       FROM Products ap
       JOIN OrderDetails aod
       ON ap.ProductId = aod.ProductId
       GROUP BY aod.OrderId );

AvgOrderValue
300

La consulta interna obtiene el valor total del pedido para cada orden. La consulta externa calcula el valor medio de un pedido.

También podemos averiguar la cantidad media pedida por producto.

SELECT ROUND(AVG(Quantity), 2) AS AvgOrderQuantity
FROM OrderDetails;

AvgOrderQuantity
2.67

Por término medio, nuestros clientes compran entre 2 y 3 artículos de cualquier producto en un pedido.

Veamos qué cambia cuando consideramos sólo los valores únicos de la columna Quantity.

SELECT ROUND(AVG(DISTINCT Quantity), 2) AS AvgOrderQuantity
FROM OrderDetails;

AvgOrderQuantity
3

El valor de salida cambia, porque ya no consideramos los valores duplicados que aparecen en la columna Quantity de la OrderDetails tabla.

Ejemplos con MAX() y MIN()

Por último, pero no menos importante. Las funciones MAX() y MIN() son bastante sencillas. Vamos a averiguar los pedidos más antiguos y los más recientes.

SELECT MIN(OrderDate) AS EarliestOrder,
       MAX(OrderDate) AS LatestOrder
FROM Orders;

EarliestOrder	LatestOrder
11-12-2020	10-10-2021

La función MIN() devuelve la fecha más antigua, y la función MAX() devuelve la fecha más reciente.

También podemos identificar los productos más baratos y los más caros. Puedes buscar en la Products tabla para hacerlo.

SELECT MIN(UnitPrice) AS CheapestProductPrice,
       MAX(UnitPrice) AS MostExpensiveProductPrice
FROM Products;

CheapestProductPrice	MostExpensiveProductPrice
20	600

Averigüemos cuántos pedidos hay por cliente, y luego obtengamos el número mínimo y máximo de pedidos por cliente.

SELECT MIN(NumberOfOrders) AS MinNumberOfOrders,
       MAX(NumberOfOrders) AS MaxNumberOfOrders
FROM
     ( SELECT CustomerId,
              COUNT(OrderID) AS NumberOfOrders
       FROM Orders
       GROUP BY CustomerId );

MinNumberOfOrders	MaxNumberOfOrders
1	2

La consulta interna selecciona la columna CustomerId y el número total de pedidos realizados por un determinado cliente. La función COUNT(OrderId) cuenta el número de pedidos por cliente. A continuación, agrupamos nuestros datos por la columna CustomerId utilizando una cláusula GROUP BY. En este paso, la función COUNT(OrderId) cuenta los pedidos por cliente y no para todos los clientes juntos. La consulta externa selecciona los valores mínimo y máximo de la columna NumberOfOrders de la consulta interna.

Las funciones agregadas de SQL como herramienta esencial en la ciencia de los datos

Mediante el uso de funciones agregadas, podemos encontrar fácilmente respuestas a preguntas específicas, como cuántos clientes hay o cuál es el precio medio de un pedido. Las funciones de agregación de SQL nos permiten analizar los datos de forma eficaz.

Estas funciones básicas de agregación de SQL son muy útiles en la ciencia de los datos. Con ellas, puedes organizar los datos como quieras y extraer la información que necesites.

Hemos revisado muchos ejemplos con las funciones agregadas COUNT(), SUM(), AVG(), MIN(), y MAX(). También hemos cubierto ejemplos de *, la palabra clave DISTINCT y la sentencia CASE como argumentos de las funciones de agregación. Ahora, está listo para crear su base de datos y practicar un poco más.

Tags: