WITH pruža način za pisanje dodatnih izjava za korištenje u velike zahtjeve. Ove izjave, koje se također nazivaju zajedničkim tabličnim izrazima (CTE), mogu se smatrati privremenim definicijama tablica koje postoje samo za jedan upit. Dodatna naredba u klauzuli WITH može biti SELECT, INSERT, UPDATE ili DELETE, a sama klauzula WITH pripojena je glavnoj naredbi, koja također može biti SELECT, INSERT, UPDATE ili DELETE.
7.8.1. SELECT u WITH
Glavna svrha SELECT-a u klauzuli WITH je rastaviti složene upite na jednostavnije dijelove. Na primjer, zahtjev:
WITH regional_sales AS (SELECT regija, SUM(iznos) AS total_sales FROM narudžbe GROUP BY regija), top_regions AS (SELECT regija FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)) SELECT regija, proizvod, SUM(količina ) AS product_units, SUM(iznos) AS product_sales FROM narudžbe WHERE regija IN (SELECT regija FROM top_regions) GROUP BY regija, proizvod;
prikazuje rezultate prodaje samo za vodeće regije. WITH klauzula definira dva dodatni operater regional_sales i top_regions tako da se rezultat regionalne_prodaje koristi u top_regions, a rezultat top_regions se koristi u glavnoj SELECT upit. Ovaj bi se primjer mogao prepisati bez WITH, ali tada bismo trebale dvije razine ugniježđenih podupita SELECT. Ovo se može učiniti malo lakše korištenjem gore prikazane metode.
Neobavezna klauzula RECURSIVE pretvara WITH iz samo prikladne sintaktičke konstrukcije u sredstvo za obavljanje nečega što nije moguće u standardnom SQL-u. Korištenjem RECURSIVE, WITH upit može pristupiti vlastitom rezultatu. Vrlo jednostavan primjer zbrajanja brojeva od 1 do 100:
S REKURZIVNIM t(n) AS (VRIJEDNOSTI (1) UNION ALL SELECT n+1 FROM t WHERE n< 100) SELECT sum(n) FROM t;
U opći pogled Rekurzivni WITH upit uvijek se piše kao nije rekurzivni dio, zatim UNION (ili UNION ALL), a zatim rekurzivni dio, gdje samo u rekurzivnom dijelu možete pristupiti rezultatu upita. Takav zahtjev se izvršava na sljedeći način:
Procjena rekurzivnog upita
Rekurzivni dio se procjenjuje tako da rekurzivna referenca na sam upit pristupa trenutnom sadržaju radne tablice. Za UNION (ali ne UNION ALL), duplicirani retci i retci koji dupliciraju prethodno primljene se odbacuju. Svi preostali retci uključeni su u rezultat rekurzivnog upita i također su smješteni u privremeni međustol.
Sadržaj radne tablice zamjenjuje se sadržajem scenske tablice, a zatim se scenska tablica briše.
Izračunava se nerekurzivni dio. Za UNION (ali ne UNION ALL), dvostruki retci se odbacuju. Svi preostali retci uključeni su u rezultat rekurzivnog upita i također su smješteni u privremeni radni list.
Sve dok radni stol nije prazan, ponavljaju se sljedeći koraci:
U gore prikazanom primjeru, radna tablica u svakoj fazi sadrži samo jedan red i vrijednosti od 1 do 100 se sekvencijalno akumuliraju u njoj u stotom koraku, zahvaljujući WHERE stanje, ništa se ne vraća, tako da procjena upita završava.
Rekurzivni upiti obično se koriste za rad s hijerarhijskim ili stablom podatkovnih struktura. Kao koristan primjer možete stvoriti upit koji pronalazi sve izravne i neizravne komponente proizvoda, koristeći samo tablicu s izravnim vezama:
WITH RECURSIVE uključeni_dijelovi(pod_dio, dio, količina) AS (SELECT pod_dio, dio, količina FROM dijelovi WHERE dio = "naš_proizvod" UNION ALL SELECT p.pod_dio, p.dio, p.količina FROM uključeni_dijelovi pr, dijelovi p WHERE p.dio = pr.sub_parts SELECT sub_parts, SUM(quantity) as total_quantity FROM included_parts GROUP BY sub_parts
Kada radite s rekurzivnim upitima, važno je osigurati da rekurzivni dio upita ne završi vraćanjem nijednog niza (redova), inače će petlja biti beskonačna. Ponekad je dovoljno upotrijebiti UNION umjesto UNION ALL jer će to odbaciti redove koji su već u rezultatu. Međutim, petlja često proizvodi retke koji se ne podudaraju točno s prethodnima: u takvim slučajevima može imati smisla provjeriti jedno ili više polja kako bi se utvrdilo je li trenutna točka dosegnuta ranije. Standardna metoda Rješenje takvih problema je izračunavanje niza s već obrađenim vrijednostima. Na primjer, razmotrite sljedeći upit koji traži tablicu grafikona pomoću polja veze:
S REKURZIVNIM search_graph(id, link, data, depth) AS (SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link) SELECT * FROM search_graph;
Ovaj zahtjev će se ponavljati ako poveznice sadrže petlje. Budući da kao rezultat moramo dobiti " dubina", samo mijenjanje UNION ALL u UNION neće izbjeći petlju. Umjesto toga, moramo nekako odrediti što smo već postigli trenutna linija, prošavši neki put. Da bismo to učinili, dodamo dva stupca put i ciklus i dobijemo upit zaštićen ciklusom:
S REKURZIVNIM search_graph(id, link, data, dubina, path, cycle) AS (SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path ||. g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle) SELECT * FROM search_graph;
Osim sprječavanja petlji, vrijednosti polja često su korisne same za predstavljanje " načini » vodi do određene linije.
Općenito, kada trebate provjeriti više polja da biste otkrili ciklus, trebali biste koristiti niz nizova. Na primjer, ako trebate usporediti polja f1 i f2:
S REKURZIVNIM search_graph(id, link, data, dubina, path, cycle) AS (SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.podaci, sg.dubina + 1, put ||. ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle) SELECT * FROM search_graph;
Trag
Često je jedno polje dovoljno za prepoznavanje ciklusa, a tada se ROW() može izostaviti. Ovo neće koristiti niz podataka kompozitni tip, ali jednostavan niz, koji je učinkovitiji.
Trag
Ovaj rekurzivni algoritam za procjenu upita proizvodi čvorove poredane duž staze uranjanja. Da biste dobili rezultate poredane po dubini, vanjskom upitu možete dodati stupac ORDER BY. put", dobiven kao što je gore prikazano.
Dobar trik za testiranje upita koji se mogu petljati je dodavanje LIMIT nadređenom upitu. Na primjer, sljedeći će se upit ponavljati ako ne dodate klauzulu LIMIT:
S REKURZIVNIM t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t) SELECT n FROM t LIMIT 100;
Ali u u ovom slučaju to se ne događa, jer u PostgreSQL Upit WITH proizvodi onoliko redaka koliko nadređeni upit zapravo prihvaća. Ova se tehnika ne preporučuje u proizvodnom okruženju jer se drugi sustavi mogu ponašati drugačije. Dodatno, ovo neće raditi ako vanjski upit razvrstava rezultate rekurzivnog upita ili ih spaja s drugom tablicom, budući da će u takvim slučajevima vanjski upit obično odabrati cijeli rezultat upita WITH.
WITH upita imaju korisno svojstvo- procjenjuju se samo jednom za cijeli nadređeni upit, čak i ako im taj upit ili susjedni WITH upiti pristupaju više puta. Na taj način se složeni izračuni čiji su rezultati potrebni na više mjesta mogu staviti u WITH upite u svrhu optimizacije. Osim toga, takvi upiti izbjegavaju neželjene procjene funkcija s nuspojave. Međutim, također postoji poleđina- Optimizator ne može prenijeti ograničenja nadređenog upita u WITH upit na način na koji to može za obični podupit. Upit WITH obično se izvršava doslovno i vraća sve retke, uključujući one koje nadređeni upit tada može odbaciti. (No, kao što je gore spomenuto, izračun može prestati ranije ako referenca na ovaj upit zahtijeva samo ograničen broj linije.)
Gornji primjeri prikazuju samo klauzulu WITH s SELECT, ali se može koristiti s naredbama INSERT, UPDATE i DELETE na isti način. U svakom slučaju, u biti stvara privremenu tablicu kojoj se može pristupiti u glavnoj naredbi.
7.8.2. Promjena podataka u WITH
Također možete koristiti izjave za modificiranje podataka (INSERT, UPDATE ili DELETE) u klauzuli WITH. To vam omogućuje izvođenje nekoliko različitih operacija odjednom u jednom zahtjevu. Na primjer:
WITH moved_rows AS (DELETE FROM products WHERE "date" >= "2010-10-01" AND "date"< "2010-11-01" RETURNING *) INSERT INTO products_log SELECT * FROM moved_rows;
Ovaj upit zapravo premješta retke iz products u products_log. Izjava DELETE u WITH, uklanja navedene retke iz proizvoda i vraća njihov sadržaj u klauzuli RETURNING; a zatim glavni upit čita ovaj sadržaj i umeće ga u tablicu products_log.
Imajte na umu da je klauzula WITH u ovom slučaju pridružena naredbi INSERT, a ne naredbi SELECT ugniježđenoj unutar INSERT-a. Ovo je neophodno jer WITH može sadržavati samo izjave koje mijenjaju podatke prema gornja razina zahtjev. Međutim, primjenjuju se normalna pravila WITH vidljivost, tako da se rezultatu WITH također može pristupiti iz ugniježđene SELECT izjave.
Izjave koje mijenjaju podatke u WITH obično su popraćene klauzulom RETURNING (pogledajte odjeljak 6.4), kao što je prikazano u ovom primjeru. Važno je razumjeti da se privremena tablica koja se može koristiti u ostatku upita stvara iz rezultata RETURNING, i Ne ciljna tablica operatora. Ako naredba za modificiranje podataka u WITH nije popraćena klauzulom RETURNING, privremena tablica se ne stvara i ne može joj se pristupiti u ostatku upita. Međutim, takav će zahtjev ipak biti izvršen. Na primjer, recimo sljedeći ne baš praktičan upit:
WITH t AS (DELETE FROM foo) DELETE FROM bar;
Izbrisat će sve retke iz tablica foo i bar. U ovom slučaju, broj uključenih redaka koje će klijent primiti računat će se samo na temelju redaka uklonjenih iz trake.
WITH RECURSIVE included_parts(sub_parts, part) AS (SELECT sub_parts, part FROM parts WHERE part = "naš_proizvod" UNION ALL SELECT p.sub_parts, p.part FROM included_parts pr, parts p WHERE p.part = pr.sub_part) DELETE FROM parts WHERE dio IN (SELECT dio FROM uključeni_dijelovi);
Ovaj upit uklanja sve izravne i neizravne komponente proizvoda.
Operatori koji mijenjaju podatke u WITH izvršavaju se samo jednom i uvijek u cijelosti, bez obzira prihvaća li glavni upit njihov rezultat. Imajte na umu da se ovo razlikuje od ponašanja SELECT-a u WITH: kao što je objašnjeno u prethodnom odjeljku, SELECT se izvršava samo dok glavni upit zahtijeva njegove rezultate.
Ugniježđene izjave u WITH izvršavaju se istovremeno jedna s drugom i s glavnim upitom. Dakle, redoslijed kojim će izjave u WITH zapravo promijeniti podatke je nepredvidiv. Sve ove izjave se izvršavaju s jednim snimka podataka(vidi Poglavlje 13) tako da ne mogu " vidjeti " kako svaki od njih mijenja ciljne tablice. Ovo smanjuje nepredvidivost stvarnog redoslijeda promjene reda i znači da je RETURNING jedina opcija za prosljeđivanje promjena iz ugniježđenih WITH izjava u glavni upit. Na primjer, u ovom slučaju:
SA t AS (AŽURIRANJE proizvoda SET cijena = cijena * 1,05 VRAĆANJE *) SELECT * FROM proizvoda;
vanjska izjava SELECT vratit će cijene koje su bile prije radnje UPDATE, dok će u upitu
WITH t AS (UPDATE products SET price = price * 1.05 RETURNING *) SELECT * FROM t;
vanjski SELECT će vratiti promijenjene podatke.
Promjena istog niza više puta unutar iste izjave nije podržana. Dogodit će se samo jedna od nekoliko promjena, a pouzdano utvrditi koju je često vrlo teško (a ponekad čak i nemoguće). Ovo također vrijedi kada je redak izbrisan i izmijenjen u istoj izjavi: rezultat može biti samo ažuriranje. Stoga, općenito, takve operacije preklapanja treba izbjegavati. Konkretno, izbjegavajte podupiti WITH, koji mogu utjecati na retke koje je izmijenila glavna izjava ili izjave ugniježđene unutar nje. Rezultat takvih zahtjeva bit će nepredvidiv.
Trenutačno izjava koja mijenja podatke u WITH ne može ciljati tablicu za koju uvjetno pravilo ili pravilo ALSO ili INSTEAD ako se sastoji od više izjava.
standard SQL:1999 i otvaranje mogućnosti korištenja jezika u aplikacijama za koje prethodno nije bio prikladan. Radi se o o mogućnostima analitičkog i rekurzivni upiti . Ove teme nisu logično povezane; ujedinjuje ih samo činjenica da su odgovarajući alati vrlo glomazni i nisu uvijek laki za razumijevanje. U ovom kratkom predavanju ne nastojimo pružiti puni opis mogućnosti navedene u SQL standardu. Naš cilj je samo da opći nacrt opisati SQL pristup u ovim smjerovima.Analitičke aplikacije obično ne zahtijevaju detaljne podatke izravno pohranjene u bazi podataka, već neke njihove generalizacije i agregate. Na primjer, analitika ne zanima plaće konkretna osoba u određeno vrijeme i promijeniti plaće određena kategorija ljudi na određeno vrijeme. Korištenjem SQL terminologije, tipični upit baze podataka iz analitičke aplikacije sadrži klauzulu GROUP BY i pozive agregatne funkcije. Iako se u ovom tečaju jedva dotičemo pitanja implementacije SQL-orijentiranih DBMS-ova, od opća razmatranja Treba biti jasno da su upiti s klauzulom GROUP BY općenito "teški" za DBMS, budući da grupiranje tablice općenito zahtijeva vanjska sorta.
U sustavima baza podataka posebno dizajniranim za analitičke aplikacije, problem se obično rješava eksplicitno redundantnom pohranom agregiranih podataka (tj. rezultata poziva agregatne funkcije). Naravno, to zahtijeva dinamičko prilagođavanje pohranjenih agregatnih vrijednosti kada se detaljni podaci mijenjaju, ali za takve specijalizirane baze podataka to nije previše opterećujuće, jer se analitičke baze podataka ažuriraju relativno rijetko.
Međutim, ne može si svako poduzeće priuštiti istovremeno održavanje operativne baze podataka za rad redovite aplikacije operativna obrada transakcije (OLTP), kao što su računovodstvo, HR i druge aplikacije, te analitička baza podataka za aplikacije operativni analitička obrada (OLAP). Morate pokrenuti analitičke aplikacije na detaljnim operativnim bazama podataka, a te aplikacije pristupaju DBMS-u s brojnim dugotrajnim upitima s GROUP BY klauzulama i pozivima agregatne funkcije.
Programeri jezičnog standarda SQL pokušali su istodobno riješiti dva problema: smanjiti broj upita potrebnih u analitičkim aplikacijama i smanjiti troškove GROUP BY upita koji daju tražene sažete podatke. U ovom predavanju raspravljat ćemo o najvažnijim, s naše točke gledišta, konstrukcijama SQL jezik, olakšavajući formuliranje, provedbu i korištenje rezultata analitički upiti: odjeljci GRUPIRAJ PO ZBIRNOM I GRUPIRAJ PO KOCKU i novi funkcija agregata GRUPIRANJE, što vam omogućuje ispravnu interpretaciju rezultata analitički upiti ako je dostupno nedefinirane vrijednosti.
Tradicionalno, SQL nikada nije imao sposobnost formuliranja rekurzivni upiti, gdje pod rekurzivni upit(pojednostavljeno rečeno) razumijemo upit prema tablici koji se sam na neki način mijenja kada se ovaj upit izvrši. Dopustite mi da vas podsjetim da je to ugrađeno u osnovnu semantiku SQL izjava: Prije nego što se izvrši klauzula WHERE, rezultat klauzule FROM mora biti u potpunosti procijenjen.
Međutim, programeri aplikacija često moraju rješavati probleme za koje tradicionalni načini formuliranja upita u SQL jeziku nisu dovoljni: na primjer, pronalaženje rute između dvije zadane geografske točke, određivanje zajedničkog skupa komponenti za sastavljanje određene jedinice itd. Tvrtke koje proizvode SQL-orijentirane DBMS-ove pokušale su zadovoljiti takve potrebe kroz vlasnička rješenja s ograničenim rekurzivnim svojstvima, ali do pojave standarda SQL:1999 nije bilo općih standardiziranih alata.
Treba napomenuti da postoji određeni pritisak između unesenih generiranih tablica. Međutim, samo za linearna rekurzija su osigurani dodatne značajke kontrola reda izračuna rekurzivno definiran generirana tablica i kontrola nepostojanja ciklusa. Valja napomenuti da se pri čitanju standarda ponekad stječe dojam da sami njegovi autori još nisu u potpunosti shvatili sve moguće posljedice, što može proizaći iz uporabe uvedenih struktura. Mislim da bismo u budućim verzijama standarda trebali očekivati pojašnjenja i/ili ograničenja upotrebe ovih konstrukata. S tim u vezi, u ovom predavanju ograničavamo se na opće definicije rekurzivne konstrukcije SQL jezika i rasprava jednostavan slučaj rekurzivni upit.
Kombiniranje upita iste vrste
Prikažite brojeve prodavača s oznakom "jak prodavač" ili "slab". Prodavač se smatra jakim ako on prosječni trošak trgovine su više od 500, slabe - manje od 500(zahtjev je prikazan na listu 39 , proizlaziti - Stol 51 ):
GROUP BY N_Seller
IMATI prosj. (trošak)<500
GROUP BY N_Seller
IMATI prosj.(Cost)>500;
Stol 51. Rezultat upita kod operatera Union
Prikažite brojeve prodavača s oznakom "jak prodavač" ili "slab". Prodavač s prosječnom vrijednošću transakcije većom od 500 smatra se jakim, slab prodavatelj je manji od 500, sortiraj po aktivnosti(zahtjev je prikazan na listu 40 , proizlaziti - Stol 52 ):
SELECT N_Seller, "Weak Seller" kao Aktivnost IZ Transakcija
GROUP BY N_Seller
IMATI prosj. (trošak)<500
UNION SELECT N_Seller, "Strong Seller" kao aktivnost FROM Transactions
GROUP BY N_Seller
IMATI PROSJEČNI (CIJENA)>500
ORDER BY Aktivnost;
Stol 52. Rezultat zahtjeva s Unijom i Order by
Operatori presjeka i osim rade slično, što odgovara operacijama presjeka i razlike u relacijskoj algebri. Za predmetno područje"prodajni" popisi gradova kupaca i prodavača možda nisu isti, tako da možemo riješiti razne probleme s ovim popisima, kao što je prikazano u tablici. 53.
Stol 53. Korištenje operatora spajanja za upite istog tipa
Za list. 25 dao je primjer zahtjeva u kojem su pretresani Ivanovljevi podređeni. Postavlja se pitanje kako izgraditi cjelokupnu hijerarhiju podređenih, a ne samo svoje neposredno podređene. Zapravo, moramo primijeniti ovaj isti upit za Sheet. 25 do rezultata samog obavljanja, zatim ponovno itd., sve dok podređenih ne bude sve više i više niske razine hijerarhija.
Konstruirajte hijerarhiju svih Ivanovljevih podređenih u "dužini"(zahtjev je prikazan na listu 41 , proizlaziti - Stol 54 ).
Rekurzivni upiti mogu se prepoznati po ključna riječ S REKURZIVOM. Da bi bio pozvan rekurzivno, upit mora imati ime (u ovom slučaju - proizvod). Zahtjev se sastoji od dva dijela, kombinirana pomoću UNION-a. U prvom dijelu - šefovi, u drugom dijelu - njihovi podređeni. Upit pronalazi prvog podređenog, zatim prvog podređenog prvog i tako dalje. Ti se upiti nazivaju dugotrajnim upitima za pretraživanje.
List. 41. Rekurzivni upit “po duljini”
(SELECT N, ime
OD prodavača
WHERE Ime = 'Ivanov'
ODABERITE Prodavače. N, Ime prodavača
OD prodavača, prodaja
SELECT * FROM Cont;
Stol 54. Rezultat rekurzivnog upita “po duljini”
Ako prvo trebamo navesti sve Ivanovljeve podređene, zatim Ivanovljeve podređene, itd., tada moramo koristiti rekurzivne upite u širinu koristeći SEARCH BREADTH FIRST operator. Upit postavlja broj iteracije pomoću operatora SET.
Izgradite hijerarhiju svih Ivanovljevih podređenih "u širinu"(zahtjev je prikazan na listu 42 , proizlaziti - Stol 55 ).
List. 42. Rekurzivni upit u širinu
S REKURZIVNIM Nast. (N, Ime, N_Glava) as
(SELECT N, ime
OD prodavača
WHERE Ime = 'Ivanov'
OD prodavača, prodaja
WHERE Cont.N = Sellers.N_Chief);
PRVO PRETRAŽI U ŠIRINU OD N_Chief, N
POSTAVITE stupac_narudžbe
SELECT * FROM Cont
ORDER BY stupac_narudžbe;
Stol 55. Rezultat upita "prvo u širinu".
Kod rekurzivnog upita moguća je situacija petlje. Iako to nije slučaj u našem primjeru, možemo pretpostaviti da postoje situacije kada jedan tim radi na dva projekta, te je jedna osoba u prvom projektu šef, a u drugom podređeni, i obrnuto, druga osoba u prvom projektu je podređen. A u drugom - gazda. U ovom slučaju, prethodni rekurzivni upiti će se ponavljati.
Izgradite hijerarhiju svih Ivanovljevih podređenih, uzimajući u obzir mogućnost petlje(list 43) .
List. 43. Rekurzivni upit uzimajući u obzir petlje
S REKURZIVNIM Nast. (N, Ime) as
(SELECT N, ime
OD prodavača
WHERE Ime = 'Ivanov'
ODABERITE Prodavače. N, Prodavači. Ime
OD prodavača, prodaja
WHERE Cont.N = Sellers.N_Chief);
POSTAVITE oznaku ciklusa na "Y" zadano "N"
KORIŠTENJE biciklističke staze
SELECT * FROM Cont
D/Z 6. Za primjer iz D/Z 4 smislite sljedeće upite:
- Upit s jednom tablicom koja procjenjuje agregatnu funkciju pomoću iskaza where, having, order by.
- Upit za spajanje više tablica pomoću klauzule where, jedna se tablica mora koristiti više puta u upitu pod aliasima.
- Upit desnim, lijevim ili punim spajanjem.
- Upit s dva podupita u where i koji ima klauzule
- Upit s podupitom koristeći sve, bilo koje ili postoji.
- Ugniježđeni upit s unijom dviju tablica (jedna od varijanti spajanja), neizravno povezanih trećom.
- Kombiniranje upita iste vrste.
- Rekurzivni upit.
Pitanja za samotestiranje:
1. Koja je razlika između korištenja operatora group by, group by rollup, group by rollup cube, grouping, organizer by, partition?
2. Koja je razlika između korištenja naredbi where i having?
3. Koji upit spaja... na podudaranje? gdje operater?
4. Kakvu vrstu operacije relacijska algebra odgovara naredbi where?
5. Kojoj operaciji relacijske algebre odgovara operator spajanja?
Ovi rezultati izgledaju puno korisnije. Sada u tablici ReachableFrom polje Odredište sadrži sve gradove do kojih se može doći iz bilo kojeg grada koji se nalazi u polju Izvor iste tablice, ne čineći više od jednog zaustavljanja. Zatim, pri sljedećem prolazu, rekurzija će obraditi rute s dva međustajanja i nastavit će to činiti sve dok se ne pronađu svi gradovi do kojih se može doći.
Nakon završetka rekurzije, treća i posljednja izjava SELECT (koja nije uključena u rekurziju) odabire iz ReachableFrom samo one gradove do kojih se može doći iz Portlanda. U ovom primjeru možete doći do svih ostalih šest gradova, i to s prilično malim brojem međustajališta. Tako da ne morate juriti okolo kao da vozite na štuli s oprugom.
Ako pažljivo pogledate kod rekurzivnog upita, vidjet ćete da ne izgleda ništa jednostavnije od sedam zasebnih upita. Međutim, ovaj zahtjev ima dvije prednosti:
- nakon lansiranja vanjska intervencija više nije potrebna;
- radi brzo.
Ako možete, zamislite pravu zrakoplovnu tvrtku koja ima mnogo više gradova na karti svojih ruta. I što više moguća mjesta imenovanja, one više pogodnosti iz rekurzivne metode.
Što upit čini rekurzivnim? Da tablicu ReachableFrom definiramo na temelju nje same. Rekurzivni dio definicije je druga SELECT naredba, koja se nalazi odmah nakon UNION. ReachableFrom je privremena tablica koja se puni podacima kako dolazi do rekurzije. I ovo se punjenje nastavlja sve dok sva moguća odredišta ne budu u ReachableFrom. U ovoj tablici neće biti dvostrukih redaka jer ih operator UNION neće propustiti. Kada se rekurzija završi, tablica ReachableFrom sadržavat će sve gradove do kojih se može doći iz bilo kojeg početnog grada. Treća i posljednja izjava SELECT vraća samo one gradove do kojih možete doći iz Portlanda. Stoga vam želimo ugodno putovanje.
Gdje još možete koristiti rekurzivni upit?
Svaki problem koji se može predstaviti kao struktura stabla može se riješiti pomoću rekurzivnog upita. Klasičan primjer kako se takvi upiti koriste u industriji je obrada materijala (proces pretvaranja sirovina u finalni proizvod). Recimo da vaša tvrtka izbacuje novi benzinsko-električni hibridni automobil. Takav se stroj sastavlja od sklopova (motor, baterije itd.), koji se pak sastoje od manjih podsklopova (koljenasto vratilo, elektrode itd.), a oni - od još manjih komponenti.
Podaci o svim tim komponentama pohranjeni su u relacijska baza podataka vrlo teško - osim, naravno, ako ne koristi rekurziju. Rekurzija omogućuje da se, počevši od cijelog stroja, dođe do najmanjeg dijela na bilo koji način. Želite pronaći podatke o vijku za pričvršćivanje koji drži terminal negativna elektroda pomoćna baterija? To je moguće – i to bez puno vremena. Suoči se s ovime SQL zadaci može pomoću strukture WITH RECURSIVE (rekurzivni operator).
Osim toga, rekurzija je sasvim prirodna u analizi "što ako". Na primjer, što se događa ako Vannevar Airlines odluči prestati letjeti iz Portlanda u Charlotte? Kako će to utjecati na letove u gradove koji su trenutno dostupni iz Portlanda? Rekurzivni upit će odmah odgovoriti na ova pitanja.
