Calcular o tempo m�dio

12/03/2020

Ol�, como fa�o para calcular o tempo m�dio, por exemplo a tabela que irei extrair a informa��o possui apenas um campo DATA_HORA.
A query que utilizei para ver se tr�s as infos corretas foi:

SELECT MIN(DATA_HORA) AS DATA_INI, MAX(DATA_HORA) AS DATA_FIM
FROM INVENTARIO_HISTORICO

J� tentei utilizar o AVG para tirar a media n�o deu certo, e �ltima observa��o � que n�o posso exibir as colunas de DATA_INI e DATA-FIM, apenas a TEMPO_MEDIO.

Andrey Cesar

Curtir t�pico + 0

Responder

Posts

12/03/2020

Stella Oliveira

Para SQL SERVER:

SELECT CAST(CAST(CAST(CAST(E3TimeStamp as float)*1440 as bigint) as float)/1440 as datetime) as Minuto, AVG(Campo) as M�dia
FROM Tabela
GROUP BY CAST(CAST(CAST(CAST(E3TimeStamp as float)*1440 as bigint) as float)/1440 as datetime)

� M�dias por hora:

SELECT CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)/24 as datetime) as Hora, AVG(Campo) as M�dia
FROM Tabela
GROUP BY CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)/24 as datetime)

� M�dias por dia:

SELECT CAST(FLOOR(CAST(E3TimeStamp as float)) as datetime) as Dia, AVG(Campo) as M�dia
FROM Tabela
GROUP BY CAST(FLOOR(CAST(E3TimeStamp as float)) as datetime)

Responder

Gostei + 0

12/03/2020

Emerson Nascimento

tempo m�dio de qu�, se voc� n�o tem um intervalo?

Responder

Gostei + 0

13/03/2020

Andrey Cesar

Resolvi dessa maneira e deu certo o resultado esperado:

SELECT U.NOME AS NOME DO USUARIO, Q1.INTERVALO / Q1.QTD AS MEDIA
FROM USUARIO U
LEFT JOIN
(SELECT ID_[CAMPO], ID_[CAMPO], MAX(DATA_HORA) - MIN(DATA_HORA) AS INTERVALO, COUNT(*) AS QTD
FROM [TABELA]
GROUP BY 1,2) Q1

Responder

Gostei + 0