Skip site navigation (1) Skip section navigation (2)

Re: Oracle Analytical Functions

From: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>
To: "'Willem Buitendyk'" <willem(at)pcfish(dot)ca>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: Oracle Analytical Functions
Date: 2008-01-30 21:47:22
Message-ID: 008801c86389$b256c700$17045500$@r@sbcglobal.net (view raw or flat)
Thread:
Lists: pgsql-general
> and I would like to create a new view that takes the first table and
> calculates the time difference in minutes between each row so that the
> result is something like:
> 
> client_id,datetime, previousTime, difftime
> 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1
> 455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1
> 455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5
> 299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24
>
> Any idea how I could replicate this in SQL from PG.  Would this be an
> easy thing to do in Pl/pgSQL?  If so could anyone give any directions
> as to where to start?

You can create a set-returning function, that cursors over the table,
like this:


CREATE OR REPLACE FUNCTION lagfunc(
	OUT client_id INT, 
	OUT datetime timestamp, 
	OUT previousTime timestamp, 
	OUT difftime interval)
RETURNS SETOF RECORD as $$ 
DECLARE
	thisrow RECORD;
	last_client_id INT;
	last_datetime timestamp;
BEGIN

    FOR thisrow IN SELECT * FROM all_client_times ORDER BY client_id,
datetime LOOP
	IF thisrow.client_id = last_client_id THEN
		client_id := thisrow.datetime;
		datetime := thisrow.datetime;
		previousTime := last_datetime;
		difftime = datetime-previousTime;
		RETURN NEXT;
	END IF;
	last_client_id := thisrow.client_id;
	last_datetime := thisrow.datetime;
    END LOOP;

   RETURN;
END;
$$ LANGUAGE plpgsql;

select * from lagfunc() limit 10;
select * from lagfunc() where client_id = 455;


Here I used an interval, but you get the idea.





In response to

Responses

pgsql-general by date

Next:From: Ivan Sergio BorgonovoDate: 2008-01-30 21:49:42
Subject: Re: Mailing list archives/docs project
Previous:From: Dann CorbitDate: 2008-01-30 21:33:57
Subject: Re: Oracle Analytical Functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group