Re: Oracle Analytical Functions

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.