Store the sum instead of the amount, then if you need the amount just use this
amount(0) = sum(0)
amount(t_i) = sum(t_i) - sum(t_(i-1))

This way, if you need to select all amount it only takes O(n).
Insert/delete/update takes longer but you select more often than youi
insert/delete/update.

On 7/20/06, Michael Sizaki <[EMAIL PROTECTED]> wrote:
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




--
Julien

Reply via email to