How can I edit my query to only pull in records after the SECOND most recent 0 value in the "Within_100" field

my query:

SELECT id,
       unixdatetime,
       ordernumber,
       date_time,
       next_unixdatetime,
       next_unixdatetime - unixdatetime AS diff,
       CASE cls
       WHEN 'WITHIN_100' THEN 1
       ELSE 0
       END AS within_100
from   (
  select distinct 
         ID,
         UnixDateTime,
         OrderNumber,
         TIMESTAMP '1970-01-01 00:00:00 UTC'   UnixDateTime * INTERVAL '1' SECOND
           AS Date_Time
  from   DB
  where  TIMESTAMP '1970-01-01 00:00:00 UTC'   UnixDateTime * INTERVAL '1' SECOND
           > SYSTIMESTAMP - INTERVAL '2' DAY
)
MATCH_RECOGNIZE(
  ORDER BY unixdatetime
  MEASURES
    NEXT(unixdatetime) AS next_unixdatetime,
    classifier() AS cls
  ALL ROWS PER MATCH
  PATTERN (within_100* any_row)
  DEFINE
    within_100 AS NEXT(unixdatetime) < unixdatetime   100
) m

output result:

so i would only want to pull in ID records 5 & 6 thank you

CodePudding user response：

Add $ to the pattern to only match the most recent pattern and not a global match:

SELECT id,
       unixdatetime,
       ordernumber,
       date_time,
       next_unixdatetime,
       next_unixdatetime - unixdatetime AS diff,
       CASE cls
       WHEN 'WITHIN_100' THEN 1
       ELSE 0
       END AS within_100
from   (
  select distinct 
         ID,
         UnixDateTime,
         OrderNumber,
         TIMESTAMP '1970-01-01 00:00:00 UTC'   UnixDateTime * INTERVAL '1' SECOND
           AS Date_Time
  from   DB
  where  TIMESTAMP '1970-01-01 00:00:00 UTC'   UnixDateTime * INTERVAL '1' SECOND
           > SYSTIMESTAMP - INTERVAL '2' DAY
)
MATCH_RECOGNIZE(
  ORDER BY unixdatetime
  MEASURES
    NEXT(unixdatetime) AS next_unixdatetime,
    classifier() AS cls
  ALL ROWS PER MATCH
  PATTERN (within_100* any_row $)
  DEFINE
    within_100 AS NEXT(unixdatetime) < unixdatetime   100
) m

Which, for the sample data:

CREATE TABLE db (ID, UnixDateTime, OrderNumber) AS
SELECT 1, 1662615688, 100 FROM DUAL UNION ALL
SELECT 2, 1662615752, 100 FROM DUAL UNION ALL
SELECT 3, 1662615765, 100 FROM DUAL UNION ALL
SELECT 4, 1662615859, 100 FROM DUAL UNION ALL
SELECT 5, 1662615987, 100 FROM DUAL UNION ALL
SELECT 6, 1662616031, 100 FROM DUAL;

Outputs:

fiddle