OK,
this is quite brutal and is going to be very expensive, but I think it
does what you want. The trick lies in the idea of joining a table with
itself. Thus, I first define a virtual copy of the table (in my case
"dup") and then produce a query that joins the table to this copy. Enjoy.
Oliver
detail=# \d mileage Table "mileage"Attribute | Type | Modifier
-----------+-----------+----------miles | integer | date | timestamp |
detail=# select * from mileage;miles | date
-------+------------------------ 5 | 2000-08-01 00:00:00+02 9 | 2000-08-02 00:00:00+02 4 | 2000-08-03
00:00:00+02
(3 rows)
detail=# \d dup View "dup"Attribute | Type | Modifier
-----------+-----------+----------miles | integer | date | timestamp |
View definition: SELECT mileage.miles, mileage.date FROM mileage;
detail=# select mileage.miles, mileage.date, sum(dup.miles) from mileage, dup where dup.date <= mileage.date group by
mileage.date,mileage.miles order by mileage.date;miles | date | sum
-------+------------------------+----- 5 | 2000-08-01 00:00:00+02 | 5 9 | 2000-08-02 00:00:00+02 | 14 4 |
2000-08-0300:00:00+02 | 18
(3 rows)
