Neste artigo, vamos aprender a função SQL Average que é conhecida como função AVG() no T-SQL. A função AVG() é uma função agregada que calcula o valor médio de um conjunto de dados numéricos que retorna da instrução SELECT.
Introduction
Assumir que temos uma colecção de números. Em primeiro lugar, somamos todos os membros da colecção e depois dividimos o número total de membros da colecção. Como resultado, o número obtido será a média. Vamos explicar esta noção matemática com um exemplo simples.
John é estudante na universidade e decide registar as suas despesas todos os dias. O seguinte quadro e tabela representam as despesas de John na última semana.
Agora, vamos calcular a despesa média de John na última semana. No primeiro, somaremos todas as despesas da semana acima mencionada.
($20+$60+$20+$42+$10+$15+$8) = $175 é o montante total das despesas da semana.
Na segunda etapa, dividiremos o montante total das despesas em 7 porque esta colecção é formada por 7 membros. Por outras palavras, uma semana consiste em sete dias.
$175 / 7 = $25 é a despesa média da semana.
Após discutir o conceito matemático da média, vamos continuar a aprender o básico da função AVG() em SQL.
Data Preparation
Com a ajuda da seguinte consulta, vamos criar a tabela WeekExpense e depois inserir as despesas de 3 semanas de John. Vamos utilizar esta tabela em todos os exemplos deste artigo.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/td> CREATE TABLE Weekte expense
( WeekNumber VARCHAR(20),WeekDayName VARCHAR(50), Expense MONEY)
INSERT INTO WeekExpense
VALUES
(‘Week05′,’Monday’, 20 ),
( ‘Week05′,’Tuesday’, 60 ),
( ‘Semana05’, ‘Quarta’, 20 ),
( ‘Semana05’, ‘Quinta’, 42 ),
( ‘Semana05’, ‘Sexta’, 10 ),
( ‘Semana05’, ‘Sábado’, 15 ) ,
( ‘Semana05’, ‘Domingo’, 8 ),
(‘Semana04’, ‘Segunda-feira’, 29 ),
( ‘Semana04’, ‘Terça-feira’, 17 ),
( ‘Semana04’, ‘Quarta’, 42 ),
( ‘Semana04’, ‘Quinta’, 11 ),
( ‘Semana04’, ‘Sexta’, 43 ),
( ‘Semana04’, ‘Sábado’, 10 ) ,
( ‘Semana04’, ‘Domingo’, 15 ),
(‘Semana03’, ‘Segunda-feira’, 10 ),
( ‘Semana03’, ‘Terça-feira’, 32 ),
( ‘Semana03’, ‘Quarta-feira’, 35 ),
( ‘Semana03’, ‘Quinta’, 19 ),
( ‘Semana03’, ‘Sexta-feira’, 30 ),
( ‘Semana03’, ‘Sábado’, 10 ) ,
( ‘Semana03’, ‘Domingo’, 15 )
GO
SELECT *
DA SemanaDespesa
|
SQL Sintaxe da função média
AVG() a função de sintaxe parecerá a seguinte na sua forma simples:
|
1
2
3
|
SELECT AVG ( nome da coluna )
DE TABLENAME
ONDE CONDIÇÃO
|
TODAS as palavras-chave permitem-nos calcular uma média para todos os valores do conjunto de resultados e é utilizada por defeito. A palavra-chave DISTINCT implementa a função AVG() apenas para valores únicos.
AVG() exemplo de função
A seguinte consulta irá calcular a despesa média de John com a ajuda da função AVG().
|
1
2
3
|
A imagem seguinte ilustra a metodologia de cálculo da função AVG() por defeito de utilização.
Como podemos ver, o AVG() considera todos os valores de dias de semana e fins de semana no seu cálculo. Além disso, podemos obter o mesmo resultado quando adicionamos a palavra-chave ALL à sintaxe.
|
1
2
3
|
SELECT AVG(ALL Expense) AS
DA SEMANADespesa
WHERE WeekNumber =’Week05′
|
Se quisermos ignorar valores duplicados durante o cálculo da função AVG(), podemos usar a palavra-chave DISTINCT. Depois de executar a consulta abaixo, vamos analisar o resultado:
|
1
2
3
|
SELECT AVG(DISTINCT Expense) AS
DA DESPESA SEMANA
ONDE SemanaNúmero =’Semana05′
|
A palavra-chave DISTINCT elimina os valores duplicados, portanto, tem em conta apenas uma das despesas cujos valores são de 20 dólares no cálculo. A imagem seguinte ilustra basicamente o mecanismo de funcionamento da palavra-chave DISTINCT.
SQL Função média e valores NULL
função AVG() não considera os valores NULL durante o seu cálculo. Agora, vamos estudar um exemplo desta questão. No início, actualizaremos as despesas de domingo como NULL na tabela de Despesas da Semana.
|
ACTUALIZAR SemanaDespesas
SET
Despesas = NULL
WHERE WeekDayName = ‘Domingo’ AND
WeekNumber = ‘Week05’
|
Agora, executaremos a seguinte consulta, a fim de calcular o valor médio.
|
1
2
3
|
Como podemos ver, o valor NULL não foi tido em conta pela função AVG() no cálculo. A imagem seguinte ilustra o método de cálculo:
Se quisermos incluir os valores NULL no cálculo, podemos utilizar a função ISNULLL. A função ISNULL é utilizada para alterar os valores NULL para os valores definidos. Assim, vamos executar o seguinte para incluir as expressões NULL no cálculo.
|
1
2
3
|
SELECT AVG(ISNULL(Despesa),0)) AS
DA SemanaDespesa
WHERE WeekNumber =’Week05′
|
A imagem seguinte ilustra o método de cálculo da consulta anterior:
Neste ponto, devemos salientar aqui que incluímos a expressão NULL no cálculo como 0.
SQL Utilização média da função com declaração GROUP BY
A declaração GROUP BY é utilizada para agrupar os dados e é utilizada principalmente com funções agregadas.
John decidiu calcular a despesa média de todas as semanas. Para tratar do problema de John, precisamos de usar a declaração GROUP BY e a função AVG() ao mesmo tempo. A consulta seguinte irá calcular a despesa média de cada semana individual.
|
1
2
3
4
|
Quando tivermos dado uma vista de olhos ao conjunto de resultados da consulta, podemos ver que as médias foram calculadas para todas as semanas separadamente.
Bonus Tip: Detalhes do plano de execução da função SQL Média
O plano de execução ajuda a compreender os detalhes de execução de uma consulta. Quando analisamos o plano de execução de uma consulta, podemos obviamente compreender o que se passa nos bastidores.
Agora, analisaremos a seguinte consulta plano de execução real com o ApexSQL Plan para que possamos compreender claramente o que acontece nos bastidores da função AVG().
|
1
2
>/td>>>>> SELECT AVG(Despesa) AS
DA SemanaDespesa
|
O plano de execução visual da consulta é mostrado como abaixo:
O scan da tabela lê todas as linhas das tabelas porque não criamos nenhum índice nesta tabela. No passo seguinte, o operador agregado Stream calcula a soma e o valor de contagem das expressões.
Compute scalar toma estas expressões do agregado Stream e calcula o valor médio através da seguinte fórmula.
|
1
2
3
4
5
|
CASE
QUANDO = (0)
ENTÃO NULL
ELSE / CONVERT_IMPLICIT(money, 0)
END
|
Nesta fórmula, devemos comentar um ponto. Quando o número total das expressões retornar 0, o cálculo médio retornará NULL. Se o número total de expressões retorna 0, o valor médio será NULL. A principal intenção disto é evitar a divisão por erro zero.
Conclusão
Neste artigo, aprendemos a função SQL Média e reforçámos a nossa aprendizagem com exemplos e ilustrações básicas. Ao longo do caminho, discutimos os detalhes do plano de execução da função AVG().
- Autor
- Posts recentes
A maior parte da sua carreira tem estado centrada na Administração e Desenvolvimento de Base de Dados do SQL Server. Os seus interesses actuais são a administração de bases de dados e Business Intelligence. Pode encontrá-lo no LinkedIn.
Ver todos os posts de Esat Erkec
- Mitos de Optimização de Consultas – 23 de Março, 2021
- Sintomas de Farejamento de Parâmetros no SQL Server – 17 de Março de 2021
- Usando a Correcção Automática do Plano para Afinação de Consultas – 4 de Março de 2021