避免循环计算运行平均值/统计数据列

问题描述：

我想加快以下（PostgreSQL）代码，并且我推测它可以帮助摆脱（某些）循环，但是我没有看到方法来做到这一点。欢迎任何关于加速的建议。提前致谢！避免循环计算运行平均值/统计数据列

该代码为不同部分的每个列计算一些统计量（平均值，斜率）。该部分由滑动时间窗（例如60分钟）确定。因此，通过这些我很感兴趣，计算其统计

为每列不同的列如下

循环的代码，我依次移动我的时间窗口，并计算在该窗口中的值的统计信息。

for col_name in ..... a list of column names 
truncate small_table;   -- where statistics are temporarily stored 
for cur in select time from big_table loop 
    execute 'select regr_slope('|| col_name ||', time) as slope,' 
     || ' avg(' || col_name || ') as mean' 
     || ' from big_table where' 
     || ' time <=' || cur.time 
     || ' and time >=' || cur.time-60 
     into result; 

    execute 'insert into small_table values($1,$2,$3)' 
     using cur.time, result.slope, result.mean; 
end loop; 

execute 'update big_table set ' 
    || col_name || '_slope = small_table.slope, ' 
    || col_name || '_mean = small_table.mean ' 
    || ' where big_table.time=small_table.time'; 
end loop;

small_table，其中结果被暂时储存，引入避免对big_table多个更新。

实际上有相当多的列（约50），这可能放缓的另一个因素？

是否BIG_TABLE对时间列的索引？ – Laurence

是的。感谢澄清。 –

数据点之间是否存在固定的时间间隔或是随机的？ – Laurence

答

如果您动态生成以下SQL模式，您至少可以在一个查询中执行所有这些操作。我不确定它是否会有更好的表现，但（显然你需要遍历所有列并添加它们）。在担心在代码中构建SQL之前，我会测试性能。

Update 
    big_table b 
Set 
    field1_slope = x.field1_slope, 
    field1_mean = x.field1_mean, 
    field2_slope = x.field2_slope, 
    field2_mean = x.field2_mean 
From (
    Select 
     b1.time, 
     regr_slope(b2.field1, b2.time) field1_slope, 
     avg(b2.field1) field1_mean, 
     regr_slope(b2.field2, b2.time) field2_slope, 
     avg(b2.field2) field2_mean 
    From 
     big_table b1 
      Inner Join 
     big_table b2 
      On b2.time >= b1.time and b2.time < b1.time + 60 
    Group By 
     b1.time 
    ) x 
Where 
    b.time = x.time;

我对PostgreSQL不太熟悉，可能有办法消除对大表的引用之一。

Example SQL Fiddle

Another way with cursors

这很好地工作 - 内部连接，分组和显式分配的组合。谢谢！ –

避免循环计算运行平均值/统计数据列

相关推荐