Не показывать запись, если разница между временем двух записей меньше определенного интервала времени
У меня есть список с клиентами и датой их покупки:
| 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 шт):
Попробуйте такой простой запрос (на 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