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

问题描述

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

数据样本:

<上一页>ID时间戳温度1 2007-09-14 22:56:12 5.392 2007-09-14 22:58:12 5.343 2007-09-14 23:00:12 5.164 2007-09-14 23:02:12 5.545 2007-09-14 23:04:12 5.306 2007-09-14 23:06:12 5.207 2007-09-14 23:10:12 5.398 2007-09-14 23:12:12 5.349 2007-09-14 23:20:12 5.1610 2007-09-14 23:24:12 5.5411 2007-09-14 23:30:12 5.3012 2007-09-14 23:33:12 5.2013 2007-09-14 23:40:12 5.3914 2007-09-14 23:42:12 5.3415 2007-09-14 23:44:12 5.1616 2007-09-14 23:50:12 5.5417 2007-09-14 23:52:12 5.3018 2007-09-14 23:57:12 5.20

主要挑战:

我如何学习代码以每 15 分钟进行一次区分，而由于采样频率不同，没有准确的 15 分钟时间间隔.

解决方案

假设你想在每 15 分钟间隔后重新开始滚动平均:

选择id,温度，avg(temp) over (partition by group_nr order by time_read) as rolling_avg从 (选择身份证，温度，time_read,间隔组，id - row_number() over (partition by interval_group order by time_read) as group_nr从 (选择身份证，time_read,'epoch'::timestamp + '900 seconds'::interval * (extract(epoch from time_read)::int4/900) as interval_group,温度从读数) t1) t2按 time_read 排序；

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

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

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.

Data Sample:

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

Main Challenges:

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.

解决方案

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;

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

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

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