Oskar Skibski, grupa nr 4, sala 2043

Bazy danych 2015/16

Laboratorium 3 (19.10.2015) - SQL / GRUPOWANIE

Dzisiaj nauczymy się trudnej sztuki grupowania wierszy oraz sprawdzimy jak działa rekurencja w ORACLu.

GROUP BY

W SQL istnieje możliwość grupowania wierszy. Przykładowo jeżeli chcemy przedstawić zbiorcze statystyki wierszy rozdzielając je podług zawartości jednej z kolumn możemy użyć konstrukcji GROUP BY. Grupować można także po wielu kolumnach. Sekcje GROUP BY umieszczamy po warunkach wybierających wiersze.

Po grupowaniu wierszy w sekcji SELECT nie możemy odnosić się do kolumn po których nie grupujemy w zwykły sposób, musimy zamiast tego używać funkcji agregujących. Jest to dość oczywiste, przykładowo kiedy pogrupujemy tabelę reprezentującą osoby po nazwisku to odwoływanie się do imienia nie ma sensu - imiona dla danego nazwiska mogą być przecież różne.

SELECT deptno, COUNT(*) ile FROM emp GROUP BY deptno ORDER BY deptno;
SELECT deptno, job, AVG(sal) zarobki FROM emp WHERE mgr IS NOT NULL GROUP BY deptno, job ORDER BY job;

Istnieje 5 funkcji agregujących:

• COUNT - zwraca ilość wierszy; parametrem może być * lub nazwa dowolnej kolumny - w pierwszym przypadku liczona jest ilość wierszy, w drugim ilość podanych (nie równych NULL) wartości w kolumnie; możliwe jest także policzenie ilości różnych wartości w kolumnie poprzez umieszczenie słówka DISTINCT przed nazwą kolumny
• MIN / MAX - zwraca wartość minimalną / maksymalną z danej grupy; pomijane są wartość NULL
• SUM / AVG - zwraca sumę / średnią wartości w kolumnie; tak jak powyżej, pomijane są wartości NULL

Funkcji tych można używać także kiedy tabela nie jest grupowana, odnosić się będą wówczas do wszystkich wyselekcjonowanych wierszy.

O ile warunki na wiersze które grupujemy możemy umieścić w WHERE, to nie wiemy dotychczas gdzie umieścić warunki dotyczące cech grupy. Jak wybrać na przykład grupy które mają więcej niż 3 elementy? Z pomocą przychodzi nam kolejna konstrukcja SQL - HAVING.

SELECT COUNT(*), job, MIN(sal), MAX(sal) FROM emp GROUP BY job HAVING COUNT(*) > 3 ORDER BY job;

Warto zatrzymać się w tym miejscu na chwilę i zastanowić się nad tym jaka jest kolejność wykonywach operacji. Zapytanie:

• wybiera wiersze z tabeli z FROM pasujące do warunku WHERE
• te wiersze grupuje według klauzuli GROUP BY
• odrzuca grupy które nie pasują do warunku HAVING
• dla każdej grupy wylicza wartość kolumn agregujących z SELECT

Rekurencja

No dobrze, umiemy wypisywać już całkiem dużo, ale jak na przykład wypisać wszystkich (niekoniecznie bezpośrednich) podwładnych danego pracownika? Z pomocą przychodzi nam konstrukcja CONNECT BY.

SELECT empno, ename, PRIOR ename AS mgr_ename, LEVEL 
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

Po START WITH musimy umieścić warunek na wiersze od jakich budowa naszego drzewa ma się zacząć. W CONNECT BY definiujemy warunek złączenia dwóch wierszy. Aby odróżnić ich atrybuty używamy słówka PRIOR - atrybut poprzedzony tym słowem dotyczy wiersza "ojca", a nie poprzedzony - wiersza "syna". Tak jak w zapytaniach na wielu tabelach, może być on bardziej złożony niż jedna równość. Jeżeli chcemy przygotować ORACLE'a na pojawienie się cykli w naszym drzewie dodajemy słowo NOCYCLE - w przeciwnym wypadku po znalezieniu cyklu zgłosi wyjątek, obrazi się i nic nam nie wypisze.

W sekcji SELECT używać możemy także atrybutów wiersza "ojca", tak jak w CONNECT BY poprzedzamy je słówkiem PRIOR. Dodatkowo dochodzi nam parę specjalnych zmiennych/atrybutów. Najważniejszym jest LEVEL który informuje nas o tym na którym stopniu zagłębienia dany wiersz wystąpił (pierwszy poziom ma LEVEL 1). Innym przydatnym jest CONNECT_BY_ROOT, który pozwala nam sięgnąć do wiersza od którego nasza gałąź drzewa się zaczęła.

SELECT ename, CONNECT_BY_ROOT ename AS menago
FROM emp
START WITH job = 'MANAGER'
CONNECT BY PRIOR empno = mgr;

Dobrze uświadomić sobie, że warunek WHERE odnosi się do zbudowanego drzewa, dlatego także tam możemy na przykład odnosić się do zmiennej LEVEL czy PRIOR ename.

Wbudowane funkcje

Obejrzyj format dat, funkcje na datach oraz funkcje na napisach.