PostgreSQL: Documentatie: 13: 7.8. Met Queries (gemeenschappelijke Tabeluitdrukkingen)

de basiswaarde van SELECT in WITH is het opsplitsen van ingewikkelde query ‘ s in eenvoudigere delen. Een voorbeeld is:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

die toont per product omzet totalen in alleen de top verkoop regio ‘ s. De WITH – clausule definieert twee hulpcommando ‘ s met de naam regional_sales en top_regions, waarbij de uitvoer van regional_sales wordt gebruikt in top_regions en de uitvoer van top_regions wordt gebruikt in de primaire SELECT – query. Dit voorbeeld had geschreven kunnen worden zonder WITH, maar we hadden twee niveaus van geneste sub-SELECTs nodig. het is een beetje makkelijker om deze weg te volgen.

de optionele RECURSIVE modifier verandert WITH van een louter syntactisch gemak in een functie die dingen bereikt die anders niet mogelijk zijn in standaard SQL. Met RECURSIVE kan een WITH query verwijzen naar zijn eigen uitvoer. Een heel eenvoudig voorbeeld is deze query om de gehele getallen van 1 tot en met 100 op te tellen:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

de algemene vorm van een recursieve WITH query is altijd een niet-recursieve term, dan UNION (of UNION ALL), dan een recursieve term, waarbij alleen de recursieve term een verwijzing naar de eigen uitvoer van de query kan bevatten. Een dergelijke query wordt als volgt uitgevoerd:

in het voorbeeld hierboven heeft de werktabel slechts een enkele rij in elke stap, en neemt de waarden van 1 tot en met 100 in opeenvolgende stappen. In de 100ste stap is er geen uitvoer vanwege de WHERE clausule, en dus eindigt de query.

recursieve queries worden meestal gebruikt om hiërarchische of boomstructuur gegevens te verwerken. Een handig voorbeeld is deze zoekopdracht om alle directe en indirecte onderdelen van een product te vinden, alleen gegeven een tabel die onmiddellijke insluitingen toont:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_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(quantity) as total_quantityFROM included_partsGROUP BY sub_part

bij het werken met recursieve query ‘ s is het belangrijk om er zeker van te zijn dat het recursieve deel van de query uiteindelijk geen tuples retourneert, of anders zal de query voor onbepaalde tijd doorlopen. Soms kan het gebruik van UNION in plaats van UNION ALL dit bereiken door rijen die vorige uitvoerrijen dupliceren, weg te gooien. In een cyclus worden echter vaak geen rijen uitgevoerd die volledig dupliceren: het kan nodig zijn om slechts één of een paar velden te controleren om te zien of hetzelfde punt al eerder is bereikt. De standaardmethode voor het omgaan met dergelijke situaties is het berekenen van een array van de reeds bezochte waarden. Overweeg bijvoorbeeld de volgende query die een tabel graph doorzoekt met een link – veld:

WITH RECURSIVE 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;

deze query wordt herhaald als de link relaties cycli bevatten. Omdat we een “depth” uitvoer nodig hebben, zou het veranderen van UNION ALL naar UNION de looping niet elimineren. In plaats daarvan moeten we herkennen of we dezelfde rij opnieuw hebben bereikt terwijl we een bepaald pad van links volgen. We voegen twee kolommen path en cycle toe aan de lusgevoelige query:

WITH RECURSIVE search_graph(id, link, data, depth, 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;

afgezien van het voorkomen van cycli, is de array-waarde vaak op zichzelf nuttig als representatie van het “pad” dat wordt genomen om een bepaalde rij te bereiken.

in het algemene geval waarin meer dan één veld moet worden gecontroleerd om een cyclus te herkennen, gebruik een reeks rijen. Bijvoorbeeld, als we velden f1 moesten vergelijken en f2:

WITH RECURSIVE search_graph(id, link, data, depth, 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 || 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;

een handige truc voor het testen van query ‘ s als u niet zeker weet of ze kunnen lus is het plaatsen van een LIMIT in de bovenliggende query. Bijvoorbeeld, deze query zou lus voor altijd zonder de LIMIT:

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

dit werkt omdat de implementatie van PostgreSQL slechts zoveel rijen van een WITH query evalueert als daadwerkelijk door de bovenliggende query worden opgehaald. Het gebruik van deze truc in de productie wordt niet aanbevolen, omdat andere systemen anders kunnen werken. Ook zal het meestal niet werken als je de outer query de resultaten van de recursieve query laat sorteren of aan een andere tabel toevoegt, omdat in dergelijke gevallen de outer query meestal probeert alle uitvoer van de WITH query op te halen.

een nuttige eigenschap van WITH queries is dat ze normaal gesproken slechts eenmaal worden geëvalueerd per uitvoering van de ouder query, zelfs als ze meer dan eens worden verwezen door de ouder query of broer of zus WITH queries. Dure berekeningen die op meerdere plaatsen nodig zijn, kunnen dus binnen een WITH – zoekopdracht geplaatst worden om overbodig werk te voorkomen. Een andere mogelijke toepassing is om ongewenste meervoudige evaluaties van functies met bijwerkingen te voorkomen. Echter, de andere kant van deze medaille is dat de optimizer niet in staat is om beperkingen van de ouder query naar beneden te duwen in een multi-reference WITH query, omdat dat alle toepassingen van de uitvoer van de WITH query zou kunnen beà nvloeden wanneer het slechts één zou moeten beà nvloeden. De multiply-reference WITH query zal worden geëvalueerd als geschreven, zonder onderdrukking van rijen die de bovenliggende query later zou kunnen verwerpen. (Maar, zoals hierboven vermeld, evaluatie kan vroeg stoppen als de verwijzing (s) naar de query vereisen slechts een beperkt aantal rijen.)

echter, als een WITH query niet-recursief en neveneffect-vrij is (dat wil zeggen, het is een SELECT die geen vluchtige functies bevat) dan kan het worden gevouwen in de ouder query, waardoor gezamenlijke optimalisatie van de twee query niveaus. Standaard gebeurt dit als de bovenliggende query slechts één keer verwijst naar de WITH query, maar niet als het meer dan één keer verwijst naar de WITH query. U kunt die beslissing overschrijven door MATERIALIZED op te geven om een aparte berekening van de WITH – query te forceren, of door NOT MATERIALIZED op te geven om te forceren dat deze wordt samengevoegd met de bovenliggende query. Deze laatste keuze riskeert een dubbele berekening van de WITH query, maar het kan nog steeds een netto besparing opleveren als elk gebruik van de WITH query slechts een klein deel van de volledige uitvoer van de WITH query nodig heeft.

een eenvoudig voorbeeld van deze regels is

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

deze WITH query zal worden gevouwen en hetzelfde uitvoeringsplan produceren als

SELECT * FROM big_table WHERE key = 123;

In het bijzonder, als er een index op key is, zal deze waarschijnlijk worden gebruikt om alleen de rijen met key = 123op te halen. Aan de andere kant wordt in

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

de WITH query gematerialiseerd, waarbij een tijdelijke kopie van big_table wordt geproduceerd die vervolgens met zichzelf wordt samengevoegd — zonder voordeel van een index. Deze query zal veel efficiënter worden uitgevoerd als

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

, zodat de beperkingen van de ouder query direct kunnen worden toegepast op scans van big_table.

een voorbeeld waarin NOT MATERIALIZED ongewenst kan zijn is

WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

hier zorgt materialisatie van de WITH query ervoor dat very_expensive_function slechts eenmaal per tabelrij wordt geëvalueerd, niet tweemaal.

de voorbeelden hierboven tonen alleen dat WITH wordt gebruikt met SELECT, maar het kan op dezelfde manier worden bevestigd aan INSERT, UPDATE of DELETE. In elk geval biedt het effectief tijdelijke tabel (s) die kunnen worden verwezen in het hoofdcommando.