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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
