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

[Jay Sprenkle]

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?

Perhaps you can calculate the results as you insert the data
and store it in the database? It's not faster, but it moves the
calculations to a time when someone isn't staring at the screen
waiting for them!


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com