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.

, ,

SQL

SQL – instalacja Oracle 11g XE

14 Gru , 2014  

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:

  • 11GB danych użytkownika,
  • 1GB RAM,
  • 1 CPU

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.

Co będzie nam potrzebne?

Całą zabawę podzielimy na 3 obszary:

  • Instalacja i konfiguracja karty sieciowej Microsoft Loopback
  • Instalacja bazy danych Oracle 11g XE
  • Instalacja Oracle Data Access Components (ODAC)

Kolejność jest istotna wiec zabieramy się najpierw za konfiguracje karty sieciowej.

 

Instalacja i konfiguracja karty sieciowej Microsoft Loopback

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

SQL - instalacja Oracle 11g XE

Przechodzimy dalej i zaznaczamy pole „Zainstaluj sprzęt, który wybiorę ręcznie z listy„.

SQL - instalacja Oracle 11g XE

W kolejnym oknie wybieramy Karta sieciowa

SQL - instalacja Oracle 11g XE

W kolejnym oknie oszukujemy Kartę Microsoft Loopback

SQL - instalacja Oracle 11g XE

Następnie naciskamy dwa razy Dalej i karta sieciowa została zainstalowana na komputerze, jedyne co nam zostało to ją skonfigurować.

 

Konfiguracja protokołu internetowego (TCP/IP) karty Microsoft Loopback

Przechodzimy ponownie do Panelu Sterowania/Sieć i Internet/Połączenia sieciowe odnajdujemy w/w kartę.

SQL - instalacja Oracle 11g XE

Prawym myszki wybieramy właściwości. W oknie właściwości Karty Microsoft Loopback wybieramy Protokół internetowy (TCP/IP)

SQL - instalacja Oracle 11g XE

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.

SQL - instalacja Oracle 11g XE

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.

SQL - instalacja Oracle 11g XE

Zapisujemy wprowadzone zmiany w pliku hosts i uruchamiamy ponownie komputer.

 

 Instalacja bazy danych Oracle 11g XE

Ściągnięty plik rozpakowujemy i uruchamiamy instalację.

pliki

pojawia się nam okno  przygotowanie do instalacji

SQL - instalacja Oracle 11g XE

……

 

Instalacja Oracle Data Access Components (ODAC)

 

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:

 

SQL - instalacja Oracle 11g XE odac

Następnie pojawi się okno, gdzie klikamy Next

SQL - instalacja Oracle 11g XE

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

SQL - instalacja Oracle 11g XE ODAC

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

SQL - instalacja Oracle 11g XE

Kolejne okno pozwala nam na konfiguracje pliku tnsnames.ora, wypełniamy poszczególne pola. Pamiętaj ze zawsze może je edytować w pliku.

SQL - instalacja Oracle 11g XE

 

Kolejne okno pokazuje nam co będzie instalowane, pozostaje nam tylko nacisnąć Install

SQL - instalacja Oracle 11g XE

Czekamy cierpliwie aż instalacja dotrze do końca.

SQL - instalacja Oracle 11g XE

I czekamy na informacją, że dotarliśmy do końca instalacji.

 

Konfiguracja nasłuchu (Oracle Listener)

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.

 

, , , ,