Обсуждение: Running Totals and stuff...
Hi, I keep all of my financial data in Postgresql ( 7.4.2). My "Check" register records deposits, withdrawals (as amount) , date, category and other stuff. The following sorta works... SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >= thedate ) AS total FROM checks x ORDER BY thedate,oid ; The problem is that all transactions on the same date get the total of all transactions for that date, so the resulting table is only "sorta" a running total. If I change the rascal to look like SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.oid >= oid ) AS total FROM checks x ORDER BY thedate,oid ; I get the right results, but this relies on the fact the oids in the check table are currently *sorted* (when the table is sorted by thedate) at least it appears that way via a very brief inspection.... I suspect if I deleted a record and added a record the oids would get out of sequence. Is there a slick way to tell if a column (say the oids column) is in "sorted" order when the table is sorted by date? Assuming the oids get out of wack with respect to the date, is it possible to easily construct a table of the checks sorted by date and then "glue on" a column of ascending integers so the running total sql statement will function properly? Jerry
> -----Original Message----- > From: Jerry LeVan [mailto:jlevan@adelphia.net] > Sent: Monday, May 31, 2004 8:18 PM > To: Postgres General > Subject: [GENERAL] Running Totals and stuff... > > > Hi, > > I keep all of my financial data in Postgresql ( 7.4.2). > My "Check" register records deposits, withdrawals (as amount) > , date, category and other stuff. > > The following sorta works... > > SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >= > thedate ) AS total > FROM checks x > ORDER BY thedate,oid ; > > The problem is that all transactions on the same date get the > total of > all > transactions for that date, so the resulting table is only > "sorta" a running total. > > If I change the rascal to look like > SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.oid > >= oid ) AS > total > FROM checks x > ORDER BY thedate,oid ; > > I get the right results, but this relies on the fact the oids > in the check table are currently *sorted* (when the table is > sorted by thedate) at least it appears that way via a very > brief inspection.... I suspect if I deleted a record and > added a record the oids would get out of sequence. Eventually, the OID values will wrap-around. You cannot safely assume that they are ordered. > Is there a slick way to tell if a column (say the oids column) is in > "sorted" > order when the table is sorted by date? > > Assuming the oids get out of wack with respect to the date, is it > possible > to easily construct a table of the checks sorted by date and > then "glue > on" > a column of ascending integers so the running total sql > statement will function properly? Why not just use a "group by" that involves all the things you want to group by? Probably, I do not properly understand what you are trying to do. If you create a 64 bit bigint sequence called "CheckSequence" it is pretty unlikely that it will wrap around from normal usage.
What platforms now support 64 bit ints for sequences? "Dann Corbit" <DCorbit@connx.com> wrote: <quote ------------------------------------------------> Why not just use a "group by" that involves all the things you want to group by? Probably, I do not properly understand what you are trying to do. If you create a 64 bit bigint sequence called "CheckSequence" it is pretty unlikely that it will wrap around from normal usage. <quote ------------------------------------------------>
Dennis Gearon <gearond@fireserve.net> writes: > What platforms now support 64 bit ints for sequences? I think all of the currently-marked-as-supported ones do. (Hint: if your platform passes the sequence regression test, it has 64-bit sequences.) regards, tom lane