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

Пролог

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

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

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

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

Но сперва важно научиться определять выбросы (находить аномалии), причем делать это автоматически, а не «глазами».

Что такое выбросы

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

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

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

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

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

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

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

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

Писать запрос мы будем с использованием базового диалекта SQL, который будет понятен вам, какую бы СУБД вы ни использовали.

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

SELECT
  date,
  SUM(totals.visits) AS visits
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
GROUP BY date

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

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

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

IF(visits > visits_ma + 2 * visits_stddev, 1, 0) AS spike

Это условие означает следующее:

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

Проиллюстрируем найденные значения на графике:

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

IF(visits < visits_ma + 2 * visits_stddev, 1, 0) AS spike

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

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

Эпилог

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

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

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

Теперь Вы можете протестировать написанный нами выше запрос на своих данных.