Pessoal, um colega aqui do trabalho chegou a essa solução:
Em Sexta-feira, 6 de Dezembro de 2013 21:21, Andre Santos <[email protected]> escreveu: Chiappa Super dica essa da função MEDIAN ! Muito bom!!! Valeu! :) Por curiosidade, fui procurar nas documentações e vi que ela existe desde a versão 10g (10.1). Tiago, se a gente seguir a lógica da função AVG, realmente não é possível usar tipo DATE ou TIMESTAMP... pois não se pode somar datas e nem dividí-las. Então o algoritmo para achar uma "data/hora média" (ou "mediana") tem de ser outro mesmo. [ ]'s André Em 6 de dezembro de 2013 17:49, J. Laurindo Chiappa <[email protected]> escreveu: > >okdoc : Sobre a MEDIAN, veja lá na doc que é dito que é via Ordenação, então >deduzo que o algoritmo seja , uma vez obtido o menor e o maior valor através >dessa ordenação, se calcula a DIFERENÇA entre esses dois pontos, veja o meu >exemplo que parece que é isso mesmo que ele faz... >Se não é isso que vc precisa, aí é mesmo partir pra conversão para >number/aritmética de datas e similares pra poder usar a AVG.... > > >[]s > >Chiappa > >--- Em [email protected], Tiago de Assis Pimenta <tiagopimenta@...> >escreveu >> > >> Chiappa, >> >> Eu tentava ver a versão executando o "select * from v$version", e não tinha >> acesso, vou tentar via sql*plus. >> >> Quanto ao MEDIAN, vou dar uma olhada com carinho na documentação para ver >> como ele faz a média sem fazer a soma, pois no meu caso seria a média >> aritimética simples, ou seja, se tivesse 3 registros com os seguintes dados >> "01:00:00", "02:00:00", "03:00:00", a média seria a soma de todos os valores >> e dividir pela quantidade total de registros (3), exatamente como o AVG faz. >> >> Obrigado. >> >> >> > > Em Sexta-feira, 6 de Dezembro de 2013 18:22, J. Laurindo Chiappa > <jlchiappa@...> escreveu: > >> >> >> Tiago, em verdade a não ser que o teu DBA seja tão retentivo que não te dê >> acesso nem sequer ao sqlplus, ele não toca Apito nenhum na identificação de >> versão - basta vc conectar no sqlplus com um usuário qualquer que >> NECESSARIAMENTE vc já recebe a versão Exata do database , veja : >> >> [oracle@localhost ~]$ sqlplus zemane/lixao >> >> SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 6 17:57:02 2013 >> >> Copyright (c) 1982, 2010, Oracle. All rights reserved. >> >> Connected to: >> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production >> With the Partitioning, OLAP, Data Mining and Real Application Testing options >> >> SQL> >> >> ==> tá aí, no caso conectei num banco 11.2.0.2.0 , e como os primeiros >> dígitos são a versão major e os segundos são o release, sei que estou num >> 11G R2, yep ??? >> >> André , a coisa é assim : eu lembrava que a Documentação do 11gr2 (a versão >> que assumo sempre) dizia que a AVG aceita NUMBERs (inclusive porque a lógica >> que ela aplica é somar os valores todos e depois dividir pela qtdade de >> valores presentes, média aritmetica simples), *** MAS TAMBÉM **** datatypes >> que possam ser convertidos para NUMBER, o que em tese é o caso de um DATE, >> ele é um NUMBER disfarçado, é isso que permite a Aritmética de datas, >> inclusive.... Por isso que achei que era possível se trabalhar com DATEs, no >> caso do colega transformando o TIMESTAMP em DATE... >> De curiosidade, porém, eu fui fazer o teste nesse mesmo banco da versão >> acima e a minha idéia não se aplica : >> >> SQL> alter session set NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'; >> >> Session altered. >> >> SQL> alter session set NLS_TIMESTAMP_FORMAT='dd/mm/yyyy hh24:mi:ss.ff'; >> >> Session altered. >> >> SQL> create table TAB_TEST1 (c1 date); >> >> Table created. >> >> SQL> insert into TAB_TEST1 values(to_date('01/10/2013 10:10:10', 'dd/mm/yyyy >> hh24:mi:ss')); >> >> 1 row created. >> >> SQL> insert into TAB_TEST1 values(to_date('03/12/2013 20:20:20', 'dd/mm/yyyy >> hh24:mi:ss')); >> >> 1 row created. >> >> SQL> select avg(c1) from TAB_TEST1; >> select avg(c1) from TAB_TEST1 >> * >> ERROR at line 1: >> ORA-00932: inconsistent datatypes: expected NUMBER got DATE >> >> ==> então Realmente, para ter a média aritmética via AVG seria necessário se >> fazer Explicitamente a conversão do DATE para NUMBER, seja via CAST, >> TO_NUMBER(colunadate, 'J'), aritmética de datas (subtraindo SYSDATE, sei >> lá), algo do tipo ... >> >> Agora : também consultando a documentação 11gr2, eu vi que ela dispõe de uma >> OUTRA função de cálculo de médias, chamada MEDIAN, que eu não conhecia das >> versões anteriores : a documentação aponta que essa sujeita não faz soma, >> mas sim Ordena , então funciona para DATEs ** e ** para TIMESTAMPs : >> >> SQL> select median(c1) from TAB_TEST1; >> >> MEDIAN(C1) >> ------------------- >> 02/11/2013 03:15:14 >> >> SQL> >> >> SQL> create table TAB_TESTE2 (c1 timestamp); >> >> Table created. >> >> SQL> insert into TAB_TESTE2 values(to_timestamp('01/10/2013 >> 10:10:10.111111', 'dd/mm/yyyy hh24:mi:ss.ff')); >> >> 1 row created. >> >> SQL> insert into TAB_TESTE2 values(to_timestamp('03/12/2013 >> 20:20:20.333333', 'dd/mm/yyyy hh24:mi:ss.ff')); >> >> 1 row created. >> >> SQL> select median(c1) from tab_teste2; >> >> MEDIAN(C1) >> ---------------------------------------------------------- >> 02/11/2013 03:15:15.222222 >> >> SQL> >> >> ==> OU SEJA, muda a minha Orientação para o Tiago, que passa a ser : se no >> seu exato release lá dele estiver presente a MEDIAN, E SE a lógica dela que >> não faz soma, atende, é Experimentar a MEDIAN.... >> >> []s >> >> Chiappa >> > > --- Em [email protected], Tiago de Assis Pimenta <tiagopimenta@> > escreveu > >> > >> > Concordo com você Chiappa, pena que o DBA aqui da empresa não concorde com >> > isso, enfim, agradeço sua ajuda, já deu uma luz aqui para mim. >> > >> > >> > > > > Em Sexta-feira, 6 de Dezembro de 2013 14:26, Andre Santos > > <andre.psantos.ti@> escreveu: > >> > >> > >> > Pessoal >> > >> > Por favor, corrijam-me se eu estiver errado... mas acho que AVG também não >> > aceita valores "date" (aí não adiantaria usar CAST...). >> > >> > >> > Teria de definir uma lógica (transformando tipos), como o Chiappa sugeriu. >> > >> > >> > [ ]'s >> > >> > André >> > >> > >> > >> > >> > > > > Em 6 de dezembro de 2013 12:35, J. Laurindo Chiappa <jlchiappa@> escreveu: > >> > >> > >> > > >> > >Então : quando vc desenvolve para / utiliza um RDBMS, vc ** >> > >OBRIGATORIAMENTE ** tem que saber pelo menos o Release além da >> > >versão-alvo :não basta só falar "ah, é 11G", pois EXISTEM SIM diferenças >> > >Significativas em diversos casos entre um 11G R1 e um 11G R2, yep ?? >> > >No ponto Específico que vc pergunta, porém, não há diferença >> > >significativa, pois afaik ambos ( 11Gr1 E 11gr2) Não Possuem uma >> > >função/built-in específico para médias de timestamp - LEVANTE porém a >> > >versão e release que vc usa efetivamente, consulte a Doc para confirmar >> > >mas iirc é isso mesmo... >> > > >> > > >> > >[]s >> > > >> > >Chiappa >> > > >> > >--- Em [email protected], Tiago de Assis Pimenta >> > ><tiagopimenta@> escreveu >> > >> >> > > >> > >> Chiappa, >> > >> >> > >> A versão **EXATA** eu não tenho, mas é a 11G e eu tinha pensado >> > >> realmente em transformar tudo em segundos, fazer a média e depois >> > >> converter de volta, mas tinha esperança que existisse uma função >> > >> analítica ou algo parecido que fizesse a média. >> > >> >> > >> Abs >> > >> >> > >> >> > >> >> > > >> > > Em Sexta-feira, 6 de Dezembro de 2013 10:54, J. Laurindo Chiappa >> > > <jlchiappa@> escreveu: >> > > >> > >> >> > >> >> > >> Bem, como vc não nos dá a ** versão exata ** do RDBMS em questão pode >> > >> haver variações (** CONSULTE ** a documentação da sua versão para >> > >> confirmar ou negar) , mas de modo geral afaik a função que calcula >> > >> médias aritméticas, a AVG, ainda não aceita timestamps, apenas DATEs e >> > >> NUMBERs : assim, se vc não precisa da fração de segundos no seu cálculo >> > >> de média, vc simplesmente transforma na sua query os TIMESTAMPs em >> > >> DATEs (via CAST ou demais funções de conversão)... Já se vc Precisa da >> > >> fração de segundos, aí imho o melhor procedimento seria transformar em >> > >> NUMBER o timestamp (número de segundos, certamente) via extract(second >> > >> from colunatimestamp) , e aí a expressão para cálculo seria algo tipo : >> > >> select avg(extract(second from colunatimestamp) ...... >> > >> Claro, isso te dá o número de segundos, SE vc quisesse ter dias, horas, >> > >> minutos, E segundos (com fração), provavelmente divide o inteiro de >> > >> segundos por 86400, o inteiro se segundos por 3600 para obter horas, é >> > >> isso, creio.... >> > >> Não testei, escrevo de cabeça, mas deve ser algo MUITO muito próximo, >> > >> veja aí.... >> > >> >> > >> ´[]s >> > >> >> > >> Chiappa >> > >> >> > > >> > > --- Em [email protected], Tiago de Assis Pimenta >> > > <tiagopimenta@> escreveu >> > > >> > >> > >> > >> > Pessoal, bom dia. >> > >> > >> > >> > Preciso fazer a média de um campo timestamp e gostaria de saber se >> > >> > alguém já fez isso ? Qual a melhor maneira de se fazer ? >> > >> > >> > >> > Abraços >> > >> > >> > >> >> > > >> > > >> > >> > > select id, first_dt + avg(dt - first_dt) avg_dt, avg(dt - first_dt) * 60 from (select id, dt, first_value(dt) over(partition by id) first_dt from (select id, to_date(to_char(hora, 'dd/mm/rrrr hh24:mi:ss'),'dd/mm/rrrr hh24:mi:ss') dt from tiago)) group by id, first_dt; Aparentemente, nos testes que fizemos, deu certo, ai queria compartilhar essa solução e ouvir um possível problema que esse select poderá ter. Abraços.
