[sqlite] How to calculate the sum up to a row better than O(n^2)?

[Michael Sizaki]

Hi,


Suppose I have a database:
  CREATE TABLE data (timestamp INTEGER, amount INTEGER);
  INSERT INTO data VALUES(1,10);
  INSERT INTO data VALUES(2,20);
  INSERT INTO data VALUES(3,5);
  INSERT INTO data VALUES(4,2);
  ...

Now I want to see the sum up to the timestamp:

 SELECT
    timestamp,(SELECT sum(amount)
        FROM data as d
        WHERE d.timestamp<=data.timestamp)
  FROM data ORDER BY timestamp;

This works fine for small data sets. But it is obviously
a quadratic problem. Is there a more efficient way to do
the same thing?


Michael