Usar funções agregadas
O T-SQL fornece funções agregadas como SUM, MAX e AVG para executar cálculos que usam vários valores e retornam um único resultado.
Trabalhar com funções agregadas
A maioria das consultas que analisamos operam em uma linha de cada vez, usando uma cláusula WHERE para filtrar linhas. Cada linha retornada corresponde a uma linha no conjunto de dados original.
Muitas funções agregadas são fornecidas no SQL Server. Nesta seção, veremos as funções mais comuns, como SUM, MIN, MAX, AVG e COUNT.
Ao trabalhar com funções agregadas, você precisa considerar os seguintes pontos:
- As funções agregadas retornam um único valor (escalar) e podem ser usadas em instruções SELECT em praticamente qualquer lugar onde um único valor possa ser usado. Por exemplo, essas funções podem ser usadas nas cláusulas SELECT, HAVING e ORDER BY. No entanto, não podem ser utilizados na cláusula WHERE.
- As funções de agregação ignoram NULLs, exceto ao usar COUNT(*).
- As funções de agregação em uma lista SELECT não têm um cabeçalho de coluna, a menos que você forneça um alias usando AS.
- As funções de agregação em uma lista SELECT operam em todas as linhas passadas para a operação SELECT. Se não houver nenhuma cláusula GROUP BY, todas as linhas que satisfaçam qualquer filtro na cláusula WHERE serão resumidas. Você aprenderá mais sobre o GROUP BY no próximo tópico.
- A menos que você esteja usando GROUP BY, não deve combinar funções agregadas com colunas não incluídas em funções na mesma lista SELECT.
Para ir além das funções internas, o SQL Server fornece um mecanismo para funções agregadas definidas pelo usuário por meio do .NET Common Language Runtime (CLR). Esse tópico está além do escopo deste módulo.
Funções agregadas incorporadas
Como mencionado, o Transact-SQL fornece muitas funções agregadas internas. As funções mais usadas incluem:
Nome da função
Sintaxe
Descrição
SUM
SOMA(expressão)
Totaliza todos os valores numéricos não NULL em uma coluna.
AVG
AVG(expressão)
Calcula a média de todos os valores numéricos não NULL em uma coluna (soma/contagem).
MIN
MIN(expressão)
Retorna o menor número, a data/hora mais antiga ou a primeira cadeia de caracteres que ocorre (de acordo com as regras de classificação de agrupamento).
MAX
MAX(expressão)
Retorna o maior número, a data/hora mais recente ou a última cadeia de caracteres (de acordo com as regras de classificação de agrupamento).
CONTAGEM ou COUNT_BIG
COUNT(*) ou COUNT(expressão)
Com (*), conta todas as linhas, incluindo linhas com valores NULL. Quando uma coluna é especificada como expressão, retorna a contagem de linhas não NULL para essa coluna. COUNT retorna um int; COUNT_BIG retorna um big_int.
Para usar um agregado interno em uma cláusula SELECT, considere o seguinte exemplo no banco de dados de exemplo MyStore :
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
Os resultados desta consulta são mais ou menos assim:
Preço Médio
Preço Mínimo
Preço Máximo
744.5952
2.2900
3578.2700
Observe que o exemplo acima resume todas as linhas da tabela Production.Product . Poderíamos facilmente modificar a consulta para retornar os preços médios, mínimos e máximos de produtos em uma categoria específica, adicionando uma cláusula WHERE, como esta:
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;
Ao usar agregações em uma cláusula SELECT, todas as colunas referenciadas na lista SELECT devem ser usadas como entradas para uma função agregada ou ser referenciadas em uma cláusula GROUP BY.
Considere a seguinte consulta, que tenta incluir o campo ProductCategoryID nos resultados agregados:
SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
A execução desta consulta resulta no seguinte erro
Msg 8120, Nível 16, Estado 1, Linha 1
A coluna 'Production.ProductCategoryID' é inválida na lista de seleção porque não está contida em uma função agregada ou na cláusula GROUP BY.
A consulta trata todas as linhas como um único grupo agregado. Portanto, todas as colunas devem ser usadas como entradas para agregar funções.
Nos exemplos anteriores, agregamos dados numéricos, como o preço e as quantidades, no exemplo anterior. Algumas das funções agregadas também podem ser usadas para resumir dados de data, hora e caracteres. Os exemplos a seguir mostram o uso de agregados com datas e caracteres:
Esta consulta retorna a primeira e a última empresa pelo nome, usando MIN e MAX:
SELECT MIN(CompanyName) AS MinCustomer,
MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;
Esta consulta retornará o primeiro e o último valores para CompanyName na sequência de agrupamento do banco de dados, que neste caso é ordem alfabética:
MinCustomer
MaxCliente
Uma loja de bicicletas
Empresa Yellow Bicycle
Outras funções podem ser aninhadas com funções agregadas.
Por exemplo, a função escalar YEAR é usada no exemplo a seguir para retornar apenas a parte do ano da data do pedido, antes que MIN e MAX sejam avaliados:
SELECT MIN(YEAR(OrderDate)) AS Earliest,
MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;
Mais Antiga
Mais Recente
2008
2021
As funções MIN e MAX também podem ser usadas com dados de data, para retornar os valores cronológicos mais antigos e mais recentes. No entanto, AVG e SUM só podem ser usados para dados numéricos, que incluem inteiros, dinheiro, float e tipos de dados decimais.
Usando DISTINCT com funções agregadas
Você deve estar ciente do uso de DISTINCT em uma cláusula SELECT para remover linhas duplicadas. Quando usado com uma função agregada, DISTINCT remove valores duplicados da coluna de entrada antes de calcular o valor de resumo. DISTINCT é útil ao resumir ocorrências únicas de valores, como clientes na tabela de pedidos.
O exemplo a seguir retorna o número de clientes que fizeram pedidos, independentemente de quantos pedidos eles fizeram:
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;
COUNT(<some_column>) apenas conta quantas linhas têm algum valor na coluna. Se não houver valores NULL, COUNT(<some_column>) será o mesmo que COUNT(*). COUNT (DISTINCT <some_column>) conta quantos valores diferentes existem na coluna.
Usando funções agregadas com NULL
É importante estar ciente da possível presença de NULLs em seus dados e de como NULL interage com componentes de consulta T-SQL, incluindo a função de agregação. Há algumas considerações a ter em conta:
- Com exceção de COUNT usado com a opção (*), as funções de agregação T-SQL ignoram NULLs. Por exemplo, uma função SOMA adicionará apenas valores não NULL. NULLs não são avaliados a zero. COUNT(*) conta todas as linhas, independentemente do valor ou não valor em qualquer coluna.
- A presença de NULLs em uma coluna pode levar a cálculos imprecisos para o AVG, que somará apenas linhas preenchidas e dividirá essa soma pelo número de linhas não NULL. Pode haver uma diferença nos resultados entre AVG(<coluna>) e (SOMA(<coluna>)/CONTAGEM(*)).
Por exemplo, considere a seguinte tabela chamada t1:
C1
C2
1
NULL
2
10
3
20
4
30
5
40
6
50
Esta consulta ilustra a diferença entre como o AVG lida com NULL e como você pode calcular uma média com uma coluna computada SUM/COUNT(*):
SELECT SUM(c2) AS sum_nonnulls,
COUNT(*) AS count_all_rows,
COUNT(c2) AS count_nonnulls,
AVG(c2) AS average,
(SUM(c2)/COUNT(*)) AS arith_average
FROM t1;
O resultado seria:
sum_nonnulls
count_all_rows
count_nonnulls
média
arith_average
150
6
5
30
25
Neste conjunto de resultados, a coluna denominada média é o agregado que obtém internamente a soma de 150 e divide pela contagem de valores não nulos na coluna c2. O cálculo seria 150/5, ou 30. A coluna chamada arith_average divide explicitamente a soma pela contagem de todas as linhas, de modo que o cálculo é 150/6 ou 25.
Se você precisar resumir todas as linhas, sejam NULL ou não, considere substituir as NULLs por outro valor que não será ignorado pela sua função agregada. Você pode usar a função COALESCE para esta finalidade.