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:
|
1 2 3 4 |
select prac.imie || ' ' || prac.nazwisko nazwa_pracownika, stan.stanowisko, prac.place from pracownicy prac, stanowisko stan where prac.stan_id = stan.stan_id order by prac.stan_id, prac.place desc; |
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()
|
1 2 3 4 5 6 7 8 |
select prac.imie || ' ' || prac.nazwisko nazwa_pracownika, stan.stanowisko, prac.place, RANK() over (partition by stan.stanowisko order by prac.place desc) "RANK", DENSE_RANK() over (partition by stan.stanowisko order by prac.place desc) "DENSE_RANK" from pracownicy prac, stanowisko stan where prac.stan_id = stan.stan_id order by prac.stan_id, prac.place desc; |
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.
W dzisiejszym artykule przedstawie krok po kroku jak zainstalować darmową bazę danych Oracle 11g XE z możliwością wykorzystania komercyjnie. Wersja darmowa ma pewnie ograniczenia:
Jest to dobre rozwiązanie dla małych i średnich firm, oraz dobra baza do testowania różnych rozwiązań w celach edukacyjnych. Warto ją znać bo większe firmy stosują głównie rozwiązania Oracle. Ranking popularności możecie znaleźć na stronie: https://db-engines.com/en/ranking
Dla potrzeb dydaktycznych warto zainstalować bazę danych na wirtualnej maszynie. Jeśli nigdy nie słyszałeś o wirtualnej maszynie zajrzyj tu. Jednym słowem jest to komputer w komputerze. Pozwala nam to uruchamiać bazę tylko wtedy kiedy jej potrzebujemy i nie obciąża na co dzień komputera. Druga sprawa możemy przetestować realne środowisko klient-serwer. Pomijam krok instalacji wirtualnej maszyny bo nie jest to przedmiotem tego artykułu, zabieramy się za postawienie bazy lokalnie ma komputerze z system operacyjnym Windows. Wersja systemu operacyjnego Windows nie ma większego znaczenia logika działania jest taka sama. A więc zaczynamy.
Całą zabawę podzielimy na 3 obszary:
Kolejność jest istotna wiec zabieramy się najpierw za konfiguracje karty sieciowej.
Zanim rozpoczniemy instalacje bazy należy zainstalować kartę sieciową Loopback. Wirtualna karta sieciowa pozwala na skonfigurowanie procesu nasłuchu systemu zarządzania bazą danych Oracle tak, aby możliwa była praca z bazą w przypadku odłączenia komputera od serwera DHCP przydzielającego adresy IP w sieci.
W zależności od wersji systemu Windows polecenia mogą się różnic, dla Windows 7 przechodzimy do Panelu Sterowania i wybieramy Menadżer urządzeń. W oknie zaznaczamy Karty sieciowe i w górnym menu wybieramy polecenie Akcja następnie Dodaj starszy sprzęt. Pojawia się okno Kreatora
Przechodzimy dalej i zaznaczamy pole „Zainstaluj sprzęt, który wybiorę ręcznie z listy„.
W kolejnym oknie wybieramy Karta sieciowa
W kolejnym oknie oszukujemy Kartę Microsoft Loopback
Następnie naciskamy dwa razy Dalej i karta sieciowa została zainstalowana na komputerze, jedyne co nam zostało to ją skonfigurować.
Przechodzimy ponownie do Panelu Sterowania/Sieć i Internet/Połączenia sieciowe odnajdujemy w/w kartę.
Prawym myszki wybieramy właściwości. W oknie właściwości Karty Microsoft Loopback wybieramy Protokół internetowy (TCP/IP)
W zakładce ogólne wybieramy opcję Użyj następującego adresu IP i wprowadzamy dowolny adres lokalny np: 10.10.10.10 z maską podsieci 255.255.255.0. Przyciskamy OK w oknie Właściwości.
Jedyne co trzeba jeszcze zrobić to aktualizacja wpisu w pliku hosts. Przy pomocy notatnika otwieramy plik hosts z folderu C:\Windows\system32\drivers\etc. W nowym wierszu wprowadzamy adres IP zdefiniowany dla karty Microsoft Loopback oraz pełną nazwę komputera odczytaną z Właściwości systemu dla skrótu Mój komputer.
Zapisujemy wprowadzone zmiany w pliku hosts i uruchamiamy ponownie komputer.
Ściągnięty plik rozpakowujemy i uruchamiamy instalację.
pojawia się nam okno przygotowanie do instalacji
……
Po pobraniu i rozpakowaniu plików instalacyjnych ODAC należy uruchomić instalację. Link poniżej:
https://www.oracle.com/technetwork/topics/dotnet/downloads/index.html
Pierwsze okno które na się pojawi:
Następnie pojawi się okno, gdzie klikamy Next
Pojawia się okno w którym wybieramy katalog docelowy instalacji klienta Oracle, najlepiej aby była to inna partycja dysku od instalacji serwera, np dysk D
Z listy poniżej w oknie wybieramy pakiety które chcemy aby były zainstalowane dla naszych potrzeb wystarczy Oracle Instant Client 12.1.0.1.0 i wybieramy Next
Kolejne okno pozwala nam na konfiguracje pliku tnsnames.ora, wypełniamy poszczególne pola. Pamiętaj ze zawsze może je edytować w pliku.
Kolejne okno pokazuje nam co będzie instalowane, pozostaje nam tylko nacisnąć Install
Czekamy cierpliwie aż instalacja dotrze do końca.
I czekamy na informacją, że dotarliśmy do końca instalacji.
Biblioteki ODAC instalowane są wraz z klientem bazy danych, obsługującym komunikację pomiędzy serwerem oraz aplikacją kliencką. Po zainstalowaniu ODAC należy skonfigurować proces nasłuchu klienta Oracle tak, umożliwić komunikację pomiędzy klientem a serwerem bazy danych. Najlepiej jest skopiować pliki konfiguracyjne procesu nasłuchu serwera i zapisaniu ich jako pliki konfiguracyjne procesu nasłuchu klienta Oracle.
W tym celu należy przejść do katalogu głównego instalacji serwera Oracle (np. C:\oraclexe) i przejść do do katalogu z plikami konfiguracyjnymi procesu nasłuchu serwera: C:\oraclexe\app\oracle\product\11.2.0\serwer\network\admin. Zaznaczamy i kopiujemy pliki konfiguracyjne: listener.ora oraz tsnames.ora.
Następnie wybieramy katalog główny instalacji klienta ODAC (np. D:\oracle) i przechodzimy do katalogu z plikami konfiguracyjnymi procesu nasłuch klienta: D:\oracle\product\11.0.2\client_1\network\admin. Dla bezpieczeństwa stwórzmy kopie zapasowe plików konfiguracyjnych zmieniając rozszerzenie plików listener.ora oraz tnsnames.ora na listener.ora.bak oraz tnsnames.ora.bak. Zapisujemy skopiowane pliki konfiguracyjne serwera w katalogu klienta.
Data Access Components, instalacja bazy oracle, ODAC, oracele, sql
