7.8. WITH Queries (Gemeenschappelijke Tabel Uitdrukkingen)

7.8.1. SELECT in WITH

De basiswaarde van SELECT in WITH is het opdelen van ingewikkelde queries in eenvoudiger 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;

waarmee per product verkooptotalen worden weergegeven in alleen de top verkoopregio’s. De WITH clausule definieert twee hulpstatements genaamd 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 ook zonder WITH geschreven kunnen worden, maar dan hadden we twee niveaus van geneste sub-SELECTs nodig gehad. Het is iets gemakkelijker te volgen op deze manier.

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 naar zijn eigen uitvoer verwijzen. Een heel eenvoudig voorbeeld is deze query om de gehele getallen van 1 tot 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 kan bevatten naar de eigen uitvoer van de query. Zo’n query wordt als volgt uitgevoerd:

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

Recursieve query’s worden meestal gebruikt om met hiërarchische of boomgestructureerde gegevens om te gaan. Een nuttig voorbeeld is deze query om alle directe en indirecte subonderdelen van een product te vinden, gegeven alleen een tabel die onmiddellijke insluitingen laat zien:

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 tupels zal teruggeven, anders zal de query oneindig lussen. Soms kan dit worden bereikt door UNION te gebruiken in plaats van UNION ALL, door rijen weg te gooien die eerdere uitvoerrijen dupliceren. Vaak echter omvat een cyclus geen uitvoerrijen die volledig dubbel zijn: het kan nodig zijn slechts één of enkele velden te controleren om te zien of hetzelfde punt reeds eerder is bereikt. De standaardmethode om met dergelijke situaties om te gaan is het berekenen van een array van de reeds bezochte waarden. Neem bijvoorbeeld de volgende query die een tabelgrafiek 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 zal een lus maken als de link-relaties cycli bevatten. Omdat we een “diepte” uitvoer nodig hebben, zou het veranderen van UNION ALL in UNION de looping niet elimineren. In plaats daarvan moeten wij herkennen of wij dezelfde rij weer hebben bereikt terwijl wij een bepaald pad van links volgen. We voegen twee kolommen pad en cyclus toe aan de loop-gevoelige 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;

Naast het voorkomen van cycli, is de array waarde vaak nuttig op zichzelf als weergave van het “pad” genomen om een bepaalde rij te bereiken.

In het algemene geval waar meer dan een veld moet worden gecontroleerd om een cyclus te herkennen, gebruik dan een array van rijen. Bijvoorbeeld, als we de velden f1 en f2 moeten vergelijken:

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 queries wanneer u niet zeker weet of ze een lus kunnen maken, is het plaatsen van een LIMIT in de bovenliggende query. Bijvoorbeeld, deze query zou zonder de LIMIT eeuwig doorlussen:

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

Dit werkt omdat PostgreSQL’s implementatie slechts zoveel rijen van een WITH query evalueert als er daadwerkelijk door de bovenliggende query worden opgehaald. Het gebruik van deze truc in productie is niet aan te raden, omdat andere systemen anders zouden kunnen werken. Ook zal het meestal niet werken als je de buitenste query de resultaten van de recursieve query laat sorteren of aan een andere tabel laat koppelen, omdat in dergelijke gevallen de buitenste query meestal toch zal proberen om alle uitvoer van de WITH query op te halen.

Een handige eigenschap van WITH queries is dat ze slechts eenmaal per uitvoering van de bovenliggende query worden geëvalueerd, zelfs als er meer dan eens naar verwezen wordt door de bovenliggende query of sibling WITH queries. Zo kunnen dure berekeningen die op meerdere plaatsen nodig zijn, in een WITH query worden ondergebracht om overbodig werk te voorkomen. Een andere mogelijke toepassing is het voorkomen van ongewenste meervoudige evaluaties van functies met neveneffecten. De keerzijde van deze medaille is echter dat de optimizer minder goed in staat is om restricties van de parent query naar beneden te duwen in een WITH query dan een gewone sub-query. De WITH query zal over het algemeen worden geëvalueerd zoals geschreven, zonder onderdrukking van rijen die de bovenliggende query achteraf zou kunnen weggooien. (Maar, zoals hierboven vermeld, de evaluatie kan vroegtijdig stoppen als de verwijzing(en) naar de query slechts een beperkt aantal rijen nodig hebben.)

De voorbeelden hierboven laten alleen zien hoe WITH met SELECT wordt gebruikt, maar het kan op dezelfde manier aan INSERT, UPDATE, of DELETE worden gekoppeld. In elk geval levert het effectief tijdelijke tabel(len) op waarnaar in het hoofdcommando kan worden verwezen.