🔥 Live Coding: rolling сумма за последние 7 дней

Задача: Есть таблица transactions:

• user_id • transaction_time • amount

Нужно для каждой транзакции посчитать сумму amount пользователя за последние 7 дней.

На выходе хотим получить:

• user_id • transaction_time • amount • rolling_7d_sum

Решение:

select user_id, transaction_time, amount, sum(amount) over ( partition by user_id order by transaction_time range between interval 7 day preceding and current row ) as rolling_7d_sum from transactions

🧠 Как здесь думаем:

Нам нужно не просто сгруппировать данные, а для каждой строки посмотреть назад во времени на 7 дней.

Для этого используем оконную функцию sum().

Что важно: • partition by user_id — считаем сумму отдельно по каждому пользователю • order by transaction_time — выстраиваем транзакции по времени • range between interval 7 day preceding and current row — берем окно от текущего момента минус 7 дней до текущей строки

То есть для каждой транзакции ClickHouse суммирует все amount этого пользователя, которые попали в последние 7 дней 👌

⚠️ Где можно ошибиться:

Перепутать range и rows

rows — это про количество строк range — это про диапазон значений

Здесь нам нужен именно диапазон по времени, поэтому используем range.

Забыть partition by user_id

Тогда окно посчитается сразу по всем пользователям, и результат будет неверный ❌

Не проверить тип transaction_time

Важно, чтобы это было нормальное поле даты/времени: DateTime или DateTime64. Иначе окно по interval может работать не так, как ожидается.

📌 Если у тебя ClickHouse или другая СУБД не поддерживает такой range по времени, задачу можно решать через self join или через условные агрегаты, но оконка здесь — самый чистый вариант.

🎯 Вывод:

Это классическая задача на rolling window.

Когда нужно посчитать метрику: • за последние 7 дней • за последние 30 дней • за последние N часов

чаще всего это решается через оконную функцию + временное окно.

Именно так обычно считают скользящие суммы, средние и другие time-based метрики 📊