Re: SQL: Working with huge tables of chronological data

John ha scritto:
> 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?

I'm not a SQL guru...this is just a hint. First make a union of the two table sorting for timestamp and event type, add a sequence field.

1 3.55 A1
2 3.56 B1
3 3.58 A2

Then you can make a second query selecting every event and the next in sequence

3:55, A1, 3.56 B1
3.56, B1, 3.58 A2
...

and filtering the records where both event are A-type. Received on Sat Apr 07 2007 - 01:57:53 CDT