wiki

BDA - Aula 23 - 2015/1

DML



Group By


  • Na linguagem SQL é possível obter resultados baseados em grupos de tuplas, assim, existem funções de grupo que operam sobre conjuntos de tuplas
    • O comando GROUP BY é utilizado para dividir as tuplas de uma tabela em grupos menores
    • Algumas das funções de grupo são:


AVG(n): Valor médio de n
COUNT(expr): Número de vezes que a expressão toma um valor
MAX(expr): Valor máximo de expr
MIN(expr): Valor mínimo de expr
SUM(n): Soma dos valores de n


  • Expr indica os argumentos que podem ser do tipo CHAR, Number ou DATE
    • Todas as funções de grupo, à exceção de COUNT(*), ignoram os valores nulos
    • Torna-se ainda importante destacar que as funções de grupo por si só tratam todos as tuplas de uma tabela como um grupo.


  • Exemplos:
SELECT AVG(Ordenado)
FROM Empregado;


SELECT MIN(Ordenado)
FROM Empregado
WHERE Cargo='secretario';


SELECT Cargo, AVG(Ordenado) MediaDeOrdenado
FROM Empregado
GROUP BY Cargo;


SELECT Cargo, Cod_Depar, AVG(Ordenado) MédiaDeOrdenado
FROM Empregado
GROUP BY Cod_Depar, Cargo
ORDER BY Cod_Depar;


SELECT Cargo, MAX(Ordenado) MaxDeOrdenado
FROM Empregado
GROUP BY Cargo
HAVING MAX(Ordenado) >= 300000;


Having x Where


  • A cláusula WHERE é utilizada para fazer filtros em colunas
  • Já a cláusula HAVING também permite que façamos filtros, mas não em colunas como a WHERE e sim em coluna que estejam sendo agregadas


  • Por exemplo:
SELECT TIPO_CLIENTE , COUNT(TIPO_CLIENTE)
FROM CLIENTE
WHERE TIPO_CLIENTE <> 'VIP'
HAVING COUNT(TIPO_CLIENTE) > 2
GROUP BY TIPO_CLIENTE


  • A instrução SELECT acima retorna os tipos dos clientes e quantos de cada tipo de cliente existem na tabela CLIENTE desde que haja mais de dois clientes com o mesmo tipo
  • Somente os tipos de cliente que não forem iguais a 'VIP' serão considerados.


  • Principais diferenças entre WHERE e HAVING em um SELECT


  • Vejamos um exemplo, como a consulta:
select dt_venda as 'Data',
avg(total_venda) as 'Média'
from tb_venda 
where dt_venda <> '2008-05-01' and dt_venda <> '2008-05-03' and dt_venda <> '2008-05-05'
group by dt_venda


  • Produz o mesmo resultado que :
select dt_venda as 'Data',
avg(total_venda) as 'Média'
from tb_venda 
group by dt_venda
having dt_venda <> '2008-05-01' and dt_venda <> '2008-05-03' and dt_venda <> '2008-05-05'


  • Entretanto, a consulta:
select dt_venda as 'Dia', 
sum(total_venda) as 'Total'
from tb_venda
where sum(total_venda) > 3000
group by dt_venda


  • Não substitui:
select dt_venda as 'Dia',
sum(total_venda) as 'Total'
from tb_venda 
group by dt_venda
having sum(total_venda) > 3000


  • Embora tenham comportamentos semelhantes (a cláusula WHERE e a cláusula HAVING destinam-se a filtrar resultados), elas também tem suas diferenças.
    • A primeira diferença refere-se a ordem em que são processadas.
      • Enquanto a cláusula WHERE filtra as linhas antes de agrupar, a cláusula HAVING filtra as linhas após o agrupamento (entenda-se agrupamento a presença de funções como SUM, COUNT, etc e a cláusula GROUP BY).
      • Isso já leva a uma diferença de desempenho, uma vez que na esmagadora maioria das vezes, filtrar os resultados o mais cedo possível é melhor.
      • Qual seria a utilidade de selecionar registros, agrupá-los e depois descartá-los ?
    • A segunda diferença refere-se a possibilidades de filtros.
      • Enquanto a cláusula WHERE limita-se a filtrar resultados simples, a cláusula HAVING possibilita filtros com bases em funções agregadas.
      • Não é possível por exemplo colocar filtros na cláusula WHERE usando funções como COUNT, SUM, AVG, etc e nem utilizar operadores como SOME, ANY e ALL.
      • Embora tenha funcionado no seu exemplo
    • isso é sem dúvida uma agressão ao padrão ANSI e essa construção deve ser evitada a qualquer custo.


  • Via de regra pode-se fazer o seguinte:
    • Filtros de linhas referenciando campos não agregados devem ser feitos na cláusula WHERE
    • Filtros de linhas referenciando campos agregados devem ser feitos na cláusula HAVING
    • Não utilize uma cláusula para realizar filtros da outra


Exercícios


  • 24. Inclua na tabela Eventos, registros coletados na Web para:
    • Rio de Janeiro
    • São Paulo
  • 25. Exclua um evento qualquer colocando data e hora como condição
  • 26. Aumente o valor de todos os preços de todos os eventos em 10%
  • 27. Aumente o valor dos preços de todos os eventos em 5% para São Paulo
  • 28. Altere a data de qualquer evento de Uberlândia postergando 3 dias
  • 29. Exclua o Estado de Minas Gerais da tabela Estados
  • 30. Inclua novamente Minas Gerais
  • 31. Mostre os eventos numa faixa de valores
  • 32. Mostre os eventos e o clima da cidade que sediará os eventos de Dezembro
  • 33. Mostre os eventos das seguintes cidades: São Paulo, Rio de Janeiro e Curitiba
  • 34. Mostre os eventos agrupados por cidade
  • 35. Mostre os eventos agrupados por cidade com preço inferior a R$ 30,00
  • 36. Calcule a média de preço de todos os eventos
  • 37. Calcule a média de preço dos eventos de São Paulo
  • 38. Calcule o evento de menor preço em São Paulo ou Rio de Janeiro
  • 39. Mostre todos os eventos com o nome da Cidade e Estado
  • 40. Mostre todos os eventos ordenados por data com o nome da Cidade e Estado
  • 41. Mostre todos os eventos ordenados por data e agrupados por Cidade
  • 42. Mostre todos os eventos ordenados por Preço e agrupados por Estado
  • 43. Gere e execute uma visão viewEventosMenor35 com os eventos com valores menores a R$ 35,00
  • 44. Aumente os valores de todos os eventos em 10%
  • 45. Execute a visão viewEventosMenor35
  • 46. Mostre o nome, o estado e a região das cidades que começam com a letra C
  • 47. Exclua os eventos de Curitiba
  • 48. Execute a visão viewEventosMenor35
  • 49. Gere e execute uma visão viewEventosBH com o nome do evento, nome da cidade e da região
  • 50. Gere e execute um relatório com o nome do evento, nome da cidade e da região para eventos de BH.


Disponível em “http://3.143.69.156/wiki/index.php?title=BDA_-_Aula_23_-_2015/1&oldid=37864”