Thread: summing tables
hi, i have a table consisting of 4 integers. seq is for making the table ordered. (ORDER BY SEQ ASC) a,b,c maybe null seq | a | b | c -----+----+----+--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | i am needing a sql statement to do c=a+b+"the c of the row with seq one less than myself" this statement has to run over the whole table, in seq order. how can this be acomplished??? cu&thanks erik -- Erik Thiele
update table_name set c = a + b + (select c from table_name as x where x.seq = seq-1) where c is null; additional checks are required if you want to update c when c is not null if all the c are null then this query will do nothing god help u :) ----- Original Message ----- From: "Erik Thiele" <erik@thiele-hydraulik.de> To: <pgsql-sql@postgresql.org> Sent: Tuesday, July 15, 2003 3:09 PM Subject: [SQL] summing tables > hi, > > i have a table consisting of 4 integers. > > seq is for making the table ordered. (ORDER BY SEQ ASC) > a,b,c maybe null > > > seq | a | b | c > -----+----+----+--- > 0 | 1 | 2 | 3 > 1 | 1 | 2 | > 2 | 5 | 7 | > 3 | -2 | -4 | > > > i am needing a sql statement to do > > c=a+b+"the c of the row with seq one less than myself" > > this statement has to run over the whole table, in seq order. > > > how can this be acomplished??? > > > cu&thanks > erik > > > -- > Erik Thiele > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Erik Thiele wrote: >hi, > >i have a table consisting of 4 integers. > >seq is for making the table ordered. (ORDER BY SEQ ASC) >a,b,c maybe null > > > seq | a | b | c >-----+----+----+--- > 0 | 1 | 2 | 3 > 1 | 1 | 2 | > 2 | 5 | 7 | > 3 | -2 | -4 | > > >i am needing a sql statement to do > >c=a+b+"the c of the row with seq one less than myself" > >this statement has to run over the whole table, in seq order. > > >how can this be acomplished??? > > >cu&thanks >erik > > > > Hi Erik, this should do the trick: (I have not yet considered the NULLS though...) UPDATE table t set c = a + b + (Select c from table tt where tt.seq = t.seq -1) Cheers, Dani
On Tue, 15 Jul 2003 15:16:21 +0300 "Viorel Dragomir" <bigchief@vio.ro> wrote: > update table_name > set c = a + b + (select c from table_name as x where x.seq = seq-1) > where c is null; hmmm. the query is run row by row, isn't it? but it will have different results depending on the order of those rows. look, the c value is set by one row-query and read by the row-query of the row below. does sql specify some "order is magically always as you expect it" rule? still i am a little confused. and i am sorry, i didn't initially specify that the "seq" are not gapless. i.e. seq-1 does not always exist. but seq-13 could be the next lower one! zeit=# select * from foo;seq | a | b | c -----+----+----+--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | 6 | -1 | -2 | 5 | -2 | -2 | 4 | 0 | 1 | (7 rows) i created this (gapless for easiness) table and run your query: zeit=# update foo set c = a + b + (select c from foo as x where x.seq = seq-1) where c is null; UPDATE 6 #### 6 updates??? really??? zeit=# select * from foo;seq | a | b | c -----+----+----+--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | 6 | -1 | -2 | 5 | -2 | -2 | 4 | 0 | 1 | (7 rows) hmmmm. let's try the statement of the other reply to my initial mail: UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1) zeit=# UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1); ERROR: parser: parse error at or near "t" hmmmm.... any clues? cu & thx erik > additional checks are required if you want to update c when c is not null > if all the c are null then this query will do nothing > > i have a table consisting of 4 integers. > > > > seq is for making the table ordered. (ORDER BY SEQ ASC) > > a,b,c maybe null > > > > > > seq | a | b | c > > -----+----+----+--- > > 0 | 1 | 2 | 3 > > 1 | 1 | 2 | > > 2 | 5 | 7 | > > 3 | -2 | -4 | > > > > > > i am needing a sql statement to do > > > > c=a+b+"the c of the row with seq one less than myself" > > > > this statement has to run over the whole table, in seq order. -- Erik Thiele
Ok. I disregarded the complexity of this problem. :) You may wish to do this thing from a programming language with every row at a time [in php, asp...]. Anyway here is a function in plpgsql. It solves your problem, i hope; but i don't recommend it. create function update_nulls() returns int as ' declarevar1 integer;var2 integer; begin select into var1 count(*) from tab where c is null; var2 := var1; while var1 > 0 loopupdate table_nameset c =a + b + (select x.c from table_name as x where x.seq = table_name.seq-1)where c is null; var1 := var1 - 1; end loop; return var2; end; ' language 'plpgsql'; ----- Original Message ----- From: "Erik Thiele" <erik@thiele-hydraulik.de> To: <pgsql-sql@postgresql.org> Sent: Tuesday, July 15, 2003 3:39 PM Subject: Re: [SQL] summing tables > On Tue, 15 Jul 2003 15:16:21 +0300 > "Viorel Dragomir" <bigchief@vio.ro> wrote: > > > update table_name > > set c = a + b + (select c from table_name as x where x.seq = seq-1) > > where c is null; > > hmmm. the query is run row by row, isn't it? > but it will have different results depending on the order of those rows. > > look, the c value is set by one row-query > and read by the row-query of the row below. > > does sql specify some "order is magically always as you expect it" rule? > > still i am a little confused. > > > and i am sorry, i didn't initially specify that the "seq" are not gapless. > i.e. seq-1 does not always exist. but seq-13 could be the next lower one! > > zeit=# select * from foo; > seq | a | b | c > -----+----+----+--- > 0 | 1 | 2 | 3 > 1 | 1 | 2 | > 2 | 5 | 7 | > 3 | -2 | -4 | > 6 | -1 | -2 | > 5 | -2 | -2 | > 4 | 0 | 1 | > (7 rows) > > i created this (gapless for easiness) table and run your query: > > zeit=# update foo set c = a + b + (select c from foo as x where x.seq = seq-1) where c is null; > UPDATE 6 > > #### 6 updates??? really??? > > zeit=# select * from foo; > seq | a | b | c > -----+----+----+--- > 0 | 1 | 2 | 3 > 1 | 1 | 2 | > 2 | 5 | 7 | > 3 | -2 | -4 | > 6 | -1 | -2 | > 5 | -2 | -2 | > 4 | 0 | 1 | > (7 rows) > > > hmmmm. let's try the statement of the other reply to my initial mail: > > UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1) > > zeit=# UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1); > ERROR: parser: parse error at or near "t" > > hmmmm.... > > any clues? > > cu & thx > erik > > > additional checks are required if you want to update c when c is not null > > if all the c are null then this query will do nothing > > > i have a table consisting of 4 integers. > > > > > > seq is for making the table ordered. (ORDER BY SEQ ASC) > > > a,b,c maybe null > > > > > > > > > seq | a | b | c > > > -----+----+----+--- > > > 0 | 1 | 2 | 3 > > > 1 | 1 | 2 | > > > 2 | 5 | 7 | > > > 3 | -2 | -4 | > > > > > > > > > i am needing a sql statement to do > > > > > > c=a+b+"the c of the row with seq one less than myself" > > > > > > this statement has to run over the whole table, in seq order. > > -- > Erik Thiele > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
To solve this problem efficiently you probably need the lead/lag analytic functions. Unfortunately Postgres doesn't have them. You could do it with something like: update foo set c = a+b+(select c from foo as x where seq < foo.seq ORDER BY seq desc LIMIT 1) or the more standard but likely to be way slower: update foo set c = a+b+(select c from foo as x where seq = (select max(seq) from foo as y where seq < foo.seq)) However, i would suggest that if you have an implicit relationship between records you should make that relationship explicit with a foreign key. If you had a column that contained the seq of the parent record then this would be easy. I'm really puzzled how this query as currently specified could be useful. -- greg
Erik, If you intent is to get a running total of a and b ordered by seq, you should try this (assuming the table name is t): update t set c = ( select sum(a) + sum(b) from t t1 where t1.seq <= t.seq); You should have an index on seq. If the table is very large, it is going to be painfully slow. In that case you may want to think about using a function to step thru each row. JLL Erik Thiele wrote: > > hi, > > i have a table consisting of 4 integers. > > seq is for making the table ordered. (ORDER BY SEQ ASC) > a,b,c maybe null > > seq | a | b | c > -----+----+----+--- > 0 | 1 | 2 | 3 > 1 | 1 | 2 | > 2 | 5 | 7 | > 3 | -2 | -4 | > > i am needing a sql statement to do > > c=a+b+"the c of the row with seq one less than myself" > > this statement has to run over the whole table, in seq order. > > how can this be acomplished??? > > cu&thanks > erik > > -- > Erik Thiele > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Erik Thiele <erik@thiele-hydraulik.de> writes:
> "Viorel Dragomir" <bigchief@vio.ro> wrote:
>> update table_name
>> set c = a + b + (select c from table_name as x where x.seq = seq-1)
>> where c is null;
> hmmm. the query is run row by row, isn't it?
> but it will have different results depending on the order of those rows.
No, it won't, because the SELECTs will not see the changes from the
not-yet-completed UPDATE. The above command is almost right; it needs
to be
update table_name
set c = a + b + (select c from table_name as x where seq = table_name.seq-1)
where c is null;
because inside the sub-SELECT, unadorned "seq" will refer to the SELECT's
table.
You didn't say exactly what you wanted to do with null inputs, so that
issue may need more thought.
regards, tom lane
Indeed it was a mistake not to put the table_name. in where clause. But this doesn't resolve the problem. Do you know in which order the update will modify the rows? My lucky guess is that it takes from last inserted rows to the first row. In this way only one row gets updated correctly. I add the result from my unfortunate solution, even corrected. select * from table_name;seq | a | b | c -----+---+---+--- 1 | 1 | 2 | 3 2 | 5 | 9 | 3 | 1 | 2 | 4 | 4 | 7 | 5 | 4 | 2 | 6 | 0 | 1 | (6 rows) update table_nameset c = a + b + (select c from table_name as x where seq = table_name.seq-1)where c is null; select * from table_name;seq | a | b | c -----+---+---+---- 1 | 1 | 2 | 3 2 | 5 | 9 | 17 3 | 1 | 2 | 4 | 4 | 7 | 5 | 4 | 2 | 6 | 0 | 1 | ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Erik Thiele" <erik@thiele-hydraulik.de> Cc: <pgsql-sql@postgresql.org> Sent: Tuesday, July 15, 2003 6:14 PM Subject: Re: [SQL] summing tables > Erik Thiele <erik@thiele-hydraulik.de> writes: > > "Viorel Dragomir" <bigchief@vio.ro> wrote: > >> update table_name > >> set c = a + b + (select c from table_name as x where x.seq = seq-1) > >> where c is null; > > > hmmm. the query is run row by row, isn't it? > > but it will have different results depending on the order of those rows. > > No, it won't, because the SELECTs will not see the changes from the > not-yet-completed UPDATE. The above command is almost right; it needs > to be > > update table_name > set c = a + b + (select c from table_name as x where seq = table_name.seq-1) > where c is null; > > because inside the sub-SELECT, unadorned "seq" will refer to the SELECT's > table. > > You didn't say exactly what you wanted to do with null inputs, so that > issue may need more thought. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
"Viorel Dragomir" <bc@vio.ro> writes: > Anyway, in real life this update modifies only one row with a value wich is > diff of null. It was really handy if it was specified the option ORDER for > the update command. Are you hoping to produce a running total? That's very difficult in standard SQL. That would be very different from the query you asked for. Running totals, ranking, lead/lag, are all things that are very difficult to do in standard SQL. They don't fit in the unordered set model that SQL follows so doing them without special non-standard functions is very hard and inefficient. The functions to do them don't fit well within the SQL universe either, which might be why they don't exist yet in postgres. -- greg
"Viorel Dragomir" <bigchief@vio.ro> writes:
> Indeed it was a mistake not to put the table_name. in where clause.
> But this doesn't resolve the problem.
> Do you know in which order the update will modify the rows?
No, and *it does not matter*. You are forgetting that this all runs
under MVCC rules. The sub-SELECTs will see the pre-existing versions
of the rows, whether or not the UPDATE has yet produced new versions.
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Viorel Dragomir" <bigchief@vio.ro> Cc: <pgsql-sql@postgresql.org> Sent: Tuesday, July 15, 2003 6:42 PM Subject: Re: [SQL] summing tables > "Viorel Dragomir" <bigchief@vio.ro> writes: > > Indeed it was a mistake not to put the table_name. in where clause. > > But this doesn't resolve the problem. > > > Do you know in which order the update will modify the rows? > > No, and *it does not matter*. You are forgetting that this all runs > under MVCC rules. The sub-SELECTs will see the pre-existing versions > of the rows, whether or not the UPDATE has yet produced new versions. Yes that was correct. I forgot that the subselect sees only the old rows. And the ORDER option will not make any difference, only making fool out of myself. :) I don't know if the problem is solved with my posted function. Anyway this is the mail that started all [i get a lot of emails and i'm not really searching for a solution on this matter]: " hi, i have a table consisting of 4 integers. seq is for making the table ordered. (ORDER BY SEQ ASC) a,b,c maybe null seq | a | b | c -----+----+----+--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | i am needing a sql statement to do c=a+b+"the c of the row with seq one less than myself" this statement has to run over the whole table, in seq order. how can this be acomplished??? cu&thanks erik -- Erik Thiele "
The primary problem was that the update command doesn't modify rows in the order u want to do it. I think the update starts with the latest inserted rows. I guess. Anyway, in real life this update modifies only one row with a value wich is diff of null. It was really handy if it was specified the option ORDER for the update command. ----- Original Message ----- From: "Greg Stark" <gsstark@mit.edu> To: "Viorel Dragomir" <bigchief@vio.ro> Cc: <pgsql-sql@postgresql.org> Sent: Tuesday, July 15, 2003 5:55 PM Subject: Re: [SQL] summing tables > > To solve this problem efficiently you probably need the lead/lag analytic > functions. Unfortunately Postgres doesn't have them. > > You could do it with something like: > > update foo set c = a+b+(select c from foo as x where seq < foo.seq ORDER BY seq desc LIMIT 1) > > or the more standard but likely to be way slower: > > update foo set c = a+b+(select c from foo as x where seq = (select max(seq) from foo as y where seq < foo.seq)) > > > However, i would suggest that if you have an implicit relationship between > records you should make that relationship explicit with a foreign key. If you > had a column that contained the seq of the parent record then this would be > easy. I'm really puzzled how this query as currently specified could be > useful. > > > -- > greg > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster