UFABC UAB TSI TECNOLOGIA EM SISTEMAS DE INFORMAÇÃO

(1)

[email protected]

UFABC –UAB TSI –

T

ECNOLOGIA EM

S

ISTEMAS DE

I

NFORMAÇÃO

A

ULA

6B: SQL –

PARTE

2

(2)

A

GENDA

 Agrupamento e Agregação de dados

 Agrupando resultados de consultas

 Somando dados

 Contando registros

 _{Média, máximo, mínimo}

 _Subconsultas

 Conceitos

 Formas de utilização

 Quando usar subconsultas?

(3)

A

GRUPANDO

D

ADOS



_{Cláusula GROUP BY}

 Agrupa os dados pelo campo selecionado

 Recurso para definirmos totalizações (por exemplo)



_{Agrupamentos só são realizados após o conjunto de dados}

ser recuperado

(4)

A

GRUPANDO

D

ADOS

mysql> select idProj -> from projFunc -> group by idProj; +---+ | idProj | +---+ | 1 | | 2 | +---+

2 rows in set (0.00 sec) mysql> select idProj

-> from projFunc; +---+ | idProj | +---+ | 1 | | 1 | | 2 | | 2 | | 2 | | 2 | +---+ 6 rows in set (0.00 sec)

(5)

A

GREGANDO

D

ADOS

 A idéia de agregar é sumarizar alguma informação de um conjunto de registros  Somatórias  Máximos valores  Mínimos valores  _Médias  Quantidade de valores

 Retirar da responsabilidade da aplicação, uma vez que o SGBD já realiza isso

 Algumas consultas necessitam desse tipo de informação como critérios para novas consultas

(6)

F

UNÇÕES DE

A

GREGAÇÃO

 _Max(campo)

 Retorna o maior valor do campo dentro de um conjunto

 Min(campo)

 Retorna o menor valor do campo dentro de um conjunto

 _Avg(campo)

 Retorna o valor médio do campo

 Sum(campo)

 Retorna a somatória dos valores do campo

 Count(campo)

(7)

U

SANDO

F

UNÇÕES DE

AGREGAÇÃO



_{Contando quantos funcionários há na empresa}

mysql> select count(*) from funcionario; +---+

| count(*) | +---+ | 8 | +---+

1 row in set (0.06 sec)



_{Contando quantos departamentos há na empresa}

mysql> select count(*) from departamento; +---+

| count(*) | +---+ | 3 | +---+

(8)

U

SANDO

A

GREGAÇÃO E

A

GRUPAMENTOS



_{Contando quantos funcionários há por projeto}

mysql> select idProj, count(idFunc) -> from projFunc group by idProj; +---+---+ | idProj | count(idFunc) | +---+---+ | 1 | 2 | | 2 | 4 | +---+---+ 2 rows in set (0.00 sec)

(9)

A

GRUPANDO

, J

UNTANDO

,

A

GREGANDO

...



Selecionando os nomes dos projetos que os funcionários

trabalham

mysql> select p.descrP, pf.idProj, count(pf.idFunc) -> from projFunc pf inner join projeto p using (idProj)

-> group by idProj;

+---+---+---+ | descrP | idProj | count(pf.idFunc) | +---+---+---+ | Folha de Pagto | 1 | 2 | | Sist. Escolar | 2 | 4 | +---+---+---+ 2 rows in set (0.13 sec)

(10)

F

ILTROS PARA DADOS

AGREGADOS



Cláusula HAVING



_{Diferença básica entre HAVING e WHERE}

 _{Where  dados brutos (originados diretamente da tabela}

ou da view), sem nenhum tratamento

 _{Having  dados agregados (somatórias, maiores, menores,}

(11)

F

ILTROS PARA DADOS

AGREGADOS



_{Filtrando os projetos que possuem mais de 2}

funcionários alocados

mysql> select p.descrP, pf.idProj, count(pf.idFunc)

-> from projFunc pf inner join projeto p using (idProj) -> group by idProj

-> having count(pf.idFunc) > 2;

+---+---+---+ | descrP | idProj | count(pf.idFunc) | +---+---+---+ | Sist. Escolar | 2 | 4 | +---+---+---+ 1 row in set (0.06 sec)

(12)

A

GRUPAMENTOS



_{Por que utilizar agrupamentos?}

 Organizar a informação que se quer recuperar



_{Exemplos de agrupamentos}

 Totais de vendas por região geográfica

 Encontrar vendedores que mais realizaram vendas em um determinado ano

 Determinar freqüências de ocorrências de elementos

(13)

U

M

E

XEMPLO

M

AIS

E

LABORADO

 Este exemplo é um exemplo existente no livro

 “Aprendendo SQL – O´Rilley”

 E também pode ser obtido pelo endereço

 A idéia é que o modelo retrate uma instituição financeira (Banco), onde existem várias agências que oferecem produtos a clientes.

 Produtos podem ser Poupança (SAV-savings), financiamentos (LOA – loans), depósitos (CHK–checks), entre outros

 A idéia é apenas apresentá-lo para que vocês possam “ler” um modelo de dados já existente e entender também como as consultas foram geradas.

 Este exemplo é bastante rico se levarmos em considerações operações de agregações e agrupamentos

(14)

U

M

E

XEMPLO MAIS

ELABORADO

–

AS TABELAS

 Account

 _{Uma conta (produto) específico}

aberto para um cliente em particular

 Branch

 Uma agência bancária onde uma transação é realizada

 Customer

 Cliente do banco (pode ser pessoa física ou jurídica)

 Business

 Cliente pessoa jurídica

 Individual

 _{Cliente pessoa física}

 Department

 Departamento do banco

 Employee

 _{Funcionário do banco}

 Officer

 O Administrador da pessoa jurídica (pode ter várias)

 Product

 Um produto oferecido pelo banco (linha de crédito para PJ, hipoteca imobiliária, financiamento de carros)

 Product_type

 Tipo dos produtos oferecidos (financiamentos, conta bancária, seguros)

 Transaction

 Uma ocorrência em uma conta bancária

(15)

E

NTENDENDO O

E

XEMPLO



Um cliente (customer) é

uma entidade que pode ser

especializada em duas

outras

 _{Pessoa física (individual)}  Pessoa jurídica (business) 

_{Todo cliente pessoa jurídica}

tem um ou mais gerente

(que pode ser gerente de

apenas 1 pessoa jurídica)

(16)

E

NTENDENDO O

E

XEMPLO

 Todo produto (product) oferecido pelo banco tem uma categoria (product_type) associado a ela

 Além disso todo funcionário (employee) trabalha tem um departamento (department) associado a ele e é gerenciado por outro funcionário

(auto-relacionamento)

(17)

E

NTENDENDO O

E

XEMPLO



_{O cliente tem uma}

ou várias contas

(account) e toda

conta está associada

a um produto

(product) e a uma

agência (branch)

onde a conta foi

aberta

(18)

E

NTENDENDO O

E

XEMPLO

 Toda transação (transaction) está

associada a uma conta (que pode ser crédito ou débito)

 _{Toda transação também}

está associada à um empregado (que a

efetiva) e a uma agência onde a transação terá efeito (um depósito pode ser feito em uma agência referenciando uma conta de outra agência)

(19)

A

GRUPAMENTOS POR VÁRIAS

COLUNAS



_{GROUP BY pode ser utilizado para agrupar}

dados por várias colunas



_{Geração de totais e subtotais}



_{No exemplo dos bancos}



_{Caso simples: somar todos os saldos de contas}

existentes



_{Elaborando um pouco: somar todos os saldos por}

tipos de produtos (crédito para pequenas

empresas, poupança, etc)



_{Elaborando mais ainda: somar todos os saldos por}

(20)

A

GRUPAMENTOS POR VÁRIAS

COLUNAS

mysql> select product_cd, open_branch_id, sum(avail_balance) as total -> from account;

+---+---+---+ | product_cd | open_branch_id | total | +---+---+---+ | CHK | 2 | 170754.46 | +---+---+---+ 1 row in set (0.03 sec)

mysql> select product_cd, open_branch_id, sum(avail_balance) as total -> from account group by product_cd;

+---+---+---+ | product_cd | open_branch_id | total | +---+---+---+ | BUS | 4 | 9345.55 | | CD | 2 | 19500.00 | | CHK | 2 | 73008.01 | | MM | 3 | 17045.14 | | SAV | 2 | 1855.76 | | SBL | 3 | 50000.00 | +---+---+---+ 6 rows in set (0.08 sec)

Caso 1:

somando

tudo

Caso 2:

agrupando

por tipos

de

produtos

(21)

A

GRUPAMENTO POR VÁRIAS

COLUNAS

mysql> select product_cd, open_branch_id, sum(avail_balance) as total -> from account group by product_cd, open_branch_id;

+---+---+---+ | product_cd | open_branch_id | total | +---+---+---+ | BUS | 2 | 9345.55 | | BUS | 4 | 0.00 | | CD | 1 | 11500.00 | | CD | 2 | 8000.00 | | CHK | 1 | 782.16 | | CHK | 2 | 3315.77 | | CHK | 3 | 1057.75 | | CHK | 4 | 67852.33 | | MM | 1 | 14832.64 | | MM | 3 | 2212.50 | | SAV | 1 | 767.77 | | SAV | 2 | 700.00 | | SAV | 4 | 387.99 | | SBL | 3 | 50000.00 | +---+---+---+ 14 rows in set (0.03 sec)

Caso 3:

agrupando

por

produto e

por

agência em

que foi

aberto

(22)

G

ERANDO

S

UMÁRIOS

(R

OLL

U

PS

)



Um recurso bastante poderoso oferecido pelo SGBD é a

geração automática de sumários (RollUps)

 _{Facilita a recuperação ordenada de informações com as}

respectivas totalizações automaticamente



_{Utiliza-se um agrupamento, indicando a necessidade de}

gerar totalizações

(23)

G

ERANDO

S

UMÁRIOS

(R

OLL

U

PS

)

mysql> select product_cd, open_branch_id, sum(avail_balance) as total -> from account group by product_cd, open_branch_id WITH

ROLLUP;

+---+---+---+ | product_cd | open_branch_id | total | +---+---+---+ | BUS | 2 | 9345.55 | | BUS | 4 | 0.00 | | BUS | NULL | 9345.55 | | CD | 1 | 11500.00 | | CD | 2 | 8000.00 | | CD | NULL | 19500.00 | | CHK | 1 | 782.16 | | CHK | 2 | 3315.77 | | CHK | 3 | 1057.75 | | CHK | 4 | 67852.33 | | CHK | NULL | 73008.01 | | MM | 1 | 14832.64 | | MM | 3 | 2212.50 | | MM | NULL | 17045.14 | | SAV | 1 | 767.77 | | SAV | 2 | 700.00 | | SAV | 4 | 387.99 | | SAV | NULL | 1855.76 | | SBL | 3 | 50000.00 | | SBL | NULL | 50000.00 | | NULL | NULL | 170754.46 | +---+---+---+ 21 rows in set (0.01 sec)

Ao invés de deixar a responsabilidade da somatória por conta da aplicação, o SGBD já realiza isso para você. As linhas que contém NULL representam totalizações parciais ou geral Totais parciais Total geral

(24)

R

ESUMOS MAIS ELABORADOS

 Alguns SGBDs implementam a opção WITH CUBE em suas opções de agrupamento

 MySQL não suporta essa opção

 WITH ROLLUP

 Organiza totais pela ordem dos campos que foram especificados na consulta

 Totaliza as agências por tipo de produtos

 Totaliza os produtos

 _{WITH CUBE}

 Totaliza todas as combinações existentes

 _{Totalizaria também todas as agências (independente do tipo de}

(25)

S

UBCONSULTAS



_{Basicamente uma subconsulta é uma consulta contida em}

outra instrução SQL



_{Tipos de resultados}

 Única linha com única coluna

 _{Única linha e múltiplas colunas}  _{Múltiplas linhas com única coluna}  _{Múltiplas linhas e colunas}



_{Dois tipos de consulta}

 Subconsultas não correlatas

(26)

P

OR QUE SE USAR

SUBCONSULTAS

?



_{Muitas vezes é possível obter o mesmo resultado de uma}

subconsulta através das cláusulas de filtros (WHERE)

indicando igualdades

 Mas quando não se souber os valores que devem ser combinados para se fazer o filtro?

 _{Ou não se sabe a quantidade de valores que devem ser}

comparados para se obter os filtros



_{Ideal: usar subconsultas}

 Uma consulta retorna um conjunto de valores que serão combinados (conferidos) com os valores da consulta principal e, então, retornados aos usuários

(27)

S

ITUAÇÃO



_{Deseja-se, a partir de uma tabela existente, buscar}

todos os funcionários que são supervisores

mysql> select * from funcionario;

+---+---+---+---+---+---+---+ | idFunc | nomeF | emailF | sexo | dataAd | idDepto | idSuper | +---+---+---+---+---+---+---+ | 1 | Isidro | [email protected] | M | 2010-01-01 | 1 | NULL | | 2 | Sezefredo | [email protected] | M | 2010-01-01 | 1 | 1 | | 3 | Adamastor | [email protected] | M | 2010-01-01 | 1 | 1 | | 4 | Deosdedite | [email protected] | M | 2010-01-01 | 1 | 1 | | 5 | Energarda | [email protected] | F | 2010-01-01 | 2 | 2 | | 6 | Josicleide | [email protected] | F | 2010-01-01 | 2 | 2 | | 7 | Nilsonclecio | [email protected] | M | 2010-01-01 | 3 | 6 | | 8 | Roberval | [email protected] | M | 2010-01-01 | 3 | 6 | | 9 | Cheirosa | [email protected] | F | 2010-04-30 | 2 | 1 | | 10 | Caue | [email protected] | M | 2010-04-30 | 1 | 1 | | 11 | Ermenegildo | [email protected] | M | 2010-05-03 | 7 | 1 | +---+---+---+---+---+---+---+ 11 rows in set (0.01 sec)

(28)

C

OMO FAZER

?

 1a tentativa – fácil, porém limitada (usando filtros simples)

3 rows in set (0.00 sec)

 Poblema desta abordagem

 Caso surja outro supervisor, a consulta deve ser refeita para incluir na cláusula WHERE o código do novo supervisor

(29)

C

OMO FAZER

?



_{2a tentativa – Subconsultas}

 Como pensar em subconsultas?

 Pense em INTERSECÇÃO, SUBTRAÇÃO de conjuntos ou SUBCONJUNTOS



_{Conjunto 1 – recuperar os códigos de funcionários e seus}

respectivos nomes

 _{cujos códigos sejam coincidentes na coluna SUPERVISOR da}

(30)

C

OMO FAZER

?

+---+ | idSuper | +---+ | NULL | | 1 | | 1 | | 1 | | 2 | | 2 | | 6 | | 6 | | 1 | | 1 | | 1 | +---+ +---+ | idFunc | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | +---+

(31)

A

S CLÁUSULAS

IN

E

NOT IN



_{Lembram da teoria dos conjuntos?}

 Idéia por trás das subconsultas



_IN

 Indica que os dados que fazem parte do filtro DEVEM fazer parte do conjunto obtido na subconsulta (intersecção com um subconjunto)



NOT IN

 _{Indica que os dados que fazem parte do filtro NÃO DEVEM}

fazer parte do conjunto obtido na subconsulta (Subtração de conjuntos)

(32)

T

RANSFORMANDO O RACIOCÍNIO EM

SQL

mysql> select nomeF from funcionario where

3 rows in set (0.00 sec) _{Esta consulta retorna}

um conjunto de ID de supervisores dos funcionários

(33)

T

RANSFORMANDO O RACIOCÍNIO EM

SQL

mysql> select nomeF from funcionario where

3 rows in set (0.00 sec)

O filtro indica todos os IDs de funcionários que fazem parte do conjunto obtido na subconsulta

(34)

A

LGUMAS OBSERVAÇÕES



Existem casos em que elementos da query principal não

combinam (match) com elementos do conjunto obtido na

subconsulta



Operador IN indica que se houver algum elemento na

subconsulta, a condição é satisfeita



_{O operador NOT IN indica que um elemento da query}

principal não deve ter nenhum correspondente na

subconsulta

(35)

M

AIS OBSERVAÇÕES



_{Ao usar NOT IN, deve-se certificar que os valores}

retornados pela subconsulta não contenham valores NULL

 O SBGD vai igualar o valor do lado esquerdo da expressão com cada valor da subconsulta

 Ao igualar o valor com NULL, o resultado é “desconhecido” e, portanto a consulta não resultará no esperado

(36)

S

ITUAÇÃO



_{Recuperar todos os funcionários que não são}

supervisores – teoricamente a mesma instrução com

uma restrição a mais na 2ª subconsulta

mysql> select nomeF from funcionario

-> where idFunc not in (Select distinct idsuper from funcionario); Empty set (0.00 sec)

mysql> select nomeF from funcionario -> where idFunc in

8 rows in set (0.00 sec)

O simples fato de uma subconsulta retornar uma única linha com um campo NULL, já pode alterar o resultado de toda a consulta

(37)

O

PERADOR

ALL



_{Permite realizar operações com TODOS os elementos do}

subconjuntos



_{Dessa forma, algum tipo de operação complementar deve}

ser indicado

(38)

O

PERADOR

ALL



_{O exemplo anterior também pode ser escrito da}

seguinte forma:

mysql> select nomeF from funcionario where idFunc <> ALL

(39)

O

PERADOR

ANY



_{Da mesma forma que o ALL, o ANY também compara}

elementos da consulta inicial com os elementos da

subconsulta



_{Operadores complementares também fazem-se}

necessários



_{Assim que, pelo menos 1 elemento da subconsulta satisfaz}

a condição, ele já faz parte do resultado



Observação

 NOT IN  <> ALL

(40)

S

UBCONSULTAS COM MAIS DE

UM CAMPO



_{Subconsultas podem retornar mais de um campo}



_{A ordem de filtro dos campos que serão referenciados na}

subconsulta deverá ser exatamente a mesma dos campos

retornados pela subconsulta

(41)

S

UBCONSULTAS

C

ORRELATAS



_{Subconsultas vistas até agora são ditas Não Correlatas}

 _{A subconsulta é executada independentemente}

 Inclusive é uma forma de depuração das informações obtidas



_{Subconsultas correlatas possuem elementos dependentes}

da instrução-contêiner

 Ou seja, campos da consulta principal fazem parte do critério de filtro da subconsulta

(42)

C

OMO FUNCIONA UMA

SUBCONSULTA CORRELATA

?

 Ao invés de ser executada uma única vez, antes da execução da consulta-contêiner, a subconsulta correlata é executada para cada linha-candidata

 Linhas que podem ser incluídas no resultado final

 É a consulta que mais exige acessos ao disco.

 Portanto de menor eficiência.

 Seu uso deve ser muito bem planejado, pois, em geral é possível recuperar boa parte do que se pretende usando subconsultas não correlatas

 _{Mesmo que tabelas diferentes façam parte da subconsulta correlata}  Situação: no exemplo das contas bancárias, como recuperar os códigos

(43)

S

UBCONSULTAS

C

ORRELATAS



1o passo – buscar as somatórias das contas de todos os

clientes

Select sum(a.avail_balance) from account a



_{2o passo – buscar de um determinado cliente}

Select sum(a.avail_balance) from account a where a.cust_id = ???



Porém deve-se recuperar TODOS os clientes que tiverem

conta e, finalmente filtrar o valor final pelo critério

(44)

S

UBCONSULTAS CORRELATAS

mysql> select c.cust_id, c.cust_type_cd, c.city -> from customer c

-> where (select sum(a.avail_balance) -> from account a

-> where a.cust_id = c.cust_id) -> between 5000 and 10000;

+---+---+---+ | cust_id | cust_type_cd | city | +---+---+---+ | 4 | I | Waltham | | 7 | I | Wilmington | | 11 | B | Wilmington | +---+---+---+ 3 rows in set (0.09 sec)

A presença do campo c.cust_id tanto na consulta quanto na subconsulta torna-a correlata

(45)

O

PERADOR

EXISTS



Muito utilizado em consultas correlatas



_{Simplesmente retorna a quantidade de registros}

encontrados

 0, 1 ou mais registros



O conteúdo dos dados recuperados não é importante

(46)

E

XEMPLO



_{É possível recuperar o número da conta e o saldo}

disponível das contas que possuem transação no dia 03 de

maio de 2010?



_{1o passo – recuperar as transações que possuem}

determinado código de conta

 _{Se existir alguma transação para esta conta, mostra o}

número da conta e o saldo existente

(47)

S

UBCONSULTAS

C

ORRELATAS

mysql> select a.account_id, a.product_cd, a.cust_id, a.avail_balance -> from account a

-> where EXISTS

-> (select 1 from -> transaction t

-> where t.account_id = a.account_id -> and t.txn_date='2008-09-22'); Empty set (0.00 sec)

mysql> select a.account_id, a.product_cd, a.cust_id, a.avail_balance -> from account a

-> where EXISTS

-> (select 1 from -> transaction t

-> where t.account_id = a.account_id -> and t.txn_date='2010-05-03');

+---+---+---+---+ | account_id | product_cd | cust_id | avail_balance | +---+---+---+---+ | 1 | CHK | 1 | 1057.75 | +---+---+---+---+ 1 row in set (0.00 sec)

A subconsulta deve retornar algo, não importando os valores

“Select 1” indica que se houver alguma coisa, retorna apenas um valor „1‟. Em geral, quando usa-se EXISTS, usa-se „select 1‟ ou „select *‟ para indicar que existem valores