Не показывать запись, если разница между временем двух записей меньше определенного интервала времени

У меня есть список с клиентами и датой их покупки:

id time_
1 05.04.2021 10:02:54
1 05.04.2021 10:02:58
1 05.04.2021 13:28:02
2 05.04.2021 11:02:54

Нужно вывести id клиента и время его покупки time_, но в случае, если покупка была осуществлена менее чем 2 минуты перед предыдущей покупкой, то такую покупку надо игнорировать.

Ожидаемый результат:

id time_
1 05.04.2021 10:02:58
1 05.04.2021 13:28:02
2 05.04.2021 11:02:54

Я попыталась с помощью ROW_NUMBER создать колонку, которая содержала бы номер следующей покупки и показывать покупки, где разница между временем предыдущей и последующей покупки более 120 секунд, но столкнулась с проблемой, что при создании условия difference_seconds> 120 в подзапросе t3 изменяются значения ROW_NUMBER в t2.

Каким образом можно это исправить?

То есть, SELECT * from t2 дает результат:

id time_ id2 time_2 rn
1 05.04.2021 10:02:54 1 05.04.2021 10:02:58 1
1 05.04.2021 10:02:54 1 05.04.2021 13:28:02 2
1 05.04.2021 10:02:58 1 05.04.2021 13:28:02 1
1 05.04.2021 13:28:02 null null 1
2 05.04.2021 11:02:54 null null 1

Но:

SELECT * FROM t2 
WHERE  difference_seconds>120
id time_ id2 time_2 rn
1 05.04.2021 10:02:54 1 05.04.2021 13:28:02 1
1 05.04.2021 10:02:58 1 05.04.2021 13:28:02 1
1 05.04.2021 13:28:02 null null 1
2 05.04.2021 11:02:54 null null 1

Мой код:

WITH t1 AS (
  SELECT id, time_
  FROM base_name)
, t2 AS (
  SELECT t1.id, t1.time_, t2.id AS id2, t2.time_ AS time_2,
    ROUND((t2.time_-t1.time_)*24*60*60,0) AS difference_seconds
    ROW_NUMBER() OVER (
      PARTITION BY t1.id, t1.time_ ORDER BY t1.time_ ASC, t2.time_ ASC) AS rn 
FROM t1 LEFT JOIN t1 t2 ON t1.id=t2.id AND t2.time_>t1.time_)
, t3 AS (
  SELECT * FROM t2 
  WHERE rn=1
  AND difference_seconds>120
) 
SELECT * FROM t3 

Ответы (1 шт):

Автор решения: 0xdb

Попробуйте такой простой запрос (на db<>fiddle):

with q as (
    select 
        q.*, case when (q.ts-prev) > interval '2' minute then 'y' end matched 
    from (
        select t1.*, lag (t1.ts, 1, date'-4712-01-01') over (
            partition by id order by t1.ts asc) prev
    from t1) q)
select q.id, to_char (q.ts, 'dd.mm.yyyy hh24:mi:ss') time
from q
where matched = 'y'

Результат:

        ID TIME               
---------- -------------------
         1 05.04.2021 10:02:54
         1 05.04.2021 13:28:02
         2 05.04.2021 11:02:54
→ Ссылка