Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
Pessoal, tenho a seguinte dúvida..
Tenho uma tabela onde vai gerando log conforme o cadastro de informações no sistema..
Essas informações são cadastradas e gera uma coluna datetime, para saber o dia e a hora que foi cadastrada.
Porém, eu gostaria de tirar uma média diária, de quanto tempo levou entre um cadastro e outro..
Ex. hoje teve 04 cadastros..
09:00
09:10
09:20
09:30
Assim, a pesquisa me retornaria a resposta de 10 min de média entre um cadastro e outro.
Como fazer isso numa pesquisa direto no banco?
Tabela: interacoes
coluna: data
De preferencia, com a opção de eu escolher o dia da consulta (between), não somente para "hoje".
Muito Obrigado.
Dúvida adicional.. tem como o sistema levar em consideração a 01 hora de almoço do funcionário.. tipo..
Soma as horas, desconta 01 hora, e divide pelo número de entradas.. algo assim?
Ai que tá.. não conheço as entradas do mysql pra fazer esses cálculos.. eu estou aprendendo sobre mysql e a maioria dos códigos que uso, são adaptações que faço de outros que já conheço..
Mas esse tipo de pesquisa nunca fiz.. se alguém puder me ajudar com a construção deste select eu ia agradecer.. não conheço as variáveis..
E para constar.. mota.. a planilha tem um id sequencial sim.. deixei o nome de "id" msm..
Também só para constar.. são cerca de 100 registros por dia.. então as média vai ter que levar isso tudo em conta.
Publica a estrutura da tabela.
/applications/core/interface/imageproxy/imageproxy.php?img=http://s18.postimg.org/5533n0za1/estrutura.jpg&key=9d783df9fcee1d64468bf5f7ec409ee983dd82ce08a8c8f40295c00338d07da3" alt="estrutura.jpg" />
Abraço.
obter a data
select data
from tabela
ligando a anterior
select *
from tabela t1,tabela t2
where t1.id = (t2.id-1)
dif. entre datas
select (t1.data - t2.data)
from tabela t1,tabela t2
where t1.id = (t2.id-1)
A diferença entre datas é em dias logo se multiplicar por 24 e por 60 terá em munutos
select 24 60 (t1.data - t2.data)
from tabela t1,tabela t2
where t1.id = (t2.id-1)
média desta história
select avg (24 60 (t1.data - t2.data) )
from tabela t1,tabela t2
where t1.id = (t2.id-1)
Só pra ver se eu entendi.. as datas estão todas na tabela interacoes.. então ficaria..
>
select avg (24 60 (interacoes.data - interacoes.data) ) from interacoes,interacoes where interacoes.id = (interacoes.id-1)
???
Não, como a tabela e referenciada duas vezes é preciso de um aliais
select avg(24 60 (i2.data - i1.data)) media
from interacoes i1,interacoes i2
where i1.id = (i2.id-1)
Entendi...
O resultado foi esse..
416919732.9735487000
Acredito pq ele puxou os vários meses que o banco tem..
Como faço para limitar para a data X apenas?
Não, fiz bobagem ...
Tem de dividir pois o resultado é em dias.
select avg(((i2.data - i1.data)/(24 * 60)) media
from interacoes i1,interacoes i2
where i1.id = (i2.id-1)
Como faço para limitar para a data X apenas?
Condição no WHERE ...
30 últimos dias (por exemplo)
select avg(((i2.data - i1.data)/(24 * 60)) media
from interacoes i1,interacoes i2
where i1.id = (i2.id-1)
and i1.data >= (now() -30)
Veja o manual do MySql para detalhes do uso do SQL.
Cara primeiro deu erro de sintax, ai eu tirei um dos ( , ali de depois do avg, ai foi.. mas tá retornando NULL.. o que pode ser?
Alguma data nula talvez, acrescente um
and data i1.data is not null
and data i2.data is not null
a query
publica como ficou a query
Fiz assim... mas retornou null igual..
O estranho é que é impossível ter data nula, pq a data é inserida no momento do cadastro da informação.. ela pega a hora e data do ato do cadastro..
SELECT avg( (
i2.data - i1.data
) / ( 24 *60 ) ) media
FROM interacoes i1, interacoes i2
WHERE i1.id = ( i2.id -1 )
AND i1.data >= ( now( ) -30 )
AND i1.data IS NOT NULL
AND i2.data IS NOT NULL
Corrigindo ...
é para multiplicar mesmo , pois
1dia = 24 horas
1 h = 60 minutos
1 dia = 1440 minutos
SELECT avg((24 60 (i2.data - i1.data))) media
FROM interacoes i1, interacoes i2
WHERE i1.id = ( i2.id -1 )
AND i1.data >= ( now( ) -30 )
AND i1.data IS NOT NULL
AND i2.data IS NOT NULL
1) o que isto retorna ?
SELECT i1.id,i1.data,(24 60 (i2.data - i1.data)) x,i2.id,i2.data
FROM interacoes i1, interacoes i2
WHERE i1.id = ( i2.id -1 )
AND i1.data >= ( now( ) -30 )
AND i1.data IS NOT NULL
AND i2.data IS NOT NULL
O normal retornou null..
O que tu botou no 01) retornou..
MySQL retornou um conjunto vazio (ex. zero registros). ( Consulta levou 0.3722 segundos )
se isto
SELECT i1.id,i1.data,(24 60 (i2.data - i1.data)) x,i2.id,i2.data
FROM interacoes i1, interacoes i2
WHERE i1.id = ( i2.id -1 )
AND i1.data >= DATE_SUB(data,INTERVAL 31 DAY)
AND i1.data IS NOT NULL
AND i2.data IS NOT NULL
retornar linhas
rode ...
SELECT avg(24 60 (i2.data - i1.data)) x
FROM interacoes i1, interacoes i2
WHERE i1.id = ( i2.id -1 )
AND i1.data >= DATE_SUB(data,INTERVAL 31 DAY)
AND i1.data IS NOT NULL
AND i2.data IS NOT NULL
Nas duas opções retorna...
#1052 - Column 'data' in where clause is ambiguous
SELECT avg(24 60 (i2.data - i1.data)) x
FROM interacoes i1, interacoes i2
WHERE i1.id = ( i2.id -1 )
AND i1.data >= DATE_SUB(NOW(),INTERVAL 31 DAY)
AND i1.data IS NOT NULL
AND i2.data IS NOT NULL
x = 34186317.6068376100
Muito alto, estranho ... tente com DATEDIFF
SELECT avg(24 60 (DATEDIFF(i2.data,i1.data))) x
FROM interacoes i1, interacoes i2
WHERE i1.id = ( i2.id -1 )
AND i1.data >= DATE_SUB(NOW(),INTERVAL 31 DAY)
AND i1.data IS NOT NULL
AND i2.data IS NOT NULL
x = 10.5832
Isso é minutos??
Se sim.. tenho 2 dúvidas..
1) Ele verifica só os registros, entre o primeiro e o último né? Mas no caso de mais dias.. por ex.. um registro acabando as 18:00 e começando no outro dia as 09:00.. ele não vai ver um espação gigante?
2) Teria como no cálculo, subrair a 01 hora de almoço do funcionário? pois se não der... acho que influencia tb no resultado.. não?
Isso é minutos??
Sim
1) Ele verifica só os registros, entre o primeiro e o último né? Mas no caso de mais dias.. por ex.. um registro acabando as 18:00 e começando no outro dia as 09:00.. ele não vai ver um espação gigante?
Diferença entre as data/hora , qualquer que seja ela. Calculando em minutos
2) Teria como no cálculo, subrair a 01 hora de almoço do funcionário? pois se não der... acho que influencia tb no resultado.. não?
bastaria subtrair 60 minutos da diferença, mas isto remete a uma coisa , isto talvez deva ser agrupado por funcionário...
o que muda tudo pois a sequencia anterior não deve ser a do mesmo id_user ....
a jogada do id -1 não funciona ...
a query calculou a média de minutos entre os lançamento sem levar em conta o id_user
==============
vou pensar uma solução simples com o id_user , veja se vc imagina algo ....
Puts, pior que ver com Id-user seria bacana msm.. pois eu iria colocar isso justamente no relatório dos usuários..
Creio que seja isto ...
SELECT i1.id_user,avg(24 60 (DATEDIFF(i2.data,i1.data))) x
FROM INTERACOES I1, INTERACOES I2
WHERE i1.id_user = i2.id_user
and i1.id < i2.id
AND i1.data >= DATE_SUB(NOW(),INTERVAL 31 DAY)
AND I2.DATA = (SELECT MAX(I3.DATA)
FROM INTERACOES I3
WHERE i3.id_user = i2.id_user
and i3.data < i1.data)Tá rodando a uns 2 minutos e não sai do "carregando".. na primeira tentativa deu a msm coisa..
to executando direto no banco...
/applications/core/interface/imageproxy/imageproxy.php?img=http://s23.postimg.org/m7oykrufv/Sem_t_tulo.jpg&key=499757f5c7d6d5090fdbf3f4361242db737669dde210d8b3125969ae14b26f95" alt="Sem_t_tulo.jpg" />
Não deve ser muito rápida mesmo.
A tabela tem índice ?
A chave deve ser um id sequencial.
Faça um join da tabela com ela mesma de forma que id=(id-1), ou seja um registro será ligadonao anterior.
Calcule a diferença em minutos.
Calcule a média desta diferença.