Historia pytania
Mediana absolutne odchylenie (MAD) została wprowadzona przez Gaussa w 1816 roku jako solidny wskaźnik rozproszenia statystycznego, a następnie sformalizowana przez Hampela w latach 70-tych XX wieku dla analiz odpornych na wartości odstające. W przeciwieństwie do odchylenia standardowego, które kwadratuje odchylenia i dlatego jest nadwrażliwe na skrajne wartości, MAD toleruje do 50% zanieczyszczonych danych bez zniekształcenia. W ANSI SQL obliczanie MAD stało się praktyczne z normą SQL:2003, która wprowadziła funkcje agregujące z uporządkowanymi zestawami, takie jak PERCENTILE_CONT, co umożliwia deklaratywne obliczanie mediany bez pętli proceduralnych.
Problem
Obliczenie MAD wymaga zagnieżdżonej operacji mediany: najpierw należy określić medianę zbioru danych, a następnie znaleźć medianę absolutnych różnic pomiędzy każdą obserwacją a tą medianą. W ANSI SQL jest to wyzwaniem, ponieważ odwołanie się do wyniku agregacji w tej samej klauzuli SELECT w celu obliczenia poszczególnych odchyleń wymaga self-join lub skorelowanego podzapytania, co pogarsza wydajność na dużych zestawach danych czasowych. Dodatkowo, standardowe funkcje STDDEV generują zawyżone progi, gdy dane czujników zawierają skoki transmisji lub błędy kalibracji, co czyni solidne MAD niezbędnym do dokładnego wykrywania anomalii.
Rozwiązanie
Użyj Common Table Expression (CTE), aby podzielić obliczenia na logiczne etapy. Najpierw użyj PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category), aby obliczyć medianę dla każdej grupy. Następnie oblicz absolutne odchylenie dla każdego wiersza w odniesieniu do mediany grupy. Na koniec ponownie zastosuj PERCENTILE_CONT, aby wyprowadzić MAD z tych odchyleń. Ta metoda jest czysto oparte na zbiorach, wykorzystuje optymalizator silnika bazy danych do funkcji okna i unika przetwarzania wiersz po wierszu.
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;
Zakład produkcyjny zainstalował tysiące czujników wibracji na taśmach transportowych, aby przewidzieć awarie łożysk. Statyczne progi alarmowe zawiodły, ponieważ zimowe temperatury obniżały naturalnie bazowe wartości w porównaniu do lata, co powodowało fałszywe alarmy w zimnych miesiącach i pominięte sygnały w gorących miesiącach. Zespół inżynieryjny potrzebował statystycznej metody, która dostosowywała się do unikalnej historii każdego czujnika, nie będąc wpływaną przez sporadyczne zakłócenia transmisji.
Zespół rozważył trzy podejścia architektoniczne.
Przetwarzanie statystyczne po stronie klienta polegało na eksportowaniu dziennych zrzutów CSV do Pythona przy użyciu bibliotek Pandas i SciPy. Oferowało to bogate funkcje statystyczne i szybkość prototypowania, ale wprowadziło 24-godzinną latencję danych i stworzyło ryzyko bezpieczeństwa przez przenoszenie wrażliwych danych operacyjnych poza zaporę bazy danych SQL.
Proceduralne rozwiązania SQL wykorzystywały kursory i tabele tymczasowe do iterowania przez historię każdego czujnika, sortując wartości w celu zidentyfikowania środkowego wiersza. To podejście działało na starszych systemach, które nie miały nowoczesnych funkcji okna, jednak cierpiało na poważne problemy z wydajnością z powodu kompleksowości O(n²) oraz nadmiernych zatorów blokujących, co zajmowało ponad 45 minut na przetworzenie miliona wierszy.
Funkcje okien SQL ANSI implementowane za pośrednictwem CTE obliczały mediany w oparciu o zbiory za pomocą PERCENTILE_CONT. To rozwiązanie wykonywało się całkowicie w obrębie silnika bazy danych w mniej niż 800 milisekund i obejmowało 50 milionów rekordów, minimalizując przeciążenie sieci oraz korzystając z równoległości optymalizatora, choć wymagało zgodności z SQL:2003 lub nowszym.
Zespół wybrał podejście funkcji okien SQL ANSI, ponieważ zrównoważyło wydajność w czasie rzeczywistym z ścisłymi wymaganiami prawnymi dotyczącymi danych, które zabraniały eksportu danych. Uzyskane wartości MAD ustanowiły dynamiczne progi, w których każde odczyty przekraczające mediana ± 3 * MAD wywoływały natychmiastowe powiadomienia o konserwacji. To zmniejszyło liczbę fałszywych alarmów o 94% i wykryło trzy zbliżające się awarie łożysk dwa dni wcześniej niż poprzedni statyczny system.
Dlaczego MAD jest preferowane nad odchyleniem standardowym do wykrywania anomalii w systemach telemetrii opartych na SQL?
Odchylenie standardowe oblicza pierwiastek kwadratowy ze średniej kwadratów odchylenia od średniej, metryka, która eksploduje, gdy istnieją wartości odstające, ponieważ kwadratowanie wzmacnia duże odległości. W przeciwieństwie do tego, MAD używa mediany, która jest odpornym na rozkład estymatorem, który ignoruje wielkość ekstremalnych wartości odstających do 50% objętości danych. Dla implementacji ANSI SQL oznacza to, że uszkodzenie jednego czujnika wysyłającego wartość 9999 w znaczący sposób zawyży STDDEV, ale pozostawi MAD prawie bez zmian, zapobiegając fałszywemu zawyżeniu progów, które maskuje przyszłe subtelne anomalie.
Jak PERCENTILE_CONT i PERCENTILE_DISC różnią się przy obliczaniu median dla dyskretnych odczytów czujników i który powinieneś użyć do MAD?
PERCENTILE_CONT(0.5) wykonuje interpolację liniową między dwoma centralnymi wartościami, gdy liczba wierszy jest parzysta, zwracając hipotetyczną wartość, która może nie istnieć w twojej tabeli (np. uśredniając 20 i 30, aby zwrócić 25). PERCENTILE_DISC(0.5) zwraca najmniejszą rzeczywistą wartość z zestawu danych, której skumulowany rozkład jest większy lub równy 0.5. Przy obliczaniu MAD dla dyskretnych odczytów całkowitych czujników, PERCENTILE_DISC jest często bezpieczniejsze, ponieważ zapewnia, że próg odpowiada rzeczywistemu obserwowanemu pomiarowi, unikając ułamkowych odchyleń, które komplikują interpretację.
Czy MAD można obliczyć bez CTE z użyciem pojedynczego self-join, i jakie są kompromisy wydajnościowe?
Tak, ale jest to nieefektywne. Możesz złączyć tabelę na sensor_id, aby porównać każdy wiersz z każdym innym, aby znaleźć medianę, ale to prowadzi do złożoności O(n²). Alternatywnie, używając wyprowadzonego podzapytania do najpierw obliczenia mediany, a następnie łączenia się z powrotem w celu obliczenia odchyleń, zmusza bazę do zmaterializowania wyników pośrednich lub wielokrotnego przeszukiwania tabeli. CTE pozwala optymalizatorowi traktować obliczenie mediany jako bufor lub stół roboczy, który jest obliczany raz i wielokrotnie wykorzystywany, co zazwyczaj prowadzi do jednej operacji sortowania i liniowej złożoności O(n log n). Kandydaci często zapominają, że optymalizatory ANSI SQL mogą przekształcać CTE w wewnętrzne stoły robocze, czyniąc je bardziej wydajnymi niż skorelowane podzapytania w liście SELECT.