| 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 -> REPOST: Re: SQL Event Behaviour Pattern Query
On 22 Dec 2001 08:05:33 -0800, Thomas Kyte <tkyte_at_us.oracle.com>
wrote:
>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;
> 11 end;
> 12 /
>
>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'
> 15 /
>
>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 )
> 18 where event_name = 'A' and fe = 'A' and cnt > 0
> 19 /
>
>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 )
> 18 where event_name = 'A' and fe = 'A' and cnt > 0
> 19 /
>
>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
> 26 /
>
>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 Corp
>
Thank you very much for your really thorough reply.
Indeed, as I have no access to latest versions, SQL *Plus cannot do all the tricks for me, even if you show me the way.
Unreasonably maybe, I desperately tried to adapt your script and use the terms "over", "range", "preceding", but these were unknown commands.
Still, I have learned a lot and will keep experimenting. As a relative newcomer to SQL *Plus, I think I must grasp the basics quickly and start learning PL/SQL too, for that is the real tool in this business!
In the meantime, I'll carry on with my topsy-turvy manual or formulaic counting in Excel, where I export my torturously extracted queries. :-)
Some happy, lucky experts...!
Many thanks again.
Regards,
Thomas
This message was cancelled from within Mozilla. Received on Thu Dec 27 2001 - 05:04:53 CST
 |
 |