时间范围的聚合

Question

我有一个数据集，它包含日期{yyyy/mm/dd}、时间{h，m，s}和温度{float}作为单独的列。

我想通过average函数来汇总每天的温度值。

问题是，我不知道如何查询时间属性，例如自动聚合{h,m, (0-5)s}和{h,m, (5-10)s}以及{h,m, (10-15)s}和...。

Answer 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
;

如果您想要所有日期的平均值：

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
;

我认为你的问题的重要部分是按照秒除以范围大小的整数结果进行分组。