We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Pattern match with Timestamp Difference — Vertica Forum

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