consulta de minutos e transformar para hora e valor referente a hora fazer a média
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
Davison
Curtidas 0
Respostas
Emerson Nascimento
09/06/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.
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.
GOSTEI 0
Davison
09/06/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
GOSTEI 0
Emerson Nascimento
09/06/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;
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;
GOSTEI 0
Davison
09/06/2011
Emerson, muito obrigado
GOSTEI 0