Сложный последовательный анализ пути пользователя (например, определение момента "отвала" в многошаговом процессе) можно реализовать через оконные функции, через self-join или процедурно вне SQL. Как выбрать подход для продакшен-пайплайна?

Короткий ответ

Выбор определяется не предпочтением языка, а формой задачи, объёмом, частотой изменения правил и местом потребления результата. Для LAG, LEAD, накопительных состояний и первого нарушения последовательности SQL обычно даёт лучший баланс.

Как это работает подробнее

Self-join понятен при сравнении двух конкретных событий, но число соединений растёт вместе с числом шагов и создаёт fan-out. Оконный запрос сортирует события внутри пользователя и позволяет анализировать соседние состояния в одной логической цепочке.

Процедурный код оправдан для сложных автоматов состояний, рекурсивных правил или внешних вызовов. Его цена — отдельная инфраструктура, сериализация данных и более трудная проверка промежуточных результатов.

Пример кода

journey_dropoff.sqlКопировать

WITH ordered AS (
  SELECT user_id, event_time, step_name,
         LEAD(step_name) OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS next_step
  FROM user_events
)
SELECT step_name, next_step, COUNT(DISTINCT user_id) AS users
FROM ordered
GROUP BY step_name, next_step
ORDER BY step_name, users DESC;

Что использовать на практике

• Начинайте с окон. Используйте стабильный порядок по времени и event_id.
• Материализуйте повторяемый результат. Не пересчитывайте тяжёлый путь для каждого дашборда.
• Профилируйте на реальном объёме. Сравнивайте стоимость сортировки, shuffle и размер промежуточных данных.

Подводные камни

• Неопределённый порядок событий. Одинаковое время без tie-breaker делает путь нестабильным.
• Self-join на много шагов. Он раздувает строки и скрывает неоднозначность сопоставления.
• Преждевременный перенос в код. Простая оконная логика превращается в дорогой и непрозрачный пайплайн.