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:

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.

CodePudding user response：

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:

fiddle