Блог

Мне нужна помощь в объединении перекрывающихся временных интервалов, если интервал не превышает 4 минут (например, только там, где id = 1).

У меня есть следующая таблица:

 --------------------------------------
id | action    | date
--------------------------------------
1  | started   | 2020-08-18 13:51:02
1  | suspended | 2020-08-18 13:51:04
2  | started   | 2020-08-18 13:52:14
2  | suspended | 2020-08-18 13:52:17
3  | started   | 2020-08-18 13:52:21
3  | suspended | 2020-08-18 13:52:24
1  | started   | 2020-08-18 13:57:21
1  | suspended | 2020-08-18 13:57:22
1  | started   | 2020-08-18 15:07:56
1  | suspended | 2020-08-18 15:08:56
1  | started   | 2020-08-18 15:09:11
1  | suspended | 2020-08-18 15:09:11
1  | started   | 2020-08-18 15:09:11
1  | suspended | 2020-08-18 15:09:13
  

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

 --------------------------------------
id | action    | date
--------------------------------------
1  | started   | 2020-08-18 13:51:02
1  | suspended | 2020-08-18 13:51:04
1  | started   | 2020-08-18 13:57:21
1  | suspended | 2020-08-18 13:57:22
1  | started   | 2020-08-18 15:07:56
1  | suspended | 2020-08-18 15:09:13
  

Как это можно сделать? Я буду очень благодарен за вашу помощь!

Вы хотите исключить пары приостановленных / запущенных с одинаковым идентификатором в течение 4 минут. Используйте lag() и lead() :

 select t.*
from (select t.*,
             lag(date) over (partition by id order by date) as prev_date,
             lead(date) over (partition by id order by date) as next_date
      from t
     ) t
where (action = 'start' and
       prev_date > date - interval '4 minute'
      ) or
      (action = 'suspended' and
       next_date < date   interval '4 minute'
      );
  

Общеизвестно, что функции даты и времени зависят от базы данных. Это просто добавление или вычитание 4 минут, что может сделать любая база данных, но синтаксис может отличаться.

Вы хотите отфильтровать определенные строки, что общего с удаляемыми строками? Кажется, вам нужны первая ‘начатая’ и последняя ‘приостановленная’ строки. Можете ли вы просто игнорировать «начатые» строки, если за предыдущие 4 минуты была другая «начатая» строка, и игнорировать «приостановленные» строки, если в течение следующих 4 минут есть еще одна «приостановленная» строка?

 from my_table a
where action = 'started' and not exists (
    select 1 from my_table b
    where b.id = a.id and b.action = 'started'
      and datediff(minute, b.date, a.date) <= 4 -- row exists in the previous 4 min
)
  

То же самое для «приостановлено», но другим способом. Это не сработает, если разница между последним ‘запущенным’ и ‘приостановленным’ составляет > 4 минут, но это можно преодолеть с помощью другого условия для проверки отсутствия запуска в течение последних 4 минут.

Если вам нужно получить перекрывающиеся интервалы продолжительностью не более 4 минут, можете использовать этот запрос:

 --cte where creating groups with time intervals
with base_cte as
(
select Tab.id,Tab.NumGr,Tab.date,
Tab.action from
(
select * from
(
--selecting only values where time difference <= 4 min 
 select *,sum(TimeDiff)over(partition by id,NumGr order by date rows unbounded preceding)SumTimeInterval from
  (
--creating a group 
   select sum(Num)over(partition by id order by date rows unbounded preceding )NumGr, * from
    (
     select date,lead(date)over(partition by id order by date)lead_date,id,action, 
     lead(action)over(partition by id order by date)lead_action,
     --split intervals between overlaps (240seconds)
      iif(TimeDiff>240,1,0)Num,TimeDiff from
      (
       --find time difference in seconds between current and next date (partition by id)
        select datediff(second,date,LEAD(date)over(partition by id order by date))TimeDiff,* from Table
      )A
    )B
  )C 
  --selecting only pairs within time intervals
  where TimeDiff<=240
--checking duration interval:<=4 min
 )D where SumTimeInterval<=240
)E

CROSS JOIN LATERAL
(values (id,NumGr,date,action),
        (id,NumGr,lead_date,lead_action)
)Tab(id,NumGr,date,action)

)

--selectig data with start/end overlapping time interval
select id,date,action from base_cte base 
where date 
in (select max(date) from base_cte where NumGr=base.NumGr)
or date in
(select min(date) from base_cte where NumGr=base.NumGr)