Creating required SQL script

Question

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:

Why `SELECT .. from dual`? Just do `INSERT INTO TEST1 (ID,DOC,C_TIME) VALUES (100,'A1', TO_DATE('2020-09-27 13:20:43','YYYY-MM-DD HH24:MI:SS'));` — Wernfried Domscheit, Oct 06 '20 at 14:56
@WernfriedDomscheit: or, since we have a timestamp, `INSERT INTO TEST1 (ID,DOC,C_TIME) VALUES (100,'A1', TIMESTAMP '2020-09-27 13:20:43')` — GMB, Oct 06 '20 at 14:58
You expect `100 A1` but not `101 A1` and `102 A1` but again you want `103 A1`. What is the logic here ? — Sujitmohanty30, Oct 06 '20 at 15:24
@Sujitmohanty30 I don't expect `101 A1` because **c_time** of `101 A2` subtract **c_time** of `101 A1` is more than 3 minutes — Rahid Zeynalov, Oct 06 '20 at 15:42

score 1 · Answer 1 · answered Oct 06 '20 at 14:51

1

You can use lag():

select t.*
from (
    select t.*, lag(c_time) over(partition by id order by c_time) lag_c_time
    from test1 t
) t
where c_time < lag_c_time + interval '3' minute or lag_c_ctime is null

It is unclear whether you want to first row per id or not. The above query brings it - if you don't, just remove condition lag_c_time is null from the where clause.

answered Oct 06 '20 at 14:51

GMB

216,147
25
84
135

Hi dear @GMB I have tried this one but it didn't returns me the second row, evean if i add the is null. – Rahid Zeynalov Oct 06 '20 at 14:58
@RahidZeynalov: it does return the row where `id` is `1` and `doc` is `'A2'` (if that's what you caled the "second row"). See [this db fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=d232a9ae0c9432daf647f418bc20b790). – GMB Oct 06 '20 at 15:02
If subtraction between second and first c_time column is less than 3 minutes , then yes I want to see these rows too – Rahid Zeynalov Oct 06 '20 at 15:12

score 1 · Accepted Answer · answered Oct 06 '20 at 16:51

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

Yes you are great , Thank you :) – Rahid Zeynalov Oct 06 '20 at 16:56 — Rahid Zeynalov, Oct 06 '20 at 16:56

Creating required SQL script

2 Answers2