T-SQL bez tajemnic cz. 2
12.05.17r.
Przyglądamy się bliżej funkcjom okien, które umożliwiają efektywne operacje na danych bez konieczności ręcznego zarządzania każdym rekordem. Zapraszam do odkrycia ich potencjału i zastosowań.
Jak pamiętacie w poprzednim artykule porównaliśmy różne sposoby wyliczeń sum narastających (running totals). Wnioski płynące z testów różnych zapytań były jednoznaczne – funkcje okien to potężne narzędzie! Przyjrzymy się im bliżej w części 2 „T-SQL Bez Tajemnic”.
Po pierwsze powinniśmy pamiętać, że funkcje okien zachowują logikę „set-based approach”, co między innymi oznacza, że pisząc zapytanie piszemy co chcemy osiągnąć, a nie kontrolujemy wynik zapytania wiersz po wierszu budując jego rezultat, jak w przypadku kursorów.
Serwer SQL daje nam natywne wsparcie dla funkcji okien. Po drugie, musimy wyjaśnić pojęcie okna, które jest kluczem do dalszej pracy z funkcjami.
1. Okno jest „wirtualnym” zbiorem lub podzbiorem rekordów, na których pracujemy. Umożliwia nam sortowanie rekordów w oknie, niezależnie od sposobu sortowania całego wyniku.
2. Okno umożliwia nam odwoływanie się do innych rekordów bez użycia połączeń (self join) lub innych technik.
3. Okna można partycjonować, tj. dzielić na grupę rekordy o tych samych wartościach.
4. Okno może mieć ramki, w których wyróżniamy górny lub dolny zakres.
Przechodząc powoli do bardziej konkretnych wyjaśnień musimy zwrócić uwagę jeszcze na elementy składowe okna jakimi są:
1. Over – określa zbiór rekordów o odpowiedniej kolejności dla okna i funkcji w nim użytych.
2. Partition by – określa ilość grupowań w oknie dla tych samych wartości podobnie, jak group by. Jeżeli pominiemy okno składa się tylko z jednej partycji.
3. Rows lub Range – tworzą ramki w partycji okna, umożliwiając określenie górnej i dolnej granicy. Wymaga sortowania.
Przykład 1 – OVER() i funkcja RANK()
Najprostszą funkcją okna jest RANK() – umożliwia wygenerowanie pozycji w rankingu, gdzie kolejne pozycje zależą od wartości pola sortowanego. Jeżeli wartości od 2 do 140 wiersza są identyczne rekordy w rankingu uzyskują pozycję 2, a kolejny rekord pozycję 141.
Przykład 2 – OVER() i funkcja DENSE_RANK()
Jeżeli chcemy utworzyć ranking bez pominięcia miejsca w liczbie porządkowej powinniśmy użyć DENSE_RANK().
Przykład 3 – PARTITION BY
Wyniki w powyższych przykładach możemy podzielić na regiony, w tym celu rozszerzymy składnię OVER o PARTITION BY. Pozycjonowanie rekordów od tego momentu rozpoczyna się od pozycji 1 dla każdego kolejnego regionu.
Jednak, moim zdaniem, najwięcej korzyści w pisaniu zoptymalizowanych zapytań, daje nam możliwość definiowania ramek. To one umożliwiają nam używanie offset’ów w wyliczeniach, pokazanych
w poprzedniej części artykułu „T-SQL Bez Tajemnic”. Jak partycje dają nam możliwość podziału okna na grupy, tak ramka umożliwia nam dynamiczne określanie grupy rekordów, na których chcemy pracować. Kluczowym elementem tworzącym ramkę jest użycie w składni OVER jednego ze słów:
1. ROWS – wyliczenie dla każdego kolejnego rekordu.
2. RANGE – dla grupy rekordów z tej samej kategorii.
OK, ale jak określić granice ramki? Do tego możemy użyć BETWEEN - określające grupę rekordów
i zależnie od potrzeb:
1. N PRECEDING – N poprzednich rekordów.
2. N FOLLOWING – N nastepnych rekordów.
3. UNBOUNDED PRECEDING – od początku (od góry).
4. UNBOUNDED FOLLOWING – do końca (w dół).
5. CURRENT ROW – bieżący rekord.
Przykład 4 – ROWS i RANGE BETWEEN
Jak widać na załączonym zapytaniu w przypadku ROWS sumy wyliczane są narastająco. W przypadku użycia RANGE dla każdego rekordu z kategorii np. United Kingdom wartość jest stała, bo została wyliczona dla zakresu wyznaczonego przez nazwę kraju.
Przykład 5 – PRECEDING i FOLLOWING
W przypadku PRECEDING i FOLLOWING sprawa jest prosta, ale jakże przydatna. Wyliczenie sum1 zależy od wartości pola SubTotal rekordu poprzedniego, bieżącego i następnego. Pole sum2 wyliczane jest
z 3 kolejnych rekordów licząc od wartości bieżącej!
Mam nadzieję, że ten artykuł choć trochę wyjaśnił użycie funkcji okien i poszerzył Waszą znajomość języka T-SQL. Nie są to wszystkie funkcji dostępne w SQL Server - jeżeli chcecie zgłębić temat okien bardziej - zapraszam na szkolenie
Microsoft 20761.
------------------------------------------------------
Autor:
Adam Białas – MCT, MCSE: Data Platform, Business Intelligence