> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of telenieko@gmail.com
> Sent: Tuesday, October 30, 2012 7:34 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Average Balance "life"
>
> Hi there,
>
> Not sure if this is the right place to post this question:
>
> I'm tryting to find a way to do the following without going row by row on
the
> client program doing the calculations:
>
> I've got a "movements" table (from an accounting program) for which I've
> already done monthly balances and running balances but now I need to
> calculate the "life" of the balances.
>
> Ie, account 1 is 0 and gets 1000 on day 1, -1000 on day 3. That would make
a
> life of two days for this balance. Note this is completely diferent to
"average
> balance".
>
> In words that would be "Every time money goes into this account it takes
an
> average of X days to go out".
>
> Any ideas on how could I do this calculation inside PostgreSQL to avoid
> transfering all rows to the client?
>
> In case you are curious, I want to do this calculation to see which
accounts
> have "balance lifes" to long in order to find accounting errors.
>
> Thanks,
> marc
Start learning about Window functions/clauses:
http://www.postgresql.org/docs/9.2/interactive/tutorial-window.html
The lag function over a window ordered by date will allow you to calculate
how many days since the last transaction.
You sample data is simplistic to the point of being unusable. "Balance
Life" may have a concrete definition in your domain but the example and
descriptions are lacking. If indeed you mean "days between transactions"
then a direct window function application will solve the problem easily.
But given that most accounts have numerous debits and credits flowing
through them the logic by which you choose the endpoints is unclear but
fundamental to the solution you seek. At first blush you seem to need to
decide whether you want to deal with FIFO, LIFO, or specific-lots. Do you
have some other identifier (i.e., control) attached to these amounts that
would aid in choosing the endpoints?
Regardless, most likely any pure SQL solution is going to require the use of
Window functions so at least learn what those can do and then if you still
need help reply back with more details.
David J.