Pattern match with Timestamp Difference
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.
select SYS,EventTimeStamp,EventId,AdditionalInfo, event_name()Event_1,pattern_id() as Pattern_1,match_id() as Match_1
PARTITION By SYS
Order by EventTimeStamp
E1 AS EventId LIKE '%AMBNM%',
E2 As EventId LIKE '%ABNXC%'
PATTERN P as (E1 E1 E2 +?)
ROWS MATCH FIRST EVENT )
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
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: