Re: Working with huge tables of chronological data

"John" <acide.ascorbique_at_gmail.com> wrote in message news:1175871313.425881.201500_at_n76g2000hsh.googlegroups.com...
> 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
>

Correct, the option using the analytic lag() would only give you the adjacent pairs.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html