> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Willem Buitendyk > Sent: Wednesday, January 30, 2008 1:15 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Oracle Analytical Functions > > I'm trying to replicate the use of Oracle's 'lag' and 'over partition > by' analytical functions in my query. I have a table (all_client_times) > such as: > > client_id, datetime > 122, 2007-05-01 12:00:00 > 122, 2007-05-01 12:01:00 > 455, 2007-05-01 12:02:00 > 455, 2007-05-01 12:03:00 > 455, 2007-05-01 12:08:00 > 299, 2007-05-01 12:10:00 > 299, 2007-05-01 12:34:00 > > 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 > > In Oracle I can achieve this with: > > CREATE OR REPLACE VIEW client_time_diffs AS SELECT client_id,datetime, > LAG(datetime, 1) OVER (partition by client_id ORDER BY > client_id,datetime) AS previoustime from all_client_times; > > 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 could certainly create a cursor and then just use age() or other time difference extraction method as appropriate: http://www.postgresql.org/docs/8.2/static/sql-declare.html http://www.postgresql.org/docs/8.2/static/functions-datetime.html ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
