How about something like this:
SELECT
client_id
, datetime
, lagged as previoustime
, datetime - lagged difftime
FROM (
SELECT
client_id
,datetime
,(SELECT MAX(datetime)
FROM all_client_times def
WHERE def.client_id = abc.client_id
AND def.datetime < abc.datetime) as lagged
FROM all_client_times abc
)
WHERE lagged is not null
If you have records with no previous data or multiple rows, you'll
need to play with this to get it to work but it should point in the
right direction.
Hope that helps,
LewisC
--- Willem Buitendyk <willem@pcfish.ca> wrote:
> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire
> to
> choose an index scan if your joining column's datatypes do
> not
> match
>
Lewis R Cunningham
An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/
LewisC's Random Thoughts
http://lewiscsrandomthoughts.blogspot.com/