Pattern match with Timestamp Difference

Jack123

Hi,

 

I am using the pattern match function. I want to associate difference of timestamp say about some 1 minute within which these pattern should occur. How can I add this condition in the define clause.

I want (E2.Timestamp-E1.timestamp) < 1 minute. In oracle we can do as its mentioned in https://docs.oracle.com/database/121/DWHSG/pattern.htm#DWHSG9303. However, I get an error if try to impose the timestamp difference based on Event (as in Oracle). Need pointers to proceed.

 

Sample Query:

 

select SYS,EventTimeStamp,EventId,AdditionalInfo, event_name()Event_1,pattern_id() as Pattern_1,match_id() as Match_1

from
T1
MATCH (
PARTITION By SYS
Order by EventTimeStamp
DEFINE
E1 AS EventId LIKE '%AMBNM%',
E2  As EventId LIKE '%ABNXC%' 
PATTERN P as (E1 E1 E2 +?)
ROWS MATCH FIRST EVENT )

colinwurtz

I would use what you have in your sample query and write another query on top to specify the time differences.

 

You also may need to utilize the pattern_id, event_id, and lead/lag functions to compare two adjacent row timestamps to one another.

 

Something like this:

 

selct 
*
from
(
 -- your match query
)data
where 
timestampdiff(end,start,'mm') > 1