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-03 00:00:00+02 | 18
(3 rows)
