dimanche 25 juin 2023

Need help using match_recognize in Oracle 21c

I truly hope I can find understaning here as to what is wrong with this code. I have some equipment that send event data as event tags to a database; each timestamped with the time of the equipment of origin.

There is a problem with an equipment's peripheral, and as a consequence these affected equipment sent the relevant event tags, exactly 16 of them, everytime, chronologically ordered. Searching the data manually on the table, it does contain the pattern, coming from different equipment ID.

The purpose is to quantify how many times this problem (pattern) happpens, and on which equipment.

As I was researching what function could help me, I came across Oracle MATCH_RECOGNIZE as a possible tool to obtain a solution. If I am not mistaken, this function uses or emulates regex of some sort (I am not an regex expert). I haven't been able to fine tune the code to show me if it can pick the pattern. I was using inside the PATTERN clause either a '+' (one or more -- plus quantifier), or '*' (zero or more -- star quantifier). If I use +, the result is zilch. If I use * I get data, but there also appears other tags with do not belong to the pattern.

What I would like is to make the code obtain the pattern, with no other event tag.

Below I am sharing the code.

--DROP TABLE TESTPATTERN
SELECT
TO_char(EventDateTime, 'YYYY-MM-DD HH:MM:SS')AS EventDateTime,
EqpID,
EventTag
FROM TESTPATTERN 
MATCH_RECOGNIZE (
 PARTITION BY EqpID
 ORDER BY EventDateTime
    MEASURES
        FIRST(EventDateTime) AS start_date,
        LAST(EventDateTime) AS end_date,
        FIRST(EventTag) AS first_tag,
        LAST(EventTag) AS last_tag
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW 
PATTERN (EventTag1+ EventTag2+ EventTag3+ EventTag4+ EventTag5+ EventTag6+ EventTag7+ EventTag8+ EventTag9+ EventTag10+ EventTag11+ EventTag12+ EventTag13+ EventTag14+ EventTag15+ EventTag16+ EventDateTime1+)
 DEFINE
EventTag1 AS EventTag = 'THOR',
  EventTag2 AS EventTag = 'MJOLNIR',
  EventTag3 AS EventTag = 'LOKI',
  EventTag4 AS EventTag = 'HULK',
  EventTag5 AS EventTag = 'HAWKEYE',
  EventTag6 AS EventTag = 'CAPNAMER',
  EventTag7 AS EventTag = 'FURY',
  EventTag8 AS EventTag = 'STARK',
  EventTag9 AS EventTag = 'FURY',
  EventTag10 AS EventTag = 'STARK',
  EventTag11 AS EventTag = 'THOR',
  EventTag12 AS EventTag = 'MJOLNIR',
  EventTag13 AS EventTag = 'LOKI',
  EventTag14 AS EventTag = 'HULK',
  EventTag15 AS EventTag = 'HAWKEYE',
  EventTag16 AS EventTag = 'CAPNAMER',
  EventDateTime1 as EventDateTime > prev(EventDateTime)
) MR
WHERE 1=1
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)
AND (START_DATE IS NOT NULL OR END_DATE IS NOT NULL OR FIRST_TAG IS NOT NULL OR LAST_TAG IS NOT NULL)

Thank you.

Aucun commentaire:

Enregistrer un commentaire