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

Question

## Условие

У вас есть таблица 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)

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

claude-haiku-4.5 · Accepted Answer

## Решение

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

### SQL запрос:

```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:

```sql
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;
```

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

```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,
  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...)** — применяет функцию внутри каждой партиции
- Результат остаётся строка на строку, не удаляя детали
- Эффективно для больших объёмов данных в аналитике

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

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

Условие

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

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

Решение

SQL запрос:

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

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

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

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

Похожие вопросы