[SQL] Need to subtract values between previous and current row
Hi, I have a table that contains data like this: ID ATIME (MM/dd/) == 110/12/2006 210/14/2006 310/18/2006 410/22/2006 510/30/2006 Now I need a query that will subtract atime of row 1 from row 2, row2 from row3 and so on... Can anyone please help? Regards. -- -- Partha Guha Roy *** Take everything you like seriously, except yourselves. - Rudyard Kipling
Re: [SQL] Need to subtract values between previous and current row
On fös, 2006-12-15 at 18:27 +0600, Partha Guha Roy wrote: > Hi, > > I have a table that contains data like this: > > ID ATIME (MM/dd/) > == > 110/12/2006 > 210/14/2006 > 310/18/2006 > 410/22/2006 > 510/30/2006 > > Now I need a query that will subtract atime of row 1 from row 2, row2 > from row3 and so on... if there are no missing IDs you can use a self join SELECT t1.atime,t2,atime FROM t AS t1 JOIN t AS t2 ON (t1.ID=t2.ID+1) otherwise, you could use a loop in a pl/pgpsql function gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Need to subtract values between previous and current row
On Fri, 2006-12-15 at 18:27 +0600, Partha Guha Roy wrote: > I have a table that contains data like this: > > ID ATIME (MM/dd/) > == > 110/12/2006 > 210/14/2006 > 310/18/2006 > 410/22/2006 > 510/30/2006 > > Now I need a query that will subtract atime of row 1 from row 2, row2 > from row3 and so on... > > Can anyone please help? How about something like this select x.id, x.atime, x.atime - y.atime as diff from yourtable x, yourtable y where x.id + 1 = y.id; Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Need to subtract values between previous and current row
I see 3 solutions. A) self join B) define a procedure that return a set of records. this use only a single table scan on the ordered table not tested, just the genera idea: CREATE OR REPLACE FUNCTION foo() RETURNS SETOF AS ... DELARE previous_time int8; --(or whaever datatype you have) rec record ; rec2 ; BEGIN FOR rec in select id, time from yourtable ORDER BY ID LOOP select into rec2 id, rec.time - previous_time; return next rec2; END LOOP; END; ... C) embedding R in Postgres http://www.joeconway.com/oscon-pres-2003-1.pdf http://www.omegahat.org/RSPostgres/ This may be a solution to implement complex cross-rows aggregation. But I never got the time to test it; I'd be interested to know which concerns this solution can show (stability, memory & cpu load, concurent requests) Cheers, Marc
Re: [SQL] Need to subtract values between previous and current row
> > ID ATIME (MM/dd/) > == > 110/12/2006 > 210/14/2006 > 310/18/2006 > 410/22/2006 > 510/30/2006 > > Now I need a query that will subtract atime of row 1 from row 2, row2 from > row3 and so on... just an idea. select (A.atime - max(B.atime)) duration from table A join table B on (A.atime > B.atime) group by A.atime; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need to subtract values between previous and current row
Hi, Thanks for everyones email. Let me clarify a little bit more. Actually there is a state change of an entity. now the results are stored in the following manner: CIDATIME STATE 10112/10/2006 1 10112/12/2006 2 10112/14/2006 1 10112/17/2006 2 10212/14/2006 1 10212/16/2006 2 10212/18/2006 3 Now I have to find which CID stayed on which STATE for how long. Lets assume today is the 20th. So the result would be: CID DURATION STATE 101 2 1 101 2 2 101 3 1 101 3 2 102 2 1 102 2 2 102 2 3 The constraints are: I can't use any plpgsql or other function. There can be gaps in CID. No sequence can be created. Thanks. On 12/15/06, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > ID ATIME (MM/dd/) > == > 110/12/2006 > 210/14/2006 > 310/18/2006 > 410/22/2006 > 510/30/2006 > > Now I need a query that will subtract atime of row 1 from row 2, row2 from > row3 and so on... just an idea. select (A.atime - max(B.atime)) duration from table A join table B on (A.atime > B.atime) group by A.atime; Regards, Richard Broersma Jr. -- -- Partha Guha Roy *** Take everything you like seriously, except yourselves. - Rudyard Kipling
Re: [SQL] Need to subtract values between previous and current row
On fös, 2006-12-15 at 19:21 +0600, Partha Guha Roy wrote: > Thanks for everyones email. Let me clarify a little bit more. > > Actually there is a state change of an entity. now the results are > stored in the following manner: > > CIDATIME STATE > 10112/10/2006 1 > 10112/12/2006 2 > 10112/14/2006 1 > 10112/17/2006 2 > 10212/14/2006 1 > 10212/16/2006 2 > 10212/18/2006 3 > > Now I have to find which CID stayed on which STATE for how long. > > Lets assume today is the 20th. > So the result would be: > > CID DURATION STATE > 101 2 1 > 101 2 2 > 101 3 1 > 101 3 2 > 102 2 1 > 102 2 2 > 102 2 3 > > The constraints are: > I can't use any plpgsql or other function. > There can be gaps in CID. > No sequence can be created. those are constraints indeed. what do you mean by 'can't use any plpgsql or other function'? no user-defined functions, or no functions at all can you use temp tables ? what version of postgres is this? actually, this looks like a classical client-side problem. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Need to subtract values between previous and current row
> CIDATIME STATE > 10112/10/2006 1 > 10112/12/2006 2 > 10112/14/2006 1 > 10112/17/2006 2 > 10212/14/2006 1 > 10212/16/2006 2 > 10212/18/2006 3 select A.cid, (A.atime - max(B.atime)) duration, A.state from table A join table B on (A.atime > B.atime and A.cid = B.cid) group by A.atime, A.cid, A.state; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Need to subtract values between previous and current row
On fös, 2006-12-15 at 06:01 -0800, Richard Broersma Jr wrote: > > CIDATIME STATE > > 10112/10/2006 1 > > 10112/12/2006 2 > > 10112/14/2006 1 > > 10112/17/2006 2 > > 10212/14/2006 1 > > 10212/16/2006 2 > > 10212/18/2006 3 > > select A.cid, (A.atime - max(B.atime)) duration, A.state > from table A join table B > on (A.atime > B.atime and A.cid = B.cid) > group by A.atime, A.cid, A.state; not bad, except you miss the initial state for each cid, and I do not see how you get the final duration. this inspired me: test=# create table t (cid int, atime date, state int); CREATE TABLE test=# insert into t values (101,'2006-12-10',1); INSERT 0 1 test=# insert into t values (101,'2006-12-12',2); INSERT 0 1 test=# insert into t values (101,'2006-12-14',1); INSERT 0 1 test=# insert into t values (101,'2006-12-17',2); INSERT 0 1 test=# insert into t values (102,'2006-12-14',1); INSERT 0 1 test=# insert into t values (102,'2006-12-16',2); INSERT 0 1 test=# insert into t values (102,'2006-12-18',3); INSERT 0 1 test=# select A.cid, (min(B.atime)-A.atime) as duration, A.state from t as A join (select * from t union all select distinct on (cid) cid, '2006-12-20'::date,0 from t ) as B on (A.atime < B.atime and A.cid = B.cid) group by A.atime, A.cid, A.state order by a.cid,a.atime; cid | duration | state -+--+--- 101 |2 | 1 101 |2 | 2 101 |3 | 1 101 |3 | 2 102 |2 | 1 102 |2 | 2 102 |2 | 3 (7 rows) gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need to subtract values between previous and current row
> not bad, except you miss the initial state for each cid, > and I do not see how you get the final duration. > cid | duration | state > -+--+--- > 101 |2 | 1 > 101 |2 | 2 > 101 |3 | 1 > 101 |3 | 2 > 102 |2 | 1 > 102 |2 | 2 > 102 |2 | 3 > (7 rows) Good catch! I should have read the requirements a little more closely, but at 4:30 am when I just woke up, slight details are very hard for me to focus on. :o) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] join and sort on 'best match'
Hi team I was just asking that If table ITEM3 has all the columns then why we need to have a join ?? even we put a sorting very easily. *** Any way I have a very intersting question to all of you. I want to fetch more that 70,000 BLOB from different customer servers. the issue is there are some BOLB files with common names on all servers. So I want merge these files into a single BLOB during fetching data. I am able to fetch the BLOB data from all cust servers but unfortunatelly it overwrite previous file. So pls provide any simple query format for the same, assuming two table tab1 & tab 2. Ashish . INDIA On 12/14/06, Markus Schaber <[EMAIL PROTECTED]> wrote: Hi, Ashish, "Ashish Ahlawat" <[EMAIL PROTECTED]> wrote: > hi pls tell me > > if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need > join ??* Please try to write in English, so we can understand and answer your questions. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] join and sort on 'best match'
On fös, 2006-12-15 at 22:10 +0530, Ashish Ahlawat wrote: > > > Hi team > > I was just asking that If table ITEM3 has all the columns then why we > need to have a join ?? even we put a sorting very easily. > On 12/14/06, Markus Schaber <[EMAIL PROTECTED]> wrote: > Hi, Ashish, > > "Ashish Ahlawat" <[EMAIL PROTECTED]> wrote: > > > if table *Item 3 : news, nature, greenpeace, whale has all > clmn y v need > > join ??* it seems these where not columns of a table. the organisation was like this: table items: id name --- 1foo 2bar 3item3 table tags: tag_id text - 1news 2nature 3tennisballs 4greenpeace 5cannibals 6whale and table items_tags: item_id tag_id 31 32 34 36 ... so table items needs to be joined to table tags via table items_tags to retrieve the connections from item3 to news, nature, greepeace and whales. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Unions and Grouping
I have a question about the SQL Specifications in regards to Unions... I recently put together a query that involved unions similar to the following: SELECT 'Query 1' as id, my_value FROM my_view UNION SELECT 'Query 2' as id, my_value FROM my_other_view ; The first query in the union gave me 39 records and the second gave me 34 records. I was expecting the union to give me 39 + 34 = 73 records. When I ran this against DB2, I got 35 records (not sure about PostgreSQL - will have to try it when I get home). What I found was when I did a group by my_value on each query I got two values that then added to 35. The reason was, my_value was duplicated in my_view and in my_other_view. What the Union appeared to be doing was to gather the data and then do a group by on the complete results. I expected it to only eliminate duplicates BETWEEN the two queries, not WITHIN the queries. My question, what do the SQL Specifications say should happen on a Union? Is it supposed to eliminate duplicates even WITHIN the individual queries that are being unioned? Thanks! -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] Unions and Grouping
"Aaron Bono" <[EMAIL PROTECTED]> writes: > My question, what do the SQL Specifications say should happen on a Union? > Is it supposed to eliminate duplicates even WITHIN the individual queries > that are being unioned? Yes. SQL92 7.10 saith: b) If a set operator is specified, then the result of applying the set operator is a table containing the following rows: i) Let R be a row that is a duplicate of some row in T1 or of some row in T2 or both. Let m be the number of duplicates of R in T1 and let n be the number of duplicates of R in T2, where m >= 0 and n >= 0. ii) If ALL is not specified, then Case: 1) If UNION is specified, then Case: A) If m > 0 or n > 0, then T contains exactly one dupli- cate of R. B) Otherwise, T contains no duplicate of R. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Unions and Grouping
On 12/15/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Aaron Bono" <[EMAIL PROTECTED]> writes: > My question, what do the SQL Specifications say should happen on a Union? > Is it supposed to eliminate duplicates even WITHIN the individual queries > that are being unioned? Yes. SQL92 7.10 saith: b) If a set operator is specified, then the result of applying the set operator is a table containing the following rows: i) Let R be a row that is a duplicate of some row in T1 or of some row in T2 or both. Let m be the number of duplicates of R in T1 and let n be the number of duplicates of R in T2, where m >= 0 and n >= 0. ii) If ALL is not specified, then Case: 1) If UNION is specified, then Case: A) If m > 0 or n > 0, then T contains exactly one dupli- cate of R. B) Otherwise, T contains no duplicate of R. regards, tom lane So if I don't want the duplicated WITHIN the two queries to be eliminated, I use UNION ALL? Thanks! -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] Unions and Grouping
On fös, 2006-12-15 at 16:17 -0600, Aaron Bono wrote: > On 12/15/06, Tom Lane <[EMAIL PROTECTED]> wrote: > "Aaron Bono" <[EMAIL PROTECTED]> writes: > > My question, what do the SQL Specifications say should > happen on a Union? > > Is it supposed to eliminate duplicates even WITHIN the > individual queries > > that are being unioned? > > Yes. SQL92 7.10 saith: > [snip SQL92 standardese] > > So if I don't want the duplicated WITHIN the two queries to be > eliminated, I use UNION ALL? with UNION ALL, no duplicates will be discarded, either "within" or "between" the queries. if I understand you, you are looking for having only queries that are both in Q1 and Q2 removed, but not for example, any duplicates in Q1 that are not in Q2. if this is what you want, then I think you may not have thought it through. what about a row that is twice in Q1 and three times in Q2? how many copies of this row should be in the result? gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Unions and Grouping
On 12/15/06, Ragnar <[EMAIL PROTECTED]> wrote: On fös, 2006-12-15 at 16:17 -0600, Aaron Bono wrote: > On 12/15/06, Tom Lane <[EMAIL PROTECTED]> wrote: > "Aaron Bono" <[EMAIL PROTECTED]> writes: > > My question, what do the SQL Specifications say should > happen on a Union? > > Is it supposed to eliminate duplicates even WITHIN the > individual queries > > that are being unioned? > > Yes. SQL92 7.10 saith: > [snip SQL92 standardese] > > So if I don't want the duplicated WITHIN the two queries to be > eliminated, I use UNION ALL? with UNION ALL, no duplicates will be discarded, either "within" or "between" the queries. if I understand you, you are looking for having only queries that are both in Q1 and Q2 removed, but not for example, any duplicates in Q1 that are not in Q2. if this is what you want, then I think you may not have thought it through. what about a row that is twice in Q1 and three times in Q2? how many copies of this row should be in the result? gnari What I want is two-fold: 1. I want to eliminate all duplicates which is exactly what UNION does - so I am using a straight UNION 2. I want to understand how UNION and UNION ALL work (which I do now - thanks) I didn't really want the duplicates between the two queries eliminated but not within. That is just what I had expected to see the first time I ran the query. As you pointed out, this really isn't desirable so I am glad it doesn't work this way. Thanks for all the input. -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
