基于PostgreSQL中时间戳的移动平均 [英] Moving Average based on Timestamps in PostgreSQL

问题描述

我想通过时间戳执行移动平均。
我有两列：温度和时间戳记（时间日期），我想基于每15分钟连续的温度观测值执行移动平均值。换句话说，选择数据以基于15分钟的时间间隔执行平均值。此外，对于不同的时间序列，可能有不同数量的观测值。我的意思是所有窗口大小都是相等的（15分钟），但是每个窗口中的观察值可能不同。
例如：
对于第一个窗口，我们必须计算n个观测值的平均值，而对于第二个窗口，则需要计算n + 5个观测值的平均值。

I wanted to perform moving average through timestamps. I have two columns: Temperature and timestamps (time-date) and I want to perform the moving average based on every 15 minutes successive temperature observations. In other words, selecting data to perform the average based on 15 minutes time interval. Moreover, it is possible to have different number of observations for different time sequences. I meant all the window sizes are equal (15 minutes) but it is possible to have different number of observations in each window. For example: For a first window we have to calculate the average of n observation and for second window calculate the average of the observation for n+5 observation.

数据样本：

ID   Timestamps          Temperature
1    2007-09-14 22:56:12 5.39
2    2007-09-14 22:58:12 5.34
3    2007-09-14 23:00:12 5.16
4    2007-09-14 23:02:12 5.54
5    2007-09-14 23:04:12 5.30
6    2007-09-14 23:06:12 5.20
7    2007-09-14 23:10:12 5.39
8    2007-09-14 23:12:12 5.34
9    2007-09-14 23:20:12 5.16
10   2007-09-14 23:24:12 5.54
11   2007-09-14 23:30:12 5.30
12   2007-09-14 23:33:12 5.20
13   2007-09-14 23:40:12 5.39
14   2007-09-14 23:42:12 5.34
15   2007-09-14 23:44:12 5.16
16   2007-09-14 23:50:12 5.54
17   2007-09-14 23:52:12 5.30
18   2007-09-14 23:57:12 5.20

主要挑战：

如何学习识别代码每15分钟一次，但由于采样频率不同，没有确切的15分钟时间间隔。

How I can learn the code to discriminate every 15 minute while there are not exact 15 minutes time intervals due to different sampling frequency.

推荐答案

假设您要重新启动每15分钟间隔后的滚动平均值：

Assuming you want to restart the rolling average after each 15 minute interval:

select id, 
       temp,
       avg(temp) over (partition by group_nr order by time_read) as rolling_avg
from (       
  select id, 
         temp,
         time_read, 
         interval_group,
         id - row_number() over (partition by interval_group order by time_read) as group_nr
  from (
    select id, 
           time_read, 
           'epoch'::timestamp + '900 seconds'::interval * (extract(epoch from time_read)::int4 / 900) as interval_group,
           temp
    from readings
  ) t1
) t2
order by time_read;

它基于 Depesz的解决方案按时间范围分组：

It is based on Depesz's solution to group by "time ranges":

这是一个SQLFiddle示例： http://sqlfiddle.com/#!1/0f3f0/2

Here is an SQLFiddle example: http://sqlfiddle.com/#!1/0f3f0/2

这篇关于基于PostgreSQL中时间戳的移动平均的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！