Rekurzívne dotazy

1. 1.12.2004 Rekurzívne dotazy Juraj Fečanin Aleš Plšek

2. Cíle 1.cíl Jak funguje rekurze v SQL? 2.cíl Zásady implementace

3. Obsah • Úvod • Konstrukce a průběh rekurzivních dotazů • Logické hierarchie a jejich vztah k rekurzi • Zastavení rekurzivního výpočtu • Příklady, pokročilejší techniky rekurze • Zásady implementace • Závěr

4. I. kapitola Úvod

5. Úvod • SQL příkaz – správnost výsledku, čitelnost a efektivnost • Rekurzivní SQL příkaz čitelnost a srozumitelnost • Proč tedy používat rekurzivní dotazy? • Někdy jediný efektivní způsob získání výsledku

6. Použití • Efektivní řešení problému na komplexních strukturách dat. • Vhodné při dotazech, kdy každý zpracovaný záznam bude součástí výsledku (Najdi všechny zaměstnance pracující pro Boba.) • Nebezpečné pro dotazy s nízkým počtem záznamů v odpovědi (Najdi pět nejrychlejších spojů z Bostonu do Dallasu.)

7. Další informace • Základní pravidlo: Vyloučit co největší počet záznamů jak nejrychleji to je možné • Vede ke snížení čitelnosti • Pozor u zacyklených dat! • Problémy s rekurzivními SQL příkazy • Při dodržení správných zásad mohou být efektivním a užitečným nástrojem

8. K čemu se rekurze používá? • Vytvoření náhodného vzorku dat (Sample data) • Výpis prvních n záznamů • Generování jednoduchých parserů • Zjištení hierchických vazeb mezi záznamy • Normalizace a denormalizace datových struktur

9. II. kapitola Konštrukcia a priebeh rekuzívnych dotazov

10. Rekurzia v SQL • Výraz je rekurzívny, ak používa sám seba v svojej definícii • Náhrada programu, ktorý by spracovával výsledky dotazov • Nebezpečenstvo zacyklenia

11. Nerekurzívne dotazy 1 • Tabuľka, na ktorej si vysvetlíme základné princípy rekurzívnych dotazov

12. Nerekuzívne dotazy 2 • Mená zamestnancov, ktorých nadriadený je pán Hoover a zarábajú viac ako $100.000 • Čo ak chceme vypísať mená všetkých Hooverových podriadených (nielen priamo), ktorí zarábajú viac ako $100.000

13. Pravidlá pre rekurzívne dotazy 1 • Definujeme výraz s použitím klauzule WITH • na jeho základe sa vytvorí dočasný pohľad • tento výraz musí byť definovaný ako zjednotenie dvoch oddelených častí (UNION ALL) • prvá časť - inicializačný poddotaz - bez rekurzie - vyhodnocovaný ako prvý • druhá časť - rekurzívny poddotaz - pridáva nové riadky do dočasného pohľadu - pozor na zarážku

14. Pravidlá pre rekurzívne dotazy 2 • Rekurzívny poddotaz • nemá obsahovať stĺpcové funkcie, SELECT DICTINCT, GROUP BY alebo HAVING • má obsahovať odkaz na výraz, v ktorom je zahrnutý sám, ale nie poddotaz nižšej úrovne • každý stĺpec rekurzívneho poddotazu musí byť kompatibilný s príslušným stĺpcom inicializačného poddotazu(cast)

15. Pravidlá pre rekurzívne dotazy 3 • Za klauzulou WITH musí byť definovaný dočasný pohľad

16. WITH adepti(meno,plat) AS ((SELECT meno,plat[inicializačný poddotaz] FROM zamestnanci WHERE nadriadeny=‘Hoover’) UNION ALL (SELECT z.meno,z.plat [rekurzívny poddotaz] FROM adepti AS a,zamestnanci AS z WHERE z.nadriadeny=a.meno)) SELECT meno[finálny dotaz] FROM adepti WHERE plat>100000; Príklad

17. Príklad - komentár • pri vyhodnocovaní dočasného pohľadu, databáza vidí iba riadky, ktoré boli pridané v predchádzajúcej iterácii • systém pokračuje v rekurzii, kým sa do pohľadu pridávajú riadky • potreba byť opatrný – nekonečný cyklus

18. Odlišný pohled • Dočasná tabulka jako fronta záznamů • Na začátku obsahuje záznamy získané z inicializačního dotazu • Postupně na každý záznam spušten iterační dotaz, výsledky se přidávají na začátek fronty • Proces končí při vyprázdnění fronty

19. Odlišný pohled - ilustrace AAA BBB CCC DDD EEE FFF GGG

20. Odlišný pohled - poznámky • Výstup rekurzivního dotazu může být použit v dalším rekurzivním výrazu (Najdi všechny státy v USA a pak všechny města v každém státě.) • Pozor u UNION ALL, nestačí jen UNION (s ohledem na duplicity) • Pro efektivnější zpracování dotazu se doporučuje vhodná indexace záznamů • Výstupem je dočasná tabulka – zachována platnost všech definovaných omezení pro dočasné tabulky

21. Možnosti použitia • ak je rekurzívny dotaz použitý vo vnútri výrazu CREATE VIEW, definuje sa rekurzívny pohľad • ak vo vnútri INSERT výrazu, je jeho výsledok vložený do cieľovej tabuľky • veľmi silná technika • napr. chceme vytvoriť tabuľku ČÍSLA so stĺpcami PORADOVÉ a NÁHODNÉ čísla od 1 do 1000 náhodné čísla od 1 do 1000

22. Príklad CREATE TABLE čísla(poradové Integer,náhodné Integer); INSERT INTO čísla(poradové,náhodné) WITH dočasné(n) AS (VALUES(1) [inicializačný poddotaz] UNION ALL SELECT n+1 FROM dočasné [rekurzívny poddotaz] WHERE n<1000) SELECT n,integer(rand()*1000) FROM dočasné;

23. III. kapitola Logické hierarchie a jejich vztah k rekurzi

24. Logické hierarchie • Logické hierarchie v reálném světě a jejich reprezentace v relačních databázích • Typy hierarchií • Divergentní • Konvergentní • Rekurzivní • Další rozeznávaná vlastnost • Vyvážená/Nevyvážená

25. Divergentní hierarchie AAA BBB CCC DDD EEE FFF GGG • Žádný objekt nemá více než jednoho předka • Libovolný počet potomků • Často obsahuje každá vrstva vždy jen jeden typ objektů • Např. geografická hierarchie = země,kraj,město,ulice • Implementace - stačí jedna tabulka

26. Konvergentí hierarchie • Libovolný počet předků a potomků • Reprezentují např. logické objekty, výrobky(popis součástek) • Implementace – dvě tabulky • Tabulka popisující objekty • Tabulka popisující vztahy mezi objekty AAA BBB CCC DDD EEE FFF GGG

27. Konvergentí hierarchie - příklad Objekty Vztahy AAA BBB CCC DDD EEE FFF GGG

28. Rekurzivníhierarchie • Libovolný počet předků • Objekt může být přímo či nepřímo svým předkem • V reálném světě je tato hierarchie téměř vždy špatná • Většinou nahrazuje konvergentní hierarchii tam, kde to přispěje ke zjednodušení • Realizace - jako u konvergentní hierarchie AAA BBB CCC DDD EEE FFF GGG

29. Rekurzivníhierarchie - příklad Objekty Vztahy AAA BBB CCC DDD EEE FFF GGG

30. Vyvážené/Nevyvážené hierarchie • Vyvážené hierarchie • úroveň = jednotná množina hodnot, stejná vzdálenost objektů od kořene • Většinou každá úroveň obsahuje jiné typy objektů (např. stát, město, ulice) • Nevyvážené hierarchie • Všechny objekty jednotného typu (např. společnost vlastnící další společnosti)

31. Datové/Ukazatelové hierarchie • Stejný design, různé použití • Ukazatelová hierarchie • V hlavních tabulkách uloženy jen data • Logické struktury a hierarchie definovány zvlášť • Použití např. v bankovních aplikacích

32. Datové hierarchie • Databáze popisuje vztahy mezi objekty (tabulky obsahující informace o všech součástech letadla) • Při rekurzivním zpracování je třeba být velmi opatrný (např. pro spočítání váhy objektů potřebujeme uvažovat jak počet podobjektů, tak jejich hmotnosti)

33. IV. kapitola Zastavení rekurzivního výpočtu

34. Zastavení rekurzivního výpočtu • Jak zjistit, kdy se má výpočet ukončit? • Prevence zacyklení • Techniky zastavení výpočtu: • Skončit po průchodu určitého počtu úrovní • Nevracet se na už jednou navštívená místa

35. Rekurzivní data • Uvažujme strukturu dat AAA BBB CCC DDD EEE FFF GGG

36. Zastavení po průchodu n úrovněmi WITH Parent(ckey,lvl) AS ( (SELECT DISTINCT pkey, 0 FROM TROUBLE WHERE pkey = ‘AAA’) UNION ALL (SELECT C.ckey,P.lvl + 1 FROM Trouble C, Parent P WHERE P.ckey = c.pkey AND P.lvl +1 < 4 ) ) SELECT ckey, lvl FROM Parent;

37. Stop na n-té úrovní, výpis cest WITH Parent(ckey,lvl,path,loc) AS ((SELECT DISTINCT pkey, 0 , VARCHAR (pkey,20) , 0 FROM TROUBLE WHERE pkey = ‘AAA’) UNION ALL (SELECT C.ckey,P.lvl + 1 ,P.path|| ’>’ ||C.ckey , LOCATE (C.ckey|| ’>’ ||P.path) FROM Trouble C , Parent P WHERE P.ckey = c.pkey AND P.lvl +1 < 4 )) SELECT ckey, lvl, path, loc FROM Parent;

38. Najdi všechny potomky WITH Parent(ckey,lvl,path,loc) AS ( ( SELECT DISTINCT pkey, 0 , VARCHAR (pkey,20) FROM TROUBLE WHERE pkey = ‘AAA’) UNION ALL ( SELECT C.ckey,P.lvl + 1 , P.path|| ’>’ ||C.ckey FROM Trouble C , Parent P WHERE P.ckey = c.pkey AND LOCATE (C.ckey|| ’>’ ||P.path) = 0 ) ) SELECT ckey, lvl, path FROM Parent;

39. Zastavení při nalezení cyklu WITH Parent(ckey,lvl,path) AS ( ( SELECT DISTINCT pkey, 0 , VARCHAR (pkey,20) FROM TROUBLE WHERE pkey = ‘AAA’) UNION ALL ( SELECTCASE WHENLOCATE (C.ckey||’>’, P.path) > 0 THEN RAISE_ERROR(‘70001’,’Error: Loop in database found’) ELSE C.CKEY END ,P.lvl + 1 , P.path|| ’>’ ||C.ckey FROM Trouble C , Parent P WHERE P.ckey = c.pkey ) SELECT ckey, lvl, path FROM Parent; )

40. V. kapitola Príklady Problém priechodu častí, Rekurzívne vyhledávánie, CAST výraz,…

41. Problém priechodu častí 1 • výroba lietadiel • tabuľka všetkých častí, použitých v istom type lietadla, a ich komponent krídlo 1 5 1 výstuha kormidlo podvoz. 100 2 5 3 10 8 čáp 4 nit Orientovaný acyklický graf

42. Problém priechodu častí 2 Aký je počet nitov použitých na jednom krídle? • problém: Musíme uvažovať aj počet častí obsahujúcich nity. • použijeme tie isté pravidlá ako v predchádzajúcich príkladoch • dočasný pohľad: ČASTI_KRÍDLA • riadok obsahuje komponentu a počet komponent pre istú časť krídla • inicializačný poddotaz vyberie časti, použité priamo pri kompletizovaní krídla • rekurzívny poddotaz vyberá časti na nižších úrovniach kompletizácie • acyklický graf rekurzia sa zastaví

43. Realizácia WITH časti_krídla(komponenta,počet) AS ((SELECT komponenta,počet [inicializačný poddotaz] FROM komponenty WHERE časť=‘krídlo’) UNION ALL (SELECT k.komponenta,č.počet*k.počet [rekurzívny poddotaz] FROM časti_krídla č,komponenty k WHERE č.komponenta=k.časť)) SELECT sum(počet) AS počet FROM časti_krídla WHERE komponenta=‘nit’; Výsledok:

44. Realizácia 2 WITH časti_krídla(komponenta,počet) AS ((SELECT komponenta,počet FROM komponenty WHERE časť=‘krídlo’) UNION ALL (SELECT k.komponenta,č.počet*k.počet FROM časti_krídla č,komponenty k WHERE č.komponenta=k.časť)) SELECT komponenta,sum(počet) AS počet FROM časti_krídla GROUP BY komponenta; Výsledok:

45. Rekurzívne vyhľadávanie Nájdi najlacnejšiu trasu zo San Fracisca do New Yorku. POZOR!!! Graf nie je acyklický. Letová mapa 275 Chicago 250 San Francisco New York 300 50 100 225 Los Angeles Dallas 200

46. San Francisco – New York Na úvod skúsme vyhľadať cesty zo San Francisca do New Yorku. • inicializačný poddotaz zistí všetky mestá, do ktorých sa dá doletieť zo San Francisca jediným letom • rekurzívny poddotaz nájde všetky mestá, ktoré sú dosažitelné z pôvodných • pre každé dosiahnuté mesto sa zaznamená trasa a celková cena • nakoniec sa zo získaných ciest vyberú tie, ktoré končia v New Yorku

47. WITH cesty(do,trasa,cena) AS ((SELECT do,do,cena FROM lety WHERE z=‘San Francisco’) UNION ALL (SELECT l.do, c.trasa ||’,’|| l.do, c.cena+l.cena FROM cesty c,lety l WHERE c.do=l.z)) SELECT trasa,cena FROM cesty WHERE do=‘New York’; Realizácia

48. WITH cesty(do,trasa,cena) AS ((SELECT do,do,cena FROM lety WHERE z=‘San Francisco’) UNION ALL (SELECT l.do, c.trasa ||’,’|| l.do, c.cena+l.cena FROM cesty c,lety l WHERE c.do=l.z)) SELECT trasa,cena FROM cesty WHERE do=‘New York’; Dva problémy: do stĺpca trasa vkladáme stále dlhší a dlhší reťazec (musíme systému nejakým spôsobom oznámiť jeho maximálnu dĺžku) dotaz neskončí, kým systému nedôjdu prostriedky Realizácia

49. CAST výraz • zmena hodnoty z jedného dátového typu na iný • definované v SQL92 • cieľový dátový typ musí byť dobre definovaný (veľkosť, rozsah, presnosť – ak je def.) • nastavené hodnoty sú Decimal(5,0), Char(1), Graphic(1) • ostatné typy bez hodnôt • nemožnosť konverzie • pri nezhode veľkostí CAST ( výraz AS typ ) NULL CAST (c1+c2 AS Decimal(8,2)) CAST (meno || adresa AS Varchar(255)) CHYBA zaokrúhlenie

50. Riešenie prvého problému • systém od nás potrebuje informáciu o tom, kam až môže narásť dĺžka položky • môžme určiť, že dĺžka trasy môže byť obmedzená na 100 znakov (čo je dosť miesta pre trasy, ktoré nás zaujímajú) • pomocou pretypovania stĺpca na Varchar(100) v inicializačnom aj rekurzívnom poddotaze sa s týmto problémom vysporiadame • zmeny: • inicializačný poddotaz: CAST(do AS Varchar(100)) • rekurzívny poddotaz: CAST(c.trasa ||’,’|| l.do AS Varchar(100))