Odpowiedź na pytanie.

Historia pytania.

Statystyczna moda reprezentuje najczęściej występującą wartość w zbiorze danych. Podczas gdy ANSI SQL definiuje standardowe funkcje agregujące takie jak AVG, SUM i COUNT, zauważalnie pomija wbudowaną funkcję agregacyjną MODE. Ten brak wynika z koncentracji modelu relacyjnego na wynikach skalarowych oraz z inherentnej niejednoznaczności, jaką moda wprowadza przy występowaniu remisów. W związku z tym praktycy muszą rekonstruować ten pomiar statystyczny za pomocą utworzonych tabel i funkcji okiennych.

Problem.

Obliczanie mody wymaga zidentyfikowania wartości o maksymalnej liczbie wystąpień w każdej partycji. Złożoność wynika z dwóch ograniczeń: po pierwsze, funkcje agregujące nie mogą być zagnieżdżane bezpośrednio (np. MAX(COUNT(*))), a po drugie, remisy dla najwyższej liczby wystąpień muszą być rozwiązywane deterministycznie, aby zapewnić dokładnie jeden wynik na grupę. Rozwiązanie musi działać jako jedna deklaratywna instrukcja bez pętli procedurowych lub specyficznych dla dostawcy rozszerzeń.

Rozwiązanie.

Podejście wykorzystuje dwuetapową strukturę CTE (Common Table Expression). Najpierw oblicz liczby wystąpień używając GROUP BY z COUNT(*). Następnie zastosuj funkcję okienną RANK() podzieloną według kluczy grupujących, uporządkowaną według liczby wystąpień malejąco oraz wartości rosnąco dla rozwiązania remisów. Filtrowanie dla RANK() = 1 zwraca modę. Ta metoda jest ściśle zgodna z ANSI SQL:2003 i wykonuje się w jednym przeszukiwaniu tabeli.

WITH frequency_cte AS (
  SELECT 
    group_id, 
    target_value,
    COUNT(*) AS val_freq
  FROM observations
  GROUP BY group_id, target_value
),
rated_cte AS (
  SELECT 
    group_id,
    target_value,
    RANK() OVER (
      PARTITION BY group_id 
      ORDER BY val_freq DESC, target_value ASC
    ) AS freq_rank
  FROM frequency_cte
)
SELECT group_id, target_value AS mode_value
FROM rated_cte
WHERE freq_rank = 1;

Sytuacja z życia.

Zespół analityki e-commerce potrzebował raportować najpopularniejszy rozmiar produktu (moda) dla każdej kategorii odzieży na bazie miesięcznej, aby zoptymalizować poziomy zapasów w magazynie. Tabela sales zawierała miliony wierszy z kolumnami category_id, sale_month i size_label. Krytyczna zasada biznesowa wymagała, aby jeśli dwa rozmiary były równo ustawione dla najwyższej liczby sprzedaży, system musiał konsekwentnie wybierać mniejszy rozmiar alfanumeryczny (np. „M” przed „L”), aby utrzymać deterministyczne prognozy zapasów.

Rozwiązanie 1: Skorelowane podzapytanie z porównaniem skalarnym.

Jednym z podejść było użycie skorelowanego podzapytania do znalezienia maksymalnej liczby dla każdej grupy, a następnie połączenie w celu znalezienia pasującego rozmiaru. Ta metoda polegała na standardowej funkcjonalności SQL-92 dostępnej w systemach legacy. Podzapytanie obliczało maksymalną częstość na parę kategoria-miesiąc, a zapytanie zewnętrzne filtrowało rozmiary odpowiadające tej częstości. Mimo że była powszechnie kompatybilna, to podejście cierpiało na kwadratową złożoność czasową O(n²) z powodu korelacji. Wymagało wielu przejść nad danymi i miało trudności w eleganckim rozwiązywaniu remisów, często wymagając dodatkowych podzapytań do rozwiązywania duplikatów. Plan zapytania obejmował zagnieżdżone połączenia pętlowe, które znacznie się degradują w miarę wzrostu liczby sprzedaży.

Rozwiązanie 2: Funkcja okienna z deterministycznym rankingiem.

Wybrane rozwiązanie wykorzystało funkcje okienne ANSI SQL:2003 jak opisano w ogólnym rozwiązaniu powyżej. Dzięki zmaterializowaniu częstości w CTE i zastosowaniu RANK(), optymalizator bazy danych mógł wykorzystać operacje oparte na sortowaniu i agregacje haszujące. To podejście działało w czasie liniowo-logarytmicznym O(n log n), skalowało się poziomo z odpowiednim indeksowaniem na category_id i sale_month oraz naturalnie radziło sobie z rozwiązywaniem remisów przez drugorzędny klucz sortowania. Deterministyczne rozwiązywanie remisów zapewniło spójne dane wejściowe dla algorytmu zapasów, zapobiegając nieprzewidywalnym rekomendacjom między uruchomieniami raportów.

Wynik.

Wdrożenie zmniejszyło czas generowania raportu z 12 minut do 8 sekund na zestawie danych o 50 milionach rekordów. Deterministyczne rozwiązanie remisów wyeliminowało rozbieżności w automatycznych systemach zamawiania, zmniejszając braki zapasów dla sekundarnych popularnych rozmiarów o 15%.

Co często umyka kandydatom.

Dlaczego zagnieżdżanie agregatów, takich jak MAX(COUNT(*)), skutkuje błędem składniowym, a jak logiczna kolejność przetwarzania SQL wymusza podejście oparte na CTE?

Wielu kandydatów próbowało napisać SELECT group_id, MAX(COUNT(*)) FROM ..., nie zdając sobie sprawy, że ANSI SQL zabrania zagnieżdżania funkcji agregujących. Logiczna kolejność przetwarzania narzuca, że WHERE, GROUP BY i HAVING są wykonywane przed SELECT, co oznacza, że wyniki agregatów nie są dostępne w fazie grupowania. Podejście oparte na CTE lub podqwejścia tworzy potok, w którym pierwszy etap materializuje liczniki jako tabelę pochodną, udostępniając je jako wartości skalarne do późniejszego rankingowania funkcji okiennych w drugim etapie. Zrozumienie tego rozdzielenia faz agregacji i okna jest kluczowe do konstruowania ważnych zapytań SQL.

Jak wybór między RANK(), DENSE_RANK() i ROW_NUMBER() wpływa na poprawność obliczania mody, gdy występują remisy, i dlaczego deterministyczne rozwiązywanie remisów jest niezbędne?

Kandydaci często domyślnie korzystają z ROW_NUMBER(), ponieważ gwarantuje dokładnie jeden wiersz na partycję. Jednak ROW_NUMBER() przypisuje dowolnie różne liczby całkowite do związanych wierszy w oparciu o fizyczną kolejność sortowania, co potencjalnie może prowadzić do wyboru innej wartości mody przy każdym wykonaniu, jeśli drugi klucz sortowania jest pominięty. RANK() poprawnie identyfikuje wszystkie związane wartości jako rangę 1, wymagając wyraźnej logiki rozwiązywania remisów (np. MIN(target_value)), aby spełnić wymóg „dokładnie jednego wyniku” w sposób deterministyczny. DENSE_RANK() również zwraca związane wiersze, ale z kolejnym numerowaniem, co czyni je nieodpowiednimi do prostego filtrowania bez dodatkowej logiki. Deterministyczne zachowanie zapewnia, że aplikacje analityczne i downstreamowe pipeline’y ETL otrzymują spójne, reprodukowalne wyniki.

Jakie są implikacje kardynalności i pamięci używania samopołączenia w porównaniu do funkcji okiennych do analizy częstości, i jak wpływa to na planowanie zapytań?

Powszechnym nieporozumieniem jest to, że funkcje okienne zawsze przewyższają wydajnością połączenia. W obliczaniu mody podejście samopołączenia dołączałoby zgrupowaną tabelę częstości do samej siebie na group_id i val_freq = max_freq, potencjalnie produkując iloczyn kartezjański w obrębie grup, jeśli występuje wiele remisów. To tworzy zestawy wyników pośrednich o kardynalności równej sumie remisów, co może powodować wzrost użycia pamięci. Z drugiej strony, funkcje okienne, takie jak RANK(), wykonują obliczenia oparte na sortowaniu, wymagając pamięci proporcjonalnej do rozmiaru partycji w celu utrzymania bufora sortującego. Kandydaci pomijają fakt, że podczas gdy funkcje okienne są zazwyczaj szybsze, mogą zrzucać dane na dysk, jeśli rozmiary partycji przekraczają work_mem (w terminologii PostgreSQL) lub równoważne limity bufora, podczas gdy haszowe połączenia samodzielne mogą lepiej radzić sobie w przypadku bardzo wysokiej kardynalności kluczy grupujących z niewieloma remisami. Zrozumienie tych kompromisów pozwala deweloperom analizować plany EXPLAIN i optymalizować ustawienia bufora.