PostgreSQL: Dokumentation: 13: 7.8. Med frågor (Vanliga Tabelluttryck)

grundvärdet för SELECT i WITH är att bryta ner 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 – satsen definierar två hjälpsatser med namnet regional_sales och top_regions, där utmatningen av regional_sales används i top_regions och utmatningen av top_regions används i den primära SELECT – frågan. Det här exemplet kunde ha skrivits utan WITH, men vi hade behövt två nivåer av kapslade sub-SELECT s. det är lite lättare att följa på detta sätt.

den valfria RECURSIVE modifieraren ändrar WITH från enbart syntaktisk bekvämlighet till en funktion som åstadkommer saker som inte annars är möjliga i standard SQL. Med hjälp av RECURSIVE kan en WITH – fråga hänvisa till sin egen utgång. Ett mycket enkelt exempel är denna fråga för 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 av 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 egen utgång. En sådan fråga utförs enligt följande:

i exemplet ovan har arbetsbordet bara en enda rad i varje steg, och det tar värdena från 1 till 100 i successiva steg. I det 100: e steget finns det ingen utgång på grund av WHERE – klausulen, och så avslutas frågan.

rekursiva frågor används vanligtvis för att hantera hierarkiska eller trädstrukturerade data. Ett användbart exempel är den här frågan för att hitta alla direkta och indirekta deldelar av en produkt, med endast en tabell som visar omedelbara inklusioner:

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 du arbetar med rekursiva frågor är det viktigt att vara säker på att den rekursiva delen av frågan så småningom kommer att returnera inga tupler, annars kommer frågan att slinga på obestämd tid. Ibland kan man använda UNION istället för UNION ALL genom att kassera rader som duplicerar tidigare utmatningsrader. Men ofta innebär en cykel inte utmatningsrader som är helt dubbla: det kan vara nödvändigt att bara kontrollera ett eller några fält för att se om samma punkt har uppnå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 en tabell graph med ett link – fä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;

den här frågan kommer att loopas om link – relationerna innehåller cykler. Eftersom vi behöver en” djup ” – utgång, skulle det bara inte eliminera looping att bara ändra UNION ALL till UNION. Istället måste vi känna igen om vi har nått samma rad igen när vi följer en viss länkväg. Vi lägger till två kolumner path och cycle till den slingbenä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älv som representerar den “sökväg” som tagits för att nå en viss rad.

i det allmänna fallet där mer än ett fält måste kontrolleras för att känna igen en cykel, använd en rad rader. Till exempel, om vi behövde jämföra fält 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 trick för att testa frågor när du inte är säker på om de kan slinga är att placera en LIMIT i den överordnade frågan. Till exempel skulle denna fråga slinga för alltid 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 implementering utvärderar bara så många rader av en WITH fråga som faktiskt hämtas av den överordnade frågan. Att använda detta trick i produktion rekommenderas inte, eftersom andra system kan fungera annorlunda. Också, det brukar inte fungera om du gör den yttre frågan Sortera rekursiva frågans resultat eller ansluta dem till någon annan tabell, eftersom i sådana fall den yttre frågan brukar försöka hämta alla WITH frågans utgång ändå.

en användbar egenskap hos WITH frågor är att de normalt utvärderas endast en gång per körning av den överordnade frågan, även om de hänvisas till mer än en gång av den överordnade frågan eller syskon WITH frågor. Således kan dyra beräkningar som behövs på flera ställen placeras inom en WITH – Fråga för att undvika överflödigt arbete. En annan möjlig tillämpning är att förhindra oönskade flera utvärderingar av funktioner med biverkningar. Den andra sidan av detta mynt är dock att optimeraren inte kan driva begränsningar från den överordnade frågan ner till en multiplicerad refererad WITH-fråga, eftersom det kan påverka alla användningar av WITH – frågans utmatning när den bara ska påverka en. Den multiplicerade refererade WITH – frågan kommer att utvärderas som skriven, utan att undertrycka rader som den överordnade frågan kan kassera efteråt. (Men som nämnts ovan kan utvärderingen sluta tidigt om referensen(erna) till frågan kräver endast ett begränsat antal rader.)

men om en WITH-fråga Är icke-rekursiv och bieffektfri (det vill säga det är en SELECT som inte innehåller några flyktiga funktioner) kan den vikas in i föräldrafrågan, vilket möjliggör gemensam optimering av de två frågenivåerna. Som standard händer detta om den överordnade frågan refererar till WITH – frågan bara en gång, men inte om den refererar till WITH – frågan mer än en gång. Du kan åsidosätta det beslutet genom att ange MATERIALIZED för att tvinga separat beräkning av WITH – frågan, eller genom att ange NOT MATERIALIZED för att tvinga den att slås samman till den överordnade frågan. Det senare valet riskerar att duplicera beräkningen av WITH – frågan, men det kan fortfarande ge en nettobesparing om varje användning av WITH – frågan bara behöver en liten del av WITH – frågans fulla utgång.

ett enkelt exempel på dessa regler är

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

denna WITH fråga kommer att vikas, vilket ger samma exekveringsplan som

SELECT * FROM big_table WHERE key = 123;

i synnerhet, om det finns ett index på key, kommer det förmodligen att användas för att hämta bara raderna med key = 123. Å andra sidan, i

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

kommer WITH — frågan att materialiseras, vilket ger en tillfällig kopia av big_table som sedan förenas med sig själv-utan fördel av något index. Denna fråga kommer att utföras mycket mer effektivt om den skrivs som

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;

så att den överordnade frågans begränsningar kan tillämpas direkt på skanningar av big_table.

ett exempel där NOT MATERIALIZED kan vara oönskat är

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;

här säkerställer materialisering av WITH – frågan att very_expensive_function utvärderas endast en gång per tabellrad, inte två gånger.

exemplen ovan visar bara att WITH används med SELECT, men det kan bifogas på samma sätt till INSERT, UPDATE eller DELETE. I varje fall ger det effektivt tillfälliga tabeller som kan hänvisas till i huvudkommandot.