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
