1(c) Instytut Informatyki Politechniki Poznańskiej Język SQL. Rozdział 7.Zaawansowane mechanizmy w zapytaniachOgraniczanie rozmiaru zbioru wynikowego, klauzula WITH, rekurencja, zapytania hierarchiczne.

(1)

Język SQL. Rozdział 7.

Zaawansowane mechanizmy w zapytaniach

Ograniczanie rozmiaru zbioru wynikowego, klauzula WITH, rekurencja, zapytania

hierarchiczne.

(2)

Ograniczanie liczności zbioru wynikowego (1)

• Klauzula

FETCH FIRST <n> ROWS ONLY

^:

• ogranicza rozmiar zbioru wynikowego do n rekordów z początku zbioru,

• zastosowanie: zapytania „Top-N”,

• FIRST może zostać zastąpione przez NEXT^,ROWS ^przezROW^.

• Klauzula

FETCH FIRST <n> PERCENT ROWS ONLY

^:

• działa analogicznie, ale rozmiar zbioru wynikowego jest wyrażany jako procent zbioru rekordów, odczytanych przez zapytanie.

SELECT nazwisko, placa_pod FROM pracownicy ORDER BY placa_pod DESC

FETCH FIRST 3 ROWS ONLY;

(3)

Ograniczanie liczności zbioru wynikowego (2)

• Uwaga!

FETCH FIRST<n> ROWS ONLY

zwraca dokładnie n rekordów: jeśli na pozycjach n, n+1, ... są rekordy o tej samej wartości kryterium porządkującego zbiór, rekordy od pozycji n+1, ...

nie są zwracane.

• dotyczy to również FETCH FIRST<n> PERCENT ROWS ONLY^.

FETCH FIRST 8 ROWS ONLY;

NAZWISKO PLACA_POD --- --- WEGLARZ 1730 BLAZEWICZ 1350 SLOWINSKI 1070 BRZEZINSKI 960 MORZY 830 KROLIKOWSKI 645,5 KOSZLAJDA 590 KONOPKA 480 HAPKE 480 JEZIERSKI 439,7 MAREK 410,2 MATYSIAK 371 BIALY 250 ZAKRZEWICZ 208

NAZWISKO PLACA_POD --- --- WEGLARZ 1730 BLAZEWICZ 1350 SLOWINSKI 1070 BRZEZINSKI 960 MORZY 830 KROLIKOWSKI 645,5 KOSZLAJDA 590 KONOPKA 480

(4)

Ograniczanie liczności zbioru wynikowego (3)

• Problem rozwiązuje zastąpienie

ROWS ONLY

^klauzulą

ROWS WITH TIES

^.

FETCH FIRST 8 ROWS WITH TIES;

NAZWISKO PLACA_POD --- --- WEGLARZ 1730 BLAZEWICZ 1350 SLOWINSKI 1070 BRZEZINSKI 960 MORZY 830 KROLIKOWSKI 645,5 KOSZLAJDA 590 KONOPKA 480 HAPKE 480 JEZIERSKI 439,7 MAREK 410,2 MATYSIAK 371 BIALY 250 ZAKRZEWICZ 208

NAZWISKO PLACA_POD --- --- WEGLARZ 1730 BLAZEWICZ 1350 SLOWINSKI 1070 BRZEZINSKI 960 MORZY 830 KROLIKOWSKI 645,5 KOSZLAJDA 590 KONOPKA 480 HAPKE 480

(5)

Ograniczanie liczności zbioru wynikowego (4)

• Klauzula

OFFSET <n> ROWS

^:

• pomija zadaną liczbę rekordów od początku zbioru,

• przesunięcie musi być wyrażone liczbą rekordów.

• Możliwe jest połączenie obu klauzul:

SELECT nazwisko, placa_pod FROM pracownicy

ORDER BY placa_pod DESC OFFSET 3 ROWS;

SELECT nazwisko, placa_pod FROM pracownicy

ORDER BY placa_pod DESC OFFSET 3 ROWS

FETCH NEXT 5 ROWS WITH TIES;

(6)

Ograniczanie liczności zbioru wynikowego (5)

• Rozwiązanie charakterystyczne dla Oracle – wykorzystanie pseudokolumny ROWNUM.

• ROWNUM – numer rekordu w zbiorze wynikowym, przyznawany w momencie odczytu rekordu z dysku (ale przed sortowaniem!):

• Wykorzystanie w zapytaniu „Top-n”:

SELECT ROWNUM, nazwisko, etat, placa_pod FROM pracownicy;

SELECT ROWNUM, nazwisko, etat, placa_pod FROM pracownicy ORDER BY placa_pod DESC;

SELECT ROWNUMAS pozycja, T.nazwisko, T.etat, T.pensja FROM (SELECT nazwisko, etat, placa_pod AS pensja

FROM pracownicy

ORDER BY pensja DESC) T WHERE ROWNUM <= 3;

(7)

Adres rekordu

• Określa dokładnie lokalizację rekordu.

• Składa się z:

• numeru obiektu (tabeli lub perspektywy), do której należy rekord,

• numeru pliku,

• numeru bloku dyskowego wewnątrz pliku,

• numeru krotki wewnątrz bloku.

• Dostępny za pomocą pseudokolumny ROWID:

• Zastosowania:

• jednoznaczna identyfikacja rekordu w całej bazie danych,

• jako referencja do rekordu w poleceniu SQL.

SELECT ROWID, id_zesp FROM pracownicy WHERE nazwisko = 'HAPKE';

(8)

CTE (1)

• CTE – Common Table Expression – wspólne wyrażenie tablicowe.

• Konstrukcja ułatwiająca tworzenie skomplikowanych zapytań przez możliwość definicji tymczasowych zbiorów rekordów.

• Składnia:

• W definicji kolejnych zbiorów można odwołać się do zbiorów wcześniej zdefiniowanych.

WITH nazwa_zbioru_1[(struktura_zbioru_1)] AS (SELECT …),

nazwa_zbioru_2[(struktura_zbioru_2)] AS (SELECT …) …

SELECT … FROM nazwa_zbioru_1…;

(9)

CTE (2)

• Przykłady:

WITH prac_zespAS

(SELECT nazwa, nazwisko, etat,

placa_pod + NVL(placa_dod,0) AS placa FROM pracownicy JOIN zespoly USING (id_zesp)) SELECT * FROM prac_zesp

WHERE placa > 1200

ORDER BY nazwa, placa DESC;

WITH prac_zesp(nazwa, nazwisko, etat, placa) AS (SELECT nazwa, nazwisko, etat,

placa_pod + NVL(placa_dod,0)

FROM pracownicy JOIN zespoly USING (id_zesp)) SELECT * FROM prac_zesp

WHERE placa > 1200

ORDER BY nazwa, placa DESC;

(10)

CTE (3)

• Przykłady:

WITH profesorowieAS

(SELECT * FROM pracownicy WHERE etat = 'PROFESOR'), asystenciAS

(SELECT id_szefa FROM pracownicy WHERE etat = 'ASYSTENT') SELECT * FROM profesorowiepr

WHERE EXISTS

(SELECT 'a' FROM asystenci WHERE id_szefa = pr.id_prac) ORDER BY nazwisko;

WITH asystenciAS

(SELECT id_szefa FROM pracownicy WHERE etat = 'ASYSTENT'), profesorowieAS

(SELECT * FROM pracownicy p

WHERE etat = 'PROFESOR' AND EXISTS (SELECT 'a' FROM asystenci

WHERE id_szefa = p.id_prac)) SELECT * FROM profesorowie

(11)

CTE – zastosowania (1)

SELECT nazwa, SUM(placa_pod) AS suma_plac FROM pracownicy p JOIN zespoly USING (id_zesp) GROUP BY nazwa

HAVING 2 >=

(SELECT COUNT(SUM(placa_pod))

FROM pracownicy JOIN zespoly USING (id_zesp)

GROUP BY nazwa HAVING SUM(placa_pod) > SUM(p.placa_pod)) ORDER BY suma_plac DESC;

WITH

zespoly_stat AS

(SELECT nazwa, SUM(placa_pod) AS suma_plac FROM pracownicy JOIN zespoly USING (id_zesp) GROUP by nazwa)

SELECT * FROM zespoly_stat s WHERE 2 >=

(SELECT COUNT(*) FROM zespoly_stat

WHERE suma_plac > s.suma_plac) ORDER BY suma_plac DESC;

(12)

CTE – zastosowania (2)

WITH

zespoly_prac AS

(SELECT nazwisko, nazwa, id_zesp

FROM pracownicy JOIN zespoly USING (id_zesp)) SELECT nazwisko, nazwa

FROM zespoly_prac zp

WHERE 3 <= (SELECT COUNT(*) FROM pracownicy WHERE id_zesp = zp.id_zesp);

SELECT nazwisko, nazwa

FROM pracownicy JOIN zespoly USING (id_zesp) WHERE 3 <= (SELECT COUNT(*)

FROM pracownicy

WHERE id_zesp = ???);

SELECT nazwisko, nazwa FROM (SELECT nazwa, nazwisko, id_zesp

FROM pracownicy JOIN zespoly USING(id_zesp)) zp WHERE 3 <= (SELECT COUNT(*)

(13)

CTE – zastosowania (3)

WITH

obliczenia AS

(SELECT nazwisko, placa_pod,

placa_pod * 0.2 AS inwestycja,

placa_pod * 0.2 * power(1.1, 10) AS kapital FROM pracownicy)

SELECT nazwisko, placa_pod, inwestycja, kapital,

kapital - inwestycja AS zysk FROM obliczenia

ORDER BY nazwisko;

SELECT nazwisko, placa_pod,

placa_pod * 0.2 AS inwestycja,

placa_pod * 0.2 * power(1.1, 10) AS kapital,

placa_pod * 0.2 * power(1.1, 10) - placa_pod * 0.2 AS zysk FROM pracownicy

ORDER BY nazwisko;

(14)

Rekurencja

• Relacja ZNA:

• Problem: znajdź możliwości kontaktu przy wykorzystaniu relacji "osoba zna osobę".

• Reguły:

• kontakt(X,Y) <= zna(X,Y)

• kontakt(X,Y) <= zna(X,Z) i kontakt(Z,Y) WITH kontakt(kto, kogo) AS

(SELECT kto, kogo FROM zna

UNION ALL

SELECT z.kto, k.kogo

FROM zna z JOIN kontakt k ON z.kogo = k.kto) SELECT kto, kogo FROM kontakt;

KTO KOGO

Jaś Małgosia

Małgosia Piotrek

Jaś Adaś

Adaś Ola

Jaś Piotrek

KTO KOGO

Jaś Małgosia

Małgosia Piotrek

Jaś Adaś

Adaś Ola

(15)

Zapytania hierarchiczne

• Zapytania hierarchiczne pozwalają na rekurencję w relacjach zawierających dane hierarchiczne (np. szef – podwładny – podwładny podwładnego – …).

• Konieczna identyfikacja korzenia hierarchii (może być wiele korzeni – wiele hierarchii) oraz warunku wyszukującego rekordy-dzieci danego rekordu-rodzica.

• Realizacja w standardzie SQL – rekurencyjna klauzula WITH.

• Realizacja w Oracle:

• od Oracle11gR2 – rekurencyjna klauzula WITH,

• przed Oracle11gR2 – klauzule CONNECT BY i START WITH.

Węglarz100,

Błażewicz110, 220, Konopka

Brzeziński130,

Biały210,

Jezierski170,

Koszlajda160,

Królikowski150, 190, Matysiak Marek180,

(16)

Zapytania hierarchiczne w standardzie SQL

• Rekurencyjna klauzula WITH:

• musi wykonywać operację zbiorową UNION ALL,

WITH

podwladni (id_prac, id_szefa, nazwisko, poziom) AS -- definicja korzenia hierarchii

(SELECT id_prac, id_szefa, nazwisko, 1 FROM pracownicy

WHERE nazwisko = 'WEGLARZ' UNION ALL

-- rekurencyjna definicja niższych poziomów

SELECT p.id_prac, p.id_szefa, p.nazwisko, poziom+1

FROM podwladni s JOIN pracownicy p ON s.id_prac = p.id_szefa) -- wskazanie sposobu przeszukiwania hierarchii i sortowania rekordów-dzieci SEARCH DEPTH FIRST BY nazwisko SET porzadek_potomkow SELECT id_prac, id_szefa, nazwisko, poziom

FROM podwladni

ORDER BY porzadek_potomkow;

(17)

Zapytania hierarchiczne w Oracle

• Pseudokolumna LEVEL reprezentuje poziom rekurencji w drzewie hierarchii.

• Operator PRIOR służy do odwoływania się do rodzica danego węzła.

• Klauzula START WITH definiuje korzeń drzewa.

• Klauzula ORDER SIBLINGS BY określa sposób uporządkowania rekordów-dzieci.

SELECT id_prac, id_szefa, nazwisko, LEVEL AS poziom FROM pracownicy

CONNECT BY id_szefa = PRIOR id_prac START WITH nazwisko = 'WEGLARZ' ORDER SIBLINGS BY nazwisko;

SELECT id_prac, id_szefa, nazwisko, LEVEL AS poziom FROM pracownicy

CONNECT BY id_szefa = PRIOR id_prac START WITH etat = 'PROFESOR';