Oskar Skibski, grupa nr 4, sala 2043
Trik 1: grupy z identycznymi wartościami
Czasem grupując wiersze wiemy, że w danej grupie wszystkie wartości w kolumnie po której nie grupujemy też są sobie równe. Na przykład jeżeli pogrupujemy tabelę (chociażby po złączeniu ją z inną) po kluczu głownym (takim jakim jest empno w emp) to wiemy, że w każdej grupie pola z tej tabeli będa sobie równe. W skrajnym przypadku możemy wiedzieć, że grupy mają jeden wiersz (np. dodając warunek HAVING COUNT(*) = 1). Oracle jednak nie jest taki domyślny i nie pozwoli na wypisanie wartości z pól po których nie grupujemy. Jak sobie z tym poradzić? Możemy zrobić to na dwa sposoby. Pierwszym jest pogrupowanie także po tych kolumnach - skoro we wszystkich grupach wartości w nich są równe grupy nie rozpadną się bardziej, a Oracle przestanie się pluć.
SELECT dept.deptno, dname, loc, COUNT(*) immanuel FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.deptno, dname, loc;
Drugim sposobem jest użycie funkcji agregujacej MIN lub MAX która działa na wszystkich typach danych.
SELECT dept.deptno, MIN(dname), MIN(loc), COUNT(*) immanuel FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.deptno;
Trik 2: wiersz naj
Inną częstą sytuacją jest potrzeba wypisania danych z wiersza który zawiera największą/najmniejszą wartość w kolumnie. Przykładowo możemy szukać najbogatszego pracownika - największą pensję łatwo wybrać za pomocą funkcji agregującej, ale jak wybrać inne pola z tego wiersza? Można to zrobić podzapytaniem, ale ten efekt uzyskamy też sprytnie uzywając klasycznej konstrukcji łączenia - w tym celu wystarczy złączyć tabelę samą ze sobą (LEFT/RIGHT JOIN) dla każdego wiersza szukając innych z wartościami większymi; wiersz bez dopasowania jest tym z największą wartością.
SELECT A.* FROM emp A LEFT JOIN emp B ON A.sal < B.sal WHERE B.sal IS NULL;
Warto zwrócić uwagę, że nie musieliśmy tu używać grupowania.
Trik 3: rankingi
Wiemy jak wypisać najbogatszego pracowika, ale jak wypisać np. trzech pierwszych? A uogólniając, załóżmy że tworzymy listę rankingową pracowników podług ich płac. Jak teraz przypisać każdemu pracownikowi jego pozycję w tym rankingu? Umiemy posortować wyświetlane wiersze w odpowiedniej kolejności i pierwszy pomysł wiąże się z konstrukcją ORDER BY i specjalną zmienną ROWNUM, która każdemu wierszowi wyniku przypisuje kolejny numer:
SELECT A.*, ROWNUM pozycja FROM (SELECT ename, sal FROM emp ORDER BY sal) A; # trochę źle!!
Co jednak się stanie jeżeli pracownicy będa zarabiali tyle samo? Konstrukcja ROWNUM niedeterministycznie przypisze któremuś z nich niższą pozycję! (Ten sam problem pojawiłby się nam gdybyśmy próbowali jej użyć w problemie "wiersz naj" powyżej). Wiedząc, że konstrukcja ROWNUM jest przereklamowana przypominamy sobie ponownie o złączeniu tabel i grupowaniu i piszemy coś takiego:
SELECT A.*, COUNT(B.sal)+1 pozycja FROM emp A LEFT JOIN emp B ON A.sal < B.sal GROUP BY A.empno;
Zapytanie to wygląda prosto, ale warto zwrócić uwagę na parę niuansów: każdy pracownik dostaje w nim grupę z osobami które zarabiają (ŚCIŚLE!!) więcej niż on. Najbogatszy pracownik dostanie sztuczną grupę z wierszem nullowy, ilość osób zliczamy zatem nie COUNT(*), a COUNT(B.sal). Alternatywnie można by też było dodać samego pracownika do wyszukiwanej grupy - unikniemy wtedy sztucznej grupy - jednak trzeba to zrobić ostrożnie i porównać klucz główny:
SELECT A.*, COUNT(*) FROM emp A LEFT JOIN emp B ON A.sal < B.sal OR A.empno = B.empno GROUP BY A.empno;
...a nie zmienić < na <=, bo przy równych pensjach dwaj najbogatsi pracownicy znaleźliby się na drugiej, a nie pierwszej pozycji (i pewnie ich by to nie ucieszyło).
Pobierz i wgraj bazę danych zwierzeta.sql.
Na wykładzie poznaliśmy jednak inny - bardziej zbliżony do datalogu - sposób pisania zapytań rekurencyjnych. Niestety, nie działa on w Oraclu :(. Aby go odpalić logujemy się do Postgresa:
> psql -h labbd bd logingdzie login to nasz login. (Powinien działać, jeżeli nie działa możemy korzystać ze wspólnego konta scott z hasłem tiger (nie mylić z hasłem "tiger tiger")). Teraz możemy pisać zapytania rekurencyjne. Ogólny format wygląda tak:
WITH RECURSIVE nazwa AS (
zapytanie inicjujące
UNION [ALL]
zapytanie poszerzające zbiór
)
zapytanie korzystające z nazwa
Działa to tak: zawartość tabeli nazwa jest najpierw wypełniana wierszami z zapytania inicjującego. Następnie korzystając z aktualnej wersji tabeli wyliczamy nowe wiersze i zastępujemy aktualną zawartość nimi. Wykonujemy te kroki aż tabela nazwa będzie pusta. Po zakończeniu procedury, tabela nazwa zawiera wiersze z zapytania inicjującego oraz wszystkie wygenerowane wiersze w tym procesie.
Prześledźmy ten proces na przykładzie zapytania liczącego sumę liczb od 1 do 100:
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
Najpierw do roboczej tabeli oraz do wyniku trafia wiersz z wartością 1. Następnie wyliczane jest zapytanie w oparciu o robocza tabelę: wynikiem jest wiersz (2). Wiersz idzie zatem do wyniku i zastępuje roboczą tabelę: ma ona zatem tylko wiersz (2). Ponownie wyliczamy i dostajemy (3). W rezultacie robocza tabela ma zawsze jeden wiersz. W końcu po setnym kroku zapytanie jest puste, a zatem na koniec wynikiem są wszystkie wygenerowane wiersze włącznie z inicjacją, czyli (1,2,3,...,100).
Jeżeli nie dodamy słowa ALL, wówczas na każdym kroku eliminowane będą duplikaty. Dokładniej - po wygenerowaniu nowych wierszy (także przy inicjacji) usuwamy duplikaty z tej tabeli, ale także wiersze które już wcześniej wypisaliśmy na wynik. Jest to dodatkowa robota, więc jeżeli zapytanie nie generuje duplikatów to lepiej użyć ALL.
Jeżeli chcemy wypisać wszystkie osoby za które odpowiada KING (czyli jego podwładnych i jego samego) możemy napisać coś takiego:
WITH RECURSIVE sub AS (
SELECT * FROM emp WHERE ename = 'KING'
UNION ALL
SELECT emp.* FROM emp, sub WHERE emp.mgr = sub.empno
)
SELECT * FROM sub;
W każdym kolejnym kroku dostajemy teraz kolejny poziom podwładnych.
Warto też wiedzieć, że jeżeli zapytanie nam się zapętla, to możemy do ostatniego zapytania dodać LIMIT (konstrukcja LIMIT 100 wypisuje maksymalnie 100 wierszy zapytania). Postres jest na tyle mądry, że będzie wyliczał tak długo nowe wiersze jak długo musi, np. poniższe zapytanie zwróci 10 wierszy z jedynką:
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n FROM t
)
SELECT * FROM t LIMIT 10;