SQL

SQL – funkcja rankingowa RANK()

26 Gru , 2014  

W systemach relacyjnych baz danych od dawna już zawitały mechanizmy analityczne, które pozwalają z poziomu bazy za pomocą funkcji analitycznych takich ja np. rank(), dense_rank() przetwarzać dane bez konieczności stosowania dodatkowego oprogramowania.  W tym artykule zajmiemy się jedną z funkcji rankingowych bez której nie wyobrażam sobie codziennej pracy 🙂

Do funkcji rankingowych zaliczamy takie funkcje jak: RANK(), DENSE_RANK(), CUME_DIST(); PERCENT_RANK(), NTILE(), ROW_NUMBER().

Ogólna składnia użycia funkcji rankingowych jest następująca:

funkcja_rankingowa() over ([partition by kolumna_1] order by kolumna_2 [desc] [nulls first | last])

gdzie:

zapis w nawiasach kwadratowych [] jest opcjonalny, nie musi występować a funkcja zadziała poprawnie.

funkcja_rankingowa() – nazwa funkcji rankingowej, np RANK()

partition by kolumna_1 – podajemy nazwę kolumny/kolumn po której dzielimy tabele na obszary.

Obrazowo można powiedzieć ze dzielimy jedną tabele na „x” małych tabel i funkcja działa niezależnie na każdą z nich. Jeśli nie użyjemy wyrażenia partition by to rankingowi podlegają wszystkie rekordy tabeli.

kolumna_2 – kolumna po której sortujemy kolejność rekordów

desc – sortuje rekordy od największej do najmniejszej, domyślnie użyte jest wyrażenie asc która działa odwrotnie.

nulls first – powoduje że wartości puste trafiają na początek rankingu

nulls last –  powoduje że wartości puste trafiają na koniec rankingu Funkcje rankingu wyznaczają pozycję danego rekordu porównując go z wartościami innych rekordów w określonym zbiorze danych. Podział zbioru danych pozwala na stworzenie oddzielnych rankingów dla każdego zbioru. To tyle teorii a teraz przejdźmy do przykładu.

Przykład Wyobraźmy sobie taką sytuacje, przychodzi do nas Boss i prosi nas o listę dwóch pracowników, którzy w ramach danego stanowiska zarabiają najwięcej. Najlepszą metodą będzie użycie funkcji rank(). Owszem ktoś powie, że można to zrealizować za pomocą tabeli przestawnej w MS Excelu, ale po co sięgać do dodatkowych narzędzi skoro z poziomu bazy może również to zrealizować, ba mało tego funkcja rank() bardzo często przydaje się w innych zastosowaniach np. z użyciem funkcji merge ale o tym w innym artykule. Lista pracowników wygląda tak jak poniżej. Wszystkie zapytania działają na przykładowej bazie, którą przygotowałem i jest zbliżona do przykładowej bazy HR dostępnej podczas instalacji Oracl’a. Moją bazę po liftingu możesz pobrać tu. zapytanie:

SQL - funkcja rankingowa RANK()

4 i 5 rekord pokazuje, że na tym samym stanowisku „Kierownik Produktu” jest dwóch pracowników którzy zarabiają tyle samo. Dodajmy teraz do zapytania funkcję RANK() i DENSE_RANK()

 

 

SQL - funkcja rankingowa RANK()

 Czym się różni funkcja RANK() od DENSE_RANK()?

Różnica pomiędzy tymi funkcjami jest taka, że w sytuacji gdy rekordy znajdują się na pozycji ex aequo powstaje przerwa w numeracji, gdy używamy funkcji RANK(). Nie dzieje się tak dla funkcji DENSE_RANK() co bardzo dobrze widać na powyższym rysunku. Nasz Boss poprosił nas o listę 2 pracowników najlepiej zarabiających na każdym stanowisku. Musimy ograniczyć się do rekordów gdzie w kolumnie RANK jest wartość nie większa od liczby 2. Aby tego dokonać musimy obrać nasze zapytanie w dodatkowego select’a z warunkiem „RANK” < =2, dlaczego? A no dlatego bo funkcje analityczne działają na rekordach będących wynikiem zapytania. Działanie funkcji analitycznych odbywa się na samym końcu po sortowaniu i przed takimi działaniami jak: grupowanie, łączenie tabel, warunku where , warunku having.

, ,



One Response

  1. Biały pisze:

    Dziękuję za bardzo dobre wytłumaczenie!

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

WP2Social Auto Publish Powered By : XYZScripts.com