(Resolvido) Query complexa usando GROUP BY

[luisguzzardi]

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!

[Kakao]

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

Editado Março 28, 2011 por Kakao

[luisguzzardi]

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.

[Kakao]

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 Março 28, 2011 por Kakao

[luisguzzardi]

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 Março 28, 2011 por luisguzzardi

[Kakao]

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
    )

[luisguzzardi]

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

[Kakao]

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 Março 28, 2011 por Kakao

[luisguzzardi]

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 Março 29, 2011 por Denis Courcy
Melhorar o entendimento do código

[Kakao]

Para usar nomes de campos com acentos use crases:

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

[luisguzzardi]

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

[Denis Courcy]

...

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.

[Kakao]

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.

[Denis Courcy]

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.

[luisguzzardi]

...

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 Março 30, 2011 por Denis Courcy
Melhorar entendimento do código

[Kakao]

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.

[luisguzzardi]

Ok Kakao, subi um arquivo com todos os dados do banco e acesso ao sistema, mandei por MP pra você.

obrigado pela ajuda até aqui

[Kakao]

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.

[luisguzzardi]

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 Março 30, 2011 por luisguzzardi

[Kakao]

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.

[luisguzzardi]

sim, verdade, notei que a base era diferente. era um backup e tinha dados mais antigos.

Obrigado pela ajuda, deu certo!