Calcular média

[rdpacato 2]

Olá tenho que fazer uma consulta em um campo e somar todos os valores (tirando os zeros e letras) e dividir pela quantidade (sem os zeros e letras)

 

 

ou seja:

 

1 , 2 , 3 , 0 , erro, 6 = 12/4 = 3

 

 

tem como fazer isso?

 

Se sim, teria que consultar isso em intervalos... tipo, tem um campo que vejo uma mensagem de início, outra de leitura e a de final... assim

Se tiver dentro do início e fim eu tenho que fazer esse cálculo...

[rdpacato 2]

Tenho essa consulta:

SELECT id_metria, cod_dispositivo , id_mensagem, id_consumption, dt_hora_sistema, id_la, id_lo, consumo
FROM tb_telemetria
WHERE cod_dispositivo = 'QAF2000137'
AND id_metria = (SELECT MAX (id_metria) AS id_metria FROM tb_metria WHERE cod_dispositivo = 'QAF2' AND id_mensagem = 'GTIGN')

A mensagem GTIGN é a primeira... depois vem a GTBD (que é enquanto tem dado fica mandando) a final é a GRIGF

 

 

Só que tem várias destas inicio e fim... queria pegar tudo entre a última inicial e última final para poder pegar o valor consumption e me retornar

[Motta 645]

Uma média pode ser calculado pelo agrupador AVG.

 

Mas o campo não parece ser numérico, o que é estranho.

 

Em todo caso tente testar se é numérico* e tente e avg.

 

 

 

*

trim(translate(campo,'0123456789','          ')) is null 

deve resolve a verificação de numérico

 

 

---

Mas eu reveria este modelo.

[rdpacato 2]

Entendi...

 

Mas o meu problema está em consultar entre as mensagens... pois tem que selecionar o bloco como condição...

 

e o campo está como varchar, pois ele recebe algumas mensagens de erro e valores com ponto (.) .. e não pode mudar isso :(

[Motta 645]

Eu que não entendi, pode fazer um exemplo ?

[rdpacato 2]

tipo.. como se fosse data.... quando usamos between para pesquisar entre aquele período?

 

 

Então, só que no meu caso é por mensagens, tem a mensagem de início, a do meio e a final:

Exemplo>

 

Inicial

meio

meio

meio

Final

 

então tenho que ler até encontrar a final, então pego todo o campo e faço a média dele

[Motta 645]

Tentou um MAX para fazer um "unpivot" na tabela, algo como :

select cod_dispositivo, 
       max(case when id_metria = 'inicio' then dt_hora_sistema else null end) inicio,
       max(case when id_metria = 'meio' then dt_hora_sistema else null end) meio,
       max(case when id_metria = 'final' then dt_hora_sistema else null end) final
from   tb_telemetria 
group by cod_dispositivo

Daí se calcula , tempo , médio etc.

[rdpacato 2]

Oi Motta, obrigado pela força... .mas ainda não é isso... (ou não estou entendendo como usar, que deve ser o provável rsrs), mas o que me passou pega somente o último dado de cada mensagem, correto?

 

 

 

 

Fiz esse script:

select * from tb_telemetria
where id_ult_mensagem in ('inicio','meio','final')
and cod_dispositivo = 'QAG0034306'
and cod_usuario = 6
AND to_char( dt_hora_sistema, 'DD/MM/YYYY') BETWEEN ('07/09/2014') AND ('30/10/2014')
AND to_char( dt_hora_sistema, 'HH24:MI') BETWEEN ('00:00') AND ('22:22')
order by dt_hora_sistema;

Assim esta trazendo tudo dentro das datas... mas o que preciso fazer:

 

1º - saber quantos trios veio nesse período (inicio, meio e fim, tipo é uma viagem, então quantas viagens fez?)

 

2º - Por exemplo, deu 3 viagens, em cada viagem, eu pego os valores de inicio até o final e faço a média de um campo

 

Exemplo:

1ª viagem:

inicio (começo a pegar o valor do campo)

meio (vou pegando o valor)

meio (vou pegando o valor)

final (mostro a media)

 

2ª viagem:

inicio (começo a pegar o valor do campo)

meio (vou pegando o valor)

meio (vou pegando o valor)

final (mostro a media)

 

E caso a 3ª viagem não estiver completa por causa do período, eu não mostro

 

Não sei se é melhor tratar isso na aplicação (java) do que no BD... o que vc acha Motta?

[Motta 645]

faz um exemplo básico numérico

 

publica a descrição (básica) das tabelas

[rdpacato 2]

char integer varchar varchar timestamp without

dispositivo usuario campo mensagem data

"QAG0034306"; 6; " "; "INICIO"; "1969-12-31 17:59:59"

"QAG0034306"; 6; "Inf"; "MEIO"; "2014-10-08 08:44:29"

"QAG0034306"; 6; "Inf"; "MEIO"; "2014-10-08 08:44:59"

"QAG0034306"; 6; "11."; "MEIO"; "2014-10-08 08:45:29"

"QAG0034306"; 6; "Inf"; "MEIO"; "2014-10-08 08:45:59"

"QAG0034306"; 6; "5.7"; "MEIO"; "2014-10-08 08:46:29"

"QAG0034306"; 6; "6.3"; "MEIO"; "2014-10-08 08:46:59"

"QAG0034306"; 6; "9.2"; "MEIO"; "2014-10-08 08:47:29"

"QAG0034306"; 6; "25.2"; "MEIO"; "2014-10-08 08:47:59"

"QAG0034306"; 6; "25.2"; "MEIO"; "2014-10-08 08:48:29"

"QAG0034306"; 6; "25.2"; "FINAL"; "1969-12-31 17:59:59"

"QAG0034306"; 6; " "; "INICIO"; "1969-12-31 17:59:59"

"QAG0034306"; 6; " "; "MEIO"; "2014-10-08 09:04:38"

"QAG0034306"; 6; " "; "MEIO"; "2014-10-08 09:05:08"

"QAG0034306"; 6; "13.6"; "MEIO"; "2014-10-08 09:05:38"

"QAG0034306"; 6; "Inf"; "MEIO"; "2014-10-08 09:06:08"

"QAG0034306"; 6; "Inf"; "MEIO"; "2014-10-08 09:06:38"

"QAG0034306"; 6; "Inf"; "MEIO"; "2014-10-08 09:07:08"

"QAG0034306"; 6; "17.3"; "MEIO"; "2014-10-08 09:07:38"

"QAG0034306"; 6; "12.8"; "MEIO"; "2014-10-08 09:08:08"

"QAG0034306"; 6; "5.5"; "MEIO"; "2014-10-08 09:08:38"

"QAG0034306"; 6; "39.8"; "MEIO"; "2014-10-08 09:09:38"

"QAG0034306"; 6; "39.8"; "FINAL"; "1969-12-31 17:59:59"

 

[Motta 645]

Se quer a média de tempo entre o Final e o Inicio de um dispoditivo ?

 

Se quer a média de campo (quando numérico) ?

 

Com base neste dados como seria o cálculo desta média ?

[rdpacato 2]

Se quer a média de tempo entre o Final e o Inicio de um dispoditivo ?

Não, esse quero ir somando a cada passo, da linha um até a linha 2 = soma, linha 2 a linha 3 = soma, soma de um para outro

 

Se quer a média de campo (quando numérico) ?

Isso, preciso da média do campo

 

Com base neste dados como seria o cálculo desta média ?

somar todos os valores do campo (sem o zerados e letras e vazios) e dividir pela quantidade, o resultado tenho que dividir por 100

 

Mas isso dentro de inicio até o final, quando achar outro inicio começa tudo de novo...

 

E tenho que saber quantos inicio e final deram... tipo, deu 3 viagens (3 inico e final)

[Motta 645]

Acho que a solução tem de passar por uma tabela temporária alimentada por um cursor desta tabela ordenado

pelo dispositivo e data.

 

Mas ainda não entendi o cálculo

 

para o primeiro inicio / fim do temos

"QAG0034306";        6;        " ";        "INICIO";    "1969-12-31 17:59:59"
"QAG0034306";        6;        "Inf";        "MEIO";        "2014-10-08 08:44:29"
"QAG0034306";        6;        "Inf";        "MEIO";        "2014-10-08 08:44:59"
"QAG0034306";        6;        "11.";        "MEIO";        "2014-10-08 08:45:29"
"QAG0034306";        6;        "Inf";        "MEIO";        "2014-10-08 08:45:59"
"QAG0034306";        6;        "5.7";        "MEIO";        "2014-10-08 08:46:29"
"QAG0034306";        6;        "6.3";        "MEIO";        "2014-10-08 08:46:59"
"QAG0034306";        6;        "9.2";        "MEIO";        "2014-10-08 08:47:29"
"QAG0034306";        6;        "25.2";        "MEIO";        "2014-10-08 08:47:59"
"QAG0034306";        6;        "25.2";        "MEIO";        "2014-10-08 08:48:29"
"QAG0034306";        6;        "25.2";        "FINAL";    "1969-12-31 17:59:59"

a média seria entre

11.0
5.7
6.3
9.2
25.2
25.2
25.2

média 17,97 

???

[rdpacato 2]

a soma da 107,8 ai tem que dividir pela quantidade de elementos 107,8/7 = 15,4 ai divido por 100 = 15,4/100 = 0,154

 

 

No caso tabela temporária, como funciona? ela fica armazenando os selects???

[Motta 645]

Confuso o modelo.

 

Tem um identificador para cada grupo de inicio.meios.fim ?

 

Pq o inicio é em 1969 ? Nao tem a informacao ?

 

Nao poderia so fazer uma media dos valores por dispositivo ?

 

O que significa os valores nao numéricos , pq se misturou a valores numéricos ?

[rdpacato 2]

o identificador é a própria mensagem inicio, meio e fim, é a única que da para tratar...

 

 

Estes dados vem de um dispositivo, e ele envia as vezes comandos, por isso do campo ter que aceitar letras e vazio...

 

não da por dispositivo.. estes dados são alterados... essa média tem que ser feita a cada consulta....

[Motta 645]

Supondo os dados do post #10 como deveria ser a saída dos dados com a média calculada ?

[rdpacato 2]

viagem 1:

traria a lista e na última posição:

"QAG0034306"; 6; "25.2"; "FINAL"; "1969-12-31 17:59:59" 0,154 (media) 4min (viagem)

 

 

mas to achando melhor tratar na aplicação né motta? pois no BD para separar esse resultado ficará algo imenso...

 

 

outro problema que estou encontrando é com a data... estou tentando fazer a seguinte condição:

 

AND to_char( dt_hora_dispositivo, 'DD/MM/YYYY') BETWEEN ('01/09/2014') AND ('29/09/2014')
AND to_char( dt_hora_dispositivo, 'HH24:MI') BETWEEN ('00:00') AND ('23:59')

 

se eu consulto assim vem resultado (pois tem valores do dia 26/09 até 03/10) , mas se consulto assim:

 

 

AND to_char( dt_hora_dispositivo, 'DD/MM/YYYY') BETWEEN ('01/09/2014') AND ('02/10/2014')
AND to_char( dt_hora_dispositivo, 'HH24:MI') BETWEEN ('00:00') AND ('23:59')

 

não retorna nada...

[Motta 645]

MAS COMO OS DADOS DA MÉDIA CALCULADA DEVERIAM SAIR PARA OS DADOS DO POST # 10 ?

[rdpacato 2]

Então motta... estou perdidão com isso.. não consegui desenvolver essa lógica.... não sei como...

 

Eu pensei nisso, como um campo novo (criado na hora) assim mostraria na última linha como mostrei la.. como resultado....