Proste drzewa
Świetnym przykładem na którym można zobrazować drzewa w SQL jest struktura zatrudnienia którą można podgądać w schemacie HR. Każdy pracownik (poza samym prezesem) ma jakiegoś managera. Numer managera danego pracownika jest zawarty w polu manager_id. Liczba która znajduje się w tym polu jest numerem pracownika (employee_id) tego managera. Jako że każdy manager jest jednocześnie pracownikiem, a dane wszystkich pracowników znajdują się w tabeli employees, następuje samozłączenie. Każdy manager również może mieć przełożonego. Tworzy więc nam się pewnego rodzaju drzewo managerów i ich podwładnych. Zasadę relacji pomiędzy pracownikiem a managerem przedstawia poniższy przykład:
Przełożonym pracownika „Daniel Faviet” jest inny pracownik o numerze 108. Pracownik 108 to Nancy Greenber której przełożonym jest pracownik o numerze 101 czyli Neena Kochhar. Neena Kochhar jest z kolei podwładną pracownika o numerze 100 czyli Stevena Kinga, prezesa który nie ma nad sobą już żadnych przełożonych.
Chcielibyśmy uzyskać taki efekt:
Pierwsze dwie linie :
select lpad(' ',4*(level-1)) || last_name , employee_id,
manager_id, level from employees
Zasadniczo sprowadza się do względów estetycznych. Wyświetlenie nazwiska pracownika, jego numeru oraz numeru jego managera. Pozostałe dwie klauzule:
level: To poziom zagłębienia w strukturze. Dla przykładu King ma level 1 ponieważ jest „na samej górze”. Kochhar jest na drugim poziomie zagłębienia (pod Kingiem) itd.
lpad(' ',4*(level-1)): Dzięki temu fragmentowi nazwiska pracowników są odsunięte od lewej krawędzi w zależności od zagłębienia w strukturze. Funkcja Lpad dopełnia podaną frazę znakami do określonej długości. Jeśli nie podamy jakimi znakami ma zostać dopełniona, domyślnie przyjęte zostaną spacje. Tak więć ta funkcja zwróci nam tyle spacji ile wynosi poziom zagłębienia pomniejszony o 1. Dlaczego pomniejszony o 1? Ponieważ ilość znaków jaką można dodać może wynosić 0, a level liczony jest od 1.
W celu lepszego zobrazowania stworzyłem ilustrację przedstawiającą część drzewa zatrudnienia. Porównaj ją z wynikiem ostatniego zapytania. Zauważ że Kochhar jest poziom niżej niż King. Whalen, Marvis i Greenberg są pracownikami tego samego szczebla. Grenbeerg jest przełożonym 4 osób.
Wracając do naszego zapytania:
start with manager_id is null
Określa z którego miejsca baza ma rozpocząć budowanie drzewa. „Korzeniem” jest w tym przypadku King, a więc cappo di tutti capi – szef wszystkich szefów. Jako taki ma puste pole manager_id, ponieważ nie ma żadnego przełożonego.
connect by prior employee_id= manager_id
Określa sposób w jaki ma zostać tworzone drzewo, odwzorowuje sposób łączenia wierszy. Innymi słowy co jest czynnikiem wiążącym wiersze, w jaki sposób baza ma budować kolejne poziomy. To tak, jakbyśmy powiedzieli jej:
„Stwórz kolejny poziom z pracownika którego manager_id jest takie jak employee_id bieżącego wiersza”.
Samo słowo „prior” określa która kolumna jest nadrzędna. W tym przypadku nadrzędna jest kolumna employee_id, ponieważ wskazuje na wiersz nadrzędny (manager_id pracownika wskazuje na employee_id managera).
Budowanie drzew podlega pewnemu ograniczeniu, wynikającemu zasadniczo ze zwykłej logiki. Pracownik nie może mieć dwóch bezpośrednich szefów, tak jak żaden człowiek nie może mieć dwóch matek. Dlatego budując drzewa musimy mieć całkowitą pewność, że czynnik łączący (w tym wypadku relacja employee_id=manager_id) jest unikalny. To jest nie może w tabeli pojawić się inny wiersz w którym ten sam employee_id wskazywałby na innego managera. Skąd mieć tą pewność? Ano stąd że w tym przykładzie employee_id jest kluczem głównym tabeli, tak więc nigdy nie będzie innego wiersza o tym samym empoyee_id.
Inny przykład stosowania drzew. Interesuje mnie lista pracowników którzy bezpośrednio lub pośrednio podlegają pod pracownika o id 101 (Kochhar).
Rozpoczynam więc budowanie drzewa od pracowników których bepośrednim przełożonym jest pracownik o id 101 (start with manager_id=101) . Zasada dalszego łączenia taka jak wcześniej.
Pojawiło się kilka wierszy w których manager_id jest inne niż 101. Co to oznacza? Oznacza to , że te osoby są podległe pod managera o id 101 ale nie bezpośrednio. Pomiędzy pracownikiem Kochhar a np. Faviet jest jeszcze jeden manager, będący podrzędny w stosunku do Kochhar'a ale nadrzędnym w stosunku do Faviet'a. Sprawdzamy więc kim jest osoba o employee_id 108 i kto jest jego managerem:
To samo z pracownikiem 205:
Nie jest powiedziane do jakiego poziomu zagłębienia mają być zliczani pracownicy. Wyświetlają się więc wszyscy podwładni pracownika 101, niezależnie od poziomu.
Możemy to jednak ograniczyć stosując warunkowanie z użyciem klauzuli „level”:
W powyższym przypadku wyświetlają się nam jedynie pracownicy podlegli bezpośrednio pod Kochhara. Level jest funkcją która zwraca wyniki w momencie budowania drzewa.
Z tego powodu możemy wyświetlić tylko wybrany poziom, również inne niż 1:
Budując drzewa możemy eliminować wiersze których nie chcemy wyświetlać w wyniku, możemy je również sortować:
W tym wypadku, poza tym że zostaną wyświetlone tylko wiersze o drugim poziomie zagnieżdżenia, w wyniku nie będą uwzględnione wiersze zawierające literę „a” w kolumnie „last_name”. Wynik został dodatkowo posortowany po employee_id.
Eliminować możemy pojedyncze wiersze, lub całe gałęzie. W poniższym przykładzie, w wyniku nie zostanie ujęty jedynie wiersz której pole „last_name” zawiera „Greenberg”:
Jeśli natomiast dodamy ten sam warunek ale po operatorze connect by, nie zostanie wyświetlony pracownik o nazwisku „Greenberg” ani żaden z jego podwładnych. Z naszego drzewa zniknie cała gałąź.
Ćwiczenia
- Wyświetl ranking pracowników w grupach pracowników podzielonych względem przełożonych. Ranking ma dotyczyć wysokości wypłaty.
- Wyświetl ranking pracowników w grupach pracowników podzielonych względem departamentów w których pracują. Ranking ma dotyczyć wypłaty. Interesują nas jednak tylko pracownicy otrzymujący najwyższe wynagrodzenie w grupie.
- Wyświetl ranking średnich zarobków we wszystkich działach firm. Zamiast department_id, chcielibyśmy wyświetlić nazwę departamentu.
- Wyświetl anomalia wysokości wypłat w całej firmie. Za anomalia uznajemy taką wysokość wypłaty, kiedy otrzymuje taką wypłatę mniej niż 10% osób w firmie.
- Wyświetl w postaci drzewa hierarchię zatrudnienia na podstawie tabeli employees. Wyniki mają być posortowane w taki sposób, by na początku byli wymienieni pracownicy najwyższego stopnia, a następnie kolejni wg stopnia.
- Wyświetl hierarchię pracowników w taki sposób, by id prezesa nie było stałą, a pochodziło z zagnieżdżonego zapytania.
- Wyjaśnij dlaczego w wynikach poprzedniego ćwiczenia nie jest wymieniony King? W jaki sposób można wyświetlić w jednym wyniku, wynik poprzedniego ćwiczenia, a na samym początku listy również Kinga?
- Wyświetl nazwiska, numer pracownika oraz poziom zagnieżdżenia w hierarchii firmy, ale tylko pracowników najniższego szczebla.
- Wyświetl nazwisko i datę zatrudnienia pracownika zatrudnionego najwcześniej spośród wszystkich pracowników najniższego szczebla.
-- odpowiedzi
OdpowiedzUsuń--zad1
select last_name, salary, dense_rank() over (partition by manager_id order by salary desc) as ranikng from employees order by 3;
--zad2
select * from (select last_name, salary,
dense_rank() over (partition by department_id order by salary desc) as ranking from employees )--
where ranking =1;
--zad3
select d.department_name , d.department_id
from employees e,departments d
where (e.department_id = d.department_id) ;
--zad4
select * from (
select last_name, salary,
cume_dist() over (order by salary desc) as anomalia from employees )--
where anomalia <0.1;
-- zad5
-- select lpad(' ',4*(level-1))|| last_name , manager_id ,level, employee_id from employees start with manager_id is null
-- connect by prior employee_id=manager_id ;
--zad 6
--zad7
select last_name , manager_id ,level, employee_id from employees where level=4 start with manager_id is null
connect by prior employee_id=manager_id ;
--zad8
select employee_id,last_name, level from employees where level=4 start with manager_id is null
connect by prior employee_id=manager_id;
--zad 9
select last_name from employees where hire_date=( select max(hire_date) as datunia from employees where level=4 start with manager_id is null
connect by prior employee_id=manager_id);
alternatywne rozwiązanie ćw 3:
OdpowiedzUsuńselect department_name as "Dzial" , department_id as "Nr dzialu", round((avg(salary)), 2) as "Średnia"
from employees join departments using (department_id)
group by department_name, department_id
order by 3 desc;
Korzystając z funkcji analitycznych zadanie 3 można rozwiązać w taki sposób:
OdpowiedzUsuńselect distinct(d.department_name), round((avg(e.salary) over (partition by e.department_id)),2) as srednia
from employees e join departments d on (e.DEPARTMENT_ID = d.DEPARTMENT_ID) order by d.department_name;
przecież w żadnym z podanych tutaj rozwiązań zadania numer 3 nie ma użytej funkcji rankingu, to co ty piszesz Lustrum można uzyskać przez prostsze zapytanie
OdpowiedzUsuńselect department_name, round(avg(salary)) from employees join departments using (department_id)
group by department_name
order by 1;
zapytanie do zadania numer 3 powinno wyglądać mniej tak :
OdpowiedzUsuńselect department_name, round(średnia) średnia, dense_rank() over (order by średnia) from
(select department_name, avg(salary) średnia from employees join departments using (department_id)
group by department_name);
Ten komentarz został usunięty przez autora.
OdpowiedzUsuńja zadanie 3 zrobilam tak:
OdpowiedzUsuńselect * from
(select avg(salary), department_name,
dense_rank() over (order by avg(salary)) as ranking
from employees natural join departments
group by department_name
);
Ten komentarz został usunięty przez autora.
OdpowiedzUsuńMoje rozwiązanie do zad. 9:
OdpowiedzUsuńSELECT last_name,
hire_date
FROM employees
WHERE LEVEL=4 AND hire_date LIKE
(SELECT min(hire_date)
FROM employees where level=4
START WITH manager_id IS NULL CONNECT BY
PRIOR employee_id=manager_id)
START WITH manager_id IS NULL CONNECT BY
PRIOR employee_id=manager_id;
Dobre wprowadzenie w connect by prior, ale mam problem, do którego nie umiem tego zastosować. Firma przyjmuje faktury z zewnątrz. Te faktury to level=1. Następnie dokumentami wewnętrznymi pozycje z faktury są rozpraszane po firmie, aż do miejsca docelowego. Każdy dokument ma wskazanie dok. nadrzędnego, a więc sytuacja identyczna jak w przykładzie z zatrudnieniem. Teraz trzeba wyświetlić element z dokumentu końcowego ze wskazaniem, z której faktury zewnętrznej pochodzą, pomijając wszystkie pośrednie. No i utknąłem. Dla każdej gałęzi interesuje mnie para: level =1 oraz max(level).
OdpowiedzUsuńMożesz mi to pokazać na swoim przykładzie?