Search code examples
sqloracledatetimesubquerywindow-functions

Creating required SQL script

My next question is about creating SQL script as required. Here is my wish: I want to select ID and DOC columns from my TEST1 table where the difference(subtraction) between two consecutive operations over ID column is less than three minutes. Here is my creating table and inserting test dates script:

CREATE TABLE TEST1(ID NUMBER , DOC CHAR(2), C_TIME TIMESTAMP);

INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(100,'A1',timestamp '2020-09-27 13:20:43');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(100,'A2',timestamp '2020-09-27 13:21:12');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(100,'A3',timestamp '2020-09-27 13:25:40');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(101,'A1',timestamp '2020-09-27 14:12:20');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(101,'A2',timestamp '2020-09-27 14:20:32');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(102,'A1',timestamp '2020-09-27 11:10:54');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(102,'A2',timestamp '2020-09-27 14:30:52');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(102,'A3',timestamp '2020-09-27 15:21:15');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(103,'A1',timestamp '2020-09-27 17:41:11');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(103,'A2',timestamp '2020-09-27 17:42:56');
INSERT INTO TEST1(ID,DOC,C_TIME) VALUES(103,'A3',timestamp '2020-09-27 17:44:01');
COMMIT;

So, my required output will be green rows:

enter image description here

Solution

  • If I understood it correctly, you not only require previous row value using lag but in case of first record for an id the difference in time should also be checked with the next row using lead and then finally subtract and check the difference,

    select t.id,t.doc,t.c_time
from (
    select t.*
         , lag(c_time,1) over(partition by id order by c_time) lag_c_time
         , lead(c_time,1) over(partition by id order by c_time) lead_c_time
    from test1 t
) t
where abs(extract( minute from (c_time - coalesce(lag_c_time,lead_c_time)))) < 3