🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
Как найти серии подряд идущих дней в SQL
Классическая задача gaps and islands: есть таблица событий visits, которая имеет поля:
▶️client_id (id клиента)
▶️visit_date (дата визита)
Нужно понять, сколько дней подряд клиент был активен, какая у него максимальная серия и когда она прервалась.
🟠Если пронумеровать визиты клиента по порядку через ROW_NUMBER и вычесть номер из даты, то у всех дней внутри одной непрерывной серии получится одно и то же значение. Как только в датах появляется дыра, значение меняется, значит, началась новая серия.
SELECT
client_id, visit_date,
visit_date-ROW_NUMBER()
OVER (PARTITION BY client_id
ORDER BY visit_date)*INTERVAL '1 day' AS grp
FROM visits
Дальше достаточно сгруппировать по client_id и grp:
WITH marked AS (запрос выше)
SELECT client_id,
MIN(visit_date) AS начало,
MAX(visit_date) AS конец,
COUNT(*) AS длина
FROM marked
GROUP BY client_id, grp
ORDER BY длина DESC
В итоге выведутся все серии по каждому клиенту с датами начала, конца и длиной. Максимальная серия достаётся обычным MAX(длина), текущая - фильтром по конец = CURRENT_DATE.
❗️Но есть нюансы, на которые нужно обратить внимание:
▶️Дубли. Если на клиента в один день приходится несколько строк, нумерация поедет и серии порвутся. Перед расчётом нужен DISTINCT или агрегация по дню.
▶️Шаг. Приём работает для равномерного шага. Для месяцев нужно вычитать номер из номера месяца. Для недель - привести дату к началу недели. А если разрывом считается произвольный интервал (например, пауза больше 30 дней), то вместо вычитания берётся LAG и накопительная сумма флага разрыва.