Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Working with huge tables of chronological data

Re: Working with huge tables of chronological data

From: John <acide.ascorbique_at_gmail.com>
Date: 6 Apr 2007 07:55:13 -0700
Message-ID: <1175871313.425881.201500@n76g2000hsh.googlegroups.com>


Hi Jonathan,

Jonathan Lewis wrote:
> 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.
> ;

Won't this option b) only compute the consecutive A pairs? If I have:

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. The results would be: (A2, A3) ; (A3, A4) and also (A2, A4) even if A2 and A4 are not consecutive.

Thanks for helping!

John Received on Fri Apr 06 2007 - 09:55:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US