On Mon, Nov 22, 2004 at 05:50:05PM -0000, Mark Rogers wrote:
Brett Parker wrote:
Yup, that's how that's done... Here's the *MAGIC* ;)
create view testwithrunningtotal as select date,description, amount,(select sum(amount) from test as test2 where test2.date <= test.date) as balance from test order by date;
select * from testwithrunningtotal;
What sort of performance would you expect from this sort of query in a large table? If I'm reading it right it isn't really generating a running total, so much as calculating a complete sum for each transaction. Assuming that several years of records will be kept, this could get quite slow?
The performance will probably not be very clever but since my year's accounts for last year only have around 350 transactions it'll probably perform fine for me.
[That's a genuine question, not a snide remark - I'm interested to know whether PostgreSQL can optimise this somehow, or is just fast enough to do it, or if I'm reading it wrong in the first place?]
To Chris: is there a reason for generating this at the query level, rather than the application level? If you are still playing with PHP this should be trivial to calculate after you have the data from PostgreSQL. Two queries (one to calculate the total of all transactions before the start date of the report, and another to bring back the transactions for the report itself) seems to me that it ought to be quicker as the number of stored transactions grows.
There's no reason for doing at the query level except that Rekall, the front end I'm using, doesn't seem to be capable of using it. I'm not using PHP because it's not really capable of doing the multi-line data entry forms I want (which Rekall can do easily).
Still, I like the ingenuity of Brett's query (but as I do most of my db work in mySQL I have to look up to real database programmers with awe :-)
It's one of the reasons I moved from mysql to postgresql, the ability to use VIEW. (I know it's coming in mysql 5 but it's not really here yet).