Обнаружение выбросов с помощью одного SQL-запроса

Часто статистическая обработка данных затрудняется тем, что данные не очищены: либо присутствуют пробелы, либо выбросы. Выбросы, вообще говоря, косвенно хранят в себе очень много информации. Экстремальные значения позволяют проводить параллели между, казалось бы, несвязанными событиями и использовать полученную информацию при анализе. 

Очевидные примеры: почему 7 марта резко увеличились продажи цветов? Или почему сегодня “Вечернего Урганта” (выпуск с Моргенштерном) посмотрело на 1,5 млн человек больше? 

Но, к сожалению, редко ответы, как здесь, лежат на поверхности. 

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

Но сперва важно научиться определять выбросы (находить аномалии). 

Чтобы продемонстрировать один из подходов к обнаружению выбросов в данных, мы исследуем статистику посещения некоторого веб-сайта.

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

Что же такое выбросы? Визуально - это пики или скачки. С точки зрения алгоритмов - это значения, которые выходят за пределы ожидаемого диапазона.

Уметь визуально определять всплески - это, конечно, здорово, но в реальном мире это не очень полезно. Во-первых, не всегда есть возможность визуализировать данные. Во-вторых, эру ручного мониторинга и анализа мы давно оставили позади. 

Это означает, что необходимо осваивать новые автоматизированные методы для поиска выбросов. 

И в Python, и в R, и в других языках программирования разработаны целые библиотеки с различными методами поиска выбросов. 

Но сегодня мы научимся писать простые запросы для обнаружения скачков в данных с помощью SQL.

Процесс обнаружения выбросов

Сначала нужно собрать необходимые данные. В нашем случае - количество ежедневных посещений сайта.

Теперь мы находим скользящее среднее и стандартное отклонение для количества посещений, используя 7-дневное окно до текущего дня. Размер окна может быть больше или меньше в зависимости от данных.

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

Это условие означает следующее: если количество посещений в текущий день превышает среднее значение + 2 стандартных отклонения, то это значение мы считаем выбросом. 

Так как выбросы могут быть не только выше основного диапазона значений, но и ниже, можно использовать еще и следующее условие: 

Можно регулировать чувствительности обнаружения пиков, меняя количество стандартных отклонений.

Описанные выше условия нахождения скачков - не единственные. Аналогичным образом можно применять и другие критерии, в зависимости от распределения Ваших данных. Например, Критерий Роснера для нормально распределенных величин, Критерий Кимбера для экспоненциально распределенных величин, Критерий Дарлинга (для любого непрерывного распределения) и так далее.

Так будет выглядеть готовый запрос:

WITH aggregation AS (
  SELECT
    date,
    SUM(totals.visits) AS visits
  FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
  GROUP BY date
),
 
 aggregation_avgs AS (
  SELECT
    *,
     AVG(visits) OVER(ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS visits_ma,
     STDDEV(visits) OVER(ORDER BY date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS visits_stddev
  FROM aggregation
)
 
SELECT
  date,
  visits,
  IF(visits > visits_ma + 2 * visits_stddev, 1, 0) AS spike
FROM aggregation_avgs
WHERE date >= '20170101'
ORDER BY date

Видите, все довольно просто. Вы можете настроить программу так, чтобы результаты запроса приходили как уведомления или предупреждения по электронной почте. Удобство заключается в том, что этот простой метод можно применить к любым временным рядам.

Эпилог

Теперь Вы можете протестировать написанный нами выше запрос на своих данных. Если у Вас есть вопросы по конкретным алгоритмами обнаружения выбросов, то не стесняйтесь, пишите о них в комментариях. И мы с радостью Вам об этом расскажем!

Увеличивайте свой багаж знаний вместе с IT Resume!