Re: SQL: Working with huge tables of chronological data

On Thu, 05 Apr 2007 07:27:23 -0700, 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, allow me to take a shot: Ralph Kimball describes the timeline table in his DW toolkit book. He uses fixed length intervals (connected with the warehouse granularity). Also, you may want to introduce a synthetic event C, defined as the absence of B in the given period. SQL, as opposed to Perl is not a solution for all problems.

-- 
http://www.mladen-gogala.com