Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Working with huge tables of chronological data
Hi Jonathan,
Jonathan Lewis wrote:
> Option b)
> Use the analytic lag(,1) function
>
> select
> flag, event, prior_event, timestamp, prior_timestamp
> from (
> select
> flag, lag(flag,1) over (order by timestamp) prior_flag,
> event, lag(event,1) over (order by timestamp) prior_event
> timestamp, lag(timestamp,1) over (order by timestamp) prior_timestamp
> from
> (the union all query)
> where
> flag = prior_flag
> and flag = 'A' -- if you just want A's without a B in between.
> ;
Won't this option b) only compute the consecutive A pairs? If I have:
Chronology
(03:50pm, A1) (03:51pm, B1) (03:55pm, A2) (03:58pm, A3) (03:59pm, A4) (04:00pm, B2)
I'm looking for all the sequences of events A in the chronology with no B event in the middle. The results would be: (A2, A3) ; (A3, A4) and also (A2, A4) even if A2 and A4 are not consecutive.
Thanks for helping!
John Received on Fri Apr 06 2007 - 09:55:13 CDT