select
day,
to_char(date_trunc('minute', "time"), 'HH24:MI') as "minute",
extract(second from "time")::integer / 5 as "range",
avg(temperature) as average
from (
select d::date as day, d::time as "time", random() * 100 as temperature
from generate_series('2012-01-01', '2012-01-03', '1 second'::interval) s(d)
) d
group by 1, 2, 3
order by 1, 2, 3
;
如果您想要所有天的平均值:
select
to_char(date_trunc('minute', "time"), 'HH24:MI') as "minute",
extract(second from "time")::integer / 5 as "range",
avg(temperature) as average
from (
select d::time as "time", random() * 100 as temperature
from generate_series('2012-01-01', '2012-01-03', '1 second'::interval) s(d)
) d
group by 1, 2
order by 1, 2
;
我认为您的问题的重要部分是按秒除以范围大小的整数结果进行分组。