oracle SQL counter restarts when time difference is > x

Question

I want to create a new column in my query whereby it takes into account the difference of the current rows datetime - previous datetime. This column could be a counter where if the difference is <-100, it stays as 1, but once there difference is > -100, the column is 0.

Ideally then I would want to only pull in the rows that come after the last 0 record.

My query:

with products as (
select * from (
select  distinct 
ID,
UnixDateTime,
OrderNumber,
to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(UnixDateTime,'SECOND')+ 1/24  as "Date_Time"
from DB
where
(date '1970-01-01' + UnixDateTime * interval '1' second) + interval '1' hour 
> sysdate - interval '2' day
)
),
prod_prev AS (
 SELECT p.*, 

        lag("Date_Time")over(order by "Date_Time" ASC) as Previous_Time,
        lag(UnixDateTime)over(order by "Date_Time" ASC) as UnixDateTime_Previous_Time "Date_Time") - "Date_Time" AS diff
   FROM products p    
), 
run_sum AS (
 SELECT p.*, "Date_Time"-Previous_Time as "Diff", UnixDateTime_Previous_Time-UnixDateTime AS "UnixDateTime_Diff"
   FROM prod_prev p    
)
SELECT * FROM run_sum
ORDER By UnixDateTime, "Date_Time" DESC

my query result from above query:

ID	UnixDateTime	OrderNumber	Date_Time	Previous_Time	diff	UnixDateTime_Diff
1	1662615688	100	08-SEP-2022 06:41:28	(null)	(null)	(null)
2	1662615752	100	08-SEP-2022 06:42:32	08-SEP-2022 06:41:28	0.00074	-64
3	1662615765	100	08-SEP-2022 06:42:45	008-SEP-2022 06:42:32	0.000150	-13
4	1662615859	100	08-SEP-2022 06:44:19	08-SEP-2022 06:42:45	0.001088	-128
5	1662615987	100	08-SEP-2022 06:46:27	08-SEP-2022 06:44:19	0.00148	-44
6	1662616031	100	08-SEP-2022 06:47:11	08-SEP-2022 06:46:27	0.00051	-36

the counter is the below example should be 1 if the UnixDateTime_Diff is < -100 and 0 if its >-100 then if I could only pull in records AFTER the most recent 0 record.

Answer 1

You use:

lag("Date_Time")over(order by "Date_Time" DESC)

And get the previous value when the values are ordered in DESCending order; this will get the previous higher value. If you want the previous lower value then either use:

lag("Date_Time") over (order by "Date_Time" ASC)

or

lead("Date_Time") over (order by "Date_Time" DESC)

---

If you want to perform row-by-row processing then, from Oracle 12, you can use MATCH_RECOGNIZE:

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
1	1662615688	100	2022-09-08 05:41:28.000000000 UTC	1662615752	64	1
2	1662615752	100	2022-09-08 05:42:32.000000000 UTC	1662615765	13	1
3	1662615765	100	2022-09-08 05:42:45.000000000 UTC	1662615859	94	1
4	1662615859	100	2022-09-08 05:44:19.000000000 UTC	1662615987	128	0
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