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.
---------------------------(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
