Блог

Главная - Вопросы по программированию - Нужна помощь для объединения перекрывающихся временных интервалов

Нужна помощь для объединения перекрывающихся временных интервалов

#postgresql

#postgresql

Вопрос:

Мне нужна помощь в объединении перекрывающихся временных интервалов, если интервал не превышает 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

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

Комментарии:

1. Вам придется написать некоторый код, чтобы это сделать. Обычный способ сохранить вопрос в допустимых пределах для SO — это начать работу самостоятельно, а затем показать нам, с какой частью этого у вас возникли проблемы.

2. Я удалил несовместимые теги базы данных. Пожалуйста, помечайте только конкретную базу данных.

3. Для каждой исходной строки уточняйте, как «эта строка должна быть сохранена / удалена, потому что …»

4. Что ж, это возможно, но вам было бы лучше это закодировать.

5. Пометьте свой вопрос базой данных, которую вы используете.

Ответ №1:

Вы хотите исключить пары приостановленных / запущенных с одинаковым идентификатором в течение 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 минут, что может сделать любая база данных, но синтаксис может отличаться.

Ответ №2:

Вы хотите отфильтровать определенные строки, что общего с удаляемыми строками? Кажется, вам нужны первая ‘начатая’ и последняя ‘приостановленная’ строки. Можете ли вы просто игнорировать «начатые» строки, если за предыдущие 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 минут.

Ответ №3:

Если вам нужно получить перекрывающиеся интервалы продолжительностью не более 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)