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

Re: Oracle Analytical Functions

From: Enrico Sirola <enrico(dot)sirola(at)gmail(dot)com>
To: Willem Buitendyk <willem(at)pcfish(dot)ca>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Oracle Analytical Functions
Date: 2008-01-31 13:49:44
Message-ID: 1FDFCC02-159B-4B44-B16A-80672DC39FAC@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi Willem,

Il giorno 30/gen/08, alle ore 22:15, Willem Buitendyk ha scritto:

> 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

I'd create a "previousTime" column and manage it using a trigger.  
Anyway, it depends on the time-dependancy of the table
Then you can perform "temporal" in a much easier way.
You could be interested in taking a look at the following link

http://www.cs.arizona.edu/~rts/tdbbook.pdf

Cheers,
e.


In response to

Responses

pgsql-general by date

Next:From: Karsten HilbertDate: 2008-01-31 14:03:33
Subject: Re: postgres startup method for ubuntu
Previous:From: Simon RiggsDate: 2008-01-31 13:28:48
Subject: Re: [GENERAL] Backup

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