| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Event Behaviour Pattern Query
In article <3c246581.6245402_at_news.easynet.co.uk>,
library.treasures_at_NOSPAMsaqnet.co.uk says...
>
>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
>
>
>
sigh, no version. Ok, I'll assume Oracle8i release 2 (816) and up EE. Then, analytic functions can be of great help here. Consider a table like this:
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( event_name varchar2(1), dt
date );
Table created.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare
2 l_event_name varchar2(1);
3 l_dt date default sysdate;
4 begin
5 for i in 1 .. 1000
6 loop
7 l_event_name := chr( ascii('A') + dbms_random.value(0,3) );
8 l_dt := l_dt + 1/24/60/60 * dbms_random.value(1,10);
9 insert into t values ( l_event_name, l_dt );
10 end loop;
PL/SQL procedure successfully completed.
so that has events a, b, c, d and the time between events is 1 to 10 seconds... Now to find your stuff..
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set nls_date_format =
'hh24:mi:ss';
Session altered.
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select *
2 from ( select event_name, dt, 3 count(decode(event_name,'B','B',null)) 4 over ( order by dt desc 5 range (5/24/60/60) preceding ) cnt, 6 last_value(event_name) 7 over ( order by dt desc 8 range(5/24/60/60) preceding) fe 9 from t 10 where event_name in ( 'A', 'B' ) 11 ) 12 where event_name = 'A' 13 and cnt > 0 14 and fe = 'A'
what we've done in the inline view is to create a sliding window that for each row in the table -- includes all of the rows that happen within 5 seconds after it (we ordered DESC so the preceding rows are after the current row timewise). 5/24/60/60 is just 5 seconds in DATE speak.
In this window (which only consists of the two events of interest -- A and B, we capture two bits of data:
3 count(decode(event_name,'B','B',null)) 4 over ( order by dt desc 5 range (5/24/60/60) preceding ) cnt,
the count of B's that occurred and:
6 last_value(event_name) 7 over ( order by dt desc 8 range(5/24/60/60) preceding) fe
which gets us the first event (time wise) in that sliding window. We are only interested ultimately in windows that begin with EVENT_NAME = 'A' and have some B values in them. That is what:
12 where event_name = 'A'
13 and cnt > 0 14 and fe = 'A'
does for us, we are finding the windows that start with A (fe = 'A'), have some B's in them (cnt > 0) and we are really only interested in the A events (event_name = 'A')
This shows us:
E DT CNT F
- -------- ---------- -
A 12:20:43 1 A A 12:17:46 1 A A 12:17:39 1 A A 12:12:42 1 A A 12:08:16 1 A A 12:02:01 1 A A 11:55:08 1 A A 11:53:59 1 A A 11:51:42 1 A A 11:50:51 1 A A 11:43:06 2 A A 11:42:51 2 A A 11:34:15 1 A A 11:34:13 1 A A 11:32:56 1 A A 11:25:23 1 A A 11:18:24 1 A A 11:17:20 1 A A 11:16:58 1 A A 11:16:38 1 A A 11:10:41 1 A A 11:08:14 1 A A 11:01:29 1 A A 11:00:08 1 A A 10:57:41 1 A A 10:56:59 1 A A 10:54:43 2 A A 10:54:11 1 A
28 rows selected.
So, from 10:54:11 to 12:20:43 there were 28 occurrences when an A was followed by a B within 5 seconds. 3 times, an A was followed by a B more then once....
Now, what if you wanted to see the chain of events (just hypothesizing on the next question....) You could start with this:
ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select *
2 from ( select event_name,
3 first_value( dt ) 4 over ( order by dt desc 5 range (5/24/60/60) preceding ) fv, 6 last_value( dt ) 7 over ( order by dt desc 8 range (5/24/60/60) preceding ) lv, 9 last_value(event_name) 10 over ( order by dt desc 11 range(5/24/60/60) preceding) fe, 12 count(decode(event_name,'B','B',null)) 13 over ( order by dt desc 14 range (5/24/60/60) preceding ) cnt 15 from t 16 where event_name in ( 'A', 'B' ) 17 )
E FV LV F CNT
- -------- -------- - ----------
A 12:20:45 12:20:43 A 1 A 12:17:50 12:17:46 A 1 A 12:17:41 12:17:39 A 1 A 12:12:45 12:12:42 A 1 A 12:08:20 12:08:16 A 1 A 12:02:03 12:02:01 A 1 A 11:55:11 11:55:08 A 1 A 11:54:03 11:53:59 A 1 A 11:51:47 11:51:42 A 1 A 11:50:56 11:50:51 A 1 A 11:43:11 11:43:06 A 2 A 11:42:54 11:42:51 A 2 A 11:34:17 11:34:15 A 1 A 11:34:17 11:34:13 A 1 A 11:32:59 11:32:56 A 1 A 11:25:28 11:25:23 A 1 A 11:18:25 11:18:24 A 1 A 11:17:24 11:17:20 A 1 A 11:17:01 11:16:58 A 1 A 11:16:40 11:16:38 A 1 A 11:10:44 11:10:41 A 1 A 11:08:18 11:08:14 A 1 A 11:01:34 11:01:29 A 1 A 11:00:12 11:00:08 A 1 A 10:57:43 10:57:41 A 1 A 10:57:04 10:56:59 A 1 A 10:54:46 10:54:43 A 2 A 10:54:13 10:54:11 A 1
28 rows selected.
Thats basically the same query with more information. We get the window of time in which the observations occurred. The FV and LV columns show the size of the window. So for example, sometime between 10:54:11 and 10:54:13 -- A was followed by a B -- the window was 2 seconds long.... To find the average window size:
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select round( avg( fv-lv ) * 24 * 60 * 60, 2
) avg_seconds
2 from ( select event_name,
3 first_value( dt ) 4 over ( order by dt desc 5 range (5/24/60/60) preceding ) fv, 6 last_value( dt ) 7 over ( order by dt desc 8 range (5/24/60/60) preceding ) lv, 9 last_value(event_name) 10 over ( order by dt desc 11 range(5/24/60/60) preceding) fe, 12 count(decode(event_name,'B','B',null)) 13 over ( order by dt desc 14 range (5/24/60/60) preceding ) cnt 15 from t 16 where event_name in ( 'A', 'B' ) 17 )
AVG_SECONDS
3.36
We can join that to see the actual chain of events for the "interesting" ones (the ones with cnt > 0) like this:
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * 2 from ( select *
3 from t 4 where event_name in ( 'A','B' ) 5 ) a, 6 ( select event_name, 7 first_value( dt ) 8 over ( order by dt desc 9 range (5/24/60/60) preceding ) fv, 10 last_value( dt ) 11 over ( order by dt desc 12 range (5/24/60/60) preceding ) lv, 13 last_value(event_name) 14 over ( order by dt desc 15 range(5/24/60/60) preceding) fe, 16 count(decode(event_name,'B','B',null)) 17 over ( order by dt desc 18 range (5/24/60/60) preceding ) cnt 19 from t 20 where event_name in ( 'A', 'B' ) 21 ) b 22 where b.event_name = 'A' 23 and b.fe = 'A' 24 and b.cnt > 1 25 and a.dt between b.lv and b.fv
E DT E FV LV F CNT - -------- - -------- -------- - ----------
A 11:43:06 A 11:43:11 11:43:06 A 2 B 11:43:07 A 11:43:11 11:43:06 A 2 B 11:43:11 A 11:43:11 11:43:06 A 2 A 11:42:51 A 11:42:54 11:42:51 A 2 B 11:42:53 A 11:42:54 11:42:51 A 2 B 11:42:54 A 11:42:54 11:42:51 A 2 A 10:54:43 A 10:54:46 10:54:43 A 2 B 10:54:45 A 10:54:46 10:54:43 A 2 B 10:54:46 A 10:54:46 10:54:43 A 2
9 rows selected.
Analytic functions, the coolest thing to happen to SQL since "SELECT"
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Sat Dec 22 2001 - 10:05:33 CST
 |
 |