Ir para conteúdo
Fórum Script Brasil
  • 0

(Resolvido) Query complexa usando GROUP BY


luisguzzardi

Pergunta

Olá amigos, preciso de ajuda urgente para criar uma query. Tá muito complicado de eu entender como fazer, pois não tenho muito conhecimento em Mysql.

Seguinte:

Meu sistema é para alarmes monitorados (residencias e comercios).

Os dados vem das centrais de alarmes instaladas nos clientes, via linha telefonica, entram em uma receptora de alarmes e a mesma envia para um servidor mysql, tabela 'Ocorrencias' com os seguintes campos:

OCORR_ID - INT(11) - PRIMARY AUTO INCREMENT

CODIGO_CLIENTE - INT(16)

CODIGO_OCORRENCIA - VARCHAR(50)

DATA_ENTRADA - DATETIME

ZONA - VARCHAR(8)

Exemplo:

OCORR_ID | CODIGO_CLIENTE | CODIGO_OCORRENCIA | DATA_ENTRADA | ZONA

1576276 | 1116 | E401 | 28/03/2011 16:44 | 004

1576275 | 1338 | R401 | 28/03/2011 14:32 | 001

O que eu preciso é listar todos os clientes que estão com seu alarme DESATIVADO.

Acho que essa filtragem deve ser feito através do campo CODIGO_OCORRENCIA, onde esses codigos significam:

E401 = ALARME DESARMADO

R401 = ALARME ATIVADO

E302 = FALHA NA BATERIA (e assim por diante)...

O que me interessa nessa query são os E401 e R401 (alarmes ativados e os desativados)

Preciso então listar cada cliente (acredito que usando o GROUP BY CODIGO_CLIENTE) e se o alarme dele está ativado ou desativado.

AGRUPANDO por CODIGO_CLIENTE, Tenho que pegar o ultimo registro onde CODIGO_OCORRENCIA seja E401 (alarme desarmado)

Em resumo meu resultado final teria que ser isso, listando cliente por cliente que estejam DESATIVADOS (E401):

CODIGO_CLIENTE | CODIGO_OCORRENCIA | DATA_ENTRADA | ZONA

1116 | E401 | 28/03/2011 16:44 | 004

Como nesta tabela entram ocorrencias de todos os clientes, fica tudo misturado.

Olha é bem complexo para o grau de conhecimento que eu tenho no mysql..rss

espero que tenha conseguido passar o que preciso.

Obrigado a todos!

Link para o comentário
Compartilhar em outros sites

20 respostass a esta questão

Posts Recomendados

  • 0
select codigo_cliente, codigo_ocorrencia, max(data_entrada), zona
from ocorrencias
group by codigo_cliente
where codigo_ocorrencia = 'E401'

Não deu certo amigo, pois a query retornou todos os clientes e o ultimo E401 de cada um. Mas tem clientes que tiveram um R401 depois, portanto estariam "Armados".

A sua query retorna todos os clientes como desarmados. Na verdade ela mostra qual foi a ultima desativação de cada um, e não se atualmente está armado ou desarmado.

Link para o comentário
Compartilhar em outros sites

  • 0
select codigo_cliente, codigo_ocorrencia, max(data_entrada) as data_entrada, zona
from ocorrencias o0
group by codigo_cliente 
where 'E401' = (
    select codigo_ocorrencia
    from ocorrencias
    where 
        data_entrada = o0.data_entrada
        and
        codigo_cliente = o0.codigo_cliente
    )

Editado por Kakao
Link para o comentário
Compartilhar em outros sites

  • 0
select codigo_cliente, codigo_ocorrencia, max(data_entrada) as data_entrada, zona
from ocorrencias o0
group by codigo_cliente 
where 'E401' = (
    select codigo_ocorrencia
    from ocorrencias
    where 
        data_entrada = o0.data_entrada
        and
        codigo_cliente = o0.codigo_cliente
    )

Essa query resultou em erro.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c0 where 'E401' = ( select C�DIGO_OCORR�NCIA from Ocorr�ncias where ' at line 1

Cara, o que é esse oO ? é letra ó e zero?

Editado por luisguzzardi
Link para o comentário
Compartilhar em outros sites

  • 0
select codigo_cliente, codigo_ocorrencia, data_entrada, zona
from ocorrencias o
where 
    codigo_ocorrencia = 'E401' 
    and 
    data_entrada = (
        select max(data_entrada)
        from ocorrencias
        where codigo_cliente = o.codigo_cliente
    )

Link para o comentário
Compartilhar em outros sites

  • 0
select codigo_cliente, codigo_ocorrencia, data_entrada, zona
from ocorrencias o
where 
    codigo_ocorrencia = 'E401' 
    and 
    data_entrada = (
        select max(data_entrada)
        from ocorrencias
        where codigo_cliente = o.codigo_cliente
    )

dá erro na query na parte do from ocorrencias o

Link para o comentário
Compartilhar em outros sites

  • 0

Eu testei aqui e não deu erro:

create table ocorrencias (
ocorr_id int(11) primary key auto_increment,
codigo_cliente int(16),
codigo_ocorrencia varchar(50),
data_entrada datetime,
zona varchar(8)
)
;

insert into ocorrencias (codigo_cliente, codigo_ocorrencia, data_entrada, zona)
values
(1116, 'E401', '2011-03-28 16:44', '004'),
(1338, 'R401', '2011-03-28 14:32', '001')
;

select codigo_cliente, codigo_ocorrencia, data_entrada, zona
from ocorrencias o
where
    codigo_ocorrencia = 'E401'
    and
    data_entrada = (
        select max(data_entrada)
        from ocorrencias
        where codigo_cliente = o.codigo_cliente
    )
;
+----------------+-------------------+---------------------+------+
| codigo_cliente | codigo_ocorrencia | data_entrada        | zona |
+----------------+-------------------+---------------------+------+
|           1116 | E401              | 2011-03-28 16:44:00 | 004  |
+----------------+-------------------+---------------------+------+
1 row in set (0.02 sec)

Editado por Kakao
Link para o comentário
Compartilhar em outros sites

  • 0

verdade amigo, desculpe.

Testei aqui no localhost criando a db e inserindo dados conforme postou, deu certinho.

Eu que não estou conseguindo "converter" a sua query para usar no banco de dados do sistema que trabalho.

É que os campos lá são assim:

CREATE TABLE `Ocorrências` (
    `OCORR_ID` INT(11) NOT NULL,
    `CÓDIGO_CLIENTE` VARCHAR(16) NOT NULL,
    `CÓDIGO_OCORRÊNCIA` VARCHAR(50) NOT NULL,
    `DATA_ENTRADA` DATETIME NOT NULL,
    `GRUPO` VARCHAR(5) NULL DEFAULT NULL,
    `ZONA` VARCHAR(8) NULL DEFAULT NULL,
    `atendido` INT(1) NULL DEFAULT NULL,
    `id_cliente` INT(5) NULL DEFAULT NULL,
    `nome_cliente` VARCHAR(150) NULL DEFAULT NULL,
    `tipo_ocorrencia` VARCHAR(35) NULL DEFAULT NULL,
    `grupo_ocorrencia` VARCHAR(35) NULL DEFAULT NULL,
    `descricao_ocorrencia` TEXT NULL,
    `usuario` VARCHAR(30) NULL DEFAULT NULL,
    INDEX `OCORR_ID` (`OCORR_ID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

Só os campos que postei pra você são os que me interessa nessa query.

Sim, é com acentos mesmo. É que uma parte desse sistema era feito em ACCESS 97 e toda a sua db foi convertida para o MYSQL quando atualizaram o sistema.

E as tabelas ficaram todas com acentos que já vieram do access

Você pode me ajudar a fazer a sua query funcionar com os campos acima? desculpe mas realmente tenho pouco conhecimento no mysql, e uma conversão simples da sua query eu não to conseguindo fazer.

Agradeço pela ajuda

Editado por Denis Courcy
Melhorar o entendimento do código
Link para o comentário
Compartilhar em outros sites

  • 0
Para usar nomes de campos com acentos use crases:

select `código_ocorrência` 
from `Ocorrências` 
etc...

hum, entendi agora a questão das crases.

E quanto a query, eu testei, e ela demorou demais pra dar a resposta... na verdade travou meu mysqladmin.

posso estar errado mas como fez pra agrupar por codigo_clientes sem usar o group by.

Obs: essa tabela tem 250.000 registros

Link para o comentário
Compartilhar em outros sites

  • 0
...

E quanto a query, eu testei, e ela demorou demais pra dar a resposta... na verdade travou meu mysqladmin.

posso estar errado mas como fez pra agrupar por codigo_clientes sem usar o group by.

Obs: essa tabela tem 250.000 registros

Luis,

Na verdade o mysqladmin não estava travado. ele estava fazendo um super table scan.

Se a query que você está usando foi esta

select codigo_cliente, codigo_ocorrencia, data_entrada, zona
from ocorrencias o
where
    codigo_ocorrencia = 'E401'
    and
    data_entrada = (
        select max(data_entrada)
        from ocorrencias
        where codigo_cliente = o.codigo_cliente
    )
passada pelo 'Kakao', então crie este(s) índice(s):
CREATE INDEX `ak_ocorrencia_001` ON `ocorrências`(data_entrada, `código_ocorência`)
CREATE INDEX `ak_ocorrencia_002` ON `ocorrências`(`código_cliente`, data_entrada)

A performance da consulta melhorará muito.

Link para o comentário
Compartilhar em outros sites

  • 0

Eu ia sugerir a criação de índice(s). Mas pode-se tentar também:

select 
  a.cod_cliente, 
  a.cod_ocorrencia, 
  a.data_entrada, 
  a.zona
from ocorrencias a 
  inner join (
    select cod_cliente, cod_ocorrencia, max(data_entrada) as data_entrada
    from ocorrencias
  ) as b
on 
  a.cod_cliente = b.cod_cliente and 
  a.cod_ocorrencia = b.cod_ocorrencia and 
  a.data_entrada = b.data_entrada

where
    a.cod_ocorrencia = 'E401'

Testando as duas consultas com e sem índices.

Link para o comentário
Compartilhar em outros sites

  • 0
Eu ia sugerir a criação de índice(s). Mas pode-se tentar também:

select 
  a.cod_cliente, 
  a.cod_ocorrencia, 
  a.data_entrada, 
  a.zona
from ocorrencias a 
  inner join (
    select cod_cliente, cod_ocorrencia, max(data_entrada) as data_entrada
    from ocorrencias
  ) as b
on 
  a.cod_cliente = b.cod_cliente and 
  a.cod_ocorrencia = b.cod_ocorrencia and 
  a.data_entrada = b.data_entrada

where
    a.cod_ocorrencia = 'E401'

Testando as duas consultas com e sem índices.

O caso acima também gerará TABLE SCAN., pois para cada registro lido em a, se não houver um índice correspondente em a e b então haverá uma varredura completa em b para depois informar o resultado em a

Crie indice, na tabela ocorrências, por cod_cliente, cod_ocorrencia, data_entrada.

Crie um segundo índice por cod_ocorrencia, na mesma tabela.

Link para o comentário
Compartilhar em outros sites

  • 0
...

E quanto a query, eu testei, e ela demorou demais pra dar a resposta... na verdade travou meu mysqladmin.

posso estar errado mas como fez pra agrupar por codigo_clientes sem usar o group by.

Obs: essa tabela tem 250.000 registros

Luis,

Na verdade o mysqladmin não estava travado. ele estava fazendo um super table scan.

Se a query que você está usando foi esta

select codigo_cliente, codigo_ocorrencia, data_entrada, zona
from ocorrencias o
where
    codigo_ocorrencia = 'E401'
    and
    data_entrada = (
        select max(data_entrada)
        from ocorrencias
        where codigo_cliente = o.codigo_cliente
    )
passada pelo 'Kakao', então crie este(s) índice(s):
CREATE INDEX `ak_ocorrencia_001` ON `ocorrências`(data_entrada, `código_ocorência`)
CREATE INDEX `ak_ocorrencia_002` ON `ocorrências`(`código_cliente`, data_entrada)
A performance da consulta melhorará muito.
obrigado pela ajuda de voces. Porem nenhuma das querys retornou o resultado esperado. Nesta do Kakao, ele retorna agrupado por cliente, porém pega uma ocorrencia aleatoria E401 na db e exibe. Além disso, ainda repetiu alguns clientes. exemplo do começo do resultado da query do Kakao:
SELECT `código_cliente` , `código_ocorrência` , data_entrada, zona
FROM `Ocorrências` o
WHERE `código_ocorrência` = 'E401'
AND data_entrada = (
SELECT max( data_entrada )
FROM `Ocorrências`
WHERE `código_cliente` = o.`código_cliente` )
RESULTADO: código_cliente | código_ocorrência | data_entrada | zona 1029 E401 2010-10-01 16:18:00 000 5040 E401 2010-10-07 07:56:00 002 1252 E401 2010-10-09 00:49:00 101 1202 E401 2010-10-25 11:09:00 101 1040 E401 2010-11-12 08:02:00 161 1034 E401 2010-12-23 19:33:00 101 1034 E401 2010-12-23 19:33:00 101 1034 E401 2010-12-23 19:33:00 101 1050 E401 2011-12-31 23:50:00 101 1144 E401 2011-01-12 11:56:00 000 O resultado esperado é listar cada cliente que o ultimo evento dele seja um E401 por exemplo se agora chegou esse registro 1034 E401 2010-12-29 19:33:00 101 , este cliente será exibido nessa lista, mas se daqui 5 minutos chegar 1034 R401 2010-12-23 19:38:00 101 significa que o cliente ativou o seu alarme e portanto o cliente 1034 não deverá ser exibido nessa lista. A lista só exibiria os clientes com alarme desarmados. Se achar mais facil, posso dar o login e senha do banco de dados para voces analisarem melhor. E nesta query:
SELECT a.`código_cliente` , a.`código_ocorrência` , a.data_entrada, a.zona
FROM `Ocorrências` a
INNER JOIN (

SELECT `código_cliente` , `código_ocorrência` , max( data_entrada ) AS data_entrada
FROM `Ocorrências`
) AS b ON a.`código_cliente` = b.`código_cliente`
AND a.`código_ocorrência` = b.`código_ocorrência`
AND a.data_entrada = b.data_entrada
WHERE a.`código_ocorrência` = 'E401'

retornou vazio na db

Editado por Denis Courcy
Melhorar entendimento do código
Link para o comentário
Compartilhar em outros sites

  • 0
Nesta do Kakao, ele retorna agrupado por cliente, porém pega uma ocorrencia aleatoria E401 na db e exibe.

Além disso, ainda repetiu alguns clientes.

exemplo do começo do resultado da query do Kakao:

SELECT `código_cliente` , `código_ocorrência` , data_entrada, zona
FROM `Ocorrências` o
WHERE `código_ocorrência` = 'E401'
AND data_entrada = (
SELECT max( data_entrada )
FROM `Ocorrências`
WHERE `código_cliente` = o.`código_cliente` )
RESULTADO: código_cliente | código_ocorrência | data_entrada | zona 1029 E401 2010-10-01 16:18:00 000 5040 E401 2010-10-07 07:56:00 002 1252 E401 2010-10-09 00:49:00 101 1202 E401 2010-10-25 11:09:00 101 1040 E401 2010-11-12 08:02:00 161 1034 E401 2010-12-23 19:33:00 101 1034 E401 2010-12-23 19:33:00 101 1034 E401 2010-12-23 19:33:00 101 1050 E401 2011-12-31 23:50:00 101 1144 E401 2011-01-12 11:56:00 000
Aleatória? Você quer dizer que as que aparecem acima não são as últimas ocorrências? Se o problema for só a repetição é porque existem linhas repetidas na tabela o que eu achei que não ocorreria. Para solucionar a repetição use o distinct:
SELECT  DISTINCT `código_cliente` , `código_ocorrência` , data_entrada, zona
FROM `Ocorrências` o
WHERE `código_ocorrência` = 'E401'
AND data_entrada = (
SELECT max( data_entrada )
FROM `Ocorrências`
WHERE `código_cliente` = o.`código_cliente` )

O resultado esperado é listar cada cliente que o ultimo evento dele seja um E401

por exemplo se agora chegou esse registro 1034 E401 2010-12-29 19:33:00 101 , este cliente será exibido nessa lista, mas se daqui 5 minutos chegar 1034 R401 2010-12-23 19:38:00 101 significa que o cliente ativou o seu alarme e portanto o cliente 1034 não deverá ser exibido nessa lista. A lista

só exibiria os clientes com alarme desarmados.

Eu entendi que só deve ser retornado aqueles cujo último código de ocorrência seja E401.

Se achar mais facil, posso dar o login e senha do banco de dados para voces analisarem melhor.

Se você conseguir fazer um dump da base e disponibilizar em algum lugar, ou se não for muito grande me mandar por email eu analiso.

Link para o comentário
Compartilhar em outros sites

  • 0

O problema é que as datas estão armazenadas com precisão de minuto. Quando mais de uma ocorrência acontece dentro do mesmo minuto não há como saber qual foi a última. Veja o caso do cliente 1034:

mysql> select código_ocorrência, data_entrada from Ocorrências where código_cliente = '1034' order by data_entrada desc limit 10;
+---------------------+---------------------+
| código_ocorrência | data_entrada        |
+---------------------+---------------------+
| E321                | 2010-12-23 19:33:00 |
| E401                | 2010-12-23 19:33:00 |
| R401                | 2010-12-23 19:33:00 |
| E401                | 2010-12-23 19:33:00 |
| R401                | 2010-12-23 19:33:00 |
| E401                | 2010-12-23 19:33:00 |
| R130                | 2010-12-23 19:33:00 |
| E130                | 2010-12-23 19:32:00 |
| R401                | 2010-12-23 19:32:00 |
| E401                | 2010-12-23 19:32:00 |
+---------------------+---------------------+
10 rows in set (1.26 sec)

Apesar de a ocorrência E321 aparecer em primeiro ordenada pela data descendente ela não é necessariamente a última já que existem várias no horário de 19:33:00.

A consulta com o distinct é o melhor que dá para fazer. Repita a consulta acima para cada um dos casos que você postou e verá que onde existe o problema é porque as datas estão repetidas.

RESULTADO:

código_cliente | código_ocorrência | data_entrada | zona

1029 E401 2010-10-01 16:18:00 000

5040 E401 2010-10-07 07:56:00 002

1252 E401 2010-10-09 00:49:00 101

1202 E401 2010-10-25 11:09:00 101

1040 E401 2010-11-12 08:02:00 161

1034 E401 2010-12-23 19:33:00 101

1034 E401 2010-12-23 19:33:00 101

1034 E401 2010-12-23 19:33:00 101

1050 E401 2011-12-31 23:50:00 101

1144 E401 2011-01-12 11:56:00 000

A solução é fazer a entrada da data na base com o máximo de precisão possível, no mínimo com precisão de segundos.

Como você ainda não criou os índices a consulta está muito lenta.

Link para o comentário
Compartilhar em outros sites

  • 0

eu criei os indices, mas de alguma forma deixou mais lenta outras consultas no sistema então, eu exclui.

Agora, se o problema são as datas, não dá para indexar por OCORR_ID que é autoincrement?

Tipo agrupar por cliente, pegar a maior OCORR_ID que tiver sendo E401

Quanto a entrada da data na base não consigo mexer, porque a receptora de alarmes, que recebe a ligação telefonica do painel de alarme do cliente e processa e envia para o mysql já manda a data desse jeito.

Editado por luisguzzardi
Link para o comentário
Compartilhar em outros sites

  • 0

Boa idéia a do ocorr_id. Faça assim:

SELECT `código_cliente` , `código_ocorrência` , data_entrada, zona
FROM `Ocorrências` o
WHERE 
    `código_ocorrência` = 'E401'
    AND 
    ocorr_id = (
        SELECT max(ocorr_id)
        FROM `Ocorrências`
        WHERE `código_cliente` = o.`código_cliente` 
        )
;

Mas as datas mais recentes do cliente 1034 são aquelas que eu listei, basta repetir a consulta. A não ser que a aplicação esteja usando uma base diferente da que você me passou.

Link para o comentário
Compartilhar em outros sites

Visitante
Este tópico está impedido de receber novos posts.


  • Estatísticas dos Fóruns

    • Tópicos
      152k
    • Posts
      651,8k
×
×
  • Criar Novo...