7.8. WITH-forespørgsler (fælles tabeludtryk)

7.8.1. SELECT i WITH

Den grundlæggende værdi af SELECT i WITH er at nedbryde komplicerede forespørgsler i enklere dele. Et eksempel er:

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 kun viser salgstotaler pr. produkt i de øverste salgsregioner. WITH-klausulen definerer to hjælpeangivelser ved navn regional_sales og top_regions, hvor resultatet af regional_sales bruges i top_regions, og resultatet af top_regions bruges i den primære SELECT-forespørgsel. Dette eksempel kunne have været skrevet uden WITH, men så ville vi have haft brug for to niveauer af indlejrede sub-SELECTs. Det er lidt nemmere at følge med på denne måde.

Den valgfrie RECURSIVE-modifikator ændrer WITH fra en ren syntaktisk bekvemmelighed til en funktion, der udretter ting, som ellers ikke er mulige i standard SQL. Ved hjælp af RECURSIVE kan en WITH-forespørgsel henvise til sit eget output. Et meget simpelt eksempel er denne forespørgsel om at summere de hele tal fra 1 til 100:

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

Den generelle form for en rekursiv WITH-forespørgsel er altid et ikke-rekursivt udtryk, derefter UNION (eller UNION ALL), derefter et rekursivt udtryk, hvor kun det rekursive udtryk kan indeholde en henvisning til forespørgslens eget output. En sådan forespørgsel udføres på følgende måde:

I ovenstående eksempel har arbejdstabellen kun en enkelt række i hvert trin, og den får værdierne fra 1 til 100 i de på hinanden følgende trin. I det 100. trin er der ikke noget output på grund af WHERE-klausulen, og derfor afsluttes forespørgslen.

Rekursive forespørgsler bruges typisk til at håndtere hierarkiske eller træstrukturerede data. Et nyttigt eksempel er denne forespørgsel til at finde alle de direkte og indirekte underdele af et produkt, idet der kun er givet en tabel, der viser de umiddelbare 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 man arbejder med rekursive forespørgsler, er det vigtigt at være sikker på, at den rekursive del af forespørgslen i sidste ende ikke returnerer nogen tupler, da forespørgslen ellers vil løbe i en uendelig løkke. Nogle gange kan man opnå dette ved at bruge UNION i stedet for UNION ALL ved at kassere rækker, der duplikerer tidligere outputrækker. Ofte omfatter en cyklus imidlertid ikke outputrækker, der er fuldstændig dublerede: det kan være nødvendigt at kontrollere blot et eller få felter for at se, om det samme punkt er blevet nået før. Standardmetoden til håndtering af sådanne situationer er at beregne et array af de allerede besøgte værdier. Overvej f.eks. følgende forespørgsel, der søger i en tabelgraf ved hjælp af et link-felt:

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;

Denne forespørgsel vil gå i loop, hvis link-relationerne indeholder cyklusser. Da vi har brug for et “dybde”-output, vil en ændring af UNION ALL til UNION ikke fjerne sløjfen. I stedet er vi nødt til at erkende, om vi har nået den samme række igen, mens vi følger en bestemt sti af links. Vi tilføjer to kolonner path og cycle til den sløjfede forespørgsel:

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;

Ud over at forhindre cyklusser er array-værdien ofte nyttig i sig selv som repræsentant for den “sti”, der er taget for at nå en bestemt række.

I det generelle tilfælde, hvor mere end ét felt skal kontrolleres for at genkende en cyklus, skal du bruge et array af rækker. Hvis vi f.eks. havde brug for at sammenligne felterne f1 og 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;

Et nyttigt trick til at afprøve forespørgsler, når du ikke er sikker på, om de kan lave en løkke, er at placere en LIMIT i den overordnede forespørgsel. Denne forespørgsel ville f.eks. gå i loop for evigt uden LIMIT:

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

Dette virker, fordi PostgreSQL’s implementering kun evaluerer så mange rækker i en WITH-forespørgsel, som der faktisk hentes af den overordnede forespørgsel. Det anbefales ikke at bruge dette trick i produktionen, da andre systemer kan fungere anderledes. Det vil normalt heller ikke virke, hvis du får den ydre forespørgsel til at sortere den rekursive forespørgsels resultater eller sammenføje dem til en anden tabel, fordi den ydre forespørgsel i sådanne tilfælde normalt vil forsøge at hente hele WITH-forespørgslens output alligevel.

En nyttig egenskab ved WITH-forespørgsler er, at de kun evalueres én gang pr. udførelse af den overordnede forespørgsel, selv om der henvises til dem mere end én gang af den overordnede forespørgsel eller søskende WITH-forespørgsler. Dyre beregninger, der er nødvendige flere steder, kan således placeres i en WITH-forespørgsel for at undgå overflødigt arbejde. En anden mulig anvendelse er at forhindre uønsket flere evalueringer af funktioner med sideeffekter. Bagsiden af denne mønt er imidlertid, at optimeringsværktøjet er mindre i stand til at skubbe begrænsninger fra den overordnede forespørgsel ned i en WITH-forespørgsel end i en almindelig underforespørgsel. WITH-forespørgslen vil generelt blive evalueret som skrevet, uden undertrykkelse af rækker, som den overordnede forespørgsel måske kasserer bagefter. (Men som nævnt ovenfor kan evalueringen stoppe tidligt, hvis referencen/referencerne til forespørgslen kun kræver et begrænset antal rækker.)

Eksemplerne ovenfor viser kun, at WITH bruges sammen med SELECT, men det kan knyttes på samme måde til INSERT, UPDATE eller DELETE. I hvert tilfælde giver den effektivt en eller flere midlertidige tabeller, som der kan henvises til i hovedkommandoen.