Dúvida em formulação de query para SQL

claudiacerqn · 3 de novembro de 2013

Olá!

Estou com dificuldade para formular uma query de acordo com a extração que necessito... Abaixo faço uma descrição do meu banco e do que pretendo extrair:

Meu banco consiste em tabelas com dados anuais, de 2000 a 2009 [cada tabela é referente a um ano]. Em todas estas, tenho os seguintes dados:

ID [cada registro é um BO, e este tem um número dado pelo sistema da SSP]

Cod_tip [código atribuído a cada um dos tipos de crime]

Mes [mês da ocorrência]

Ano [ano da ocorrência]

Lat

Long

Dist [distância do local do BO a um determinado ponto]

Nome_ponto [nome do ponto]

ID_ponto [id do ponto]

cd_cruzamento [que é a chave primária da tabela].

Preciso das seguintes informações:

- Identificação do ponto (nome e id)

- por mês

- 4 variáveis para cada uma das tipologias: 1. total de crimes do tipo X [são 15!] ocorridos dentro do ponto; 2. a 100m do ponto; 3. a 200m do ponto e 4. mais de 200m do ponto.

Alguém aí poderia me ajudar? Já montei várias sintaxes, mas nenhuma deu certo... Obrigada!

Erciley Junior · 4 de novembro de 2013

Olá.

Não vou falar sobre seus campos, que acho que há informação que não deveria estar aí (Nome_ponto) e que pelo o que você falou, há duas chaves na tabela (ID e cd_cruzamento), mas não vou falar disso, hehehe.

Se entendi bem, o que você precisa seria isso abaixo

Este select irá retornar 6 colunas:

1 = ID do ponto

2 = Nome do ponto

3 = Total de crimes por ponto

4 = Total de crimes com distância ATÉ 100 metros do ponto

5 = Total de crimes com distância ENTRE 101 e 200 metros do ponto

6 = Total de crimes com distância MAIOR E IGUAL A 201 metros do ponto

SELECT ID_ponto
      ,Nome_ponto
      ,COUNT(*) AS TOTAL_CRIMES
      ,SUM( IF( DIST <= 100 , 1 , 0 )) AS CRIMES_100
      ,SUM( IF( DIST >= 101 AND DIST <= 200 , 1 , 0 )) AS CRIMES_200
      ,SUM( IF( DIST >= 201 , 1 , 0 )) AS CRIMES_201
FROM TABELA 
WHERE COD_TIP = X
GROUP BY ID_ponto,Nome_ponto;

Bom, não testei nem nada, caso não for isso, me mande uns dados que tento ajudar melhor.

claudiacerqn · 4 de novembro de 2013

Olá Erciley!

Primeiramente, obrigada pela ajuda; esta foi muito útil para eu pensar nesta estrutura; quase cheguei na extração ideal, só precisaria mudar algumas coisinhas. Seguem as especificações:

Quero chegar no seguinte resultado [em colunas]:

* mês;

* total de crimes em cada um dos pontos;

* total de crimes na distância de 100m;

* total de crimes na distância de 200m;

* total de crimes "fora" do perímetro [>200m].

Uma observação: quando espacializei os dados, achei mais fácil criar um buffer que já me retornasse estas distâncias codificadas: 1 para dentro do ponto, 2 para 100m, 3 para 200m e 4 para fora do ponto.

Espero ter sido clara e, mais uma vez, obrigada!

DiF · 4 de novembro de 2013

Olá, só para complementar... e algumas observações.

Primeiramente, não é bom utilizar uma tabela para cada ano sendo que os dados são semelhantes.

Isso fere o princípio das Normas Formas... pois podem haver dados redundantes.

Sugestão:

Modificar a tabela aproximadamente como esta:

Depois utilizar a consulta:


SELECT tbl_ponto.ID
      ,tbl_ponto.Nome
      ,COUNT(*) AS TOTAL_CRIMES
      ,SUM( IF( DIST <= 100 , 1 , 0 )) AS CRIMES_100
      ,SUM( IF( DIST >= 101 AND DIST <= 200 , 1 , 0 )) AS CRIMES_200
      ,SUM( IF( DIST >= 201 , 1 , 0 )) AS CRIMES_201
FROM tbl_ocorrencia
INNER JOIN tbl_crime ON tbl_crime.ID = tbl_ocorrencia.ID_tipo
INNER JOIN tbl_ponto ON tbl_ponto.ID = tbl_ocorrencia.ID_ponto
GROUP BY tbl_ocorrencia.ID_ponto,tbl_ponto.Nome

Então temos este resultado:

Seria isto que você queria?

Outras considerações:

Poderias aproveitar a consulta e criar uma VIEW... para poder abstrair melhor os dados.

ps: creio que ainda tem que melhorar..pois não deu o total de crimes somados, ele só somou por pontos.

Em relação ao banco apresentado acima, ainda poderias esmiuçar mais ainda, criando uma tabela para latitude e longitude.. pois poderia ocorrer N crimes no mesmo local

fazendo isso estaria respeitando a 3 Norma Formal

Att,

Dif

Erciley Junior · 5 de novembro de 2013

Olá Cláudia. Sua dúvida é algo além doq eu ou o DIF falamos?

claudiacerqn · 7 de novembro de 2013

Olá!

Desculpem a demora em responder.

Pois bem, a query quase deu certo. Como o DIF disse, ela não fez a soma dos crimes. Uma coisa que não tem dado certo mesmo é quando incluo a cláusula "SUM( IF( DIST = 1 ))" [como disse, atribuí um código às distâncias, onde: 1 refere-se a crimes ocorridos dentro de um ponto, 2 são crimes a 100m, 3 a 200m e 4 são as ocorrências de fora do perímetro].

Qual é o erro na sintaxe que não me permite dizer DIST=1?

Obrigada por ora.

DiF · 7 de novembro de 2013

Olá!
Desculpem a demora em responder.

Pois bem, a query quase deu certo. Como o DIF disse, ela não fez a soma dos crimes. Uma coisa que não tem dado certo mesmo é quando incluo a cláusula "SUM( IF( DIST = 1 ))" [como disse, atribuí um código às distâncias, onde: 1 refere-se a crimes ocorridos dentro de um ponto, 2 são crimes a 100m, 3 a 200m e 4 são as ocorrências de fora do perímetro].

Qual é o erro na sintaxe que não me permite dizer DIST=1?

Obrigada por ora.

Olá,

Você pode criar uma nova tabela chamada tbl_dist.

nela conterá: ID e dist(varchar) com os registros:

- Dentro do ponto

- A 100m

-A 200m

-A 300m

- A 400m

- Fora do perímetro

então na sua tabela de ocorrencias... modifique o campo dist para que seja o FK de tbl_dist.ID.

então depois só precisa adicionar mais um INNER JOIN para comparar o dist com o ID da tabela dist.

exemplo:


SELECT ...

FROM tbl_ocorrencia

...

INNER JOIN tbl_dist ON tbl_dist.ID = tbl_ocorrencia.ID_dist

fazendo isso respeitamos a 3 FN

Tente e veja se resolve o problema!

No aguardo.

att,

dif

Erciley Junior · 7 de novembro de 2013

Olá a todos.

Não entendi porque dizem que não está totalizando o total de crimes, pois pelo exemplo prático que o DIF fez, a coluna TOTAL_CRIMES está retornando um valor correto ao meu ver.

Olá!
Desculpem a demora em responder.

Pois bem, a query quase deu certo. Como o DIF disse, ela não fez a soma dos crimes. Uma coisa que não tem dado certo mesmo é quando incluo a cláusula "SUM( IF( DIST = 1 ))" [como disse, atribuí um código às distâncias, onde: 1 refere-se a crimes ocorridos dentro de um ponto, 2 são crimes a 100m, 3 a 200m e 4 são as ocorrências de fora do perímetro].

Qual é o erro na sintaxe que não me permite dizer DIST=1?

Obrigada por ora.

Sobre esse IF Cláudia, se você colocou apenas desta forma, então está errado mesmo.

O SUM irá somar UMA UNIDADE se o IF for satisfeito, ou ele NÃO IRÁ SOMAR UMA UNIDADE, caso o IF NÃO seja satisfeito. Ou seja, a sintaxe do seu IF está errada. ^^

SUM( IF( DIST = 1 , 1 , 0 ) ) = SOME UMA UNIDADE QUANDO DIST FOR IGUAL A 1, OU NÃO SOME, CASO DIST SEJA DIFERENTE DE 1

SUM( IF( DIST = 1 ) ) = ERRO DE SINTAXE

hehehehe, bom, é isso aí.

Quanto as dicas do DIF, acredito que seja o mais correto mesmo.

DiF · 8 de novembro de 2013

Na verdade não tem nada de errado... na soma, porém ele apresenta o total de cada!

acho que ela queria o total geral no caso do meu exemplo prático...

deveria voltar total de 12(3 + 5 + 4).. sinceramente isso eu nem lembro mais como se faz.. rsrs

edit:

Acho que matei a charada.

Basicamente utilizei uma subconsulta e a clausula CASE para contar de acordo com o ID da distancia na tabla ocorrencias.

vou postar as imagens e a consulta.

Banco de dados:

Consulta realizada:


SELECT 
      tbl_ponto.Nome
      ,tbl_ocorrencias.ID_dist
      ,CASE
          when tbl_ocorrencias.ID_dist  = 1 then 
	 	    COUNT( tbl_ocorrencias.ID_dist)
	  when tbl_ocorrencias.ID_dist  = 2 then 	
      		COUNT(tbl_ocorrencias.ID_dist) 
	  when tbl_ocorrencias.ID_dist  = 3 then 
            COUNT(tbl_ocorrencias.ID_dist) 
      when tbl_ocorrencias.ID_dist  = 4 then
		    COUNT(tbl_ocorrencias.ID_dist) 
       end as TOTAL_DIST   
      ,tbl_dist.dist
      ,( SELECT COUNT(*) FROM tbl_ocorrencias) as TOTAL_CRIMES


FROM tbl_ocorrencias
INNER JOIN tbl_crime ON tbl_crime.ID = tbl_ocorrencias.ID_tipo
INNER JOIN tbl_ponto ON tbl_ponto.ID = tbl_ocorrencias.ID_ponto
INNER JOIN tbl_dist  ON tbl_dist.ID  = tbl_ocorrencias.ID_dist
GROUP BY  tbl_ocorrencias.ID_dist

Resultado:

Repare que diferente do outro, agora ele está somando o total de registros(pela subconsulta), conta qtos crimes ocorreram em cada distancia cadastrada sendo "dentro do perímetro", a 100m, a 200m e "fora do perímetro"

Seria isto?

No aguardo.

Erciley Junior · 8 de novembro de 2013

Agora entendi DIF, mas não vejo sentido nisso, mas isso já são outros quinhentos, afinal cada um sabe o que quer ^^

Sobre totalização, pesquise sobre GROUP BY WITH ROLLUP, se bem que algum processamento na linguagem sempre vai bem.

DiF · 8 de novembro de 2013

"GROUP BY WITH ROLLUP", funciona com mysql? eu não conheço essa clausula.

O que teoricamente isso faz?

Bem, a totalização até tem sentido... mas como você disse.. poderia muito bem pegar aqueles valores e somar via linguagem.

Mas.. até que eu gostei dessse , método, pois economiza no processamento da linguagem.

Erciley Junior · 8 de novembro de 2013

Oi DIF. Funciona com mysql sim.

Isso é utilizado em conjunto com o Group By para gerar uma linha totalizadora.

Dá uma lida aqui para entender melhor: http://imasters.com.br/artigo/1128/

DiF · 8 de novembro de 2013

Oi DIF. Funciona com mysql sim.
Isso é utilizado em conjunto com o Group By para gerar uma linha totalizadora.

Dá uma lida aqui para entender melhor: http://imasters.com.br/artigo/1128/

Interessante este recurso... vou adotar nos meus projetos futuros.