我正在运行Postgres 9.2,有一张类似以下表的大表:
我会不断收到系统中传入的数值,每分钟可能会有很多个。我想要维护一个最近200个值的滚动标准差/平均数,以便确定进入系统的新值是否在平均数的3个标准差内。为了做到这一点,我需要不断更新最近200个值的当前标准差和平均数。
由于表格可能有数亿行,我不想按时间对一个传感器最后的200行进行排序,然后针对每一个新值执行vg(value)、var_samp(value)操作。我认为更新标准差和平均数会更快。
我已经开始编写一个PL/pgSQL函数,在每个特定传感器进入新值时更新滚动方差和平均数。
我可以使用类似下面的代码来实现:
这是基于http://jonisalonen.com/2014/efficient-and-accurate-rolling-standard-deviation/的方法。
窗口的大小为200个值。old_value是窗口的第一个值。当有新值进来时,我们将窗口向前移动一位。在得到结果后,我会为传感器存储以下值。
这样我就不必不断获取最后200个值并进行求和等操作。当新的传感器值进来时,我可以重复使用这些值。
我的问题是,当首次运行时,我没有传感器的先前窗口数据,即上面的三个值,因此我必须采用较慢的方式来处理。
类似于:
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会是更快/更清晰的方法,但也许在客户端代码中做这个操作会更好? 有更好的方法执行此类型的滚动统计更新吗?
avg(value) over (partition by sensor_id order by ts rows between 200 preceding and current row) as avg
,您有什么想法? - user330315