Search code examples
sqloracle-databasepattern-matchingoracle12c

Oracle - Match_recognize /\ shape detect

sample data from https://livesql.oracle.com/apex/livesql/file/tutorial_EG95N4HMCYSL1A0Y6J80UHNHB.html

Based on this data and visualization, there is 2 /\ shape's:

enter image description here

1st - from 1.05 to 06.04

2nd - from 06.04 to 12.04

and this query shows 2nd as 07.04 to 12.04:

enter image description here

I'm new in Oracle Match_recognize, and i am wondering - why is that?

Solution

  • By default, MATCH_RECOGNIZE does not allow the same row to be in different matches. If you want the last value in one match to be the first value in the next match, you need to modify the AFTER MATCH SKIP clause.

    enter image description here

    The default setting is AFTER MATCH SKIP PAST LAST ROW. Adding the code AFTER MATCH SKIP TO LAST DOWN to your query will give the results you want.

    See this db<>fiddle for a runnable example.