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

Oracle Analytical Functions

From: Willem Buitendyk <willem(at)pcfish(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Oracle Analytical Functions
Date: 2008-01-30 21:15:16
Message-ID: 47A0E8E4.9010003@pcfish.ca (view raw or flat)
Thread:
Lists: pgsql-general
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?

Appreciate the help,

Willem


Responses

pgsql-general by date

Next:From: Dann CorbitDate: 2008-01-30 21:33:57
Subject: Re: Oracle Analytical Functions
Previous:From: Tom HartDate: 2008-01-30 21:10:07
Subject: Mailing list archives/docs project

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