Условие
Даны месячные срезы сегментов клиентов:
CREATE TABLE segment(date date, ClientID varchar(6), SegmentID int);
INSERT INTO segment VALUES
('2018-01-31','A11111',2),
('2018-02-28','A11111',2),
('2018-03-31','A11111',1),
('2018-04-30','A11111',1),
('2017-11-30','B22222',1),
('2017-10-31','B22222',1),
('2017-09-30','B22222',3),
('2017-09-30','C33333',1),
('2017-10-31','C33333',1);Получить периоды действия каждого сегмента у каждого клиента: (client, segment, period_start, period_end).
Ожидаемый результат:
A11111, 2, 2018-01-31, 2018-02-28
A11111, 1, 2018-03-31, 2018-04-30
B22222, 3, 2017-09-30, 2017-09-30
B22222, 1, 2017-10-31, 2017-11-30
C33333, 1, 2017-09-30, 2017-10-31
Решение
Подход: Gaps and Islands
Островная задача: для каждой строки определяем, новая ли это «полоса» (изменился сегмент относительно предыдущей по дате); затем накопительной суммой получаем grp-id и группируем.
SQL
WITH lagged AS (
SELECT
ClientID,
SegmentID,
date,
LAG(SegmentID) OVER (PARTITION BY ClientID ORDER BY date) AS prev_seg
FROM segment
),
flagged AS (
SELECT
ClientID, SegmentID, date,
CASE WHEN prev_seg IS DISTINCT FROM SegmentID THEN 1 ELSE 0 END AS is_new
FROM lagged
),
groups AS (
SELECT
ClientID, SegmentID, date,
SUM(is_new) OVER (PARTITION BY ClientID ORDER BY date) AS grp
FROM flagged
)
SELECT
ClientID,
SegmentID,
MIN(date) AS period_start,
MAX(date) AS period_end
FROM groups
GROUP BY ClientID, SegmentID, grp
ORDER BY ClientID, period_start;Альтернатива через сравнение с ROW_NUMBER
Классическая «островная» формула:
WITH numbered AS (
SELECT
ClientID, SegmentID, date,
ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY date) AS rn1,
ROW_NUMBER() OVER (PARTITION BY ClientID, SegmentID ORDER BY date) AS rn2
FROM segment
)
SELECT
ClientID, SegmentID,
MIN(date) AS period_start,
MAX(date) AS period_end
FROM numbered
GROUP BY ClientID, SegmentID, rn1 - rn2
ORDER BY ClientID, period_start;(rn1 - rn2) остаётся постоянной внутри непрерывной серии того же сегмента.
Подводные камни
- Пропуски месяцев: если у клиента за февраль нет среза — серия «прервалась». Уточнить, считать ли пропуск разрывом. По логике задачи (не пишут о разрывах) — это всё равно одна серия, если сегмент тот же. Тогда
LAG-подход корректен. IS DISTINCT FROMобрабатываетNULLкорректно (NULL <> 1даётNULL, неTRUE).- На массиве надо чёткое сортирование —
ORDER BY dateобязателен. - Если у одного клиента в одну дату два разных сегмента — данные битые; выбрать
MIN/MAXили вынести.
Эталонный ответ
Gaps and islands: LAG + флаг новой серии + SUM() OVER (или rn1 − rn2-трюк) → группировка по клиенту, сегменту и группе → MIN/MAX(date).