Mladen Gogala wrote:
> 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.
Similarly a pipelined table function could generate all possible times
and be joined.
--
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
Received on Thu Apr 05 2007 - 17:46:11 CDT
