From: Ragnar <gnari@hive.is>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: Partha Guha Roy <partha.guha.roy@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need to subtract values between previous and current row
Date: Fri, 15 Dec 2006 16:06:56 +0000
Message-ID: <1166198816.6369.179.camel@localhost.localdomain> (raw)
In-Reply-To: <519101.72709.qm@web31802.mail.mud.yahoo.com>
References: <519101.72709.qm@web31802.mail.mud.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gnari@hive.is, rabroersma@yahoo.com, partha.guha.roy@gmail.com
Subject: Re: Need to subtract values between previous and current row
In-Reply-To: <1166198816.6369.179.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox