Всем привет! На связи Александр Грудинин, ментор профессии «Аналитик данных» 👋🏻
Разберём приём в SQL, который порой выглядит как магия, а держится на простой арифметике. Называется islands and gaps, «острова и промежутки».
✅ Задача
Найти пользователей, которые заходили в сервис 5 и более дней подряд. Как только начинаешь думать про «подряд», в голове каша из «оконок» и самосоединений. А решается всё элегантно.
✅ В чём фокус
Вот как выглядят даты входов одного пользователя. Заходил 3, 4, 5, 6, 7 января, потом пропал, вернулся 9 и 10:
2024-01-03 ← остров 1
2024-01-04
2024-01-05
2024-01-06
2024-01-07
2024-01-09 ← остров 2
2024-01-10
Глазами острова видно сразу: первый на пять дней, второй на два. А как объяснить это SQL? Хитрость такая: пронумеруем даты по порядку и вычтем номер из даты. Пока дни идут подряд, эта разность постоянна. Появился пропуск между 7 и 9 января: номер продолжает расти на 1, а дата скакнула на 3, разность подскочила и начался новый остров. Эта константа и есть идентификатор группы.
✅ Решение
with base as (
select
user_id,
login_ts::date as login_dt -- берём дату без времени: 10 входов за день это один день
from user_logins
group by 1, 2 -- схлопываем до одной строки на пользователя и день
),
grps as (
select
user_id,
login_dt,
row_number() over(partition by user_id order by login_dt) as rn,
login_dt - row_number() over(partition by user_id order by login_dt)::int as grp
from base
)
select
user_id,
count(login_dt) as cnt
from grps
group by user_id, grp
having count(login_dt) >= 5
Три шага. Первый: схлопываем таймстемпы до дат через group by, потому что может быть десять входов за день это всё равно один день. Второй: нумеруем дни и вычитаем номер из даты, получаем grp, идентификатор острова. Третий: группируем по пользователю и острову, оставляем серии от 5 дней.
Пример на PostgreSQL, в разных диалектах арифметика с датами устроена по-своему, так что вычитание номера из даты придётся адаптировать под ClickHouse, BigQuery и прочие.
Ставьте 🔥, если полезно!
📈 Симулейтив | 📱 ВК | 📱 YouTube | 📱 Канал о DS