Re: Working with huge tables of chronological data

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

Interesting problem.

The data size and available resources are likely to make a big difference when testing solutions for feasibility.

Here's a possibility, start with:

select 'A' flag, event, timestamp from tableA union all
select 'B', event, timestamp from tableB

Option a)
Order by timestamp. Open a pl/sql cursor on the result set and walk the data one row at a time, reporting rows when the current and previous rows are 'A' rows.

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. ;

I may have some errors in the analtyic code, but I hope there's enough there to give you the right idea.

Either option will lead to a massive sort operation on all your data.

-- 
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