From: | Ragnar <gnari(at)hive(dot)is> |
---|---|
To: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
Cc: | Partha Guha Roy <partha(dot)guha(dot)roy(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Need to subtract values between previous and current row |
Date: | 2006-12-15 16:06:56 |
Message-ID: | 1166198816.6369.179.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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,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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma Jr | 2006-12-15 16:21:08 | Re: Need to subtract values between previous and current row |
Previous Message | Richard Broersma Jr | 2006-12-15 14:01:28 | Re: Need to subtract values between previous and current row |