Re: SQL: Working with huge tables of chronological data

John wrote:
> Thanks for your answers, here is a clarification:
>
> - Ax and Ay are any different A events (A1, A2...). But since I'm
> looking for chronological sequences, Ay has to happen after Ax.
>
> - If I merge the two tables A and B while respecting the chronology,
> this would lead to the following. I've also add an additionnal A4
> event here to clarify.
>
> Table A
> (03:50pm, A1)
> (03:55pm, A2)
> (03:58pm, A3)
> (03:59pm, A4)
>
> Table B
> (03:51pm, B1)
> (04:00pm, B2)
>
> 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. Here the results would be:
> (A2, A3) ; (A2, A4) and (A3, A4)
>
> Thanks!
>
> John
>
> On Apr 5, 10:59 am, DA Morgan <damor..._at_psoug.org> wrote:

>> John wrote:
>>> Hi,
>>> I'm trying to figure out an efficient way to search for the non
>>> existence of events in chronological data with SQL. The goal (detailed
>>> below) seems pretty simple but so far it looks like it's tricky to do
>>> with Oracle. Here's my problem:
>>> I'm working with 2 simple but huge tables each recording a different
>>> kind of event associated with a timestamp. For instance:
>>> Table A
>>> (03:50pm, A1)
>>> (03:55pm, A2)
>>> (03:58pm, A3)
>>> Table B
>>> (03:51pm, B1)
>>> (04:00pm, B2)
>>> I'm looking for all the chronological sequences (Ax, Ay) where no B
>>> event is present between Ax and Ay. In this example, the result would
>>> be (A2, A3).
>>> I've been searching actively for an efficient solution for this
>>> problem and I couldn't find any fast enough. Do you have any idea?
>>> Thanks a lot,
>>> John
>> To me your example and explantion make no sense.
>>
>> What Ax and Ay? Please try for more clarity with your explanation.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -

Sorry but your answer begets more questions:

Is this the real problem or a simplification? Is this something that will be run once or repeatedly? Is it possible for the same time to be in A and B? Is it possible to have a B before an A beginning the sequence? Is it possible for there to be multiple Bs between As?

And I am not at all surprised it is taking a lot of time.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org