SQL Event Behaviour Pattern Query

Hello All,

I would like to query an Event table with SQL Plus*, to analyse the behaviour patterns in which the various kinds of events that are therein logged follow or precede each other within set time limits.

The basic columns of this table are Event_Name and Date.

Say, I conjecture there must be a strong tendency for event "B" to follow event "A" within 5 seconds, because already I saw several examples of this behaviour in the table.

How could I verify my suspicion by selecting and/or counting each pair of rows in the table where "A" is followed by "B" in 5, or in general, n seconds?
(Meaning that each latest "A" is followed by that "B" which occurs the earliest after that latest "A". Here, symmetry may not necessarily apply, however. For example, if there were several "As" with no any "Bs" between them, and then a "B" comes along, it would be too far fetched for me to guess which "A" caused that "B", considering my insofar unproven suspicion of causation in the first place.)

Exapmple:

EVENT_NAME                  DATE
------------------------                  -----------------

E                                        xxxxxx
F                                        xxxxxx
D                                       etc....
A     This "A" and
B     this "B" are a pair (the first pair)
C
A     This "A" or
A     this "A"  and
C
B     this "B" are a pair (the second and third pairs)

Further, how could I find easiest the avearage time that is elapsed between these pairs of "As" and "Bs"?

Also, if I am not a nuisance, if there was any kind suggestion for expressing the ratio between those "As" that are followed by "Bs" within a given time, and those "As" that are not. (Percentage would also be suitable.)

Many thanks in advance for any help!

Thomas Received on Sat Dec 22 2001 - 06:06:31 CST