滚动平均值PostgreSQL

Question

滚动平均值PostgreSQL

5

我正在运行Postgres 9.2，有一张类似以下表的大表：

CREATE TABLE sensor_values
(
  ts timestamp with time zone NOT NULL,
  value double precision NOT NULL DEFAULT 'NaN'::real,
  sensor_id integer NOT NULL
)

我会不断收到系统中传入的数值，每分钟可能会有很多个。我想要维护一个最近200个值的滚动标准差/平均数，以便确定进入系统的新值是否在平均数的3个标准差内。为了做到这一点，我需要不断更新最近200个值的当前标准差和平均数。

由于表格可能有数亿行，我不想按时间对一个传感器最后的200行进行排序，然后针对每一个新值执行vg（value）、var_samp（value）操作。我认为更新标准差和平均数会更快。

我已经开始编写一个PL/pgSQL函数，在每个特定传感器进入新值时更新滚动方差和平均数。

我可以使用类似下面的代码来实现：

newavg = oldavg + (new_value - old_value)/window_size
new_variance += (new_value-old_value)*(new_value-newavg+old_value-oldavg)/(window_size-1)

这是基于http://jonisalonen.com/2014/efficient-and-accurate-rolling-standard-deviation/的方法。

窗口的大小为200个值。old_value是窗口的第一个值。当有新值进来时，我们将窗口向前移动一位。在得到结果后，我会为传感器存储以下值。

The first value of the window.
The mean average of the window values.
The variance of the window values.

这样我就不必不断获取最后200个值并进行求和等操作。当新的传感器值进来时，我可以重复使用这些值。

我的问题是，当首次运行时，我没有传感器的先前窗口数据，即上面的三个值，因此我必须采用较慢的方式来处理。

类似于：

WITH s AS
        (SELECT value FROM sensor_values WHERE sensor_values.sensor_id = $1  AND ts >= (NOW() - INTERVAL '2 day')::timestamptz ORDER BY ts DESC LIMIT 200)
    SELECT avg(value), var_samp(value)  INTO last_window_average, last_window_variance FROM s;

但是我该如何从那个select语句中获取最后一个值（最早的）并保存？我能在PL/pgSQL中访问s中的第一行吗？

我以为PL/pgSQL会是更快/更清晰的方法，但也许在客户端代码中做这个操作会更好？有更好的方法执行此类型的滚动统计更新吗？

- Glenn Pierce

1

关于 avg(value) over (partition by sensor_id order by ts rows between 200 preceding and current row) as avg，您有什么想法？ - user330315

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- vyegorov · Answer 1

我认为，如果进行适当的索引，每次重新计算最新的200个条目不会非常缓慢。如果您创建一个索引，例如：

CREATE INDEX i_sensor_values ON sensor_values(sensor_id, ts DESC);

你可以很快地得到结果，只需要进行以下编程操作：

SELECT sum("value") -- add more expressions as required
  FROM sensor_values
 WHERE sensor_id=$1
 ORDER BY ts DESC
 LIMIT 200;

您可以从 PL/pgSQL 函数中的循环中执行此查询。如果您将尽快迁移到9.3（或更高版本），您还可以使用 LATERAL joins 来实现此目的。

我认为在这里使用覆盖索引不会起到好的作用，因为表是不断变化的，IndexOnlyScan 不会发挥作用。

最好也检查一下Loose Index scans。

P.S. 列名value应该加上双引号，因为它是一个SQL保留字。