Ответ на вопрос

История вопроса

Медиана абсолютного отклонения (MAD) была введена Гауссом в 1816 году как надежная мера статистического разброса, а позже формализована Хэмпелем в 1970-х для аналитики, устойчивой к выбросам. В отличие от стандартного отклонения, которое возводит отклонения в квадрат и, следовательно, гиперчувствительно к экстремальным значениям, MAD допускает до 50% загрязненных данных без искажения. В ANSI SQL расчет MAD стал практичным с стандартом SQL:2003, который ввел агрегатные функции с упорядоченной выборкой, такие как PERCENTILE_CONT, позволяя декларативные расчеты медианы без процедурных циклов.

Проблема

Расчет MAD требует вложенной операции медианы: сначала нужно определить медиану набора данных, а затем найти медиану абсолютных различий между каждым наблюдением и этой медианой. В ANSI SQL это сложно, поскольку наличие агрегатного результата в одном SELECT запросе для вычисления индивидуальных отклонений требует самосоединения или коррелированного подзапроса, что ухудшает производительность на больших временных рядах. Более того, стандартные функции STDDEV производят завышенные пороговые значения, когда данные сенсоров содержат всплески передачи или ошибки калибровки, что делает надежный MAD необходимым для точного обнаружения аномалий.

Решение

Используйте Общее выражение таблицы (CTE) для разделения вычислений на логические этапы. Сначала используйте PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category) для расчета медианы по группам. Затем вычислите абсолютное отклонение для каждой строки относительно её медианы группы. Наконец, снова примените PERCENTILE_CONT к этим отклонениям для получения MAD. Этот метод основан на работе с множествами, использует оптимизатор движка базы данных для оконных функций и избегает построчной обработки.

WITH group_medians AS (
  SELECT 
    sensor_id,
    reading,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY reading) 
      OVER (PARTITION BY sensor_id) AS median_val
  FROM telemetry
),
deviations AS (
  SELECT 
    sensor_id,
    ABS(reading - median_val) AS abs_dev
  FROM group_medians
)
SELECT DISTINCT
  sensor_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY abs_dev) 
    OVER (PARTITION BY sensor_id) AS mad
FROM deviations;

Ситуация из жизни

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

Команда рассмотрела три архитектурных подхода.

Обработка статистики на стороне клиента включала экспорт ежедневных CSV-дампов в Python с использованием библиотек Pandas и SciPy. Это предложило богатые статистические функции и быструю прототипизацию, но ввело 24-часовую задержку данных и создало риски безопасности, перемещая чувствительные эксплуатационные данные за пределы межсетевого экрана SQL базы данных.

Процедурные решения SQL использовали курсоры и временные таблицы для итерации по истории каждого сенсора, сортируя значения для определения средней строки. Этот подход работал на устаревших системах, лишенных современных оконных функций, но страдал от серьезного ухудшения производительности из-за сложности O(n²) и чрезмерного ожидания блокировок, занимая более 45 минут для обработки одного миллиона строк.

В оконных функциях ANSI SQL, реализованных через CTE, медианы вычислялись на основе множеств с помощью PERCENTILE_CONT. Это решение выполнялось полностью внутри движка базы данных за менее чем 800 миллисекунд на 50 миллионов записей, минимизировав сетевые накладные расходы и использовав оптимизацию параллелизма, хотя требовало соблюдения SQL:2003 или новее.

Команда выбрала подход с использованием оконных функций ANSI SQL, потому что он обеспечивал баланс между производительностью в реальном времени и строгими требованиями по управлению данными, которые запрещали экспорт данных. Полученные значения MAD установили динамические пороговые значения, где любое показание, превышающее median ± 3 * MAD, вызвало немедленные оповещения о техническом обслуживании. Это снизило количество ложных срабатываний на 94% и обнаружило три предстоящих отказа подшипников на два дня раньше, чем предыдущая статическая система.

Что кандидаты часто пропускают

Почему MAD предпочтительнее стандартного отклонения для обнаружения аномалий в системах телеметрии на базе SQL?

Стандартное отклонение вычисляет квадратный корень от среднего квадрата отклонения от среднего, метрика, которая взрывается, когда существуют выбросы, потому что возведение в квадрат усиливает большие расстояния. В то время как MAD использует медиану, которая устойчива к выбросам, игнорируя величину экстремальных отклонений до 50% объема данных. Для реализаций ANSI SQL это означает, что одна неисправность сенсора, отправляющая значение 9999, значительно увеличит STDDEV, но оставит MAD практически неизменным, предотвращая ложное увеличение порога, которое скрывает будущие тонкие аномалии.

Как PERCENTILE_CONT и PERCENTILE_DISC различаются при вычислении медиан для дискретных показаний сенсоров, и что из этого следует использовать для MAD?

PERCENTILE_CONT(0.5) выполняет линейную интерполяцию между двумя центральными значениями, когда количество строк четное, возвращая гипотетическое значение, которое может не существовать в вашей таблице (например, усредняя 20 и 30 для возврата 25). PERCENTILE_DISC(0.5) возвращает наименьшее действительное значение из набора данных, чья кумулятивная вероятность больше или равна 0.5. Для расчета MAD на дискретных целых показаниях сенсоров PERCENTILE_DISC часто безопаснее, потому что он гарантирует, что порог соответствует реальному наблюдаемому измерению, избегая дробных отклонений, которые усложняют интерпретацию.

Можно ли рассчитать MAD без CTE, используя одно самосоединение, и каковы компромиссы в производительности?

Да, но это неэффективно. Вы можете самосоединить таблицу по sensor_id, чтобы сравнить каждую строку с каждой другой строкой для нахождения медианы, но это приводит к сложности O(n²). Альтернативно, использование производного подзапроса для вычисления медианы сначала, а затем присоединения для расчета отклонений, заставляет базу данных материализовать промежуточные результаты или повторно сканировать таблицу несколько раз. CTE позволяют оптимизатору рассматривать расчет медианы как временное хранилище, пригодное для повторного использования, что, как правило, приводит к одной операции сортировки и линейной сложности O(n log n). Кандидаты часто забывают, что оптимизаторы ANSI SQL могут преобразовывать CTE в внутренние временные таблицы, что делает их более эффективными, чем коррелированные подзапросы в списке SELECT.