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

Re: Oracle Analytical Functions

From: Lewis Cunningham <lewisc(at)rocketmail(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:58:18
Message-ID: 16550.48532.qm@web35603.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)pcfish(dot)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/



In response to

pgsql-general by date

Next:From: Tom HartDate: 2008-01-30 22:01:40
Subject: Re: Mailing list archives/docs project
Previous:From: Ivan Sergio BorgonovoDate: 2008-01-30 21:49:42
Subject: Re: Mailing list archives/docs project

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