espermatch-recognize

match_recognize pattern matches without seeing an event

In this use case, the overall pattern for breaching capacity is A followed by B, or B followed by A when the capacity goes above or below 100 ... for which the below match_recognize code works perfectly!

  expression breach { (A, B) => B.seqid > A.seqid }

  select * from Event
  match_recognize (
  partition by tg, sy, route 
  measures breach(A, B) as breached, A as a, B as b
  after match skip to current row
  pattern (A B | B A)
  define 
      A as A.capacity < 100,
      B as B.capacity >= 100);

The only missing piece to above is, if the first event arrives that is >=100 before an event that is < 100, then the pattern should also fire (in which case I figure A would be equal to null.)

So in a nutshell ...

(B before the first A | A B | B A)

The results for the events below are captured in the comments ...

Event={seqid=0, tg="TGA", sy="IBM", route="DMA", capacity=120} // breach right off the bat ... which the pattern doesn't cover :( 

Event={seqid=1, tg="TGA", sy="IBM", route="DMA", capacity=25}
Event={seqid=2, tg="TGA", sy="IBM", route="DMA", capacity=30}
Event={seqid=3, tg="TGA", sy="IBM", route="DMA", capacity=35}
Event={seqid=4, tg="TGA", sy="IBM", route="DMA", capacity=55}
Event={seqid=5, tg="TGA", sy="IBM", route="DMA", capacity=60}
Event={seqid=6, tg="TGA", sy="IBM", route="DMA", capacity=50}

Event={seqid=7, tg="TGA", sy="IBM", route="DMA", capacity=160}  // breach == true for TGA/IBM/DMA
Event={seqid=8, tg="TGA", sy="IBM", route="DMA", capacity=180}
Event={seqid=9, tg="TGA", sy="IBM", route="DMA", capacity=200}

Event={seqid=11, tg="TGB", sy="IBM", route="DMA",capacity=50}   // successive messages has percent drop from 200 to 50
                                                                // doesn't unbreach TGA/IBM/DMA, different partition 
Event={seqid=12, tg="TGB", sy="IBM", route="DMA", capacity=60}
Event={seqid=13, tg="TGB", sy="IBM", route="DMA", capacity=70}
Event={seqid=14, tg="TGB", sy="IBM", route="DMA", capacity=110} // breaches == true for TGB/T/DMA

Event={seqid=10, tg="TGA", sy="IBM", route="DMA", capacity=40}  // breach == false for TGA/IBM/DMA

I have experimented with expressions like ...

not A B
!A B
A{0} B

None of which are legal expressions in Esper.

Is there a way to create a pattern that also fires when an event first arrives and is >=100 along with the existing pattern?

(Esper notebook for this example can be found over at https://notebook.esperonline.net/#/notebook/2HSBG1CS4)

Snowflake provides a nice example of the ability to match with the beginning of a partition

pattern (^ gt75)
define
    gt75 as price > 75.00

From https://docs.snowflake.com/en/user-guide/match-recognize-introduction.html#label-match-recognize-introduction-search-start-or-end-partition

Solution

  • If the pattern should fire without the A, then the pattern is "pattern (A B | B)".

    Or alternatively just:

    select * from B(capacity >= 100)