Lição 3: Implementing Aggregate Queries


Olá pessoal!

Depois de um longo período sem postar no Blog, volto com força total, espero que aproveitem e não esqueçam de deixar seus comentários.

Working with Aggregate Functions

As funções agregadas nos permitem efetuar cálculos nos valores das colunas, estaremos vendo vários exemplos e ainda utilizaremos a cláusula GROUP BY

Veja abaixo as Funções Agregadas e para que servem:

  • AVG  – Retorna a média.
  • CHECKSUM_AGG – Retorna a soma de verificação dos valores em um grupo onde os valores nulos são ignorados.
  • COUNT  – Retorna o número de valores contindos na colunaignorando os valores NULL.
  • COUNT_BIG – Faz a função do COUNT, mas retorna como tipo de dado bigint, enquanto o COUNT retorna o tipo int.
  • GROUPING – Retorna os valores 1 ou 0 e identifica as linhas como agregadas ou como detalhesquando usamos o statement GROUP BY. O valor é 1 usado para as linhas agregadas e 0 para os detalhes.
  • MAX – Retorna o valor máximo contido no data set.
  • MIN – Retorna o valor mínimo contido no data set.
  • SUM – Retorna a soma dos valores do data set.
  • STDEV – Retorna o desvio padrão para os valores do data set.
  • STDEVP – Retorna o desvio padrão para a população dos valores do data set.
  • VAR – Retorna a variância dos valores.
  • VARP– Retorna a variância estatística para a população dos valores.

A seguir estarei demosntrando em um único SELECT o uso de diversas funções agregadas.

SELECT COUNT(ListPrice) AS 'Número de Linhas'       , AVG(ListPrice) AS 'Média'       , MIN(ListPrice) AS 'Mínimo'
      , MAX(ListPrice) AS 'Máximo'       , SUM(ListPrice) AS 'Soma'       , STDEV(ListPrice) AS 'Desvio Padrão'
      , VAR(ListPrice) AS 'Variância dos Valores'
FROM Production.Product
WHERE ListPrice <> 0
ORDER BY [Média];
Fig. 19 - Exemplo de Funções Agregadas

Repare que o resultado foi uma única linha com as diversas colunas que solicitamos com seus respectivos resultados, à seguir iremos nos aprofundar nas funções agregadas e ver como podemos tirar um melhor proveito.

NOTA: Os valores NULL são ignorados por TODAS funções agregadas

Using the GROUP BY Clause

A instrução de GROUP BY é usada em conjunto com as funções agregadas para agrupar um conjunto de resultados por uma ou mais colunas, por exemplo, vamos retomar o exemplo anterior e com a ajuda da cláusula GROUP BY iremos definir subtotais para os dados agregados.

SELECT Production.Product.ProductSubcategoryID
      , COUNT(ListPrice) AS 'Número de Linhas'
      , AVG(ListPrice) AS 'Média'
      , MIN(ListPrice) AS 'Mínimo'
      , MAX(ListPrice) AS 'Máximo'
      , SUM(ListPrice) AS 'Soma'
      , STDEV(ListPrice) AS 'Desvio Padrão'
      , VAR(ListPrice) AS 'Variância dos Valores'
FROM Production.Product
WHERE ListPrice <> 0
GROUP BY Product.ProductSubcategoryID
ORDER BY [Média]; 
Fig. 20 - Exemplo de GROUP BY

Using the WITH ROLLUP and WITH CUBE Operators

Fonte adicional de pesquisa: http://imasters.uol.com.br/artigo/1128/sql_server/totalizando_dados_com_rollup_e_cube/ de Mauro Pichiliani

A função básica dos operadores WITH ROLLUP e WITH CUBE é a de que podemos obter totais apenas baseando-se na cláusula  GROUP BY e a função de agregação.

Tomemos como base o seguinte SELECT:

SELECT Title AS 'Cargo', MaritalStatus AS 'Casado(M)/Solteiro(S)', COUNT(Title) AS 'QtdadePessoas'
FROM HumanResources.Employee
GROUP BY Title, MaritalStatus
ORDER BY Title;

No exemplo apresentado acima exibiremos os campos TITLE, MARITALSTATUS, da tabela HUMANRESOURCES.EMPLOYEE e que foram agrupados por eles mesmos e finalmente ordenados pela coluna TITLE, não podemos deixar de perceber que utilizamos a função de agregação COUNT que nos retornará uma terceira coluna que contará quantas linhas com as condições solicitadas existem em nossa tabela.

Executando o SELECT acima teremos como retorno o seguinte resultado:

Com base no SELECT acima incluíremos a cláusula WITH ROLLUP para que ele nos retorne os subtotais para cada agrupamento de informações.

Abaixo SELECT que utilizaremos para o ROLLUP e logo na sequência o resultado que retornado.

SELECT Title AS 'Cargo', MaritalStatus AS 'Casado(M)/Solteiro(S)', COUNT(Title) AS 'QtdadePessoas'
FROM HumanResources.Employee
GROUP BY Title, MaritalStatus
WITH ROLLUP
ORDER BY Title;
Fig. 22 - Exemplo de ROLLUP

Fig. 22 - Exemplo de ROLLUP

Observe que no final de cada agrupamento (tomemos como base a linha 4), na coluna QtdadePessoas ele nos mostra o subtotal com a soma dos registros encontrados com o cargo ACCOUNTANT.

Já na primeira linha observe que nos foi retornado o valor 290, que significa o número de registros existentes nesta tabela, lembrando-se que os registros NULL foram desconsiderados conforme explicado em nota quando falamos de funções agregadas.

Depois de entendermos o funcionamento do ROLLUP ficou muito mais fácil entendermos o CUBE, veja o SELECT abaixo onde apenas substituímos a palavra ROLLUP por CUBE e veja o resultado que nos foi retornado na sequência.

SELECT Title AS 'Cargo', MaritalStatus AS 'Casado(M)/Solteiro(S)', COUNT(Title) AS 'QtdadePessoas'
FROM HumanResources.Employee
GROUP BY Title, MaritalStatus
WITH CUBE
ORDER BY Title;
Fig. 23 - Exemplo de CUBE

Fig. 23 - Exemplo de CUBE

O CUBE retornará o mesmo resultado que o ROLLUP, porém com um incremento ele adicionou mais duas linhas com os subtotais dos subtotais, ou seja, ele somou quantos empregados são casados (146) e quantos são solteiros (144) antes de nos retornar a quantidade de funcionários totais (290).

Using the GROUPING Aggregate Function

Como já comentamos anteriormente o uso da função agregada GROUPING serve para nos orientar a identificar quando a linha que está sendo exibida é resultado da execução de um CUBE ou ROLLUP, portanto não se esqueça quando o valor for ‘1’ significa que é resultado de um CUBE ou ROLLUP e quando for ‘0’ é resultado das demais funções.

Utilizei o mesmo SELECT dos exemplos anteriores apenas incluindo a função agregada GROUPING.

SELECT Title AS 'Cargo',
      GROUPING (Title) AS 'GRP Title',
      MaritalStatus AS 'Casado(M)/Solteiro(S)',
      GROUPING (MaritalStatus)  AS 'GRP MaritalStatus',
      COUNT(Title) AS QtdadePessoas
FROM HumanResources.Employee
GROUP BY Title, MaritalStatus
WITH ROLLUP
ORDER BYTitle;
Fig. 24 - Exemplo de GROUPING

Fig. 24 - Exemplo de GROUPING

Perceba que no resultado acima, 2 novas colunas foram adicionadas com os valores da função GROUPING, as colunas são: GRP Title e GRP MaritalStatus

 Using the HAVING Clause

E para finalizar vamos ver como utilizamos a cláusula HAVING, diferente da cláusula WHERE que ele varre toda a coluna especificada, o HAVING utilizamos junto à uma função agregada.

Vejamos um exemplo tomando como base um SELECT utilizado anteriormente.

SELECT Production.Product.ProductSubcategoryID
      , COUNT(ListPrice) AS 'Número de Linhas'
      , AVG(ListPrice) AS 'Média'
      , MIN(ListPrice) AS 'Mínimo'
      , MAX(ListPrice) AS 'Máximo'
      , SUM(ListPrice) AS 'Soma'
      , STDEV(ListPrice) AS 'Desvio Padrão'
      , VAR(ListPrice) AS 'Variância dos Valores'
FROM Production.Product
WHERE ListPrice <> 0
GROUP BY Product.ProductSubcategoryID
HAVING MAX(ListPrice) > 1000;
Fig. 25 - Exemplo da cláusula HAVING

Fig. 25 - Exemplo da cláusula HAVING

Neste exemplo incluímos um critério a mais em nosso SELECT, somente serão exibidos os dados que tenham retornado valores acima de ‘1000’ na função agregada MAX.

E é isso pessoal, espero que a espera por este novo post tenha valido a pena, aproveitem se aprofundem nos temas, deem sugestões e façam as críticas que julgarem necessárias.

Grande abraço, sucesso a todos e até o próximo post.  🙂

 Lucas A. Romão

Anúncios

One Response to Lição 3: Implementing Aggregate Queries

  1. […] This post was mentioned on Twitter by Lucas Almeida Romão, Evilázaro Alves. Evilázaro Alves said: RT @LucasRomao: Novo post: http://bit.ly/cLWWUN #EstudandoPara70433 RT e comentem! o/ […]

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: