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.
Sposób działania funkcji analitycznych.
Działają po :
- grupowaniu
- łączeniu tabel
- warunku where
- warunku having
Operują na wyniku całego zapytania. Zostają wykonane jako ostatni przed sortowaniem.
Przykładem wykorzystania tych funkcji może być np. wyświetlenie TOP10 rekordów.
Funkcje rankingu
Funkcje rankingu wyznaczają pozycję danego wiersza porównując go z wartościami innych wierszy w określonym zbiorze danych. Podział zbioru danych pozwala na stworzenie oddzielnych rankingów dla każdego zbioru.
- rank, dense_rank
- cume_dist – nie omówione w tym artykule
- percent_rank – nie omówione w tym artykule
- ntile – nie omówione w tym artykule
- row_number
Dense_rank
Przykład 1
select nazwa, kwota,
dense_rank() over (order by kwota desc) as ranking
from tablica ;
Powyższy przykład zwraca posortowane wartości wg kwoty i dodatkowo w kolumnie ranking podane jest miejsce od najwyższej kwoty do najniższej. Miejsca podawane są po kolei bez przeskoków, tzn. jeżeli dwie różne nazwy maja ta samą kwotę to w kolumnie ranking miejsce również jest takie samo, dodatkowo następny rekord ma miejsce o 1 większe od poprzedniego. Nie ma tu więc żadnych luk w numeracji, mogą pojawić się jednak pozycje o zajmujące to samo miejsce.
Przykładowy dane zwrócone przez zapytanie:
|
Przykład 2
Tym razem podzielimy na grupy w zależności od tego jakiemu managerowi podlegają, a następnie w ramach tych grup stworzymy ranking:
select nazwa, kwota, manager,
dense_rank() over (partition by manager order by kwota desc) as ranking
from tablica
Tym razem otrzymamy wyniki w grupach dla konkretnych managerów.
Przykładowe dane zwrócone przez zapytanie:
Nazwa
|
Kwota
|
Manager
|
Ranking
|
Nazwa 8
|
110
|
1
|
1
|
Nazwa 2
|
110
|
1
|
1
|
Nazwa 3
|
101
|
1
|
2
|
Nazwa 1
|
97
|
2
|
1
|
Nazwa 4
|
8
|
2
|
2
|
Nazwa 5
|
120
|
4
|
1
|
Dzielić na zespoły (partycje) możemy wg. więcej niż jednego kryterium.
Różnice między funkcją analityczną a grupowaniem.
W grupowaniu możemy 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ę.
Rank
Funkcja Rank() działa podobnie jak Dense_rank(). Różnica polega na sposobie numeracji. Jeżeli dwa rekordy zajmują tę samą pozycję, to pozostawiają one dziurę w numeracji. W przypadku tej funkcji nie mamy ciągłości numeracji.
Przykład 3
select nazwa, kwota,
rank() over (order by kwota desc) as ranking
from tablica
Przykładowy dane zwrócone przez zapytanie:
|
Row_number
Funkcja Row_number() numeruje kolejno wiersze.
Przykład 4
select nazwa, kwota,
row_number() over (order by kwota) as numer
from tablic
Przykładowy wynik powyższego zapytania:
|
Różnice między Row_number() a Desc_Rank().
Row_Number ma numerację ciągłą i niepowtarzającą się. Ustawienie rekordów w funkcji Row_number() jest przypadkowe (w kolejności określonej przez order by).
Przykład 5
select nazwa, kwota, manager
row_number() over (partition by manager order by kwota) as numer
from tablica
Przykładowe dane zwrócone przez zapytanie:
Nazwa
|
Kwota
|
Manager
|
Numer
|
Nazwa 2
|
110
|
1
|
1
|
Nazwa 8
|
110
|
1
|
2
|
Nazwa 3
|
101
|
1
|
3
|
Nazwa 1
|
97
|
2
|
1
|
Nazwa 4
|
8
|
2
|
2
|
Nazwa 5
|
120
|
4
|
1
|
Wyświetlanie pierwszych kilku rekordów.
Można korzystać z funkcji Row_number() jednak ona wymusza dodatkowe sortowanie po jakiejś kolumnie. Istnieje również funkcja rownum, która numeruje rekordy w wyniku zapytania.
Przykład 6
select kol1, kol2, rownum from tablica
where rownum < 10
W wyniku tego zapytania dostaniemy 10 pierwszych rekordów. Należy pamiętać, że numeracja wyniku zawsze jest od 1 i z uwagi na to że numerowane są dopiero wyniki nie jest możliwe w powyższym przykładzie w warunku where podać np rownum > 1, takie zapytanie zawsze zwróci pustą listę wyników.
Brak komentarzy:
Prześlij komentarz