SQL – Comandos de Agregação

SQL – Comandos de Agregação Profa. Sandra de Amo Capitulo 5 – Livro Texto Database Management Systems Ramakrishnan - Gehrke

OPERADORES DE AGREGAÇÃO • COUNT([DISTINCT] A) • Número de valores da coluna A • SUM ([DISTINCT] A) • Soma dos valores da coluna A • AVG ([DISTINCT] A) • Média dos valores da coluna A • MAX(A) • Maior valor da coluna A • MIN(A) • Menor valor da coluna A

Exemplos: AVG, Sum • Dê a média das idades dos marinheiros SELECT AVG(S.Idade) FROM Sailors S • Dê a soma das idades dos marinheiros com status 10 SELECT Sum(S.Idade) FROM Sailors S WHERE S.rating = 10

Exemplos: MAX, MIN (ERRADO) • Dê o nome e idade do marinheiro mais velho SELECT S.Snome, MAX(S.Idade) FROM Sailors S Agregado não pode aparecer junto com outro atributo A MENOS QUE SE UTILIZE O OPERADOR GROUP BY

Exemplos: MAX, MIN (CORRETO) • Dê o nome e idade do marinheiro mais velho SELECT S.Snome, S.Idade FROM Sailors S WHERE S.Idade = (SELECT Max(S2.Idade) FROM Sailors S2) Resultado da consulta é uma tabela com um único elemento (um número) Tabela é transformada em um número

Exemplos (NEM SEMPRE ACEITO POR ALGUNS SGBDs) • Dê o nome e idade do marinheiro mais velho SELECT S.Snome, S.Idade FROM Sailors S WHERE (SELECT Max(S2.Idade) FROM Sailors S2) = S.Idade

Exemplo: COUNT • Conte o número de marinheiros SELECT COUNT (*) FROM Sailors • Conte os nomes diferentes de marinheiros SELECT COUNT( DISTINCT S.Snome) FROM Sailors

Substituindo ALL por MAX • Encontre o nome dos marinheiros que são mais velhos do que o marinheiro mais velho que tem status 10. SELECT S.Snome FROM Sailors S WHERE S.Idade >= ALL (SELECT S2.Idade FROM Sailors S2 WHERE S2.Status = 10) SELECT S.Snome FROM Sailors S WHERE S.Idade >= (SELECT MAX(S2.Idade) FROM Sailors S2 WHERE S2.Status = 10)

Substituindo ANY por MIN • Encontre o nome dos marinheiros que são mais velhos do que algum marinheiro que tem status 10. SELECT S.Snome FROM Sailors S WHERE S.Idade >= ANY (SELECT S2.Idade FROM Sailors S2 WHERE S2.Status = 10) SELECT S.Snome FROM Sailors S WHERE S.Idade >= (SELECT MIN(S2.Idade) FROM Sailors S2 WHERE S2.Status = 10)

Exercicio 6 • Dê a soma das idades e a média dos status dos marinheiros que reservaram barcos vermelhos. SELECT Sum(S.Idade), AVG(S.Status) FROM Sailors S WHERE S.Sid IN (SELECT R.Sid FROM Reservas R, Barcos B WHERE B.Bid = R.Bid AND B.Cor = ‘Vermelho’)

GROUP BY - HAVING SELECT <lista-atributos> FROM <lista-tabelas> WHERE <condição sobre tuplas> GROUP BY <lista-atributos-de-agrupamento> HAVING <condição sobre os grupos>

Exemplo • Para cada nível de status, dê a idade do marinheiro mais jovem neste status Sailors Resposta Sailors Snome Idade Status Sid Snome Idade Sid Status M-Age Status 22 N1 7 45 22 N1 7 45 7 35 N6 35 33 7 29 N2 64 1 8 25,5 8 31 55,5 N3 8 31 55,5 N3 N4 25,5 8 32 N4 25,5 8 32 10 16 35 N5 10 58 N5 10 58 35 35 64 N6 7 N7 10 71 16 9 35 10 N7 71 16 35 74 N6 9 N6 9 29 N2 33 74 35 1 33 1 N8 3 85 25,5 N8 3 85 25,5 3 25,5 95 3 N9 63,5 95 3 N9 63,5 SELECT S.Status, MIN(S.Idade) AS M-Age FROM Sailors S GROUP BY S.Status

Exemplo • Para cada nível de status, dê a idade do marinheiro mais jovem neste status, excluindo-se o status 1 SELECT S.Status, Min(S.Idade) AS M-Age FROM Sailors S GROUP BY S.Status HAVING S.Status <> 1

Exemplo • Para cada nível de status diferente de 1, dê a idade do marinheiro mais jovem neste status que reservou o barco ‘102’. SELECT S.Status, Min(S.Idade) FROM Sailors S WHERE S.Sid in (Select R.Sid FROM Reservas R WHERE R.Bid = ‘102’ ) GROUP BY S.Status HAVING S.Status <> 1

Resposta Status 35 7 8 25,5 3 25,5 Exemplo • Encontre a idade do marinheiro mais jovem mas que possa votar (com pelo menos 18 anos) para cada nivel de status com ao menos dois marinheiros neste nível. SELECT S.Status, Min(S.Idade) AS M-Age FROM Sailors S WHERE S.Idade >= 18 GROUP BY S.Status HAVING COUNT(*) > 1 Sailors Snome Idade Sid Status M-Age 22 N1 7 45 N6 35 7 64 8 31 55,5 N3 N4 25,5 8 32 58 N5 10 35 71 N7 10 16 N6 9 35 74 29 N2 33 1 N8 25,5 3 85 95 3 N9 63,5

Exemplo • Encontre a idade do marinheiro mais jovem mas que possa votar (com pelo menos 18 anos) para cada nivel de status com ao menos dois marinheiros neste nível. Exiba a resposta ordenada por Status. SELECT S.Status, Min(S.Idade) AS M-Age FROM Sailors S WHERE S.Idade >= 18 GROUP BY S.Status HAVING COUNT(*) > 1 ORDER BY S.Status Resposta Status M-Age 3 25,5 35 7 25,5 8

Exemplo • Encontre a idade do marinheiro mais jovem mas que possa votar (com pelo menos 18 anos) para cada nivel de status com ao menos dois marinheiros neste nível e onde todo marinheiro neste nível tenha no máximo 60 anos. SELECT S.Status, Min(S.Idade) AS Min-A FROM Sailors S WHERE S.Idade >= 18 GROUP BY S.Status HAVING COUNT(*) > 1 AND EVERY (S.Idade <= 60) Resposta Sailors Snome Idade Sid Status Status Min-A 22 N1 7 45 35 7 7 64 N6 35 8 55,5 N3 31 8 25,5 N4 32 25,5 8 58 N5 10 35 71 N7 10 16 N6 9 74 35 29 N2 1 33 N8 3 25,5 85 63,5 N9 3 95 97 24 N10 3

A consulta a seguir é equivalente à precedente ? SELECT S.Status, Min(S.Idade) FROM Sailors S WHERE S.Idade >= 18 AND S.Idade <= 60 GROUP BY S.Status HAVING COUNT(*) > 1

Resposta : Não são equivalentes ! Resposta SELECT S.Status, Min(S.Idade) AS Min-AGE FROM Sailors S WHERE S.Idade >= 18 AND S.Idade <= 60 GROUP BY S.Status HAVING COUNT(*) > 1 Sailors Sid Status Snome Idade Status Min-A 22 N1 7 45 35 7 7 N6 64 35 8 55,5 N3 31 8 25,5 32 25,5 N4 8 3 24 58 N5 10 35 16 71 N7 10 9 74 35 N6 1 33 29 N2 N8 3 25,5 85 N9 95 3 63,5 97 3 24 N10 Encontre a idade e status dos marinheiros mais jovens que possam votar (com pelo menos 18 anos) e que tenham no máximo 60 anos, agrupados por status, onde cada grupo tem ao menos dois marinheiros nestas condições

Exemplo • Encontre a idade do marinheiro mais jovem mas que possa votar (com pelo menos 18 anos) para cada nivel de status com ao menos dois marinheiros neste nível e onde pelo menos um marinheiro no nível tenha idade inferior a 60 anos. SELECT S.Status, Min(S.Idade) FROM Sailors S WHERE S.Idade >= 18 GROUP BY S.Status HAVING COUNT(*) > 1 AND ANY (S.Idade <= 60)

Exercício 7 • Para cada barco vermelho, dê o número de reservas que foram feitas para este barco. SELECT COUNT(*) AS ContadorReserva FROM Barcos B, Reservas R WHERE R.Bid = R. Bid and B.Cor = ‘Verm’ GROUP BY B.Bid

Exercicio 8 • Encontre a média de idade dos marinheiros com mais de 18 anos, para cada nível de status que tem ao menos dois marinheiros. SELECT S.Status, AVG(S.Idade) AS Min-AGE FROM Sailors S WHERE S.Idade > 18 GROUP BY S.Status HAVING COUNT(*) > 1

Consultas dentro do comando FROM • Dê os status para os quais a média de idades dos marinheiros neste status é igual à menor das médias de idades de cada status. SELECT Temp.Status FROM (SELECT S.Status, AVG(S.Idade) FROM Sailors S GROUP BY S.Status) AS Temp WHERE Temp.AVG = (SELECT MIN (Temp.AVG) FROM Temp)

Resumo Geral do uso de Agregados SELECT <lista-seleção> FROM <lista-tabelas> WHERE <condição-sobre-tuplas> GROUP BY <lista-atributos-de-agrupamento> HAVING <condição-sobre-os-grupos> 1. lista-seleção = lista de atributos + lista de termos do tipo operador(atributo) AS novo-atributo 2. Todo atributo que aparece em “lista de atributos” deve aparecer nalista-atributos-de-agrupamento. 3. Atributos que aparecem emcondição-sobre-os-gruposdevem aparecer em “operador(atributo)”ou emlista-atributos-de-agrupamento.

Exemplo SELECT ,Min( ) AS Min-Age FROM Sailors S WHERE S.Idade >= 18 GROUP BY HAVING > 5 AND EVERY ( <= 60) S.Idade S.Status S.Status S.Status S.Idade

Particularidade de SQL 1999 Se a lista-atributos-de-agrupamento contém a chave primária de uma tabela dalista-tabelasentão cada coluna desta tabela só tem um único valor em cada grupo. Em SQL1999, tais colunas podem aparecer nalista de atributos da lista-seleção. SELECT S.Status, S.Snome, AVG(S.Idade) AS Min-AGE FROM Sailors S GROUP BY S.Sid HAVING COUNT(*) > 1 Chave de Sailors Logo: os grupos têm um único elemento

SQL – Comandos de Agregação