Recursive select / updates

From: Alex - <aintokyo(at)hotmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Recursive select / updates
Date: 2011-05-11 14:41:40
Message-ID: SNT135-w22CB8598C8F8742FCE2FB8CF860@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,I have a problem where i need to calculate totals in a table that are based on previous values and calculations.I am currently doing that in a function which works fine but was wondering if there is a more elegant or efficient way to do this.
Here is an example table, ordered by row no.The total is based on the previous rows. Note that the difference of a values does not always need to be 1
row | a | b | total |------+----+-------+------- + 1 | 3 | 11 | 0 | 2 | 5 | 34 | 22 | 3 | 6 | 64 | 67 | 4 | 7 | 525 | 176 | 5 | 8 | 9994 | 810 | 6 | 9 | 26216 | 11438 | 7 | 10 | 3363 | 48282 | 8 | 11 | 1147 | 88489 | 9 | 12 | 1037 | 129843 | 10 | 13 | 200 | 172234 |

Total Row 2(a2-a1)*b1 i.e. (5-3)*11 = 22
Total Row 3 (a3-a2)*b2 + (a3-a1)*b1 i.e (6-5)*34 + (6-3)*11 = 34+33=67
Total Row 4 (a4-a3)*b3 + (a4-a2)*b2 + (a4-a1)*b1 i.e (7-6)*64 + (7-5)*34 + (7-3)*11 = 64+68+44=176
Has anyone a clever solution for that problem?
Thanks for any suggestions.
Alex

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2011-05-11 14:42:05 Re: full text search to_tsquery performance with ispell dictionary
Previous Message Stanislav Raskin 2011-05-11 14:39:50 Re: full text search to_tsquery performance with ispell dictionary