Непрерывные периоды активности в SQL Server

Таблица активности хранит отдельные наблюдения, а бизнесу часто нужны непрерывные периоды: сколько дней клиент был активен подряд и где серия прервалась? Прежде чем выбирать оконную функцию, определите непрерывность. Соседние строки не обязательно означают соседние календарные дни.

Единица наблюдения

В примере одной датой клиента считается одно наблюдение. Несколько событий за день учитываются один раз. Без нормализации повторные даты искажают группировку и длину серии. Во входных данных специально есть повтор, пропущенный день и другой клиент.

CREATE TABLE #Activity(CustomerId int, ActivityDate date);
INSERT #Activity VALUES
(1,'20230101'),(1,'20230102'),(1,'20230102'),
(1,'20230104'),(1,'20230105'),(2,'20230102');
;WITH days AS
(SELECT DISTINCT CustomerId, ActivityDate FROM #Activity),
previous AS
(SELECT *, LAG(ActivityDate) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate) AS PrevDate FROM days),
flags AS
(SELECT *, CASE WHEN DATEDIFF(day, PrevDate, ActivityDate)=1
 THEN 0 ELSE 1 END AS NewIsland FROM previous),
groups AS
(SELECT *, SUM(NewIsland) OVER
 (PARTITION BY CustomerId ORDER BY ActivityDate
  ROWS UNBOUNDED PRECEDING) AS IslandId FROM flags)
SELECT CustomerId, MIN(ActivityDate) AS StartDate,
 MAX(ActivityDate) AS EndDate, COUNT(*) AS ActiveDays
FROM groups GROUP BY CustomerId, IslandId
ORDER BY CustomerId, StartDate;

Первая CTE удаляет повторные пары клиента и даты. LAG определяет предыдущую различную дату этого клиента. Флаг отмечает начало или расстояние, отличное от одного дня. Накопительная сумма превращает флаги в номер группы, а итоговая агрегация вычисляет начало, конец и число активных дней.

Для клиента 1 ожидаются периоды 1-2 января и 4-5 января. У клиента 2 отдельный однодневный период. COUNT(*) означает дни, поскольку первый этап установил такую гранулярность. Если оставить повторы, он считал бы события. Явная рамка ROWS делает смысл накопительной суммы понятным.

Бизнес-календарь и границы отчёта

Календарные дни, рабочие дни и последовательные наблюдения означают разные вещи. Пятница и понедельник разрывают календарную серию, но могут продолжать рабочую. Для рабочего календаря используйте поддерживаемую таблицу с последовательным номером рабочего дня и сравнивайте номера, а не прибавляйте календарные сутки.

Если источник хранит время, выберите часовую зону отчёта до выделения даты. Перевод из UTC может переместить событие на предыдущую или следующую локальную дату. Переходы на летнее время изменяют прошедшие часы, поэтому проверка ровно двадцати четырёх часов не равна проверке последовательных локальных дат. Сохраняйте исходную временную отметку для расследований.

Фильтрация создаёт дополнительную границу. Серия, видимая первого февраля, могла начаться в январе. Определите, показывается обрезанный интервал или полная серия. Для настоящего начала требуется дополнительная история либо заранее сохранённое предыдущее состояние. Исправление подписи без чтения истории ничего не доказывает.

Когда нужен другой алгоритм

Пересекающиеся интервалы объединяются иначе. Возьмите интервалы от 1 до 10, от 2 до 3 и от 9 до 12. Сравнение третьего начала только с предыдущим концом ошибочно обнаружит разрыв. Нужно сравнивать с накопительным максимумом всех предыдущих концов и заранее определить включение или исключение границ.

В дневном сценарии индекс по клиенту и дате может помочь устранению повторов и сортировке, однако окончательный ответ даёт план. На большой истории исследуйте число обработанных строк и сбросы сортировки на диск. Рано ограничивайте нужных клиентов, сохраняя достаточную историю для полного начала серии.

Проверьте один день, повторы, нескольких клиентов, пропуск и пересечение границы отчёта. Добавьте опоздавшее событие: заполнение отсутствующего дня объединяет две прежние группы. Поэтому вычисленные номера островов не являются автоматически постоянными бизнес-ключами. Если отчёты сохраняются, определите, должны ли они пересчитываться после поздних событий или оставаться историческим снимком. Для инкрементального расчёта недостаточно просто дописать новую группу: опоздавшая дата может потребовать пересчёта соседних периодов. Сохраните небольшой набор ожидаемых результатов и проверяйте его при изменении календаря или часовой зоны. Надёжность обеспечивает чёткий договор о календаре и проверка границ, а не краткость выражения SQL.

Техническая документация: Microsoft Learn: LAG · Microsoft Learn: OVER.