sql - 滚动平均postgres

Question

我正在运行 Postgres 9.2，我有一个大 table ，类似

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 会是更快/更清洁的方法，但也许更好的做法是使用客户端代码？有没有更好的方法来执行这种类型的滚动统计更新？

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还有。

附言列名 value 应该用双引号引起来，因为这是一个 SQL reserved word .