Rekurzivn

1
Rekurzivní dotazy v SQL

• Martin CermákTomáš DvorákAlena Rybicková

2
Úvod

• SQL príkaz
• snaha o citelnost, srozumitelnost
• rekurzivní SQL
• dotaz je rekurzivní, pokud je použit ve své
  vlastní definici
• hure citelné i srozumitelné dotazy
• casto jediný efektivní zpusob získání výsledku
• bez rekurze je potreba v hostitelském programu
  mít funkci, která zpracovává výsledky z dílcích
  dotazu
• výhodné pro hledání vztahu ve stromové strukture
• lze použít pro acyklické i cyklické grafy

3
Syntaxe rekurzivního dotazu

• WITH RECURSIVE ltquery_alias_namegt (
  ltcolumn_listgt ) AS ( ltselect_querygt
  )ltquery_using_query_alias_namegt
• vše podstatné je uvnitr ltselect_querygt

4
Použití klauzule WITH

• použitím klauzule WITH vzniká tzv. Common Table
  Expression (CTE)
• CTE je docasný pohled (temporary view)
• požití CTE
• ve složitých dotazech, kde je nejaký poddotaz
  použit alespon dvakrát
• v rekurzivních dotazech

5
Jednoduchý príklad

• Zamestnanec(Jmeno, Plat, Vedouci)
• hledáme zamestnance, kterí mají plat alespon
  100.000 a jejichž prímý nadrizený je Hoover
• SELECT Jmeno, PlatFROM ZamestnanecWHERE
  Vedouci Hoover AND Plat gt 100000

6
Jednoduchý príklad rekurze

• hledáme-li všechny zamestnance, jejichž
  nadrízený (nemusí být prímý) je Hoover
• potrebujeme rekurzivní dotaz
• použijeme klauzuli WITH definující Common Table
  Expression (CTE)
• obsahuje dve cásti spojené klauzulí UNION ALL
• inicializacní poddotaz
• bude zpracován jako první, neovlivnuje rekurzi
• v našem príklade vyhledá Hooverovy prímé
  podrízené
• rekurzivní poddotaz
• pridává další záznamy k docasnému pohledu (v
  závislosti na dríve nalezených)
• v našem príklade zde budou pridáni zamestnanci,
  jejich prímý nadrízený již byl pridán do
  docasného pohledu

7
Jednoduchý príklad rekurze

• WITH Adept (Jmeno, Plat)
• AS
• (( SELECT Jmeno, Plat inicializacní 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 gt 100000

8
Pravidla rekurzivního poddotazu

• nesmí obsahovat
• sloupcové opreace
• SELECT DISTINCT
• GROUP BY
• HAVING
• muže obsahovat odkaz na výraz ve kterém je sám
  definovaný, ale ne poddotaz nižší úrovne
• každý sloupec rekurzivního poddotazu musí být
  typove kompatibilní s príslušným sloupcem v
  inicializacním poddotazu
• používá se pretypování CAST

9
Složitejší dotaz nerekurzivní

• News(ID, Forum, Question)
• Hledáme fórum s nevyšším poctem príspevku
• SELECT COUNT(ID) AS Nbr, ForumFROM NewsGROUP
  BY ForumHAVING COUNT(ID) ( SELECT
  MAX(Nbr) FROM ( SELECT COUNT(ID) AS Nbr,
  Forum FROM News GROUP BY Forum )
• Hledáme vlastne MAX(COUNT(...))

10
Príklad na použití klauzule WITH

• News(ID, Forum, Question)
• WITH Q_count_news (Nbr, Forum) AS (
  SELECT COUNT(ID), Forum) FROM News
  GROUP BY Forum )SELECT Nbr, ForumFROM
  Q_count_newsWHERE Nbr (SELECT MAX(Nbr)
  FROM Q_count_news)

11
Poznámky k príkladu

• docasný pohled Q_count_news používáme pro
  zjednodušení zápisu SQL dotazu
• CTE (podobne jako pohled) musí mít název
• uvnitr CTE mohou být sloupce prejmenované

12
Použití více CTE v jednom dotazu

• WITH Q_count_news (Nbr, Forum) AS (
  SELECT COUNT(ID), Forum FROM News
  GROUP BY Forum ), Q_max_count_news (Nbr)
  AS ( SELECT MAX(Nbr) FROM
  Q_count_news )SELECT T1.FROM Q_count_news T1
  INNER JOIN Q_max_count_news T2 ON
  T1.Nbr T2.Nbr

13
Rekurze v SQL

• rekurzivní dotaz má dve cásti
• první cást ríká jak se má zacít bez rekurze
• druhá cást ríká jak má vypadat další krok
• obe cásti jsou spojeny pomocí klauzule UNION ALL
• rekurzivní dotaz vzniká použitím názvu CTE uvnitr
  druhé (rekurzivní) cásti dotazu
• je treba definovat podmínky, za kterých je
  rekurze ukoncena

14
Rekurze s výpoctem
15
Rekurze s výpoctem (2)
Part Subpart Qty
krídlo vzpera 5
krídlo kridélko 1
krídlo podvozek 1
krídlo nýt 100
vzpera nýt 10
kridélko pant 2
kridélko nýt 5
podvozek pant 3
podvozek nýt 8
pant nýt 4
16
Rekurze s výpoctem (3)

• acyklický graf
• smer šipky ríká z ceho je daný díl sestaven
• hodnoty u šipek ríkají kolik daných soucástek je
  použito u jednoho dílu
• každá rádka v tabulce je reprezentována šipkou

17
Rekurze s výpoctem (4)

• otázka Kolik nýtu je použito pri výrobe krídla?
• výpocet vyžaduje rekurzivní pruchod grafem
• musíme secíst nýty použité v jednotlivých
  soucástech krídla
• u jednotlivých soucástek musíme brát v úvahu
  jejich pocet
• dotaz bude obsahovat obvyklé cásti
• inicializacní poddotaz
• rekurzivní poddotaz
• finální dotaz

18
Rekurze s výpoctem SQL dotaz

• WITH wingparts(subquery, qty) AS (( SELECT
  subpart, qty inicializacní poddotaz FROM
  components WHERE part krídlo )
  UNION ALL ( SELECT c.subpart, w.qty
  c.qty rekurzivní poddotaz FROM wingparts
  w, components c WHERE w.subpart c.part ))

19
Rekurze s výpoctem prubeh dotazu
Subpart Qty
vzpera 5 prímé použití
kridélko 1 prímé použití
podvozek 1 prímé použití
nýt 100 prímé použití
nýt 50 z vzpery
pant 2 z kridélka
nýt 5 z kridélka
pant 3 z podvozku
nýt 8 z podvozku
nýt 8 z pantu kridélka
nýt 12 z pantu podvozku
20
Rekurze s výpoctem celý dotaz

• WITH wingparts(subquery, qty) AS (( SELECT
  subpart, qty inicializacní poddotaz FROM
  components WHERE part krídlo )
  UNION ALL ( SELECT c.subpart, w.qty
  c.qty rekurzivní poddotaz FROM wingparts
  w, components c WHERE w.subpart c.part
  ))SELECT sum(qty) AS qty finální dotazFROM
  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 strome omezená
  syntaxe
• nepodporuje rekurzivní dotazy
• klauzule START WITH, CONNECT BY
• ...

22
Syntaxe pruchodu stromu v Oracle 9i

• SELECT sloupce FROM tabulka WHERE
  podmínka3START WITH podmínka1CONNECT BY
  podmínka2ORDER BY
• Rádky vyhovující podmínce ve START WITH jsou
  považovány za korenové rádky na první úrovni
  vnorení
• Pro každou rádku na úrovni i se rekurzivne
  hledají prímí potomci vyhovující podmínce v
  klauzuli CONNECT BY na úrovni i1
• Rádka predka se v podmínce oznacuje klícovým
  slovem PRIOR

23
Syntaxe pruchodu stromu v Oracle 9i

• SELECT sloupce FROM tabulka WHERE
  podmínka3START WITH podmínka1CONNECT BY
  podmínka2ORDER BY
• Na záver jsou odstraneny rádky nevyhovující
  podmínce ve WHERE
• Pokud není definováno trídení, odpovídá poradí
  pruchodu pre-order
• Každý rádek obsahuje pseudo-sloupec LEVEL,
  obsahující úroven rádku v hierarchii

24
Oracle 9i vs. ISO 1999

• tabulka zamestnancu Emp(EmpNo, Name, Manager)
• Oarcle 9i
• SELECT LPAD( , 2Level) Name Jmeno,
  LevelFROM EmpSTART WITH Manager IS NULLCONNECT
  BY Manager PRIOR EmpNo
• ISO
• WITH Emp AS ( SELECT EName AS Jmeno, 0 AS
  Level FROM Emp x WHERE Manager IS NULL
  UNION ALL SELECT EName, Level1 FROM
  Emp y JOIN Emp ON y.Manager Emp.EmpNo)SELECT
  FROM Emp

25
SQL1999 a SQL Server 2005

• Tomáš Dvorák

26
Syntaxe

• WITH RECURSIVE ltquery_alias_namegt (
  ltcolumn_listgt ) AS ( ltselect_querygt )
  ltquery_using_query_alias_namegt
• MS SQL Server 2005 zatím nepodporuje klícové
  slovo RECURSIVE

27
Stromová struktura
Id FatherID Name
1 NULL ALL
2 1 SEA
3 1 EARTH
4 1 AIR
5 2 SUBMARINE
6 2 BOAT
7 3 CAR
Id FatherID Name
8 3 TWO WHEELES
9 3 TRUCK
10 4 ROCKET
11 4 PLANE
12 8 MOTORCYCLE
13 8 BICYCLE
28
Stromová struktura (2)
29
Stromová struktura predchudci Motorcycle

• chceme zjistit všechny predchudce Motorcycle
• zacneme rádkou obsahující Motorcycle
• SELECT Name, FatherIDFROM VehicleWHERE
  Name Motorcycle
• dotaz provádející další krok bude vypadat
  následovne
• SELECT Name, FatherIDFROM Vehicle

30
Stromová struktura predchudci Motorcycle (2)

• oba predchozí dotazy spojíme pomocí klauzule
  UNION ALL
• WITH tree (date, id) AS ( SELECT Name,
  FatherID FROM Vehicle
  WHERE Name Motorcycle UNION ALL
  SELECT Name, FatherID FROM
  Vehicle )

31
Stromová struktura predchudci Motorcycle (3)

• posledním krokem k rekurzi je vytvorení cyklu
• WITH tree (date, id) AS ( SELECT Name,
  FatherID FROM Vehicle
  WHERE Name Motorcycle UNION ALL
  SELECT Name, FatherID FROM
  Vehicle V INNER JOIN
  tree t ON t.id V.ID )SELECT FROM tree

32
Stromová struktura predchudci Motorcycle (4)

• Výsledek našeho dotazu tedy je

Data Id
MOTORCYCLE 8
TWO WHEELES 3
EARTH 1
ALL NULL
33
Predchudci bez rekurze (1)

• Dá se rekurze odstranit? ANO, pomocí zásobníku.
• Do tabulky pridáme 2 nové sloupecky RIGHTBOUND a
  LEFTBOUND
• Joe Celko SQL for smarties kapitola Trees and
  Hierarchies

34
Predchudci bez rekurze (2)

• Tabulku naplníme daty, pro nové sloupecky
• 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
Predchudci - bez rekurze (3)
36
Predchudci - bez rekurze (4)

• Dotaz na predchudce MOTORCYCLE využije intervalu
  a bude vypadat
• SELECT
• FROM Vehicles
• WHERE RightBound gt 12
• AND LeftBound lt 13

37
Zobrazení stromu (1)

• Nekdy muž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 gt
  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)
Data Level PathStr
All 1
Air 1 Air
Plane 2 AirgtPlane
Rocket 2 AirgtRocket
Earth 1 Earth
Car 2 EarthgtCar
Truck 2 EarthgtTruck
2Wheeles 2 Earthgt2Wheeles
Bicycle 3 Earthgt2WheelesgtBicycle

39
Zobrazení bez rekurze (1)

• Do tabulky potrebujeme pridat sloupecek LEVEL,
  který nám oznacuje úroven uzlu
• Spocítáme ji pri 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

Data
All
Sea
Submarine
Boat
Earth
Car
Two Wheeles
Motorcycle
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_USA
  GE 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_S
  CHEMA TCT.CONSTRAINT_SCHEMA AND
  RFC.UNIQUE_CONSTRAINT_NAME TCT.CONSTRAINT_NAME
• WHERE CTU.TABLE_CATALOG _at_DB AND
  CTU.TABLE_SCHEMA _at_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
  _at_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_na
  me
• WHERE priorT.father_table_nameltgtpriorT.table_name)
  

45
Mazání tabulek (4)

• SELECT DISTINCT
• FROM T_TREE_CONTRAINTES
• ORDER BY level

46
MS Server 2005

• Pocet rekurzivních volání je omezen na 100
• Dá se ovlivnit nastavením
• OPTION (MAXRECURSION n)
• Beta verze zatím nepodporuje klícové slovo
  RECURSION

47
Príklad Hledání nejlepšího rešení

• Alena Rybicková

48
San Francisco New York

• Flights

flightno origin destination cost
49
San Francisco New York

• hledáme jak se nejlevneji dostat ze San
  Francisca do New Yorku
• data obsahují cykly, musíme vyrešit abychom
  nelétali porád dokola

50
Rekurzivní dotaz

• docasný pohled nazvaný TRIPS tvorí UNION ALL mezi
• inicializacním poddotazem, který najde všechna
  mesta, do kterých se dá dostat ze SF na jeden let
• rekurzivním poddotazem, který najde najde všechna
  mesta, kam se lze dostat z již nalezených mest

51
První pokus

• WITH trips (destination, route, totalcost) AS
• ((SELECT destination, destination, cost
  initial subquery
• FROM flights
• WHERE origin 'SanFrancisco)
• UNION ALL
• (SELECT f.destination recursive subquery
• t.route ',' f.destination,
• t.totalcost f.cost
• FROM trips t, flights f
• WHERE t.destination f.origin))
• SELECT route, totalcost final query
• FROM trips
• WHERE destination 'NewYork'

52
Problémy

• porušení pravidla, že sloupce rekurzivního
  pododotazu nesmí být delsí než odpovídající
  sloupce inicializacního poddotazu
• do sloupce route vkládáme výraz, který roste pri
  každém zavolání rekurzivního poddotazu
• REŠENÍ
• zmeníme datový typ u obou poddotazu
  (inicializacní i rekurzivní) na Varchar(50)

53
CAST výrazy

• umožnuje zmenit hodnotu z jednoho datového typu
  na jiný
• CAST ( výraz AS datový typ )
• definuje se délka, rozsah, presnost
• CAST (c1 c2 AS Decimal(8,2))
• CAST (nameaddress AS Varchar(255))

54
CAST výrazy

• implicitní hodnoty jsou Decimal(5,0), Char(1),
  Graphic(1)
• ostatní typy, pokud nejsou definované vlastnosti,
  pri nemožnosti konverze chyba
• string
• delší je doplnen mezerami
• kratší se urízne a vrátí warning message

55
Rešení

• zmeníme datový typ u obou poddotazu
  (inicializacní i rekurzivní) na Varchar(50)
• v inicializacním poddotazu nahradíme druhý
  sloupec
• CAST(destination AS Varchar(50))
• v rekurzivním poddotaze
• CAST(t.route ',' f.destination as
  Varchar(50))

56
Problém zacyklení

• dotaz se nezastavi (dokud nevycerpá prostredky),
  protože mapa je cyklický graf
• pravidla bránící zacyklení
• vyrad všechny letové úseky které letí od SF -
  pocátek letu
• vyrad všechny letové úseky které letí z NY - cíl
  letu
• uvažuj jen lety s maximálne tremi úseky

57
Výsledný dotaz

• WITH trips (destination, route, nseg, totalcost)
  AS
• ((SELECT destination,
• CAST(destination AS
• Varchar(50)), 1, cost
• FROM flights
• WHERE origin SF'
• UNION ALL
• (SELECT f.destination CAST(t.route ','
  f.destination AS Varchar(50)),
• t.nseg 1,
• t.totalcost f.cost

• FROM trips t, flights f
• WHERE t.destination f.origin
• AND f.destination ltgt 'SF'
• AND f.origin ltgt 'NY
• AND t.nseg lt 3))
• SELECT route, totalcost
• FROM trips
• WHERE destination NY'
• AND totalcost
• (SELECT min(totalcost)
• FROM trips
• WHERE destination'NY')

58
Dotaz na nejmenší pocet úseku

• cesta s nejmenším poctem úseku
• zmeníme final query
• SELECT route, totalcost final query
• FROM trips
• WHERE destination 'NewYork'
• AND totalcost
• (SELECT min(nseg)
• FROM trips
• WHERE destination'NewYork')

59
Dotazy s více poddotazy

• rekurzivní dotazy nejsou omezené jedním
  inicializacním nebo jedním rekurzivním poddotazem
• všechny poddotazy jsou spojené pomocí UNION ALL
• letadla vlaky
• chceme se nejlevneji dostat z SF do NY
• 2 inicializacní poddotazy 2 rekurzivní
  poddotazy

60
Dotaz s více poddotazy

• WITH trips (destination, route, nseg, totalcost)
  AS
• ((SELECT destination,
• CAST(destination AS
• Varchar(50)), 1, cost
• FROM flights
• WHERE origin SF')
• UNION ALL
• (SELECT destination,
• CAST(destination AS
• Varchar(50)), 1, cost
• FROM trains
• WHERE origin 'SF')

• UNION ALL
• (SELECT f.destination
• CAST(t.route ','
• f.destination AS
• Varchar(50)),
• t.nseg 1,
• t.totalcost f.cost
• FROM trips t, flights f
• WHERE t.destination f.origin
• AND f.destination ltgt 'SF'
• AND f.origin ltgt 'NewYork'
• AND t.nseg lt 3)

61
Dotazy s více poddotazy

• UNION ALL
• (SELECT x.destination
• CAST(t.route ',' x.destination as
  Varchar(50)),
• t.nseg 1,
• t.totalcost x.cost
• FROM trips t, trains x
• WHERE t.destination x.origin
• AND x.destination ltgt 'SF'
• AND x.origin ltgt 'NY
• AND t.nseg lt 3)
• )

• SELECT route, totalcost
• FROM trips
• WHERE destination 'NY'
• AND totalcost
• (SELECT min(totalcost)
• FROM trips
• WHERE destination'NY')

62
Další použití rekurze
63
Rekurzivní vkládání

• tabulka je vytvorena a naplnena rekurzivním
  INSERT výrazem
• tabulka NUMBERS obsahuje sloupce COUNTER a
  RANDOM, COUNTER bude obsahovat císla od 1 do 1000
  a RANDOM náhodná císla od 1 do 1000 pomocí funkce
  rand()

64
Rekurzivní vkládání

• CREATE TABLE numbers(counter Integer, random
  Integer)
• INSERT INTO numbers(counter, random)
• WITH temp(n) AS
• (VALUES(1)
• UNION ALL
• SELECT n1 FROM temp
• WHERE n lt 1000)
• SELECT n, integer(rand()1000)
• FROM temp

65
Rekurzivní vkládání

• inicializacní poddotaz je tvoren výrazem
  VALUES(1), urcuje tabulku s jedním sloupcem a
  jedním rádkem obsahujícím 1
• rekurzivní poddotaz vytvárí sloupec 1000 po sobe
  jdoucích prirozených císel
• koncový SELECT (vnorený v INSERTU) generuje 1000
  náhodných císel pomocí funkce rand()

66
Shrnutí

1. dotaz tvorí UNION ALL, který se skládá z jednoho
   nebo více inicializacních a jednoho nebo více
   rekurzivních poddotazu
2. každý inicializacní poddotaz musí být
   nerekurzivní
3. rekurzivní poddotaz používá výraz ve kterém je
   vložený
4. rekurzivní poddotaz nesmí obsahovat sloupcové
   funkce, SELECT DISTINCT, GROUP BY, HAVING

67
Shrnutí

1. sloupce rekurzivního poddotazu musí odpovídat (a
   nesmí být delší než) príslušnému sloupci
   inicializacního poddotazu
2. rekurzivní poddotaz musí specifikovat jak je
   každý rádek spocítán z již existujícího rádku
3. pokud data obsahují cykly, musí rekurzivní dotaz
   obsahovat pravidla pro zastavení
4. pri psaní koncového dotazu se použije rekurzivní
   výraz a další predikáty (napr. pro nalezení
   nejlepších rešení)

68
Reference

• Don Chamberlin Recursion in SQL Tips and
  Techniques, May 1996
• Frédéric BROUARD Recursive Queries in SQL1999
  and SQL Server 2005, 2005
• www.servercentral.com
• Srini Venigalla Expanding Recursive
  Opportunities with SQL UDFs in DB2 v 7.2, March
  2005