Rekurzivní dotazy v SQL

1. Rekurzivní dotazy v SQL Martin ČermákTomáš DvořákAlena Rybičková

2. Úvod • SQL příkaz • snaha o čitelnost, srozumitelnost • rekurzivní SQL • dotaz je rekurzivní, pokud je použit ve své vlastní definici • hůře čitelné i srozumitelné dotazy • často jediný efektivní způsob získání výsledku • bez rekurze je potřeba v hostitelském programu mít funkci, která zpracovává výsledky z dílčích dotazů • výhodné pro hledání vztahů ve stromové struktuře • lze použít pro acyklické i cyklické grafy

3. Syntaxe rekurzivního dotazu • WITH [RECURSIVE] <query_alias_name> [ ( <column_list> ) ]AS ( <select_query> )<query_using_query_alias_name> • vše podstatné je uvnitř <select_query>

4. Použití klauzule WITH • použitím klauzule WITH vzniká tzv. Common Table Expression (CTE) • CTE je dočasný pohled (temporary view) • požití CTE • ve složitých dotazech, kde je nějaký poddotaz použit alespoň dvakrát • v rekurzivních dotazech

5. Jednoduchý příklad • Zamestnanec(Jmeno, Plat, Vedouci) • hledáme zaměstnance, kteří mají plat alespoň 100.000 a jejichž přímý nadřizený je ‘Hoover’ SELECT Jmeno, PlatFROMZamestnanecWHEREVedouci = ‘Hoover’ AND Plat > 100000

6. Jednoduchý příklad – rekurze • hledáme-li všechny zaměstnance, jejichž nadřízený (nemusí být přímý) je ‘Hoover’ • potřebujeme rekurzivní dotaz • použijeme klauzuli WITH definující “Common Table Expression” (CTE) • obsahuje dvě části spojené klauzulí UNION ALL • inicializační poddotaz • bude zpracován jako první, neovlivňuje rekurzi • v našem příkladě vyhledá Hooverovy přímé podřízené • rekurzivní poddotaz • přidává další záznamy k dočasnému pohledu (v závislosti na dříve nalezených) • v našem příkladě zde budou přidáni zaměstnanci, jejich přímý nadřízený již byl přidán do dočasného pohledu

7. Jednoduchý příklad – rekurze WITH Adept (Jmeno, Plat) AS (( SELECT Jmeno, Plat [inicializační poddotaz] FROM Zamestnanec WHERE Vedouci = ‘Hoover’) UNION ALL ( SELECT Z.Jmeno, Z.Plat [rekurzívní poddotaz] FROM Adept AS A, Zamestnanec AS Z WHERE Z.Vedouci = A.Jmeno )) SELECT Jmeno [finální dotaz] FROM Adepti WHERE Plat >100000;

8. Pravidla rekurzivního poddotazu • nesmí obsahovat • sloupcové opreace • SELECT DISTINCT • GROUP BY • HAVING • může obsahovat odkaz na výraz ve kterém je sám definovaný, ale ne poddotaz nižší úrovně • každý sloupec rekurzivního poddotazu musí být typově kompatibilní s příslušným sloupcem v inicializačním poddotazu • používá se přetypování – CAST

9. Složitější dotaz – nerekurzivní • News(ID, Forum, Question) • Hledáme fórum s nevyšším počtem příspěvků • SELECT COUNT(ID) AS Nbr, ForumFROMNewsGROUPBYForumHAVINGCOUNT(ID) = (SELECT MAX(Nbr)FROM ( SELECTCOUNT(ID) AS Nbr, Forum FROMNewsGROUP BYForum ) • Hledáme vlastně MAX(COUNT(...))

10. Příklad na použití klauzule WITH • News(ID, Forum, Question) • WITH Q_count_news (Nbr, Forum)AS ( SELECT COUNT(ID), Forum)FROM NewsGROUPBY Forum )SELECT Nbr, ForumFROM Q_count_newsWHERE Nbr = (SELECT MAX(Nbr)FROM Q_count_news)

11. Poznámky k příkladu • dočasný pohled Q_count_news používáme pro zjednodušení zápisu SQL dotazu • CTE (podobně jako pohled) musí mít název • uvnitř CTE mohou být sloupce přejmenované

12. Použití více CTE v jednom dotazu • WITH Q_count_news (Nbr, Forum)AS ( SELECT COUNT(ID), ForumFROM NewsGROUPBY Forum ), Q_max_count_news (Nbr)AS ( SELECT MAX(Nbr)FROM Q_count_news )SELECT T1.*FROM Q_count_news T1 INNERJOIN Q_max_count_news T2 ON T1.Nbr = T2.Nbr

13. Rekurze v SQL • rekurzivní dotaz má dvě části • první část říká jak se má začít – bez rekurze • druhá část říká jak má vypadat další krok • obě části jsou spojeny pomocí klauzule UNION ALL • rekurzivní dotaz vzniká použitím názvu CTE uvnitř druhé (rekurzivní) části dotazu • je třeba definovat podmínky, za kterých je rekurze ukončena

14. Rekurze s výpočtem

15. Rekurze s výpočtem (2)

16. Rekurze s výpočtem (3) • acyklický graf • směr šipky říká z čeho je daný díl sestaven • hodnoty u šipek říkají kolik daných součástek je použito u jednoho dílu • každá řádka v tabulce je reprezentována šipkou

17. Rekurze s výpočtem (4) • otázka: Kolik nýtů je použito při výrobě křídla? • výpočet vyžaduje rekurzivní průchod grafem • musíme sečíst nýty použité v jednotlivých součástech křídla • u jednotlivých součástek musíme brát v úvahu jejich počet • dotaz bude obsahovat obvyklé části • inicializační poddotaz • rekurzivní poddotaz • finální dotaz

18. Rekurze s výpočtem – SQL dotaz WITH wingparts(subquery, qty)AS (( SELECT subpart, qty [inicializační poddotaz]FROM componentsWHERE part = ‘křídlo’ )UNION ALL ( SELECT c.subpart, w.qty * c.qty [rekurzivní poddotaz]FROM wingparts w, components cWHERE w.subpart = c.part ));

19. Rekurze s výpočtem – průběh dotazu

20. Rekurze s výpočtem – celý dotaz WITH wingparts(subquery, qty)AS(( SELECT subpart, qty [inicializační poddotaz]FROM componentsWHERE part = ‘křídlo’ )UNIONALL ( SELECT c.subpart, w.qty * c.qty [rekurzivní poddotaz]FROM wingparts w, components cWHERE w.subpart = c.part ))SELECT sum(qty) AS qty [finální dotaz]FROM wingpartsWHERE subpart = ‘nýt’; Výsledek: qty = 183

21. Databázové servery podporující rekurzivní dotazy • MS SQL Server 2005 • IBM DB2 v7.2 • Oracle 9i • podoruje jen procházení ve stromě – omezená syntaxe • nepodporuje rekurzivní dotazy • klauzule START WITH, CONNECT BY • ...

22. Syntaxe průchodu stromů v Oracle 9i • SELECTsloupceFROMtabulka[WHEREpodmínka3]START WITH podmínka1CONNECT BYpodmínka2[ORDER BY…] • Řádky vyhovující podmínce ve START WITH jsou považovány za kořenové řádky na první úrovni vnoření • Pro každou řádku na úrovni i se rekurzivně hledají přímí potomci vyhovující podmínce v klauzuli CONNECT BY na úrovni i+1 • Řádka předka se v podmínce označuje klíčovým slovem PRIOR

23. Syntaxe průchodu stromů v Oracle 9i • SELECTsloupceFROMtabulka[WHEREpodmínka3]START WITH podmínka1CONNECT BYpodmínka2[ORDER BY…] • Na závěr jsou odstraněny řádky nevyhovující podmínce ve WHERE • Pokud není definováno třídění, odpovídá pořadí průchodu pre-order • Každý řádek obsahuje pseudo-sloupec LEVEL, obsahující úroveň řádku v hierarchii

24. Oracle 9i vs. ISO 1999 tabulka zaměstnanců: Emp(EmpNo, Name, Manager) • Oarcle 9i: SELECT LPAD(’ ’,2*Level)|| Name Jmeno, LevelFROM EmpSTART WITH Manager IS NULLCONNECT BY Manager = PRIOR EmpNo; • ISO: WITH Emp AS (SELECT EName AS Jmeno, 0 ASLevel FROM Emp x WHEREManager IS NULLUNION ALLSELECTEName, Level+1FROMEmp y JOIN Emp ON y.Manager = Emp.EmpNo)SELECT * FROM Emp;

25. SQL1999 a SQL Server 2005 Tomáš Dvořák

26. Syntaxe WITH [ RECURSIVE ] <query_alias_name> [ ( <column_list> ) ] AS ( <select_query> ) <query_using_query_alias_name> • MS SQL Server 2005 zatím nepodporuje klíčové slovo RECURSIVE

27. Stromová struktura

28. Stromová struktura (2)

29. Stromová struktura – předchůdci ‘Motorcycle’ • chceme zjistit všechny předchůdce „Motorcycle“ • začneme řádkou obsahující „Motorcycle“ SELECT Name, FatherIDFROM VehicleWHERE Name = ‘Motorcycle’ • dotaz provádějící další krok bude vypadat následovně: SELECT Name, FatherIDFROM Vehicle

30. Stromová struktura – předchůdci ‘Motorcycle’ (2) • oba předchozí dotazy spojíme pomocí klauzule UNION ALL WITHtree (date, id)AS ( SELECT Name, FatherIDFROM VehicleWHERE Name = ‘Motorcycle’UNIONALLSELECT Name, FatherIDFROM Vehicle )

31. Stromová struktura – předchůdci ‘Motorcycle’ (3) • posledním krokem k rekurzi je vytvoření cyklu WITHtree (date, id)AS ( SELECT Name, FatherIDFROM VehicleWHERE Name = ‘Motorcycle’UNIONALLSELECT Name, FatherIDFROM VehicleV INNERJOIN tree t ON t.id = V.ID )SELECT *FROM tree

32. Stromová struktura – předchůdci ‘Motorcycle’ (4) • Výsledek našeho dotazu tedy je:

33. Předchůdci bez rekurze (1) • Dá se rekurze odstranit? ANO, pomocí zásobníku. • Do tabulky přidáme 2 nové sloupečky: RIGHTBOUND a LEFTBOUND • Joe Celko: „SQL for smarties“ kapitola „Trees and Hierarchies“

34. Předchůdci bez rekurze (2) • Tabulku naplníme daty, pro nové sloupečky UPDATE VEHICLES SET LEFTBOUND = 1 , RIGHTBOUND = 26 WHERE ID = 1 UPDATE VEHICLES SET LEFTBOUND = 2 , RIGHTBOUND = 7 WHERE ID = 2 … UPDATE VEHICLES SET LEFTBOUND = 12 , RIGHTBOUND = 13 WHERE ID = 12 UPDATE VEHICLES SET LEFTBOUND = 14 , RIGHTBOUND = 14 WHERE ID = 13

35. Předchůdci - bez rekurze (3)

36. Předchůdci - bez rekurze (4) • Dotaz na předchůdce MOTORCYCLE využijeintervalů a bude vypadat: SELECT * FROM Vehicles WHERE RightBound > 12 AND LeftBound < 13

37. Zobrazení stromu (1) • Někdy můžeme chtít zobrazit data v tabulce jako strom WITH tree (data, id, level, pathstr) AS (SELECT NAME, ID, 0, CAST('' AS VARCHAR(MAX)) FROM VEHICLE WHERE ID_FATHER IS NULL UNION ALL SELECT NAME, ID, t.level + 1, t.pathstr +’>’+ V.NAME FROM VEHICLE V INNER JOIN tree t ON t.id = V.ID_FATHER) SELECT SPACE(level) + data as data, id, level, pathstr FROM tree ORDER BY pathstr, id

38. Zobrazení stromu (2)

39. Zobrazení – bez rekurze (1) • Do tabulky potřebujeme přidat sloupeček LEVEL, který nám označuje úroveň uzlu • Spočítáme ji při vkládání uzlu UPDATE VEHICLES SET LEVEL = 0 WHERE ID = 1 UPDATE VEHICLES SET LEVEL = 1 WHERE ID = 2 … UPDATE VEHICLES SET LEVEL = 0 WHERE ID = 13 UPDATE VEHICLES SET LEVEL = 1 WHERE ID = 14

40. Zobrazení – bez rekurze (2) SELECT SPACE(level)+ name AS data FROM Vehicle ORDER BY LEFT_BOUND

41. Mazání tabulek (1) • Cíl: smazat tabulku • Problém: tabulky jsou provázány integritními omezeními (FOREIGN KEY apod.) • Co chceme: posloupnost jak máme mazat tabulky, abychom nakonec mohli smazat, tu kterou chceme • Jak: pomocí rekurze projdeme tabulky, na kterých je integritní omezení

42. Mazání tabulek (1) WITH T_CONTRAINTES (table_name, father_table_name) AS ( SELECT DISTINCT CTU.TABLE_NAME, TCT.TABLE_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RFC INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU ON RFC.CONSTRAINT_CATALOG = CTU.CONSTRAINT_CATALOG AND RFC.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA AND RFC.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME

43. Mazání tabulek (2) INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT ON RFC.UNIQUE_CONSTRAINT_CATALOG = TCT.CONSTRAINT_CATALOG AND RFC.UNIQUE_CONSTRAINT_SCHEMA = TCT.CONSTRAINT_SCHEMA AND RFC.UNIQUE_CONSTRAINT_NAME = TCT.CONSTRAINT_NAME WHERE CTU.TABLE_CATALOG = @DB AND CTU.TABLE_SCHEMA = @USR) ,

44. Mazání tabulek (3) T_TREE_CONTRAINTES (table_to_delete, level) AS ( SELECT DISTINCT table_name, 0 FROM T_CONTRAINTES WHERE father_table_name = @TABLE_TO_DELETE UNION ALL SELECT priorT.table_name, level - 1 FROM T_CONTRAINTES priorT INNER JOIN T_TREE_CONTRAINTES beginT ON beginT.table_to_delete = priorT.father_table_name WHERE priorT.father_table_name<>priorT.table_name)

45. Mazání tabulek (4) SELECT DISTINCT * FROM T_TREE_CONTRAINTES ORDER BY level

46. MS Server 2005 • Počet rekurzivních volání je omezen na 100 • Dá se ovlivnit nastavením OPTION (MAXRECURSION n) • Beta verze zatím nepodporuje klíčové slovo RECURSION

47. Příklad – Hledání nejlepšího řešení Alena Rybičková

48. San Francisco – New York Flights

49. San Francisco – New York • hledáme jak se nejlevněji dostat ze San Francisca do New Yorku • data obsahují cykly, musíme vyřešit abychom nelétali pořád dokola

50. Rekurzivní dotaz • dočasný pohled nazvaný TRIPS tvoří UNION ALL mezi • inicializačním poddotazem, který najde všechna města, do kterých se dá dostat ze SF na jeden let • rekurzivním poddotazem, který najde najde všechna města, kam se lze dostat z již nalezených měst