时间范围内的汇总 [英] Aggregation on time range

问题描述

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

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.

问题是，我不知道如何查询时间属性以说例如总计 {h，m，（0-5）s} 和 {h，m，（5-10）s} 和 {h，m，（10-15）s} 和...，自动。

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.

推荐答案

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.

这篇关于时间范围内的汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！