pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed to subtract values between previous and current row
10+ messages / 5 participants
[nested] [flat]
* Need to subtract values between previous and current row
@ 2006-12-15 12:27 Partha Guha Roy <partha.guha.roy@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Partha Guha Roy @ 2006-12-15 12:27 UTC (permalink / raw)
To: pgsql-sql
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...
Can anyone please help?
Regards.
--
------------------------------
Partha Guha Roy
***********************************************
Take everything you like seriously, except yourselves.
- Rudyard Kipling
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 13:01 Ragnar <gnari@hive.is>
parent: Partha Guha Roy <partha.guha.roy@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Ragnar @ 2006-12-15 13:01 UTC (permalink / raw)
To: Partha Guha Roy <partha.guha.roy@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 13:05 Joe <dev@freedomcircle.net>
parent: Partha Guha Roy <partha.guha.roy@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Joe @ 2006-12-15 13:05 UTC (permalink / raw)
To: Partha Guha Roy <partha.guha.roy@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 13:06 Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 10+ messages in thread
From: Marc Mamin @ 2006-12-15 13:06 UTC (permalink / raw)
To: Partha Guha Roy <partha.guha.roy@gmail.com>; pgsql-sql
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 <yourRecordType> AS
...
DELARE
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;
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 13:12 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Partha Guha Roy <partha.guha.roy@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Richard Broersma Jr @ 2006-12-15 13:12 UTC (permalink / raw)
To: Partha Guha Roy <partha.guha.roy@gmail.com>; pgsql-sql
>
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 13:21 Partha Guha Roy <partha.guha.roy@gmail.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 2 replies; 10+ messages in thread
From: Partha Guha Roy @ 2006-12-15 13:21 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 13:40 Ragnar <gnari@hive.is>
parent: Partha Guha Roy <partha.guha.roy@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Ragnar @ 2006-12-15 13:40 UTC (permalink / raw)
To: Partha Guha Roy <partha.guha.roy@gmail.com>; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 14:01 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Partha Guha Roy <partha.guha.roy@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Richard Broersma Jr @ 2006-12-15 14:01 UTC (permalink / raw)
To: Partha Guha Roy <partha.guha.roy@gmail.com>; +Cc: pgsql-sql
> 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;
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 16:06 Ragnar <gnari@hive.is>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ragnar @ 2006-12-15 16:06 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Partha Guha Roy <partha.guha.roy@gmail.com>; 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Need to subtract values between previous and current row
@ 2006-12-15 16:21 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Ragnar <gnari@hive.is>
0 siblings, 0 replies; 10+ messages in thread
From: Richard Broersma Jr @ 2006-12-15 16:21 UTC (permalink / raw)
To: Ragnar <gnari@hive.is>; +Cc: Partha Guha Roy <partha.guha.roy@gmail.com>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-12-15 16:21 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-15 12:27 Need to subtract values between previous and current row Partha Guha Roy <partha.guha.roy@gmail.com>
2006-12-15 13:01 ` Ragnar <gnari@hive.is>
2006-12-15 13:05 ` Joe <dev@freedomcircle.net>
2006-12-15 13:12 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-12-15 13:21 ` Partha Guha Roy <partha.guha.roy@gmail.com>
2006-12-15 13:40 ` Ragnar <gnari@hive.is>
2006-12-15 14:01 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-12-15 16:06 ` Ragnar <gnari@hive.is>
2006-12-15 16:21 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-12-15 13:06 Re: Need to subtract values between previous and current row Marc Mamin <M.Mamin@intershop.de>
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