Kurs Oracle SQL. Podstawy funkcji analitycznych część I

Funkcje analityczne stanowią analityczne rozszerzenie funkcji SQL i różnią się metodą działania oraz sposobem użycia od tradycyjnych funkcji SQLowych. Wyznaczając wynik dla bieżącego wiersza z reguły korzystają z informacji znajdujących się w wierszach sąsiednich. Podczas przetwarzania polecenia SQL wartości funkcji analitycznych są wyliczane po wszystkich składowych operacjach (połączeniach, selekcji wierszy, grupowaniu, selekcji grup itd.). Po nich następuje jedynie sortowanie.
Funkcje analityczne są wykorzystywane wyłącznie w klauzulach SELECT oraz ORDER BY i nie mogą być używane w klauzulach WHERE, GROUP BY, HAVING. Działają wyłącznie na wierszach będących wynikiem zapytania i nie odrzuconych przez WHERE lub HAVING. Pozwalają na rozwiązanie problemów takich jak :
„Znajdź N najlepszych „ (TopN),
„Przelicz średnią na ruchomej grupie”
Aby przejść do korzystania z funkcji analitycznych należy poznać terminologię z nimi związaną.

Partycje

Stanowią autonomiczne zbiory danych w ramach których funkcje analityczne przetwarzają dane.

Okna

Pozwalają na zdefiniowanie ruchomego zakresu, określanego oddzielnie dla każdego wiersza.

Bieżący wiersz

Wiersz dla którego w danym momencie wyznaczany jest wynik funkcji.

Ogólna składnia funkcji analitycznych

NAZWA_FUNKCJI (PARAMETRY) OVER (DEFINICJA ZBIORU)


Definicją zbioru może być definicja partycji, okna lub porządku wierszy w partycji.

Analityczne funkcje rankingu

Funkcje rankingu wyznaczają pozycję danego wiersza porównując go z wartościami innych wierszy w tej samej partycji. Podział zbioru danych na partycje pozwalają na stworzenie oddzielnych rankingów dla każdej partycji. Dzięki temu jesteśmy w stanie np. wyświetlić ranking najlepiej sprzedających się produktów w każdej kategorii. Do funkcji rankingu zaliczamy:

• rank, dense_rank
  
• cume_dist
  
• percent_rank
  
• ntile
  
• row_number
  

 

 

Składnia analitycznych funkcji rankingu

NAZWA_FUNKCJI() OVER ([PARTITION BY WYRAŻENIE] ORDER BY WYRAŻENIE2)



Klauzula PARTITION BY jest opcjonalna. ORDER BY musimy zastosować zawsze korzystają z funkcji rankingu. Nie jest to tylko martwa zasada. Jak logika wskazuje aby wyznaczyć ranking musimy ustalić kryteria wg których będzie przydzielane pierwsze, drugie i kolejne miejsca. Np. pracownicy wg wzrostu, zarobków czy sex appeal'u :)
Z drugiej strony możemy ich „spartycjonować” i zrobić osobny ranking dla każdego z działów, albo zrobić konkurs na miss całej firmy :)




Dense_rank()


Jest to ogólna funkcja rankingu która nadaje kolejne numery „Zajętych miejsc” w rankingu.
Na poniższym obrazku w oparciu o funkcję dense_rank stworzyłem ranking pracowników wg zarobków. Nie dzieliłem zbioru na grupy, ranking obejmował wszystkich pracowników firmy razem (dlatego nie stosowałem PARTITION BY). Ponieważ pojawiły się osoby które miały taką samą pensję (np. De Haan i Kochhan) zajęły to samo miejsce w rankingu ex equo.
 

Podział wierszy na partycje



Tym razem podzieliłem pracowników na grupy w zależności od tego jakiemu managerowi podlegają, a następnie w ramach tych grup stworzyłem ranking:

Dzielić na partycję możemy podobnie jak grupowanie. Według więcej niż jednego kryterium. Na poniższej ilustracji widać ranking wg zarobków pracowników podzielonych na grupy wg departamentów a następnie w obrębie tych grup, jeszcze na podległych względem różnych managerów.
 

Istotne drobiazgi....

1. Funkcje analityczne działają zawsze po grupowaniu, łączeniu tabel, warunku where i having. Operują na wyniku całego zapytania. Zostają wykonane jako ostatnie (chociaż przed sortowaniem). Poniższy przykład to obrazuje.

2. Czym się właściwie różnią funkcje analityczne od grupowania?
   - w grupowaniu mogliśmy listować tylko kolumny po których grupujemy
   - Funkcje analityczne w przeciwieństwie do agregujących mogą zwracać więcej niż jedną wartość na każdą grupę.