Query design assistance - getting daily totals - Mailing list pgsql-sql

From Paul Lambert
Subject Query design assistance - getting daily totals
Date
Msg-id 475F3AAB.2050609@reynolds.com.au
Whole thread Raw
Responses Re: Query design assistance - getting daily totals
List pgsql-sql
I have a table of account balances as at the end of a working day and 
want to from that, calculate daily total figures.

Eg, let's say I have a table structure of:
year_id integer
month_id integer
working_day integer
account integer
account_balance numeric(19,4)

Example data might be something like
2007,12,1,1,100.00
2007,12,2,1,200.00
2007,12,3,1,250.00
2007,12,4,1,500.00
2007,12,5,1,575.00

I want to construct a query that will give me the daily balances from 
this information, so I would be presented with something like:
2007,12,1,1,100.00
2007,12,2,1,100.00
2007,12,3,1,50.00
2007,12,4,1,250.00
2007,12,5,1,75.00

I figure there's a couple of ways I could do it...
Firstly, build a complicated nested select where the lower level gets 
the main data, then the outer select joins it on itself where the 
working_day is equal to the working_day-1 from the nested query and then 
wrap that in another select that calculates the difference in the 
account_balance column from both.
The second option I think would be to create a function whereby I pass 
it the primary key fields (year_id,month_id,working_day,account) and 
have it do two selects and work out the difference.

I suspect the second option would be more efficient than the first, and 
probably easier to implement since it would be easier to handle 
cross-month boundaries, i.e. day 1's daily total will be the amount on 
that day minus the amount of the final day in the previous month - but 
does anyone have any alternate suggestions that would be better still?

Cheers,
Paul.

--
Paul Lambert
Database Administrator
AutoLedgers



pgsql-sql by date:

Previous
From: PostgreSQL Admin
Date:
Subject: Re: Foreign Key for multi PK or design question
Next
From: Tom Lane
Date:
Subject: Re: join on three tables is slow