1 Reply Latest reply on Jan 17, 2014 7:11 PM by Marcelinho-Oracle

    MATCH_RECOGNIZE question

    Marcelinho-Oracle

      I have a MATCH_RECOGNIZE CQLquery (see below) and wonder, why an event is always just in one match, not in multiple, as it seems.

       

      Let's say I have an event stream like this:

      E1-E2-E3-E4

       

      Assume my PATTERN(A B) is a hit for E1-E2, E2-E3 and E3-E4.

       

      Why do I just get E1-E2 and E3-E3 as a result not the E2-E3 pair? Can't E2 take the role of A and B at the same time?

      I'm pretty sure there's a reason for it I just don't understand. Can someone explain?

       

      Here's my cql  query, schematic and simplified:

       

      select ... from Channel

      MATCH_RECOGNIZE (

         PARTITION BY ...

         MEASURES ...

         PATTERN (A B)

         DEFINE

           A as ...,

           B as ...

      ) as MATCH

       

      The original query is (Use: filter temperature changes by sensor / remove redundancy):

       

      select

         MATCH.deviceId as deviceId,

         MATCH.temp as temp,

         MATCH.eventDate as eventDate,

         MATCH.tempDiff as tempDiff 

      from TempInChannel

         MATCH_RECOGNIZE (

            PARTITION BY deviceId

            MEASURES

               B.deviceId as deviceId,

               B.temp as temp,

               B.eventDate as eventDate,

               B.temp-A.temp as tempDiff

            PATTERN (A B)

            DEFINE

               A as 1=1,

               B as B.temp<>A.temp and B.deviceId=A.deviceId

         ) as MATCH

       

      thanks,

      Marcel

        • 1. Re: MATCH_RECOGNIZE question
          Marcelinho-Oracle

          OK, I can answer my own question. Reading the doc helps sometimes ;-).

           

          One can go for overlapping patterns by specifying "ALL MATCHES":

           

          select

             MATCH.deviceId as deviceId,

             MATCH.temp as temp,

             MATCH.eventDate as eventDate,

             MATCH.tempDiff as tempDiff 

          from TempInChannel

             MATCH_RECOGNIZE (

                PARTITION BY deviceId

                MEASURES

                   B.deviceId as deviceId,

                   B.temp as temp,

                   B.eventDate as eventDate,

                   B.temp-A.temp as tempDiff

                ALL MATCHES

                PATTERN (A B)

                DEFINE

                   A as 1=1,

                   B as B.temp<>A.temp and B.deviceId=A.deviceId

             ) as MATCH