7.8. WITH-förfrågningar (gemensamma tabelluttryck)

7.8.1. SELECT i WITH

Det grundläggande värdet av SELECT i WITH är att dela upp komplicerade frågor i enklare delar. Ett exempel är:

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;

som visar försäljningssummor per produkt i endast de bästa försäljningsregionerna. WITH-klausulen definierar två extra uttalanden som heter regional_sales och top_regions, där resultatet av regional_sales används i top_regions och resultatet av top_regions används i den primära SELECT-frågan. Det här exemplet hade kunnat skrivas utan WITH, men då hade vi behövt två nivåer av inbäddade sub-SELECTs. Det är lite lättare att följa på detta sätt.

Den valfria modifikationen RECURSIVE förändrar WITH från en ren syntaktisk bekvämlighet till en funktion som åstadkommer saker som annars inte är möjliga i standard-SQL. Med hjälp av RECURSIVE kan en WITH-fråga hänvisa till sitt eget resultat. Ett mycket enkelt exempel är den här frågan om att summera heltalen från 1 till 100:

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

Den allmänna formen för en rekursiv WITH-fråga är alltid en icke-rekursiv term, sedan UNION (eller UNION ALL), sedan en rekursiv term, där endast den rekursiva termen kan innehålla en hänvisning till frågans eget resultat. En sådan fråga utförs på följande sätt:

I exemplet ovan har arbetstabellen bara en enda rad i varje steg, och den tar värdena från 1 till 100 i successiva steg. I det 100:e steget finns det ingen utdata på grund av WHERE-klausulen, och därför avslutas frågan.

Rekursiva frågor används vanligtvis för att hantera hierarkiska eller trädstrukturerade data. Ett användbart exempel är denna fråga för att hitta alla direkta och indirekta underdelar till en produkt, med endast en tabell som visar omedelbara inkluderingar:

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

När man arbetar med rekursiva frågor är det viktigt att vara säker på att den rekursiva delen av frågan till slut inte returnerar några tupler, annars kommer frågan att slinga sig på obestämd tid. Ibland kan man uppnå detta genom att använda UNION istället för UNION ALL genom att kasta bort rader som duplicerar tidigare utdatarader. Ofta innebär en cykel dock inte att utdataraderna är helt dubbletter: det kan vara nödvändigt att bara kontrollera ett eller några få fält för att se om samma punkt har nåtts tidigare. Standardmetoden för att hantera sådana situationer är att beräkna en array av de redan besökta värdena. Tänk till exempel på följande fråga som söker i ett tabelldiagram med hjälp av ett länkfält:

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;

Denna fråga kommer att gå i loop om länkförhållandena innehåller cykler. Eftersom vi kräver en ”djup”-utgång skulle det inte räcka med att ändra UNION ALL till UNION för att eliminera slingan. Istället måste vi känna igen om vi har nått samma rad igen när vi följer en viss väg av länkar. Vi lägger till två kolumner path och cycle till den loop-benägna frågan:

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;

Bortsett från att förhindra cykler är arrayvärdet ofta användbart i sig självt eftersom det representerar den ”väg” som tagits för att nå en viss rad.

I det generella fallet där mer än ett fält måste kontrolleras för att känna igen en cykel, använd en array av rader. Om vi till exempel skulle behöva jämföra fälten f1 och 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;

Ett användbart knep för att testa frågor när du inte är säker på om de kan gå i loop är att placera en LIMIT i den överordnade frågan. Den här frågan skulle till exempel gå i en evig slinga utan LIMIT:

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

Detta fungerar eftersom PostgreSQL:s implementering endast utvärderar så många rader i en WITH-fråga som faktiskt hämtas av den överordnade frågan. Att använda det här tricket i produktionen rekommenderas inte, eftersom andra system kan fungera annorlunda. Dessutom fungerar det vanligtvis inte om du låter den yttre frågan sortera den rekursiva frågans resultat eller koppla dem till någon annan tabell, eftersom den yttre frågan i sådana fall vanligtvis kommer att försöka hämta hela WITH-frågans utdata ändå.

En användbar egenskap hos WITH-frågor är att de utvärderas endast en gång per exekvering av den överordnade frågan, även om de refereras till mer än en gång av den överordnade frågan eller syskon WITH-frågor. Dyra beräkningar som behövs på flera ställen kan alltså placeras i en WITH-fråga för att undvika överflödigt arbete. En annan möjlig tillämpning är att förhindra oönskade multipla utvärderingar av funktioner med sidoeffekter. Den andra sidan av myntet är dock att optimeraren har sämre möjligheter att trycka in begränsningar från den överordnade frågan i en WITH-fråga än i en vanlig underfråga. WITH-frågan kommer i allmänhet att utvärderas som den är skriven, utan att rader som den överordnade frågan kanske kastar bort i efterhand undertrycks. (Men som nämnts ovan kan utvärderingen sluta tidigt om referensen/referenserna till frågan endast kräver ett begränsat antal rader.)

Exemplen ovan visar endast att WITH används med SELECT, men det kan kopplas på samma sätt till INSERT, UPDATE eller DELETE. I varje fall ger det effektivt tillfälliga tabeller som kan refereras till i huvudkommandot.