Kontekst historyczny
Standard ANSI SQL:2011 wprowadził klauzule wykluczania ram do składni funkcji okiennych, rozwiązując problem, w którym ramy okien musiały obejmować bieżący wiersz. Przed tym udoskonaleniem programiści musieli uciekać się do uciążliwych samodopasowań lub manipulacji algebraicznych (odejmowanie bieżącej wartości od sumy) w celu obliczenia agregatów z wyłączeniem wiersza centralnego. Standard definiuje cztery opcje wykluczania: EXCLUDE NO OTHERS, EXCLUDE CURRENT ROW, EXCLUDE GROUP i EXCLUDE TIES, zapewniając deterministyczną semantykę operacji zestawowych w obrębie uporządkowanych partii.
Problem
Podczas analizy metryk konkurencyjnych—takich jak obliczanie średniej ceny sprzedaży produktów równorzędnych przy pomocy wykluczenia samego produktu z tej średniej—zapytanie musi zdefiniować okno, które obejmuje wszystkie powiązane wiersze z wyjątkiem bieżącego. Tradycyjne funkcje okienne, takie jak AVG() OVER (PARTITION BY category), zawierają bieżący wiersz, co zniekształca wynik. Wprowadzenie tego za pomocą podzapytania lub dopasowań wprowadza niepotrzebną złożoność oraz spowolnienie wydajności, szczególnie w przypadku dużych zestawów danych, w których produkty kartezjańskie lub skorelowane podzapytania byłyby zbyt kosztowne.
Rozwiązanie
Wykorzystaj klauzulę wykluczania w ramach specyfikacji okna: AVG(price) OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW). Taka składnia instruuje silnik SQL, aby najpierw ustalił pełną ramę partycjonowania, a następnie logicznie usunął bieżący wiersz przed obliczeniem agregatu. W scenariuszach wymagających wykluczenia wszystkich powiązań (np. wszystkie produkty w tej samej cenie), EXCLUDE GROUP usuwa zarówno bieżący wiersz, jak i jego rówieśników, podczas gdy EXCLUDE TIES zatrzymuje bieżący wiersz, ale usuwa powtarzające się wartości sortujące.
Zespół analityków e-commerce musi wygenerować raport „Pozycja na rynku”. Dla każdego ogłoszenia sprzedawcy dotyczącego gadżetu elektronicznego muszą wyświetlić cenę tego sprzedawcy obok średniej ceny wszystkich innych sprzedawców oferujących ten sam model gadżetu.
Podejście oparte na samodopasowaniach zostało początkowo prototypowane, w którym tabela z ofertami została dopasowana do samej siebie na podstawie model_id, wykluczając pasujące klucze główne. Zalety: Jest powszechnie wspierane w różnych dialektach SQL i jest koncepcyjnie proste. Wady: Wykonanie wykazuje złożoność O(n²) w najgorszym przypadku, powodując wykładnicze spowolnienie przy milionach wierszy; dodatkowo optymalizator zapytań często ma trudności z nierównym predykatem dopasowania, generując nieefektywne plany wykonania z rozrzutem haszowanym lub zagnieżdżonymi dopasowaniami.
Alternatywne rozwiązanie algebraiczne zostało również ocenione, obliczając globalną sumę i liczbę dla każdego modelu, a następnie uzyskując średnią pozostałych poprzez (SUM(price) - current_price) / (COUNT(*) - 1). Zalety: Unika dopasowań i wymaga tylko jednego skanowania funkcji okiennej. Wady: Katastrofalnie zawodzi, gdy COUNT(*) = 1 (dzielenie przez zero) lub gdy ceny są NULL, wymagając rozbudowanych warunków CASE; ponadto nie może być stosowane do niealgebraicznych agregatów, takich jak MEDIAN czy MODE.
Zespół ostatecznie wybrał specyfikację ramy EXCLUDE CURRENT ROW. Argumentacja: Jest deklaratywna, eliminuje potrzebę sprawdzania NULL poprzez warunki CASE, naturalnie zwracając NULL dla pustych ram, i działa w czasie O(n) przy użyciu jednego posortowanego przejścia z minimalnym obciążeniem pamięci. Rezultatem zapytania był czas generowania raportu skrócony z dwunastu minut do poniżej dziesięciu sekund.
Rezultat: Raport produkcyjny teraz dokładnie oblicza benchmarki konkurencji dla 50 milionów ogłoszeń dziennie, sprawnie obsługując rzadkie przedmioty z jednymi sprzedawcami poprzez wyświetlanie NULL (interpretowane jako „Brak konkurencji”), zamiast błędów czy wartości zerowych.
Jak zachowuje się EXCLUDE CURRENT ROW stosowane z ramami opartymi na RANGE w porównaniu do ram opartych na ROWS, szczególnie w odniesieniu do grup równorzędnych?
Gdy rama okna wykorzystuje ROWS, EXCLUDE CURRENT ROW usuwa dokładnie jeden fizyczny wiersz—bieżący wiersz—z agregacji. Jednak przy użyciu RANGE (np. RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING), „bieżący wiersz” koncepcyjnie reprezentuje wszystkie wiersze dzielące tę samą wartość porządkującą co bieżący wiersz w określonym zakresie. W tym kontekście, EXCLUDE CURRENT ROW usuwa tylko konkretną instancję wiersza, pozostawiając inne rówieśników (powiązania) w ramie. Przeciwnie, EXCLUDE GROUP usuwa bieżący wiersz oraz wszystkie rówieśników bez względu na jednostkę ramy, podczas gdy EXCLUDE TIES usuwa wszystkich rówieśników z wyjątkiem bieżącego wiersza. Kandydaci często mylą te pojęcia, zakładając, że EXCLUDE CURRENT ROW z RANGE zachowuje się jak EXCLUDE GROUP, co prowadzi do błędnych wyników agregacji, gdy istnieją duplikaty kluczy porządkowych.
Dlaczego zapytanie używające EXCLUDE CURRENT ROW w jednym wierszu partycji może zwrócić NULL, i jak to różni się od metod ręcznego odejmowania?
Standard ANSI SQL definiuje, że agregat nad pustym zbiorem zwraca NULL. Gdy EXCLUDE CURRENT ROW jest stosowane do partycji zawierającej tylko jeden wiersz, rama staje się pusta, co powoduje, że AVG, SUM czy COUNT automatycznie zwraca NULL. W przeciwieństwie do tego, metody manualne, takie jak (SUM(col) - col) / (COUNT(*) - 1), napotykają problemy z dzieleniem przez zero lub propagacją NULL w arytmetyce, wymagając jawnych instrukcji CASE do bezpiecznego obsługiwania pojedynczych partycji. Kandydaci często pomijają to automatyczne zachowanie dotyczące NULL, oczekując zera lub bieżącej wartości, i nie dostrzegają, że EXCLUDE zapewnia lepsze bezpieczeństwo przy warunkach krawędziowych.
Czy EXCLUDE może być łączony z dowolnymi rozszerzeniami ram, takimi jak przesuwne okna (np. ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING), i jakie są implikacje wydajności?
Tak, klauzule EXCLUDE są ważne z dowolnym rozszerzeniem ramy, w tym BETWEEN dla przesuwnych okien. Na przykład, AVG(val) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) oblicza średnią z dwóch poprzednich i dwóch następnych wartości, skutecznie tworząc 4-punktową średnią ruchomą skoncentrowaną na—ale wykluczającą—bieżący punkt. Pod względem wydajności nowoczesne optymalizatory implementują to za pomocą algorytmu strumieniowego z buforem pierścieniowym lub deque, utrzymując złożoność O(n) na partycję. Kandydaci często zakładają, że EXCLUDE wymaga pełnej materializacji partycji lub działa tylko z ramami UNBOUNDED, omijając fakt, że płynnie integruje się z ograniczonymi oknami dla obliczeń takich jak skorelowane współrzędne centralne czy wygładzanie odporne na wartości odstające, w których punkt centralny nie może wpływać na statystykę.