Блог

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

Например: Объект 4 инициировал событие 1, но во время выполнения этого события он также инициировал Событие 2. Поскольку событие 2 находится внутри временных рамок выполнения События 1, его необходимо отбросить.

 DateIni             DateEnd               ObjectId  Description
2021-04-24 11:59:24 2021-04-24 12:00:24   4         Event 1
2021-04-24 11:59:25 2021-04-24 12:00:23   4         Event 2
2021-04-23 20:23:35 2021-04-23 20:24:35   11        Event 3
2021-04-23 20:23:37 2021-04-23 20:24:32   11        Event 4
2021-04-23 15:17:12 2021-04-23 15:18:12   2         Event 5
2021-04-23 15:17:14 2021-04-23 15:18:09   2         Event 6
 

Я делаю это, но до сих пор безуспешно.

 from Table A
INNER join Table B
    on (A.DateEnd < B.DateIni AND A.DateIni >= B.DateEnd)
    or (A.DateEnd >= B.DateIni AND A.DateIni <= B.DateEnd)
    or (A.DateEnd > B.DateIni AND A.DateIni < B.DateEnd)
    or (A.DateEnd <= B.DateIni AND A.DateIni > B.DateEnd)
    and B.ObjectId = A.ObjectId 
    and B.Description != A.Description
 

Есть какие-нибудь предложения о том, как это можно сделать?

Возможно ли включить событие, даты которого не пересекаются? Если нет — тогда было бы проще просто использовать row_number() поверх набора и получить первое событие.

Вы можете сделать что — то подобное-могут быть лучшие варианты, но это должно сработать:

  --==== Sample data
Declare @testData Table (DateIni datetime, DateEnd datetime, ObjectId int, Description varchar(30));
 Insert Into @testData (DateIni, DateEnd, ObjectId, [Description])
 Values ('2021-04-24 11:59:24', '2021-04-24 12:00:24',  4, 'Event 1')
      , ('2021-04-24 11:59:25', '2021-04-24 12:00:23',  4, 'Event 2')
      , ('2021-04-24 12:59:25', '2021-04-24 13:00:23',  4, 'Event 7')
      , ('2021-04-23 20:23:35', '2021-04-23 20:24:35', 11, 'Event 3')
      , ('2021-04-23 20:23:37', '2021-04-23 20:24:32', 11, 'Event 4')
      , ('2021-04-23 21:23:37', '2021-04-23 21:24:32', 11, 'Event 8')
      , ('2021-04-23 15:17:12', '2021-04-23 15:18:12',  2, 'Event 5')
      , ('2021-04-23 15:17:14', '2021-04-23 15:18:09',  2, 'Event 6')
      , ('2021-04-23 16:17:14', '2021-04-23 16:18:09',  2, 'Event 9');

 --==== Result using above sample data
   With previousDates
     As (
 Select *
      , PreviousEndDate = lag(td.DateEnd, 1) over(Partition By td.ObjectId Order By td.DateIni)
      , PreviousStartDate = lag(td.DateIni, 1) over(Partition By td.ObjectId Order By td.DateIni)
   From @testData               td
        )
 Select pd.DateIni
      , pd.DateEnd
      , pd.ObjectId
      , pd.Description
   From previousDates           pd     
  Where 1 = Case When pd.DateIni Between pd.PreviousStartDate And pd.PreviousEndDate Then 0
                 When pd.DateEnd Between pd.PreviousStartDate And pd.PreviousEndDate Then 0
                 Else 1
             End;
   
 

Теперь — если все, что нас интересует, это когда началось следующее событие — и если это следующее событие началось до завершения предыдущего события:

  --==== Result using above sample data
   With previousDates
     As (
 Select *
      , PreviousEndDate = lag(td.DateEnd, 1) over(Partition By td.ObjectId Order By td.DateIni)
   From @testData               td
        )
 Select pd.DateIni
      , pd.DateEnd
      , pd.ObjectId
      , pd.Description
   From previousDates           pd     
  Where pd.PreviousEndDate Is Null
     Or pd.PreviousEndDate < pd.DateIni;
 

Или — мы можем определить, является ли это событие первым событием, и отфильтровать его таким образом:

  --==== Result using above sample data
   With firstEvents
     As (
 Select *
      , isFirstEvent = iif(lag(td.DateEnd, 1, td.DateEnd) over(Partition By td.ObjectId Order By td.DateIni) <= td.DateEnd, 1, 0)
   From @testData               td
        )
 Select fe.DateIni
      , fe.DateEnd
      , fe.ObjectId
      , fe.Description
   From firstEvents             fe
  Where fe.isFirstEvent = 1;
 

Вы бы просто использовали not exists :

 select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.objectid = t.objectid and
                        t2.dateini < t.dateini and
                        t2.dateend > t.dateend
                 );
 

Для повышения производительности вам нужен индекс (objectid, dateini, dateend) .