Pattern match with Timestamp Difference

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 )

Comments

  • 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

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file