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

Re: Oracle Analytical Functions

From: "Dann Corbit" <DCorbit(at)connx(dot)com>
To: "Willem Buitendyk" <willem(at)pcfish(dot)ca>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: Oracle Analytical Functions
Date: 2008-01-30 21:33:57
Message-ID: D425483C2C5C9F49B5B7A41F8944154701000C1B@postal.corporate.connx.com (view raw or flat)
Thread:
Lists: pgsql-general
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Willem Buitendyk
> Sent: Wednesday, January 30, 2008 1:15 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] 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


In response to

pgsql-general by date

Next:From: Adam RichDate: 2008-01-30 21:47:22
Subject: Re: Oracle Analytical Functions
Previous:From: Willem BuitendykDate: 2008-01-30 21:15:16
Subject: Oracle Analytical Functions

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