[Resolvido] Busca com AVG em datas

[Samuel Mattos 0]

Bom dia,

Tenho a seguinte sql que me traz o tempo médio de duração de atendimento de um determinado atedente como estou migrando meu sistema de banco de dados em Postgres ele funciona perfeitamente so que em oracle ele não está funcionando. Detalhe eu já faço a busca colocando uma mascara na data. Segue o código dela.

 

SELECT u.nm_usu||' '||u.ult_nm_usu AS nome, SQ.count_atend, SQ.qtde_senhas, SQ.avg_desloc, SQ.avg_atend
			FROM usuarios u	INNER JOIN
			(SELECT a.id_usu, count(DISTINCT a.id_atend) AS qtde_senhas, count(ac.id_atend) AS count_atend,
					to_char((extract(epoch from (AVG(dt_ini - dt_cha)))||' s')::interval, 'HH24:MI:SS') as avg_desloc,
					to_char((extract(epoch from (AVG(dt_fim - dt_ini)))||' s')::interval, 'HH24:MI:SS') as avg_atend
				FROM view_historico_atendimentos a
                                       INNER JOIN view_historico_atend_codif ac
                                           ON ac.id_atend = a.id_atend
				WHERE a.dt_cheg >=  to_date('2012-02-07 15:30:56','yyyy-mm-dd HH24:MI:ss')
   					AND a.dt_cheg <=  to_date('2012-02-07 15:30:56','yyyy-mm-dd HH24:MI:ss')
   					AND a.id_stat = 1
					AND a.id_uni IN (49)
                                               AND a.id_usu = 1
				GROUP BY a.id_usu
			) SQ
			ON (u.id_usu = SQ.id_usu)

 

Andei pesquisando a respeito de estração de estatisticas em cima de datas por Oracle encontrei alguns sites como este http://nandoguedes.com.br/35/oracle-trabalhando-com-datas/ mas com nenhum consegui resolver. Se eu tirar o "::interval" no meu sql developer ele não acusa nenhum erro mas tambem não funciona lançando a seguinte exeção.

erro de SQL: ORA-00907: parêntese direito não encontrado

 

No Aguardo.

[Motta 645]

... tempo médio de duração de atendimento de um determinado atedente ...

 

No Oracle a diferença entre dois campos DATE é um intervalo em dias (ou fração)

 

SELECT (SYSDATE - trunc(sysdate)) 
FROM DUAL  
UNION ALL 
SELECT (trunc(sysdate) - to_date('15/11/1889','dd/mm/yyyy')) 
FROM DUAL  

(SYSDATE-TRUNC(SYSDATE)) 
------------------------ 
0,4406712962962962962962962962962962962963 
44646                    

 

Creio que isto substitui o INTERVAL.

[Samuel Mattos 0]

A questão é estou trabalhando com timestamp verifiquei que para converter um time stamp em date tenho que antes transformar ele em char a exemplo.

 

 (to_date(to_char(x, 'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
hh24:mi:ss') 

 

Vou verificar o seu código acima e colocar na sql em questão e ja respondo se deu certo.

valeu

[Motta 645]

DECLARE
 T1 TIMESTAMP := TO_TIMESTAMP ('10-09-11 04:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF');
 T2 TIMESTAMP := TO_TIMESTAMP ('10-02-12 14:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF');
 N NUMBER; 
BEGIN
 DBMS_OUTPUT.PUT_LINE((T2-T1));
END;  

+000000153 10:00:00.000000000

DECLARE
 T1 TIMESTAMP := TO_TIMESTAMP ('10-02-12 11:10:10.121734', 'DD-MM-RR HH24:MI:SS.FF');
 T2 TIMESTAMP := TO_TIMESTAMP ('10-02-12 14:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF');
 N NUMBER; 
BEGIN
 DBMS_OUTPUT.PUT_LINE((T2-T1));
END;

+000000000 03:00:00.001266000

 

Um Exemplo.

Não usamos TIMESTAMP aqui, nunca usei isto tipo, mas não creio que seja bicho de sete cabeças.

[Samuel Mattos 0]

Ok desculpe a demora pra responder, enxuguei um poco o código e agora ele me retornar um erro.

Erro de SQL: ORA-01830: a imagem do formato da data termina antes de converter a string de entrada inteira

01830. 00000 - "date format picture ends before converting entire input string"

 

O sql é o seguinte.

SELECT u.nm_usu||' '||u.ult_nm_usu AS nome, SQ.count_atend, SQ.qtde_senhas, SQ.avg_desloc, SQ.avg_atend
			FROM usuarios u	INNER JOIN
			(SELECT a.id_usu, count(DISTINCT a.id_atend) AS qtde_senhas, count(ac.id_atend) AS count_atend,
					AVG(to_date(dt_ini) - to_date(dt_cha)) as avg_desloc,
					AVG(to_date(dt_fim) - to_date(dt_ini)) as avg_atend
				FROM view_historico_atendimentos a
                                       INNER JOIN view_historico_atend_codif ac
                                           ON ac.id_atend = a.id_atend
				WHERE a.dt_cheg >=  to_date('2012-02-07 15:30:56','yyyy-mm-dd HH24:MI:ss')
   					AND a.dt_cheg <=  to_date('2012-02-11 15:30:56','yyyy-mm-dd HH24:MI:ss')
   					AND a.id_stat = 8
					AND a.id_uni IN (29)
                                               AND a.id_usu = 1
				GROUP BY a.id_usu
			) SQ
			ON (u.id_usu = SQ.id_usu)

 

Verifiquei a mascara da data e está correta, tentei colocar uma mascara dentro do avg tambem so que não deu certo.

O que pode estar errado neste sql?

[Motta 645]

Deve ser mesmo erro na conversão, tente com o CAST

 

AVG((cast(dt_ini) as date)  -  (cast(dt_cha) as date)) as avg_desloc,

[Samuel Mattos 0]

Na realidade quando eu subtraio duas datas do tipo "timestamp" ele me retorna um interval, com este interval eu estraio os dias, hora, minuto e os segundos e coloco no avg. Agora estou procurando como formatar um interval no formato de data, quando terminar eu posto aqui valeu raça.

 

select
   avg(extract(second from intrvl)
       + extract(minute from intrvl) * 60
       + extract(hour from intrvl) * 60 * 60
       + extract(day from intrvl) * 60 * 60 * 24) average
from (
   select (ha.dt_fim - ha.dt_ini) intrvl 
   FROM view_historico_atendimentos ha WHERE ha.id_atend = 153
)

[Motta 645]

DECLARE
 T1 TIMESTAMP := TO_TIMESTAMP ('10-09-11 04:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF');
 T2 TIMESTAMP := TO_TIMESTAMP ('10-02-12 14:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF');
 N NUMBER; 
 D DATE;
BEGIN
 SELECT (CAST(T2 AS DATE) -  CAST(T1 AS DATE))
 INTO N
 FROM DUAL;
 DBMS_OUTPUT.PUT_LINE(N);
END;  


153,416666666666666666666666666666666667

[Samuel Mattos 0]

Cara consegi resolver com uma baita gambiarra literalmente, segue o código.

 

SELECT u.nm_usu||' '||u.ult_nm_usu AS nome, SQ.count_atend, SQ.qtde_senhas, SQ.avg_desloc, SQ.avg_atend
			FROM usuarios u	INNER JOIN
			(SELECT a.id_usu, count(DISTINCT a.id_atend) AS qtde_senhas, count(ac.id_atend) AS count_atend,
       to_char(extract(HOUR FROM numtodsinterval(avg( dt_avg(dt_ini, dt_cha )), 'second')), 'fm00') ||' : '||
       to_char(extract(MINUTE FROM numtodsinterval(avg( dt_avg(dt_ini, dt_cha )), 'second')), 'fm00') ||' : '||
        to_char(extract(SECOND FROM numtodsinterval(avg( dt_avg(dt_ini, dt_cha )), 'second')), 'fm00') as avg_desloc,
         to_char(extract(HOUR FROM numtodsinterval(avg( dt_avg(dt_fim, dt_ini )), 'second')), 'fm00') ||' : '||
       to_char(extract(MINUTE FROM numtodsinterval(avg( dt_avg(dt_fim, dt_ini )), 'second')), 'fm00') ||' : '||
           to_char(extract(SECOND FROM numtodsinterval(avg( dt_avg(dt_fim, dt_ini )), 'second')), 'fm00') as avg_atend
				FROM view_historico_atendimentos a
                                       INNER JOIN view_historico_atend_codif ac
                                           ON ac.id_atend = a.id_atend
				WHERE a.dt_cheg >=  to_date('2012-02-14 00:00:00','yyyy-mm-dd HH24:MI:ss')
   					AND a.dt_cheg <=  to_date('2012-02-14 23:59:59','yyyy-mm-dd HH24:MI:ss')
   					AND a.id_stat = 8
					AND a.id_uni IN (29)
                                               AND a.id_usu = 1
				GROUP BY a.id_usu
			) SQ
			ON (u.id_usu = SQ.id_usu)

 

Onde dt_avg é a seguinte função:

 

create or replace
FUNCTION dt_avg(dt1 timestamp, dt2 timestamp)
 RETURN number is  
 reg_gap number := null;

 BEGIN
        reg_gap := (extract( day from (dt1 - dt2) )*24*60*60)+ 
        (extract( hour from (dt1 - dt2) )*60*60)+
        (extract( minute from (dt1 - dt2) )*60)+
         (extract( second from (dt1 - dt2)));
      RETURN  reg_gap;
      END;

 

Se eu não usar aquelas expresão de to_char para colocar a date no formato que eu quero ele me retorna algo do tipo.

+000000000 00:01:29.000000

Se alguem ainda tiver alguma sugestão fico a disposição mas por hora a sql está atendendo ao que eu quero.