Re: SQL: Working with huge tables of chronological data

On Apr 5, 5:07 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> What can we do with the above to avoid the Cartesian join as much as
> possible? We can use LEAD to peek at the next set of values:
> SELECT
> TO_CHAR(TIME_DATE,'HH24:MI') TIME_DATE,
> TO_CHAR(LEAD(TIME_DATE,1) OVER (ORDER BY TIME_DATE),'HH24:MI')
> NEXT_TIME_DATE,
> TIME_SLOT,
> LEAD(TIME_SLOT,1) OVER (ORDER BY TIME_DATE) NEXT_TIME_SLOT,
> T1_V1,
> T1_V2
> FROM
> (SELECT
> NVL(T1.V1,T2.V1) TIME_DATE,
> DECODE(T1.V1,NULL,'B',NVL2(T2.V1,'AB','A')) TIME_SLOT,
> TO_CHAR(T1.V1,'HH24:MI') T1_V1,
> T1.V2 T1_V2,
> TO_CHAR(T2.V1,'HH24:MI') T2_V1,
> T2.V2 T2_V2
> FROM
> T1 FULL OUTER JOIN T2 ON T1.V1=T2.V1
> ORDER BY
> NVL(T1.V1,T2.V1));
Wouo thanks for such a big and detailed answer!

However I think this query only search for consecutive A pairs. As in the previous post, 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 - 10:06:36 CDT