Как найти самую длинную последовательность пересекающихся событий?

Question

У меня есть табличка Events со следующими полями:

Id
CreationDate
DeletionDate
PostId
Name

Для одного PostId может быть произвольное количество записей в таблице Events. Мне необходимо подсчитать самую длинную последовательность событий пересекающихся во времени для каждого PostId. Два события пересекаются во времени, если у них совпадает PostId и CreationDate одного события попадает в интервал между CreationDate и DeletionDate другого.

Подскажите, как реализовать подсчет наибольшей последовательности для произвольного количества событий?

Answer 1

У меня получился такой запрос

SELECT 
  PostId,
  MAX(Cnt) MaxCnt
FROM 
( 
  SELECT 
    e1.PostId,
    COUNT(IIF(e2.CreationDate BETWEEN e1.CreationDate AND e1.DeletionDate, 1, NULL)) Cnt
  FROM Events e1
    INNER JOIN Events e2 ON e2.PostId = e1.PostId
  GROUP BY
    e1.PostId, e1.CreationDate, e1.DeletionDate
) AS X
GROUP BY 
  PostId

Основаня идея:

Мы бежим по табличке Events (e1)
Для каждой записи e1 находим совпадающие с ней по PostId записи в той же табличке Events (e2).
С помочью BETWEEN проверяем совпадение интервала.
Если интервал совпадает, увеличиваем счетчик.
На выходи имеем все интервалы для каждого PostId.
Остается сгруппировать и найти максимальное значение.

Answer 2

WITH cte AS ( SELECT t1.PostId, 
                     COUNT(t2.PostId) cnt, 
                     ROW_NUMBER() OVER (PARTITION BY t1.PostId ORDER BY COUNT(t2.PostId) DESC) rn
              FROM Events t1
              JOIN Events t2 ON t1.PostId = t2.PostId
                            AND t2.CreationDate BETWEEN t1.CreationDate AND t1.DeletionDate
              GROUP BY t1.PostId, t1.CreationDate )
SELECT PostId, cnt MaxCnt
FROM cte           
WHERE rn = 1

fiddle

БЛОГ НА HUSL

Как найти самую длинную последовательность пересекающихся событий?

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