WITH biedt een manier om aanvullende instructies te schrijven voor gebruik in grote verzoeken. Deze instructies, ook wel Common Table Expressions (CTE) genoemd, kunnen worden gezien als tijdelijke tabeldefinities die slechts voor één query bestaan. De aanvullende instructie in de WITH-clausule kan SELECT, INSERT, UPDATE of DELETE zijn, en de WITH-clausule zelf is aan de hoofdinstructie gekoppeld, die ook SELECT, INSERT, UPDATE of DELETE kan zijn.

7.8.1. SELECTEER in MET

Het belangrijkste doel van SELECT in de WITH-clausule is om complexe query's in eenvoudiger delen op te splitsen. Het verzoek is bijvoorbeeld:

MET regionale_verkoop AS (SELECT regio, SUM(bedrag) AS totale_verkoop FROM bestellingen GROEPEREN OP regio), top_regio's AS (SELECT regio FROM regionale_verkoop WHERE totale_verkoop > (SELECT SUM(totale_verkoop)/10 FROM regionale_verkoop)) SELECT regio, product, SOM(hoeveelheid ) AS product_units, SUM(bedrag) AS product_sales FROM bestellingen WAAR regio IN (SELECTEER regio FROM top_regio's) GROEPEREN OP regio, product;

geeft alleen verkoopresultaten weer voor toonaangevende regio's. De WITH-clausule definieert er twee extra exploitant regional_sales en top_regions zodat het resultaat van regional_sales wordt gebruikt in top_regions en het resultaat van top_regions wordt gebruikt in de hoofdregio's SELECT-vraag. Dit voorbeeld zou kunnen worden herschreven zonder WITH , maar dan hebben we twee niveaus van geneste SELECT-subquery's nodig. Dit kan iets eenvoudiger worden gedaan met behulp van de hierboven weergegeven methode.

De optionele RECURSIVE-clausule verandert WITH van slechts een handige syntactische constructie in een manier om iets te doen dat niet mogelijk is in standaard SQL. Door RECURSIVE te gebruiken, heeft een WITH-query toegang tot zijn eigen resultaat. Een heel eenvoudig voorbeeld waarbij getallen van 1 tot 100 worden opgeteld:

MET RECURSIEVE t(n) AS (WAARDEN (1) UNIE ALLEN SELECTEREN n+1 VAN t WAAR n< 100) SELECT sum(n) FROM t;

IN algemeen beeld Een recursieve WITH-query wordt altijd geschreven als niet recursief deel, dan UNION (of UNION ALL), en dan recursief deel, waarbij u alleen in het recursieve deel toegang hebt tot het resultaat van de zoekopdracht. Een dergelijk verzoek wordt als volgt uitgevoerd:

Een recursieve zoekopdracht evalueren

Het niet-recursieve deel wordt berekend. Voor UNION (maar niet UNION ALL) worden dubbele rijen weggegooid. Alle resterende rijen worden opgenomen in het resultaat van de recursieve zoekopdracht en worden ook tijdelijk geplaatst werkblad.

Zolang de werktafel niet leeg is, worden de volgende stappen herhaald:

1. Het recursieve deel wordt geëvalueerd zodat de recursieve verwijzing naar de query zelf toegang krijgt tot de huidige inhoud van de werktabel. Voor UNION (maar niet UNION ALL) worden dubbele rijen en rijen die eerder ontvangen rijen dupliceren, weggegooid. Alle resterende rijen worden opgenomen in het resultaat van de recursieve zoekopdracht en worden ook tijdelijk geplaatst tussenliggende tafel.

   De inhoud van de werktabel wordt vervangen door de inhoud van de verzameltabel en vervolgens wordt de verzameltabel gewist.

In het bovenstaande voorbeeld bevat de werktabel in elke fase slechts één rij en worden waarden van 1 tot 100 daarin opeenvolgend verzameld WAAR voorwaarde, er wordt niets geretourneerd, dus de query-evaluatie eindigt.

Recursieve query's worden doorgaans gebruikt om met hiërarchische of boomgegevensstructuren te werken. Als nuttig voorbeeld u kunt een query maken die alle directe en indirecte componenten van een product vindt, met behulp van alleen een tabel met directe verbindingen:

MET RECURSIEVE meegeleverde_parts(sub_part, part, aantal) AS (SELECT sub_part, part, aantal FROM parts WHERE part = "ons_product" UNION ALL SELECT p.sub_part, p.part, p.quantity FROM Included_parts pr, parts p WHERE p.part = pr.sub_part) SELECT sub_part, SUM(hoeveelheid) als totale_hoeveelheid VAN opgenomen_onderdelen GROEPEREN OP sub_part

Wanneer u met recursieve query's werkt, is het belangrijk ervoor te zorgen dat het recursieve deel van de query uiteindelijk geen tupels (rijen) retourneert, anders is de lus oneindig. Soms is het voldoende om UNION te gebruiken in plaats van UNION ALL , omdat hierdoor rijen worden weggegooid die al in het resultaat voorkomen. Vaak produceert de lus echter rijen die niet precies overeenkomen met de vorige: in dergelijke gevallen kan het zinvol zijn om een ​​of meer velden te controleren om te bepalen of het huidige punt eerder is bereikt. Standaard methode De oplossing voor dergelijke problemen is het berekenen van een array met reeds verwerkte waarden. Bekijk bijvoorbeeld de volgende query, waarmee de grafiektabel wordt opgezocht via het koppelingsveld:

MET RECURSIEVE 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.diepte + 1 VAN grafiek g, zoek_grafiek sg WAAR g.id = sg.link) SELECT * VAN zoek_grafiek;

Dit verzoek wordt herhaald als de linkkoppelingen lussen bevatten. Omdat we als resultaat moeten krijgen " diepte", alleen het veranderen van UNION ALL naar UNION zal de lus niet vermijden. In plaats daarvan moeten we op de een of andere manier vaststellen wat we al hebben bereikt huidige lijn, nadat ik een eind had gelopen. Om dit te doen, voegen we twee kolommen pad en cyclus toe en krijgen we een cyclusbeveiligde query:

MET RECURSIEVE zoek_grafiek(id, link, data, diepte, pad, cyclus) AS (SELECT g.id, g.link, g.data, 1, ARRAY, false FROM grafiek g UNION ALL SELECT g.id, g.link, g.data, sg.diepte + 1, pad ||. g.id, g.id = ELK(pad) VAN grafiek g, zoek_grafiek sg WAAR g.id = sg.link EN NIET cyclus) SELECT * VAN zoek_grafiek;

Naast het voorkomen van lussen zijn arraywaarden op zichzelf vaak nuttig om " manieren » die naar een specifieke string leidt.

Wanneer u meerdere velden moet controleren om een ​​cyclus te detecteren, moet u over het algemeen een array van strings gebruiken. Als u bijvoorbeeld de velden f1 en f2 moet vergelijken:

MET RECURSIEVE zoek_grafiek(id, link, data, diepte, pad, cyclus) AS (SELECT g.id, g.link, g.data, 1, ARRAY, false FROM grafiek g UNION ALL SELECT g.id, g.link, g.data, sg.diepte + 1, pad ||. ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ELK(pad) VAN grafiek g, zoek_grafiek sg WHERE g.id = sg.link EN NIET cyclus) SELECT * VAN search_graph;

Aanwijzing

Vaak is één veld voldoende om een ​​cyclus te herkennen, en dan kan ROW() worden weggelaten. Hierbij wordt geen data-array gebruikt samengestelde soort, maar een eenvoudige array, die efficiënter is.

Aanwijzing

Dit recursieve query-evaluatiealgoritme produceert knooppunten geordend langs het immersiepad. Als u de resultaten op diepte wilt sorteren, kunt u een ORDER BY-kolom aan de buitenste query toevoegen. pad", verkregen zoals hierboven weergegeven.

Een goede truc voor het testen van query's die in een lus kunnen voorkomen, is het toevoegen van LIMIT aan de bovenliggende query. De volgende query wordt bijvoorbeeld herhaald als u geen LIMIT-clausule toevoegt:

MET RECURSIEVE t(n) AS (SELECTEER 1 UNIE ALLEN SELECTEER n+1 VAN t) SELECTEER n VAN t LIMIET 100;

Maar binnen in dit geval dit gebeurt niet, omdat in PostgreSQL Een WITH-query produceert zoveel rijen als de bovenliggende query daadwerkelijk accepteert. Deze techniek wordt niet aanbevolen in een productieomgeving, omdat andere systemen zich anders kunnen gedragen. Bovendien werkt dit niet als de buitenste query de resultaten van een recursieve query sorteert of deze samenvoegt met een andere tabel, aangezien in dergelijke gevallen de buitenste query doorgaans toch het volledige WITH-queryresultaat selecteert.

MET vragen hebben nuttige eigenschap- ze worden slechts één keer geëvalueerd voor de gehele bovenliggende query, zelfs als die query of aangrenzende WITH-query's er meerdere keren toegang toe hebben. Op deze manier kunnen complexe berekeningen waarvan de resultaten op meerdere plaatsen nodig zijn, voor optimalisatiedoeleinden in WITH-query's worden geplaatst. Bovendien vermijden dergelijke zoekopdrachten ongewenste functie-evaluaties met bijwerkingen. Er is echter ook achterkant- Het optimalisatieprogramma kan de beperkingen van een bovenliggende query niet doorgeven aan een WITH-query, zoals dat wel het geval is voor een reguliere subquery. Een WITH-query wordt doorgaans letterlijk uitgevoerd en retourneert alle rijen, inclusief de rijen die de bovenliggende query vervolgens kan weggooien. (Maar zoals hierboven vermeld, kan de berekening eerder stoppen als de verwijzing naar deze zoekopdracht alleen vereist beperkt aantal lijnen.)

In de bovenstaande voorbeelden wordt alleen de WITH-clausule met SELECT weergegeven, maar deze kan op dezelfde manier worden gebruikt met de opdrachten INSERT, UPDATE en DELETE. In elk geval wordt er in wezen een tijdelijke tabel gemaakt die toegankelijk is via de hoofdopdracht.

7.8.2. Gegevens wijzigen in WITH

U kunt ook gegevensmodificerende instructies (INSERT, UPDATE of DELETE) gebruiken in de WITH-clausule. Hierdoor kunt u in één aanvraag meerdere verschillende bewerkingen tegelijk uitvoeren. Bijvoorbeeld:

MET verplaatste_rijen AS (VERWIJDEREN UIT producten WAAR "datum" >= "2010-10-01" EN "datum"< "2010-11-01" RETURNING *) INSERT INTO products_log SELECT * FROM moved_rows;

Met deze query worden de rijen feitelijk verplaatst van products naar products_log . VERWIJDER verklaring in WITH verwijdert de gespecificeerde rijen uit producten en retourneert hun inhoud in een RETURNING-clausule; en vervolgens leest de hoofdquery deze inhoud en voegt deze in de products_log-tabel in.

Houd er rekening mee dat de WITH-clausule in dit geval aan de INSERT-instructie is gekoppeld, en niet aan de SELECT-opdracht die in de INSERT is genest. Dit is nodig omdat WITH alleen instructies kan bevatten die gegevens wijzigen bovenste niveau verzoek. Ze zijn echter van toepassing normale regels WITH-zichtbaarheid, zodat het resultaat van WITH ook toegankelijk is via een geneste SELECT-instructie.

Gegevensmodificerende instructies in WITH gaan doorgaans gepaard met een RETURNING-clausule (zie Paragraaf 6.4), zoals in dit voorbeeld wordt getoond. Het is belangrijk om te begrijpen dat een tijdelijke tabel die in de rest van de query kan worden gebruikt, wordt gemaakt op basis van het resultaat van RETURNING , en Niet doeltabel van de operator. Als de gegevensmodificerende instructie in WITH niet vergezeld gaat van een RETURNING-clausule, wordt de tijdelijke tabel niet gemaakt en is deze niet toegankelijk in de rest van de query. Een dergelijk verzoek zal echter nog steeds worden uitgevoerd. Laten we bijvoorbeeld de volgende, niet erg praktische vraag stellen:

MET t AS (VERWIJDEREN VAN foo) VERWIJDEREN VAN balk;

Het verwijdert alle rijen uit de tabellen foo en bar. In dit geval wordt het aantal betrokken rijen dat de klant ontvangt alleen geteld op basis van de rijen die uit bar zijn verwijderd.

MET RECURSIEVE include_parts(sub_part, part) AS (SELECT sub_part, part FROM parts WHERE part = "ons_product" UNION ALL SELECT p.sub_part, p.part FROM Included_parts pr, parts p WHERE p.part = pr.sub_part) VERWIJDER UIT onderdelen WAAR onderdeel IN (SELECTEER onderdeel VAN inbegrepen_onderdelen);

Deze zoekopdracht verwijdert alle directe en indirecte componenten van het product.

Operators die gegevens in WITH wijzigen, worden slechts één keer en altijd in hun geheel uitgevoerd, ongeacht of de hoofdquery hun resultaat accepteert. Merk op dat dit verschilt van het gedrag van SELECT in WITH: zoals besproken in de vorige sectie, wordt de SELECT alleen uitgevoerd zolang de resultaten ervan worden geclaimd door de hoofdquery.

Geneste instructies in WITH worden gelijktijdig met elkaar en met de hoofdquery uitgevoerd. De volgorde waarin de instructies in WITH de gegevens daadwerkelijk zullen veranderen, is dus onvoorspelbaar. Al deze instructies worden uitgevoerd met één momentopname van gegevens(zie hoofdstuk 13) zodat ze niet kunnen " zien " hoe elk van hen de doeltabellen verandert. Dit vermindert de onvoorspelbaarheid van de feitelijke rijwijzigingsvolgorde en betekent dat RETURNING de enige optie is voor het doorgeven van wijzigingen van geneste WITH-instructies aan de hoofdquery. In dit geval bijvoorbeeld:

MET t AS (UPDATE producten SET prijs = prijs * 1,05 RETOUREN *) SELECTEER * UIT producten;

de buitenste SELECT-instructie retourneert de prijzen die vóór de UPDATE-actie waren, terwijl deze in de query stonden

MET t AS (UPDATE producten SET prijs = prijs * 1,05 RETOUREN *) SELECT * VAN t;

een externe SELECT retourneert de gewijzigde gegevens.

Het meerdere keren wijzigen van dezelfde tekenreeks binnen dezelfde instructie wordt niet ondersteund. Er zal slechts één van de vele veranderingen plaatsvinden, en het betrouwbaar bepalen van welke verandering is vaak behoorlijk moeilijk (en soms zelfs onmogelijk). Dit geldt ook als een rij in dezelfde instructie wordt verwijderd en gewijzigd: het resultaat kan alleen een update zijn. Daarom moeten dergelijke overlappende handelingen in het algemeen worden vermeden. Vermijd in het bijzonder WITH-subquery's, die van invloed kunnen zijn op rijen die zijn gewijzigd door de hoofdinstructie of instructies die daarin zijn genest. Het resultaat van dergelijke verzoeken zal onvoorspelbaar zijn.

Momenteel kan een instructie die gegevens wijzigt in WITH niet gericht zijn op de tabel waarvoor voorwaardelijke regel of een ALSO- of INSTEAD-regel als deze uit meerdere instructies bestaat.

standaard SQL:1999 en opent de mogelijkheid om de taal te gebruiken in toepassingen waarvoor deze voorheen niet geschikt was. Het gaat over over de mogelijkheden van analytisch en recursieve zoekopdrachten . Deze onderwerpen zijn niet logisch met elkaar verbonden; ze zijn alleen verenigd door het feit dat de bijbehorende tools erg omslachtig en niet altijd gemakkelijk te begrijpen zijn. In deze korte lezing proberen we niet te voorzien volledige beschrijving mogelijkheden gespecificeerd in de SQL-standaard. Ons doel is alleen maar om algemene schets beschrijf de SQL-aanpak in deze richtingen.

Analytische toepassingen vereisen doorgaans geen gedetailleerde gegevens die rechtstreeks in de database zijn opgeslagen, maar enkele van hun generalisaties en aggregaten. Analytics is bijvoorbeeld niet geïnteresseerd in loon specifieke persoon op een bepaald moment, en veranderen loon een bepaalde categorie mensen gedurende een bepaalde periode. Met behulp van SQL-terminologie bevat een typische databasequery van een analytische toepassing een GROUP BY-clausule en aanroepen geaggregeerde functies. Hoewel we in deze cursus nauwelijks ingaan op de problemen van het implementeren van SQL-georiënteerde DBMS's, van algemene overwegingen Het mag duidelijk zijn dat query's met een GROUP BY-clausule over het algemeen "moeilijk" zijn voor het DBMS, omdat het groeperen van een tabel doorgaans externe sortering.

In databasesystemen die specifiek zijn ontworpen voor analytische toepassingen, wordt het probleem meestal opgelost door expliciet redundante opslag van geaggregeerde gegevens (dat wil zeggen de resultaten van oproepen geaggregeerde functies). Dit vereist uiteraard een dynamische aanpassing van opgeslagen geaggregeerde waarden wanneer gedetailleerde gegevens veranderen, maar voor dergelijke gespecialiseerde databases is dit niet al te belastend, aangezien analytische databases relatief zelden worden bijgewerkt.

Niet elke onderneming kan het zich echter veroorloven om tegelijkertijd een operationele database voor werk te onderhouden reguliere toepassingen operationele verwerking transacties (OLTP), zoals boekhouding, HR en andere applicaties, en een analytische database voor applicaties operationeel analytische verwerking (OLAP). U moet analytische applicaties uitvoeren op gedetailleerde operationele databases, en deze applicaties hebben toegang tot het DBMS met talloze tijdrovende queries met GROUP BY-clausules en -aanroepen geaggregeerde functies.

De ontwikkelaars van de SQL-taalstandaard probeerden tegelijkertijd twee problemen op te lossen: het aantal vereiste query's in analytische toepassingen verminderen, en de kosten verlagen van GROUP BY-query's die de vereiste samenvattende gegevens opleveren. In deze lezing bespreken we de voor ons belangrijkste constructies SQL-taal, waardoor de formulering, implementatie en het gebruik van resultaten worden vergemakkelijkt analytische vragen: secties GROEPEREN OP SAMENVOEGING En GROEP PER KUBUS en nieuw geaggregeerde functie GROEPERING, zodat u de resultaten correct kunt interpreteren analytische vragen indien beschikbaar ongedefinieerde waarden.

Traditioneel had SQL nooit de mogelijkheid om te formuleren recursieve zoekopdrachten, waar onder recursieve vraag(om het simpel te zeggen): we begrijpen een query naar een tabel die zelf op de een of andere manier verandert wanneer deze query wordt uitgevoerd. Laat me je eraan herinneren dat dit ingebed is in de fundamentele semantiek SQL-instructie: Voordat de WHERE-clausule wordt uitgevoerd, moet het resultaat van de FROM-clausule volledig worden geëvalueerd.

Applicatieontwikkelaars moeten echter vaak problemen oplossen waarvoor traditionele manieren om queries in de SQL-taal te formuleren niet voldoende zijn: bijvoorbeeld het vinden van een route tussen twee gegeven geografische punten, het bepalen van een gemeenschappelijke set componenten voor het samenstellen van een bepaalde eenheid, enz. Bedrijven die SQL-georiënteerde DBMS'en produceerden, probeerden aan dergelijke behoeften te voldoen door middel van eigen oplossingen met beperkte recursieve eigenschappen, maar tot de komst van de SQL:1999-standaard waren er geen algemene gestandaardiseerde tools.

Opgemerkt moet worden dat er enige druk is tussen de gegenereerde tabellen die worden ingevoerd. Echter alleen voor lineaire recursie zijn voorzien extra functies Controle van de berekeningsvolgorde recursief gedefinieerd gegenereerde tabel en controle van de afwezigheid van cycli. Opgemerkt moet worden dat bij het lezen van de standaard soms de indruk ontstaat dat de auteurs zelf nog niet alles volledig hebben gerealiseerd mogelijke gevolgen, die kunnen voortvloeien uit het gebruik van de geïntroduceerde structuren. Ik denk dat we in toekomstige versies van de standaard verduidelijkingen en/of beperkingen op het gebruik van deze constructies mogen verwachten. In dit opzicht beperken wij ons in deze lezing tot algemene definities recursieve constructies van de SQL-taal en discussie eenvoudig geval recursieve vraag.

Query's van hetzelfde type combineren

Geef het aantal verkopers weer met het label 'sterke verkoper' of 'zwak'. Een verkoper wordt als sterk beschouwd als hij gemiddelde kosten transacties zijn meer dan 500, zwak – minder dan 500(het verzoek staat op blad 39 , resultaat - Tafel 51 ):

GROEPEREN OP N_Verkoper

GEMIDDELD (kosten)<500

GROEPEREN OP N_Verkoper

MET gemiddelde (kosten)> 500;

Tafel 51. Resultaat van een zoekopdracht bij de Union-operator

Geef het aantal verkopers weer met het label 'sterke verkoper' of 'zwak'. Een verkoper met een gemiddelde transactiewaarde van meer dan 500 wordt als sterk beschouwd, een zwakke verkoper is minder dan 500, gesorteerd op activiteit(het verzoek staat op blad 40 , resultaat - Tafel 52 ):

SELECTEER N_Seller, 'Zwakke verkoper' als activiteit UIT Transacties

GROEPEREN OP N_Verkoper

GEMIDDELD (kosten)<500

UNION SELECT N_Seller, 'Sterke verkoper' als activiteit UIT Transacties

GROEPEREN OP N_Verkoper

MET gemiddelde (kosten)> 500

BESTEL PER Activiteit;

Tafel 52. Resultaat van een verzoek bij Union en Order by

De operators voor snijpunten en uitzonderingen werken op dezelfde manier en komen overeen met de snijpunt- en verschilbewerkingen in relationele algebra. Voor vakgebied"verkoop"-lijsten van steden van kopers en verkopers zijn mogelijk niet hetzelfde, dus we kunnen een verscheidenheid aan problemen met deze lijsten oplossen, zoals weergegeven in de tabel. 53.

Tafel 53. Join-operatoren gebruiken voor query's van hetzelfde type

Naar blad. 25 gaf een voorbeeld van een verzoek waarbij ondergeschikten van Ivanov werden gefouilleerd. De vraag rijst hoe de hele hiërarchie van ondergeschikten moet worden opgebouwd, en niet alleen zijn directe ondergeschikten. In feite moeten we dezelfde Sheet-query toepassen. 25 tot het resultaat van het zelf uitvoeren, dan opnieuw, enz., totdat er steeds meer ondergeschikten zijn lage niveaus hiërarchie.

Construeer een hiërarchie van alle ondergeschikten van Ivanov in “lengte”(het verzoek staat op blad 41 , resultaat - Tafel 54 ).

Recursieve zoekopdrachten kunnen worden geïdentificeerd door trefwoord MET RECURSIEF. Om recursief te kunnen worden aangeroepen, moet de zoekopdracht daadwerkelijk een naam hebben (in dit geval - Prod). Het verzoek bestaat uit twee delen, gecombineerd met behulp van UNION. In het eerste deel - de bazen, in het tweede deel - hun ondergeschikten. De zoekopdracht vindt de eerste slaaf, vervolgens de eerste slaaf van de eerste, enzovoort. Deze zoekopdrachten worden lange zoekopdrachten genoemd.

Laken. 41. Recursieve zoekopdracht “in lengte”

(SELECTEER N, Naam

VAN Verkopers

WAAR Naam = 'Ivanov'

SELECTEER Verkopers. N, Verkoper.Naam

VAN Verkopers, Verkoop

SELECTEER * VAN Cont;

Tafel 54. Resultaat van een recursieve zoekopdracht “in lengte”

Als we eerst alle ondergeschikten van Ivanov moeten opsommen, daarna de ondergeschikten van Ivanov, enz., dan moeten we breedte-eerst recursieve zoekopdrachten gebruiken met behulp van de SEARCH BREADTH FIRST operator. De query stelt het iteratienummer in met behulp van de SET-operator.

Bouw een hiërarchie van alle ondergeschikten van Ivanov “in de breedte”(het verzoek staat op blad 42 , resultaat - Tafel 55 ).

Laken. 42. Recursieve breedte-eerste zoekopdracht

MET RECURSIEVE Cont (N, Naam, N_Head) als

(SELECTEER N, Naam

VAN Verkopers

WAAR Naam = 'Ivanov'

VAN Verkopers, Verkoop

WAAR Cont.N = Verkopers.N_Chief);

ZOEK EERST IN DE BREEDTE DOOR N_Chief, N

SET order_kolom

SELECTEER * VAN Cont

BESTEL OP order_kolom;

Tafel 55. Resultaat van de “breedte eerst”-vraag

Bij een recursieve query is een lussituatie mogelijk. Hoewel dit niet in ons voorbeeld voorkomt, kunnen we aannemen dat er situaties zijn waarin één team aan twee projecten werkt, en één persoon in het eerste project de baas is, en in het tweede een ondergeschikte, en omgekeerd, de tweede persoon in het eerste project is een ondergeschikte. En in de tweede - de baas. In dit geval worden eerdere recursieve zoekopdrachten in een lus weergegeven.

Bouw een hiërarchie van alle ondergeschikten van Ivanov, rekening houdend met de mogelijkheid van looping(Blad 43) .

Laken. 43. Recursieve zoekopdracht waarbij rekening wordt gehouden met looping

MET RECURSIEVE Cont (N, Naam) als

(SELECTEER N, Naam

VAN Verkopers

WAAR Naam = 'Ivanov'

SELECTEER Verkopers. N, Verkopers. Naam

VAN Verkopers, Verkoop

WAAR Cont.N = Verkopers.N_Chief);

SET cyclusmarkering op “Y” standaard “N”

GEBRUIKEN fietspad

SELECTEER * VAN Cont

D/Z 6. Voor het voorbeeld uit D/Z 4 bedenk je de volgende vragen:

1. Een enkele tabelquery die een aggregatiefunctie evalueert met behulp van de instructies 'waar', 'volgorde per'.
2. Query's voor het samenvoegen van meerdere tabellen met behulp van de Where-clausule, één tabel moet meerdere keren worden gebruikt in de query onder aliassen.
3. Zoekopdracht via rechts, links of volledige join.
4. Query met twee subquery's in waar en met clausules
5. Query's uitvoeren met een subquery waarbij gebruik wordt gemaakt van alles, enige of bestaande.
6. Een geneste query met een samenvoeging van twee tabellen (een van de join-variëteiten), indirect verbonden door een derde.
7. Query's van hetzelfde type combineren.
8. Recursieve zoekopdracht.

Zelftestvragen:

1. Wat is het verschil tussen het gebruik van de operatoren groeperen op, groeperen op samenvouwen, groeperen op samenvattende kubus, groeperen, organisator op, partitie?

2. Wat is het verschil tussen het gebruik van waar en het hebben van instructies?

3. Welke zoekopdracht komt overeen met...on match? waar exploitant?

4. Wat voor soort operatie relationele algebra komt overeen met de waar-instructie?

5. Met welke relationele algebra-operatie komt de join-operator overeen?

Deze resultaten zien er veel nuttiger uit. In de tabel ReachableFrom bevat het veld Bestemming nu alle steden die kunnen worden bereikt vanuit elke stad in het veld Bron van dezelfde tabel, met niet meer dan één tussenstop. Vervolgens verwerkt de recursie bij de volgende pas routes met twee tussenstops en blijft dit doen totdat alle bereikbare steden zijn gevonden.

Nadat de recursie is voltooid, selecteert de derde en laatste SELECT-instructie (die niet bij de recursie is betrokken) uit ReachableFrom alleen die steden die vanuit Portland kunnen worden bereikt. In dit voorbeeld kun je alle andere zes steden bereiken, en met een vrij klein aantal tussenstops. Je hoeft dus niet te haasten alsof je op stelten met een veer rijdt.

Als je goed naar de recursieve querycode kijkt, zie je dat deze er niet eenvoudiger uitziet dan zeven afzonderlijke query's. Dit verzoek heeft echter twee voordelen:

• na de lancering is interventie van buitenaf niet langer nodig;
• het werkt snel.

Als je kunt, stel je dan een echte luchtvaartmaatschappij voor die nog veel meer steden op haar routekaart heeft staan. En hoe meer mogelijke plaatsen afspraken, die meer voordeel van de recursieve methode.

Wat maakt een query recursief? Dat we de ReachableFrom-tabel definiëren op basis van zichzelf. Het recursieve deel van de definitie is de tweede SELECT-instructie, die zich onmiddellijk na de UNION bevindt. BereikbaarVanaf is een tijdelijke tabel die wordt gevuld met gegevens wanneer de recursie plaatsvindt. En deze vulling gaat door totdat alle mogelijke bestemmingen in ReachableFrom staan. Er zullen geen dubbele rijen in deze tabel voorkomen, omdat de UNION-operator deze niet doorlaat. Wanneer de recursie voltooid is, bevat de tabel ReachableFrom alle steden die vanuit elke startstad kunnen worden bereikt. De derde en laatste SELECT-instructie retourneert alleen die steden die u vanuit Portland kunt bereiken. Wij wensen u dan ook een prettige reis.

Waar kun je nog meer een recursieve query gebruiken?

Elk probleem dat kan worden weergegeven als een boomstructuur kan worden opgelost met behulp van een recursieve query. Een klassiek voorbeeld van hoe dergelijke zoekopdrachten in de industrie worden gebruikt, is de materiaalverwerking (het proces waarbij grondstoffen in een eindproduct worden omgezet). Stel dat uw bedrijf een nieuwe benzine-elektrische hybride auto op de markt brengt. Zo'n machine is samengesteld uit samenstellen (motor, batterijen, enz.), die op hun beurt bestaan ​​uit kleinere subsamenstellen (krukas, elektroden, enz.), en die - uit nog kleinere componenten.

Gegevens over al deze componenten worden opgeslagen in relationele database erg moeilijk - tenzij het natuurlijk recursie gebruikt. Recursie maakt het mogelijk om, beginnend met een hele machine, op welke manier dan ook tot het kleinste onderdeel te komen. Wilt u gegevens vinden over de montageschroef waarmee de terminal vastzit negatieve elektrode extra batterij? Dit is mogelijk - en zonder veel tijdsinvestering. Behandel deze SQL-taken kan de WITH RECURSIVE-structuur gebruiken (recursieve operator).

Bovendien is recursie heel natuurlijk in de 'wat als'-analyse. Wat gebeurt er bijvoorbeeld als Vannevar Airlines besluit te stoppen met vliegen van Portland naar Charlotte? Welke invloed heeft dit op vluchten naar steden die momenteel bereikbaar zijn vanuit Portland? Een recursieve query zal deze vragen onmiddellijk beantwoorden.