PrepBro
← Назад к вопросам

SQL: Найти сессии длиннее среднего для пользователя

1.7 Middle🔥 191 комментариев
#SQL и базы данных

Условие

У вас есть таблица sessions:

  • session_id (integer)
  • user_id (integer)
  • session_date (date)
  • duration_seconds (integer)

Для каждой сессии определите, была ли она длиннее средней сессии этого пользователя.

Ожидаемый результат:

Таблица с колонками:

  • session_id
  • user_id
  • duration_seconds
  • user_avg_duration (средняя длительность сессий пользователя)
  • is_above_average (true/false)

Источник: типовая задача на оконные функции

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение

Это задача на применение оконных функций для сравнения значений со статистикой по группам.

SQL запрос:

SELECT 
  session_id,
  user_id,
  duration_seconds,
  ROUND(AVG(duration_seconds) OVER (PARTITION BY user_id), 2) as user_avg_duration,
  duration_seconds > AVG(duration_seconds) OVER (PARTITION BY user_id) as is_above_average
FROM sessions
ORDER BY user_id, session_date;

Как это работает:

AVG(duration_seconds) OVER (PARTITION BY user_id) вычисляет среднюю длительность сессии для каждого пользователя. Это оконная функция, которая сохраняет детальность данных — не агрегирует все в одну строку, а применяет функцию в контексте каждой строки.

Сравнение duration_seconds > AVG(...) OVER (...) возвращает TRUE если сессия длиннее среднего, FALSE иначе.

Альтернативный вариант с CASE:

SELECT 
  session_id,
  user_id,
  duration_seconds,
  ROUND(AVG(duration_seconds) OVER (PARTITION BY user_id), 2) as user_avg_duration,
  CASE 
    WHEN duration_seconds > AVG(duration_seconds) OVER (PARTITION BY user_id) 
    THEN true 
    ELSE false 
  END as is_above_average
FROM sessions;

Расширенный вариант с дополнительными метриками:

SELECT 
  session_id,
  user_id,
  duration_seconds,
  ROUND(AVG(duration_seconds) OVER (PARTITION BY user_id), 2) as user_avg_duration,
  duration_seconds > AVG(duration_seconds) OVER (PARTITION BY user_id) as is_above_average,
  ROUND(duration_seconds - AVG(duration_seconds) OVER (PARTITION BY user_id), 2) as diff_from_avg,
  MAX(duration_seconds) OVER (PARTITION BY user_id) as user_max_duration,
  MIN(duration_seconds) OVER (PARTITION BY user_id) as user_min_duration
FROM sessions
ORDER BY user_id, duration_seconds DESC;

Ключевые моменты:

  • PARTITION BY user_id — разбивает данные на группы по пользователям
  • OVER (PARTITION BY...) — применяет функцию внутри каждой партиции
  • Результат остаётся строка на строку, не удаляя детали
  • Эффективно для больших объёмов данных в аналитике

Этот запрос используется для выявления "выбросов" в поведении пользователя и анализа аномалий.