Thread: Need to subtract values between previous and current row
Hi,<br /><br />I have a table that contains data like this:<br /><br />ID ATIME (MM/dd/yyyy)<br />== ====<brclear="all" />1 10/12/2006<br />2 10/14/2006<br />3 10/18/2006<br />4 10/22/2006 <br />5 10/30/2006<br /><br />Now I need a query that will subtract atime of row 1 fromrow 2, row2 from row3 and so on...<br /><br />Can anyone please help?<br /><br />Regards.<br /><br />-- <br />------------------------------<br/> Partha Guha Roy<br />***********************************************<br />Take everythingyou like seriously, except yourselves.<br />- Rudyard Kipling
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/yyyy) > == ==== > 1 10/12/2006 > 2 10/14/2006 > 3 10/18/2006 > 4 10/22/2006 > 5 10/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
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/yyyy) > == ==== > 1 10/12/2006 > 2 10/14/2006 > 3 10/18/2006 > 4 10/22/2006 > 5 10/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
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
this use only a single table scan on the ordered table
not tested, just the genera idea:
CREATE OR REPLACE FUNCTION foo()
RETURNS SETOF <yourRecordType> AS
...
RETURNS SETOF <yourRecordType> AS
...
DELARE
previous_time int8; --(or whaever datatype you have)
rec record ;
rec2 <yourRecordType>;
previous_time int8; --(or whaever datatype you have)
rec record ;
rec2 <yourRecordType>;
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;
return next rec2;
END LOOP;
END;
...
C) embedding R in Postgres
...
C) embedding R in Postgres
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....)
(stability, memory & cpu load, concurent requests....)
Cheers,
Marc
> > ID ATIME (MM/dd/yyyy) > == ==== > 1 10/12/2006 > 2 10/14/2006 > 3 10/18/2006 > 4 10/22/2006 > 5 10/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.
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:
CID ATIME STATE
101 12/10/2006 1
101 12/12/2006 2
101 12/14/2006 1
101 12/17/2006 2
102 12/14/2006 1
102 12/16/2006 2
102 12/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.
--
------------------------------
Partha Guha Roy
***********************************************
Take everything you like seriously, except yourselves.
- Rudyard Kipling
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:
CID ATIME STATE
101 12/10/2006 1
101 12/12/2006 2
101 12/14/2006 1
101 12/17/2006 2
102 12/14/2006 1
102 12/16/2006 2
102 12/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 <rabroersma@yahoo.com> wrote:
>
> ID ATIME (MM/dd/yyyy)
> == ====
> 1 10/12/2006
> 2 10/14/2006
> 3 10/18/2006
> 4 10/22/2006
> 5 10/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
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: > > CID ATIME STATE > 101 12/10/2006 1 > 101 12/12/2006 2 > 101 12/14/2006 1 > 101 12/17/2006 2 > 102 12/14/2006 1 > 102 12/16/2006 2 > 102 12/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
> CID ATIME STATE > 101 12/10/2006 1 > 101 12/12/2006 2 > 101 12/14/2006 1 > 101 12/17/2006 2 > 102 12/14/2006 1 > 102 12/16/2006 2 > 102 12/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;
On fös, 2006-12-15 at 06:01 -0800, Richard Broersma Jr wrote:
> > CID ATIME STATE
> > 101 12/10/2006 1
> > 101 12/12/2006 2
> > 101 12/14/2006 1
> > 101 12/17/2006 2
> > 102 12/14/2006 1
> > 102 12/16/2006 2
> > 102 12/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,0from 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 | 1101 | 2 | 2101 | 3 | 1101 | 3 | 2102 |
2 | 1102 | 2 | 2102 | 2 | 3
(7 rows)
gnari
> 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.