Zasada Pareto wyłoniła się z obserwacji Vilfredo Pareto dotyczących własności ziemi we Włoszech, stając się później kamieniem milowym w kontroli jakości i zarządzaniu zapasami dzięki pracy Josepha Jurana. W bazach danych relacyjnych przekłada się to na potrzebę analizy ABC, gdzie analitycy muszą zidentyfikować krytyczną mniejszość rekordów, które generują większość wartości biznesowej, bez sięgania po zewnętrzne narzędzia statystyczne.
Problem wymaga obliczenia bieżącego procentu opadającej metryki w stosunku do całkowitej wartości, a następnie przycięcia na progu 80%. Ponieważ ANSI SQL działa na zbiorach, a nie na iteracyjnych kursorach, funkcje okna zapewniają deklaratywny mechanizm. Rozwiązanie wykorzystuje kumulację sumy podzieloną na cały zbiór wyników, uporządkowaną w kolejności malejącej według wartości, a następnie dzieli przez całkowity wynik w tym samym kontekście wiersza, aby uzyskać rangę percentylową.
Krytycznym aspektem specyfikacji ramy ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW jest zapewnienie deterministycznej akumulacji wiersz po wierszu. Jeśli konieczne jest ścisłe zarządzanie remisami — gdy wszystkie rekordy dzielące wartość graniczną muszą być uwzględnione lub wykluczone jako całość — RANGE zastąpi ROWS. Ostateczne filtrowanie musi nastąpić w zapytaniu zewnętrznym, ponieważ funkcje okna są logicznie obliczane po klauzuli WHERE.
WITH ranked_products AS ( SELECT product_id, product_name, annual_revenue, SUM(annual_revenue) OVER ( ORDER BY annual_revenue DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_revenue, SUM(annual_revenue) OVER () AS total_revenue FROM inventory ), pareto_subset AS ( SELECT product_id, product_name, annual_revenue, CAST(cumulative_revenue AS DECIMAL) / total_revenue AS cumulative_pct FROM ranked_products ) SELECT product_id, product_name, annual_revenue, cumulative_pct FROM pareto_subset WHERE cumulative_pct <= 0.80;
Krajowy detalista elektroniki stawał w obliczu rosnących kosztów podczas kwartalnych audytów zapasów, wymagając izolacji wysokowartościowych SKU reprezentujących 80% całkowitego kapitału magazynowego (50 milionów dolarów na 40 000 pozycji), aby priorytetowo traktować cykliczne liczenie.
Rozwiązanie 1: Ekstrakcja arkusza kalkulacyjnego polegała na tym, że analitycy eksportowali pliki CSV do Excel, sortowali według kosztu jednostkowego i ręcznie sumowali do osiągnięcia progu. Zaletą była zerowa potrzeba czasu deweloperskiego. Wady obejmowały awarie aplikacji przy dużych zbiorach danych, wymogi przeliczenia co godzinę i niemożność integracji w czasie rzeczywistym z systemem zarządzania magazynem.
Rozwiązanie 2: Obliczenia na poziomie aplikacji wykorzystały skrypt Python strumieniujący wiersze i utrzymujący bieżący akumulator. Zaletą była elastyczna logika i łatwe debugowanie. Wady wprowadziły znaczne opóźnienia sieciowe przy transferze milionów wierszy, blokując jednocześnie pulpit analityczny podczas jednowątkowego wykonywania, oraz ograniczenia pamięci w maszynie klienckiej.
Rozwiązanie 3: Podejście oparte na zestawach ANSI SQL wdrożyło zapytanie funkcji okna bezpośrednio w magazynie PostgreSQL. Zaletą były opóźnienia na poziomie milisekund, eliminacja ruchu danych i automatyczny odświeżanie przy nocnych aktualizacjach. Wady wymagały zaawansowanej wiedzy z zakresu SQL do utrzymania.
Wybrane rozwiązanie i wynik: Rozwiązanie 3 zostało wdrożone w formie widoku, ujawniając, że zaledwie 12% SKU odpowiadało za 80% wartości. Zakres audytu zredukowano o 88%, oszczędzając 340 roboczogodzin kwartalnie, przy pełnym zachowaniu pokrycia wartości materiałowej.
Jak wybór między specyfikacjami ram ROWS i RANGE wpływa na próg 80%, gdy istnieją powtarzające się wartości?
RANGE traktuje równe wiersze z identycznymi wartościami ORDER BY jako jedną grupę; jeśli granica 80% mieści się w remisie, RANGE obejmuje całą grupę, co potencjalnie przekracza 80%. ROWS przetwarza fizyczne przesunięcia niezależnie od remisów, co może podzielić logiczną jednostkę biznesową. Kandydaci często nie zauważają, że ANSI SQL pozwala na eksplicytną regulację tego zachowania; w raportowaniu finansowym RANGE zapewnia, że spójne okresy nie są dzielone, podczas gdy ROWS oferuje większą precyzję dla odrębnych przedmiotów.
Dlaczego obliczenie procentu kumulacyjnego musi być wykonane w tabeli pochodnej lub CTE, a nie bezpośrednio w klauzuli WHERE?
Funkcje okna są logicznie oceniane w fazie SELECT, która następuje po filtrach klauzuli WHERE. Próba filtrowania cumulative_revenue / total_revenue <= 0.8 bezpośrednio w WHERE powoduje błąd składniowy, ponieważ wynik okna nie jest jeszcze zmaterializowany. Kandydaci często mają trudności z logiczną kolejnością przetwarzania ANSI SQL: FROM → WHERE → GROUP BY → HAVING → WINDOW → SELECT → ORDER BY. Rozwiązanie wymaga zagnieżdżenia w celu obliczenia funkcji okna w zapytaniu wewnętrznym, a następnie filtrowania wynikowej kolumny w zapytaniu zewnętrznym.
Jak zoptymalizowałbyś to zapytanie, jeśli tabela zapasów zawiera miliardy wierszy, a szacowany zbiór 80% jest bardzo mały?
Kandydaci często pomijają wzorzec optymalizacji Top-N. Zamiast obliczać funkcję okna w całej tabeli, wstępny filtr za pomocą podzapytania z DENSE_RANK() lub NTILE() może ograniczyć obliczenia okna do najważniejszych kandydatów. Alternatywnie, wykorzystanie PARTITION BY, jeśli analiza jest podzielona na kategorie, zapobiega pełnym skanom tabeli. Zrozumienie, że funkcje okna wymuszają operację sortowania, a indeksowanie na kolumnie dochodów malejąco może wyeliminować koszt sortowania, jest kluczowe dla skalowania.