Uso de funções de agregação

Concluído

O T-SQL oferece funções de agregação tais como SUM, MAX e AVG para executar cálculos que assumem vários valores e retornam um único resultado.

Trabalhando com funções de agregação

A maioria das consultas que vimos opera em uma linha por vez, usando uma cláusula WHERE para filtrar linhas. Cada linha retornada corresponde a uma linha no conjunto de dados original.

Muitas funções de agregação 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 de agregação, você precisa considerar os seguintes pontos:

• As funções de agregação retornam um único valor (escalar) e podem ser usadas em instruções SELECT em quase todos os lugares em que um único valor pode ser usado. Por exemplo, essas funções podem ser usadas nas cláusulas SELECT, HAVING e ORDER BY. No entanto, não podem ser usadas 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 de 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 de 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 satisfazem qualquer filtro na cláusula WHERE serão resumidas. Você aprenderá mais sobre GROUP BY na próxima unidade.
• A menos que você esteja usando GROUP BY, não deve combinar funções de agregação com colunas não incluídas em funções na mesma lista de SELECT.

Para se estender além das funções internas, o SQL Server fornece um mecanismo para funções de agregação definidas pelo usuário por meio do CLR (Common Language Runtime) do .NET. Esse tópico não está no escopo deste módulo.

Funções de agregação internas

Como mencionado, o Transact-SQL fornece muitas funções de agregação internas. Dentre as funções frequentemente usadas estão:

Nome da função

Sintaxe

Descrição

SUM

SUM(expressão)

Totaliza todos os valores numéricos não NULL em uma coluna.

AVG

AVG (expressão)

Totaliza todos os valores numéricos não NULL em uma coluna (soma/contagem).

MÍN.

MIN (expressão)

Retorna o menor número, a data/hora mais antiga ou a cadeia de caracteres de primeira ocorrência (de acordo com as regras de classificação de agrupamento).

MÁX.

MAX (expressão)

Retorna o maior número, a data/hora mais recente ou a última cadeia de caracteres de ocorrência (de acordo com as regras de classificação de agrupamento).

COUNT ou COUNT_BIG

COUNT(*) ou COUNT(expressão)

Com (*), todas as linhas são contadas, incluindo aquelas 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 uma agregação integrada em uma cláusula SELECT, considere o exemplo a seguir 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 dessa consulta têm esta aparência:

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

Observe que o exemplo acima resume todas as linhas da tabela Production.Product. Poderíamos modificar facilmente a consulta para retornar os preços médio, mínimo e máximo para produtos em uma categoria específica, adicionando uma cláusula WHERE, desta forma:

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 de agregação 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, Level 16, State 1, Line 1

A coluna 'Production.ProductCategoryID' é inválida na lista de seleção porque não está contida nem em uma função de agregação nem 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. Algumas das funções de agregação também podem ser usadas para resumir dados de data, hora e caractere. Os exemplos a seguir mostram o uso de agregações com datas e caracteres.

Essa consulta retorna a primeira e a última empresa por nome, usando MIN e MAX.:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

Essa 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

MaxCustomer

Uma Loja de Bicicletas

Empresa Bicicleta Amarela

Outras funções podem ser aninhadas com funções de agregação.

Por exemplo, a função escalar YEAR é usada no exemplo a seguir para retornar apenas a parte do ano da data do pedido, antes de MIN e MAX serem 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 usadas para dados numéricos, que incluem os tipos de dado inteiros, dinheiro, float e decimais.

Usando DISTINCT com funções de agregação

Você deve estar ciente do uso de DISTINCT em uma cláusula SELECT para remover linhas duplicadas. Quando usado com uma função de agregação, DISTINCT remove valores duplicados da coluna de entrada antes de calcular o valor de resumo. DISTINCT é útil ao resumir ocorrências exclusivas 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 tenham feito:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT (<alguma_coluna>) simplesmente conta quantas linhas têm algum valor na coluna. Se não houver nenhum valor NULL, COUNT (<alguma_coluna>) será o mesmo que Count(*). COUNT (DISTINCT <alguma_coluna>) conta quantos valores diferentes existem na coluna.

Usando funções de agregação com NULL

É importante estar ciente da possível presença de NULLs em seus dados e de como o NULL interage com os componentes de consulta do T-SQL, incluindo a função de agregação. Alguns pontos que devem ser considerados:

• Com exceção de COUNT usado com a opção (*), as funções de agregação do T-SQL ignoram NULLs. Por exemplo, uma função SUM adicionará apenas valores não NULL. NULLs não são avaliadas como zero. COUNT(*) conta todas as linhas, independentemente de haver um valor ou não nas colunas.
• A presença de NULLs em uma coluna pode levar a cálculos imprecisos para AVG, que somarão apenas linhas populadas e dividirão essa soma pelo número de linhas não NULL. Pode haver uma diferença nos resultados entre AVG(<coluna>) e (SUM(<coluna>)/COUNT(*)).

Por exemplo, considere a seguinte tabela chamada t1:

C1

C2

1

NULO

2

10

3

20

4

30

5

40

6

50

Essa consulta ilustra a diferença entre como a 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

Nesse conjunto de resultados, a coluna chamada média é a agregação 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. Portanto, o cálculo é 150/6, ou 25.

Se você precisar resumir todas as linhas, NULL ou não, considere substituir as NULLs por outro valor que não será ignorado pela função de agregação. Para isso, você pode usar a função COALESCE.