21st Jul 2022 13 minutos de leitura

O que são funções agregadas em SQL, e como posso utilizá-las?

Índice

Como funcionam as funções agregadas
O Papel do HAVING e do GROUP BY com Funções Agregadas
Vamos praticar!
Funções Agregadas SQL como Ferramenta Essencial na Ciência dos Dados

Os dados são a sua fonte de conhecimento. E graças às funções SQL agregadas, você pode extrair de seus dados o conhecimento preciso que precisa de forma eficiente. Leia mais adiante para saber mais.

As funções principais de agregação SQL são as seguintes:

COUNT(column_name | *) retorna o número de filas em uma tabela.
SUM(column_name) retorna a soma dos valores de uma coluna numérica.
AVG(column_name) retorna o valor médio de uma coluna numérica.
MIN(column_name) retorna o valor mínimo de uma coluna selecionada.
MAX(column_name) retorna o valor máximo de uma coluna selecionada.

Neste artigo, discutiremos cada um deles com exemplos. Você descobrirá o que acontece com NULLs e duplica quando submetido a funções agregadas. Além disso, explicaremos *, a palavra-chave DISTINCT, e a declaração CASE.

Vamos começar!

Como funcionam as funções agregadas

As funções agregadas SQL acumulam dados de várias linhas em uma única linha de resumo. O valor acumulado é baseado nos valores da coluna passada como argumento. Podemos agrupar as linhas usando uma cláusula GROUP BY e filtrá-las ainda mais usando uma cláusula HAVING.

Um exemplo padrão é encontrar o número de linhas em uma tabela. Aqui, agregamos todas as linhas da tabela Livros em uma linha.

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, certo? Usando COUNT(*), você pode contar o número de todas as linhas.

Você também pode dar uma olhada em nossos outros artigos sobre funções agregadas SQL aqui e aqui.

O * Argumento para as funções agregadas

Se você me perguntar o que * significa em SQL, minha resposta é que significa para todos. Ela é comumente usada com uma declaração SELECT ao consultar todas as colunas de uma determinada tabela. Veja, por exemplo, SELECT * FROM Books, como acima.

O argumento * também pode ser usado com a função agregada COUNT(). Em seguida, ele conta todas as linhas de uma tabela. Tomemos, por exemplo, SELECT COUNT(*) as NumberOfBooks FROM Books, como acima.

Você pode agrupar os dados por alguma coluna ou mesmo por muitas colunas. Dê uma olhada no seguinte exemplo:

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

Author	NumberOfBooks
Anthony Molinaro	1
Alan Beaulieu	1
Donald Knuth	2

Isto conta o número de livros por autor.

O argumento * se aplica apenas à função agregada COUNT(). Para outras funções agregadas, uma coluna específica, ou uma combinação de colunas, é necessária como argumento.

A palavra-chave DISTINCT

A palavra-chave DISTINCT diz ao banco de dados que não queremos considerar valores duplicados.

Por exemplo, COUNT(Author) nos dá o número de todos os autores presentes em uma tabela. Mas se o mesmo autor aparece muitas vezes em uma coluna, o autor é contado muitas vezes. Dê uma olhada nisto:

SELECT COUNT(Author) AS NumberOfAuthors
FROM books;

NumberOfAuthors
4

Veja? Conta-se quatro autores porque Donald Knuth é contado duas vezes.

O que acontece se adicionarmos a palavra-chave DISTINCT?

SELECT COUNT(DISTINCT Author) AS NumberOfAuthors
FROM Books;

NumberOfAuthors
3

Desta vez, usamos a palavra-chave DISTINCT. Agora, Donald Knuth é contado apenas uma vez. Ao usar a palavra-chave DISTINCT, COUNT() deve tomar uma coluna específica como argumento. Ela retorna o número de valores únicos armazenados nessa coluna.

Da mesma forma, podemos usar a palavra-chave DISTINCT com argumentos das funções agregadas de SUM() e AVG(). Abaixo, comparamos os resultados da execução da função SUM() com e sem a palavra-chave DISTINCT.

SELECT SUM(DISTINCT Price)
AS TotalDistinctPrice
FROM Books;

TotalDistinctPrice
72

	SELECT SUM(Price)
	AS TotalPrice
	FROM Books;

TotalPrice
97

Como você pode ver, quando você usa uma palavra-chave DISTINCT, os livros com o mesmo preço são considerados apenas uma vez na SUM(). Neste caso, faz mais sentido usar a função SUM() sem a palavra-chave DISTINCT.

Da mesma forma, ao calcular um preço médio, é melhor não usar a palavra-chave DISTINCT; devemos considerar cada preço tantas vezes quanto aparece na coluna. Veja o que acontece com AVG():

SELECT AVG(DISTINCT Price)
AS TotalDistinctAvg
FROM Books;

TotalDistinctAvg
24

	SELECT AVG(Price)
	AS TotalAvg
	FROM Books;

TotalAvg
24.25

Para as funções agregadas de MIN() e MAX(), a palavra-chave DISTINCT não faz diferença. Mas também não causa nenhum erro. Por quê? Vamos considerar um conjunto de números {1, 2, 2, 3, 4, 5, 5, 6}. Seus valores máximo e mínimo são 6 e 1, respectivamente. Com a palavra-chave DISTINCT, este conjunto se torna {1, 2, 3, 4, 5, 6}, portanto os valores máximo e mínimo ainda são os mesmos.

A declaração CASE

A declaração CASE categoriza e filtra os dados. É como um gatekeeper para o argumento de uma função agregada, decidindo quais os valores a serem deixados entrar. Vejamos alguns exemplos para ilustrar este conceito.

Na consulta seguinte, usamos uma declaração CASE como argumento para a função COUNT(). Ela conta apenas os livros cujo preço é superior a US$ 20,00.

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

NumberOfExpensiveBooks
3

Uma declaração CASE também pode ser usada como argumento para outras funções agregadas. Na consulta abaixo, somamos os preços dos livros que custam exatamente $25,00. A declaração CASE dentro da função SUM() permite incluir na soma somente os livros cujo preço é de $25,00.

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

BooksSum
50

Agora, calculamos a média dos preços dos livros que custam menos de $26,00 na próxima consulta. A declaração CASE dentro da função AVG() permite incluir na média somente livros com preço abaixo de $26,00. A função AVG() é um argumento para a função ROUND(), portanto a saída da função AVG() é arredondada para duas casas decimais.

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

BooksAvg
23.33

Na próxima consulta, encontramos o preço mínimo dos livros sobre SQL que custam menos de $26,00. A declaração CASE dentro da função MIN() permite que somente livros com um preço abaixo de $26,00 sejam incluídos no conjunto.

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

BooksMin
20

A seguir, encontramos o preço máximo dos livros que custam menos de $25,00. A declaração CASE dentro da função MAX() permite que somente livros com preço abaixo de $25,00 sejam incluídos no conjunto.

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

BooksMax
20

Tenho certeza de que você já pode descobrir o resultado destas consultas!

O que acontece com os NULLs?

A resposta é simples. As funções agregadas SQL ignoram os valores NULL.

Vamos considerar uma atualização Books mesa. Desta vez, temos um preço 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) agora retorna 3, não 4, e SUM(Price) retorna 70,00. NULLs são ignorados em ambos os casos.

Você também pode usar as funções agregadas com JOINs! Dê uma olhada em nosso artigo sobre o uso de funções agregadas SQL com JOINs para saber mais.

O Papel do HAVING e do GROUP BY com Funções Agregadas

É fácil entender o que uma cláusula HAVING faz se você estiver familiarizado com a cláusula WHERE. Uma cláusula HAVING filtra os valores de saída das funções agregadas. Uma cláusula GROUP BY permite dividir seus dados em grupos e encontrar um valor agregado para cada grupo.

Vejamos um exemplo.

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

Author	AvgBookPrice
Alan Beaulieu	25
Donald Knuth	26

Nós agrupamos os dados pela coluna Autor usando uma cláusula GROUP BY. Em seguida, restringimos os valores de AVG(Price) a serem maiores que 20 usando uma cláusula HAVING.

Podemos tentar usar as cláusulas WHERE e HAVING em conjunto para ver a diferença entre elas.

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

Author	AvgBookPrice
Alan Beaulieu	25

A cláusula HAVING é freqüentemente confundida com a cláusula WHERE. Lembre-se que você não pode usar funções agregadas em uma cláusula WHERE. Certifique-se de obter prática suficiente e verifique nosso SQL HAVING Tutorial. Confira também nosso artigo sobre o uso de GROUP BY em SQL para obter ainda mais informações sobre a cláusula GROUP BY.

Funções agregadas SQL ou funções de janela SQL? Ou talvez ambas? Confira nosso artigo sobre as semelhanças e diferenças entre os dois!

Vamos praticar!

Não é suficiente apenas ler. SQL requer uma grande dose de prática. Vamos começar com alguns exemplos aqui para que você possa continuar por conta própria!

Antes de saltarmos para os exemplos, certifique-se de que você está claro sobre todos Fundamentos de SQL e Funções Comuns em SQL!

Exemplo de banco de dados

Abaixo está o projeto do banco de dados. Usaremos este banco de dados em nossos exemplos abaixo.

Vamos analisar o projeto do banco de dados, começando pela esquerda.

O Customers tabela armazena dados sobre os clientes. Sua chave primária é a coluna CustomerId. A coluna Customers e Orders As tabelas estão vinculadas utilizando a coluna CustomerId. A tabela de Pedidos armazena a data do pedido e a identificação do cliente que fez o pedido. Sua chave primária é a coluna OrderId. A ligação entre a Customers e Orders tabelas define a relação entre elas. Um cliente pode ter zero ou mais pedidos, mas um pedido pode ser atribuído a apenas um cliente.

O Orders e OrderDetails As tabelas estão vinculadas utilizando a coluna OrderId. A coluna Products e OrderDetails As tabelas estão vinculadas utilizando a coluna ProductId. A chave primária da OrderDetails A tabela é composta pelas colunas OrderId e ProductId.

Um pedido pode consistir em um ou mais produtos. Portanto, uma fila da tabela Orders tabela pode ser relacionada a uma ou mais fileiras do OrderDetails mesa. Além disso, um produto pode estar em zero ou mais pedidos. Portanto, uma fileira da Products tabela pode ser relacionada a zero ou mais filas do OrderDetails mesa.

Agora, vamos inserir alguns dados em nossas tabelas.

A Customers mesa:

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

A Orders tabela (a coluna de datas está no formato DD-MM-YYYY):

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

O OrderDetails mesa:

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

A Products mesa:

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

Agora estamos prontos para começar com os exemplos.

Exemplos com COUNT()

Começamos com a Customers mesa. Vamos descobrir quantos clientes há por país.

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

Country	NumberOfCustomers
NULL	1
Germany	1
UK	1
USA	2

Selecionamos todos os valores distintos a partir da coluna Country, incluindo o valor NULL. A coluna NumberOfCustomers armazena o número de clientes para cada valor da coluna Country.

O que acontece se usarmos a coluna Email como argumento para a função COUNT()?

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

Country	NumberOfCustomers
NULL	1
Germany	1
UK	0
USA	2

O valor da coluna NumberOfCustomers para o país "UK" se torna zero. Isto porque a coluna Email na Customers A tabela é NULL para este cliente.

Agora, vejamos um exemplo que usa as cláusulas GROUP BY e 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, selecionamos os valores da coluna País e obtemos a contagem de clientes com e-mails por país. Na cláusula WHERE, afirmamos que não consideramos os valores NULL para a coluna Country. Em seguida, agrupamos nossos dados por Country. Finalmente, restringimos os valores da coluna NumberOfCustomersWithEmail a serem maiores que 1 com uma cláusula HAVING.

Exemplos com SUM()

Vamos verificar quanto valem todos os produtos disponíveis.

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

AllProductsValue
193000

Aqui, a função agregada SUM() cria um valor de UnitPrice * AvailableInStock para cada linha e depois soma todos esses valores.

Digamos que cada pedido de valor superior a US$ 100,00 se qualifica para um desconto. Queremos saber quais pedidos se qualificam para o desconto.

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

A consulta interna seleciona todos os valores da coluna OrderId e calcula o valor de cada pedido usando a função SUM(). A consulta externa usa uma declaração CASE para decidir se o pedido se qualifica para um desconto (1) ou não (0).

Agora, digamos que definimos todos os produtos com um preço unitário superior a US$ 90,00 para serem caros. Vamos descobrir o valor total de todos os produtos caros em estoque.

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

ExpensiveProductsValue
165000

Passamos uma declaração CASE como argumento para a função SUM(). Este argumento garante que somente as linhas com o valor UnitPrice superior a $90,00 sejam consideradas. Fora isso, este exemplo é bastante similar ao primeiro desta seção.

Exemplos com AVG()

Vamos verificar qual é o preço médio de um 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

A consulta interna gera o valor total do pedido para cada pedido. A consulta externa calcula o valor médio de um pedido.

Também podemos descobrir a quantidade média encomendada por produto.

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

AvgOrderQuantity
2.67

Em média, nossos clientes compram entre 2 e 3 itens de um determinado produto em um pedido.

Vamos ver o que muda quando consideramos apenas os valores únicos da coluna Quantity.

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

AvgOrderQuantity
3

O valor de saída muda, porque não consideramos mais valores duplicados que aparecem na coluna Quantity do OrderDetails mesa.

Exemplos com MAX() e MIN()

Por último, mas não menos importante! As funções MAX() e MIN() são bastante simples. Vamos descobrir as encomendas mais antigas e as mais recentes.

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

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

A função MIN() retorna a data mais antiga, e a função MAX() retorna a data mais recente.

Também podemos identificar os produtos mais baratos e os mais caros. Você pode pesquisar a Products tabela para fazer isso.

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

CheapestProductPrice	MostExpensiveProductPrice
20	600

Vamos descobrir quantos pedidos existem por cliente, depois obtenhamos o número mínimo e 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

A consulta interna seleciona a coluna CustomerId e o número total de pedidos feitos por um determinado cliente. A função COUNT(OrderId) conta o número de pedidos por cliente. A seguir, agrupamos nossos dados pela coluna CustomerId usando uma cláusula GROUP BY. Nesta etapa, a função COUNT(OrderId) conta os pedidos por cliente e não para todos os clientes em conjunto. A consulta externa seleciona os valores mínimo e máximo da coluna NumberOfOrders a partir da consulta interna.

Funções Agregadas SQL como Ferramenta Essencial na Ciência dos Dados

Usando funções agregadas, podemos facilmente encontrar respostas a perguntas específicas, tais como quantos clientes existem ou qual é o preço médio de um pedido. As funções agregadas SQL nos permitem analisar os dados de forma eficiente.

Estas funções principais de agregação SQL são muito úteis na ciência dos dados. Com elas, é possível organizar os dados da maneira que se deseja e extrair as informações de que se precisa.

Revisamos muitos exemplos com as funções agregadas COUNT(), SUM(), AVG(), MIN(), e MAX(). Também cobrimos exemplos de *, a palavra-chave DISTINCT, e a declaração CASE como argumentos para agregar funções. Agora, você está pronto para criar seu banco de dados e praticar um pouco mais!

Tags: