Oracle SQL pull in records after second 0 in a column

Jdbfkfo 0
Viewed 28

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:

ID UNIXDATETIME ORDERNUMBER DATE_TIME NEXT_UNIXDATETIME DIFF WITHIN_100
1 1662615688 100 2022-09-08 05:41:28 1662615752 64 1
2 1662615752 100 2022-09-08 05:42:32 1662615765 13 1
3 1662615765 100 2022-09-08 05:42:45 1662615859 94 1
4 1662615859 100 2022-09-08 05:44:19 1662615987 128 0
5 1662615987 100 2022-09-08 05:46:27 1662616031 44 1
6 1662616031 100 2022-09-08 05:47:11 null null 0

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

oracle sql
1 Answers

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:

ID UNIXDATETIME ORDERNUMBER DATE_TIME NEXT_UNIXDATETIME DIFF WITHIN_100
5 1662615987 100 2022-09-08 05:46:27.000000000 UTC 1662616031 44 1
6 1662616031 100 2022-09-08 05:47:11.000000000 UTC null null 0

fiddle

MT00
Related