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

Re: Oracle Analytical Functions

From: Enrico Sirola <enrico(dot)sirola(at)gmail(dot)com>
To: Adam Rich <adam(dot)r(at)sbcglobal(dot)net>
Cc: "'Willem Buitendyk'" <willem(at)pcfish(dot)ca>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Oracle Analytical Functions
Date: 2008-01-31 16:46:59
Message-ID: D07E268A-60A7-4CEF-A368-76FC13899ADF@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi Adam,

Il giorno 31/gen/08, alle ore 16:13, Adam Rich 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:
>>> 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:
>
> I thought of a another way of doing this.  In my tests, it's a little
> faster, too.
>
> DROP SEQUENCE if exists seq1;
> DROP SEQUENCE if exists seq2;
> CREATE TEMPORARY SEQUENCE seq1 CACHE 1000;
> CREATE TEMPORARY SEQUENCE seq2 CACHE 1000;
>
> select a.client_id, b.datetime, a.datetime as previousTime,  
> (b.datetime -
> a.datetime) as difftime from
> (select nextval('seq1') as s, client_id, datetime from  
> all_client_times
> order by client_id, datetime OFFSET 0) as a
> inner join
> (select nextval('seq2') as s, client_id, datetime from  
> all_client_times
> order by client_id, datetime OFFSET 0) as b
> on a.s=(b.s-1) where a.client_id=b.client_id

very interesting indeed. I guess this strategy is more interesting than
the trigger (or rule) based one when you perform much more inserts on  
the table that
the select you proposed above. It sounds strange that the select  
proposed
is faster than a (single) select on the same table with an additional  
previousTime
column populated via trigger/rule.
Bye,
e.



In response to

pgsql-general by date

Next:From: Daniel VeriteDate: 2008-01-31 17:17:55
Subject: Re: postgres startup method for ubuntu
Previous:From: Tony CadutoDate: 2008-01-31 16:30:18
Subject: Re: Is PostGreSql's Data storage mechanism "inferior"?

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