Klauzula OVER została dodana do SQL Server „w drodze wstecz” w SQL Server 2005 i została rozszerzona w SQL Server 2012. Jest używany głównie z „funkcjami okna”; jedynym wyjątkiem jest funkcja sekwencji NEXT VALUE FOR. Klauzula OVER służy do określenia, które wiersze z zapytania są stosowane do funkcji, w jakiej kolejności są one oceniane przez tę funkcję i kiedy obliczenia funkcji powinny zostać ponownie uruchomione. Ponieważ jest używany w połączeniu z innymi funkcjami, a ten artykuł dotyczy konkretnie tylko klauzuli OVER, te funkcje zostaną omówione tylko tak, jak odnoszą się one do klauzuli OVER w podanych przykładach.

Składnia funkcji klauzula OVER to:

<function> OVER ( )

Patrząc na składnię, wydaje się, że wszystkie klauzule podrzędne są opcjonalne. W rzeczywistości każda funkcja, która może używać klauzuli OVER, określa, które klauzule podrzędne są dozwolone, a które wymagane. W zależności od używanej funkcji sama klauzula OVER może być opcjonalna. Na końcu tego artykułu znajduje się wykres, który pokazuje, które funkcje zezwalają / wymagają, które części klauzuli OVER.

Klauzula PARTITION BY służy do dzielenia zestawu wyników z zapytania na podzbiory danych lub partycje. Jeśli klauzula PARTITION BY nie jest używana, cały zestaw wyników zapytania jest partycją, która będzie używana. Używana funkcja okna jest stosowana do każdej partycji oddzielnie, a obliczenia, które wykonuje funkcja, są ponownie uruchamiane dla każdej partycji. Definiujesz zestaw wartości, które określają partycje, na które chcesz podzielić zapytanie. Te wartości mogą być kolumnami, funkcjami skalarnymi, podzapytaniami skalarnymi lub zmiennymi.

Na przykład przeanalizujmy następujące zapytanie:

SELECT COUNT(*)FROM .sys.indexes;

To zapytanie zwraca następujący zestaw wyników:

Jest to po prostu liczba wierszy zwróconych przez zapytanie – w tym przypadku liczba indeksów w bazie danych msdb. Teraz dodajmy klauzulę OVER do tego zapytania:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

Skrócone wyniki to:

To zapytanie zwraca identyfikator_obiektu i identyfikator_indeksu dla każdego indeksu oraz całkowitą liczbę indeksów w zestawie wyników. Ponieważ nie użyto klauzuli PARTITION BY, cały zestaw wyników był traktowany jako pojedyncza partycja. Nadszedł czas, aby dodać klauzulę PARTITION BY i zobaczyć, jak zmieni to wyniki:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

Skrócone wyniki to:

To zapytanie zwraca wiersz dla każdego indeksu, ale teraz zapytanie określa klauzulę PARTITION BY kolumny object_id, więc funkcja count zwraca liczbę indeksów na tym konkretnym object_id. Klauzula ORDER BY kontroluje kolejność obliczania wierszy przez funkcję. Wkrótce zostanie to wykazane. Klauzula ROWS lub RANGE określa podzbiór wierszy w partycji, które mają być zastosowane do funkcji. Używając ROWS lub RANGE, określasz początek i koniec okna. Dozwolone wartości to:

Istnieją dwie składnie określania okna:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Jeśli określono tylko ramkę początkową, domyślną ramką końcową jest CURRENT ROW.

Słowo kluczowe UNBOUNDED określa początek partycji (PRECEDING) lub koniec partycja (dla NASTĘPUJĄCYCH). BIEŻĄCY WIERSZ określa, że bieżący wiersz jest początkiem lub końcem okna, w zależności od tego, w jakiej pozycji ramy okna jest używane. „N” określa liczbę wierszy poprzedzających bieżący wiersz (dla opcji PRECEDING ) lub po bieżącym wierszu (dla FOLLOWING) do użycia dla ramy okna.

Poniżej podano prawidłowe specyfikacje okna:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

Aby użyć klauzuli ROWS lub RANGE, musisz również określić klauzulę ORDER BY. I odwrotnie, jeśli używasz klauzuli ORDER BY i nie określisz klauzuli ROWS ani RANGE, wówczas domyślny RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW jest używany.

Aby zademonstrować klauzule ORDER BY i ROWS lub RANGE, stwórzmy pewne dane testowe: dwa konta, cztery daty na konto i kwota dla każdej daty. Zapytanie pokaże oba z nich klauzule używane na różne sposoby:

To zapytanie zwraca następujący zestaw wyników:

RowNbr ”jest używana g funkcja COUNT, aby zwrócić liczbę wierszy w partycji. Partycja jest porządkowana przez TranDate, a my określamy ramę okna dla wszystkich wierszy od początku partycji do bieżącego wiersza. W pierwszym wierszu w ramie okna znajduje się tylko jeden wiersz, więc zwracana jest wartość „1.” W drugim wierszu w ramie okna są teraz dwa wiersze, więc zwracana jest wartość „2”. I tak dalej, przez pozostałe wiersze na tym koncie.

Ponieważ klauzula PARTITION BY określa konto, po zmianie konta obliczenia funkcji są resetowane, co można zobaczyć, sprawdzając wiersze drugiego konta w zestawie wyników. To jest przykład „bieżącej” agregacji, w której agregacja opiera się na wcześniejszych obliczeniach. Przykładem tego, kiedy należy użyć tej agregacji, byłoby obliczenie salda konta bankowego po każdej transakcji (inaczej zwanej sumą bieżącą).

Kolumna „DateCount” oblicza liczbę wierszy podzielonych według daty. W tym przykładzie na każdym koncie jest transakcja w każdym z tych samych czterech dat, więc każda data ma dwie transakcje (po jednej dla każdego konta). Powoduje to, że wartość „2” jest zwracana dla każdego wiersza. Jest to podobne do wykonywania zliczania, które wykorzystuje funkcję GROUP BY dla daty; różnica polega na tym, że suma jest zwracana dla każdego wiersza zamiast tylko raz dla każdej daty. Przykład kiedy użyjesz tej metody, wyświetlając „Wiersz X z Y” lub obliczając procent bieżącego wiersza w stosunku do całości.

Kolumna „Last2Count” oblicza wiersze w obrębie partycji, dla bieżącego wiersza i wiersza bezpośrednio go poprzedzającego. Dla pierwszego wiersza na każdym koncie, ponieważ nie ma żadnych poprzedzających go wierszy, zwracana jest wartość „1”. W przypadku pozostałych wierszy na każdym koncie zwracana jest wartość „2”. Jest to przykład agregacji „ruchomej” lub „przesuwnej”. Przykładem zastosowania tej metody byłoby obliczenie premii na podstawie sprzedaż z ostatnich dwóch miesięcy.

W tym miejscu pokazałem tylko klauzulę ROWS. Klauzula RANGE działa w podobny sposób, ale zamiast zajmować się wierszami w sposób pozycyjny, zajmuje się wartości zwracane przez ten wiersz. Klauzule N PRECEDING / FOLLOWING nie mogą być używane, ponieważ nie jest to pozycja. Spójrzmy szybko na różnicę między wierszami ROWS i RANGE, używając ich obu w tym samym zapytaniu. Tutaj mamy lista osób (nazwijmy je DBA) i ich stawki godzinowe. Zwróć uwagę, że wiersze z RowIDs 4 & 5 i 12 & 13 ma tę samą stawkę. Zapytanie zsumuje stawki dwa razy, raz używając ROWS, a drugie używając RANGE:

To zapytanie daje następujący zestaw wyników:

W obu kolumnach SumByRows i SumByRange klauzula OVER jest identyczna z wyjątkiem klauzuli ROWS / RANGE. Zauważ również, że ponieważ nie określono zakresu końcowego, domyślnie używany jest BIEŻĄCY WIERSZ. Ponieważ sumujemy wynagrodzenie od początku zbioru wyników do bieżącego wiersza, tak naprawdę obliczamy sumę bieżącą z kolumny Salary. W kolumnie SumByRows wartość jest obliczana za pomocą klauzuli ROWS i widzimy, że suma w bieżącym wierszu to Salary z bieżącego wiersza plus suma z poprzedniego wiersza. Jednak klauzula RANGE działa na podstawie wartości kolumny Salary, więc sumuje wszystkie wiersze z takim samym lub niższym wynagrodzeniem. Powoduje to, że wartość SumByRange jest taka sama dla wszystkich wierszy z taką samą pensją.

Jedna ważna uwaga: klauzula ORDER BY w klauzuli OVER kontroluje tylko kolejność, w jakiej będą używane wiersze w partycji. przez funkcję okna. Nie kontroluje kolejności końcowego zestawu wyników. Bez klauzuli ORDER BY w samym zapytaniu kolejność wierszy nie jest gwarantowana. Możesz zauważyć, że twoje zapytanie może zwracać się w kolejności ostatniej określonej klauzuli OVER – wynika to ze sposobu, w jaki jest to obecnie zaimplementowane w SQL Server. Jeśli zespół SQL Server w firmie Microsoft zmieni sposób, w jaki to działa, może przestać porządkować wyniki w sposób, który obecnie obserwujesz. Jeśli potrzebujesz określonej kolejności dla zestawu wyników, musisz podać klauzulę ORDER BY w samym zapytaniu.

Na koniec, oto wykres różnych funkcji, które mogą używać klauzuli OVER, a także które części klauzuli są dozwolone / wymagane / opcjonalne.

R-wymagane, O-opcjonalne, X-niedozwolone