Aggregation on time range

问题:

I have a data-set that contains date {yyyy/mm/dd} and time {h,m,s} and temperature {float} as an individual columns.

I want to aggregate temperature values for each day by average function.

The problem is that, I don't know how I can query the time attribute to say for example aggregate {h,m, (0-5)s} and {h,m, (5-10)s} and {h,m, (10-15)s} and ..., automatically.

回答1:

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
;

If you want the average for all days:

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
;

I think the important part for your question is to group by the integer result of the division of the seconds by the range size.