Array
(
)

consulta de minutos e transformar para hora e valor referente a hora fazer a média

Davison
   - 09 jun 2011


Olá, estou precisando fazer uma consulta em uma base de dados que contém as colunas "data/hora" = timestamp unix, codigo = int, valor = float, sendo que os dados armazenados são de 5 em 5 minutos e eu precisaria ter um resultado de hora em hora dos dias com a média do valor e referente ao período de 1 mês.
Por favor você teria alguma resposta para isso, não sei como fazer se será uma function ou uma procedure ou view.
Muito obrigado e parabéns pelo fourm que tem sido de grande ajuda aos colegas de sql.
 Davison

Emerson
   - 10 jun 2011

tente algo assim:

SELECT
  CONVERT(VARCHAR(10), DATA, 102) DATA,
  DATEPART(HOUR, DATA) HORA,
  SUM(VALOR) TOTAL,
  (SUM(VALOR) / COUNT(*)) MEDIA
FROM
  TABELA
WHERE
  DATA BETWEEN DATAINI AND DATAFIM
GROUP BY
  CONVERT(VARCHAR(10), DATA, 102),
  DATEPART(HOUR, DATA)

se não for exatamente isso, pelo menos deve te dar uma idéia de como resolver.

Davison
   - 10 jun 2011

Emerson, valeu pela ajuda.   Então esqueci de mencionar que o data type da coluna Date ("timestamp unix") está como bigint, não consigo utilizar o convert varchar.   Obrigado mais uma vez

Emerson
   - 10 jun 2011

então converta para o padrão 'humano':

SELECT
  CONVERT(
     VARCHAR(10),
     dateadd(second,
               ((DATA / 86400.0000000000) - (DATA / 86400)) * 24 * 60 * 60,
               dateadd( day, (DATA / 86400), '1970-1-1' )
     ),
     102
  ) DATA,
  DATEPART(
     HOUR,
     dateadd(second,
               ((DATA / 86400.0000000000) - (DATA / 86400)) * 24 * 60 * 60,
               dateadd( day, (DATA / 86400), '1970-1-1' )
     )
  ) HORA,
  SUM(VALOR) TOTAL,
  (SUM(VALOR) / COUNT(*)) MEDIA
FROM
  TABELA
WHERE
  DATA BETWEEN DATAINI AND DATAFIM
GROUP BY
  1,
  2

observações:
- poderia ter sido feito direto, com dateadd(second, DATA, '1970-1-1'), mas poderia haver um erro devido ao conteúdo do campo DATA (bigint), que dessa forma não poderia ser maior que 2.147.483.647 (int).
da forma como está apresentado o valor de DATA poderá ser de até 185.542.587.100.800;

então, da forma mais simples, ficaria assim:

SELECT
  CONVERT(VARCHAR(10), dateadd(second, DATA, '1970-1-1'), 102) DATA,
  DATEPART(HOUR, dateadd(second, DATA, '1970-1-1')) HORA,
  SUM(VALOR) TOTAL,
  (SUM(VALOR) / COUNT(*)) MEDIA
FROM
  TABELA
WHERE
  DATA BETWEEN DATAINI AND DATAFIM
GROUP BY
  1,
  2

- note que em algumas operações a divisão têm indicação de casas decimais e em outras não têm. não mude isso, senão haverá erro na conversão da data/hora;

Davison
   - 12 jun 2011

Emerson, muito obrigado