sql - 使用增量值更新行

Question

假设我在 postgresql 数据库中有一个表，其中包含以下列:time,speed

现在我添加了一列“distance”，我想通过类似的方式插入距离值:

   row[time+1].distance = row[time].distance + row[time+1].speed 

更新表格的最快方法是什么？

更新

我想尝试这样的事情:

d = 0.0
for row in select time,speed from my_table loop
  d = d + row.speed
  update my_table set distance = d where time = row.time
end loop

这是最好的方法吗？如何运行此代码段？

Answer 1

所以，我做了一个表格:

create table whatever ( time_c int4, speed int4, distance int8);

并插入一些行:

insert into whatever (time_c, speed) select i, random() * 100 from generate_series(1,10) i;

这给了我这个数据:

$ select * from whatever;
 time_c | speed | distance 
--------+-------+----------
      1 |    53 |   [null]
      2 |    17 |   [null]
      3 |    53 |   [null]
      4 |    46 |   [null]
      5 |    31 |   [null]
      6 |    18 |   [null]
      7 |    42 |   [null]
      8 |    15 |   [null]
      9 |     1 |   [null]
     10 |    51 |   [null]
(10 rows)

然后，我使用 DO 命令:

do $$
DECLARE
    tmp_cur cursor for SELECT * FROM whatever ORDER BY time_c for UPDATE;
    temprec record;
    total_distance INT4 := 0;
BEGIN
    open tmp_cur;
    LOOP
        fetch tmp_cur INTO temprec;
        EXIT WHEN NOT FOUND;
        total_distance := total_distance + temprec.speed;
        UPDATE whatever SET distance = total_distance WHERE CURRENT OF tmp_cur;
    END LOOP;
END;
$$;

这就是全部:

$ select * from whatever;
 time_c | speed | distance 
--------+-------+----------
      1 |    53 |       53
      2 |    17 |       70
      3 |    53 |      123
      4 |    46 |      169
      5 |    31 |      200
      6 |    18 |      218
      7 |    42 |      260
      8 |    15 |      275
      9 |     1 |      276
     10 |    51 |      327
(10 rows)