PostgreSQL: Dokumentasjon: 13: 7.8. Med Spørringer (Vanlige Tabelluttrykk)

grunnverdien av SELECT i WITH er å bryte ned kompliserte spørringer i enklere deler. 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 viser salgstall per produkt i bare de beste salgsområdene. WITH – klausulen definerer to hjelpesetninger kalt regional_sales og top_regions, der utdataene fra regional_sales brukes i top_regions og utdataene fra top_regions brukes i den primære SELECT – spørringen. Dette eksemplet kunne vært skrevet uten WITH, men vi ville ha trengt to nivåer av nestet under – SELECT s. det er litt lettere å følge denne måten.

den valgfrie RECURSIVE modifikatoren endres WITH fra en ren syntaktisk bekvemmelighet til en funksjon som oppnår ting som ikke ellers er mulig i standard SQL. Ved hjelp av RECURSIVE kan en WITH spørring referere til sin egen utgang. Et veldig enkelt eksempel er denne spørringen for å summere heltallene 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 formen for en rekursiv WITH spørring er alltid en ikke-rekursiv term, deretter UNION (eller UNION ALL), deretter en rekursiv term, hvor bare rekursiv term kan inneholde en referanse til spørringens egen utgang. En slik spørring utføres som følger:

i eksemplet ovenfor har arbeidsbordet bare en enkelt rad i hvert trinn, og det tar verdiene fra 1 til 100 i påfølgende trinn. I det 100. trinnet er det ingen utdata på grunn av WHERE – klausulen, og spørringen avsluttes derfor.

Rekursive spørringer brukes vanligvis til å håndtere hierarkiske eller trestrukturerte data. Et nyttig eksempel er denne spørringen for å finne alle direkte og indirekte deldeler av et produkt, gitt bare en tabell som viser umiddelbare inneslutninger:

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 arbeider med rekursive spørringer, er det viktig å være sikker på at den rekursive delen av spørringen til slutt ikke returnerer noen tupler, ellers vil spørringen sløyfe på ubestemt tid. Noen ganger kan du bruke UNION i stedet for UNION ALL ved å kaste bort rader som dupliserer tidligere utdatarader. Men ofte involverer en syklus ikke utdatarader som er helt dupliserte: det kan være nødvendig å sjekke bare ett eller noen få felt for å se om det samme punktet er nådd før. Standardmetoden for å håndtere slike situasjoner er å beregne en rekke allerede besøkte verdier. Vurder for eksempel følgende spørring som søker i en tabell graph ved hjelp av 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 spørringen vil sløyfe hvis relasjonene link inneholder sykluser. Fordi vi krever en” dybde ” – utgang, bare å endre UNION ALL til UNION ville ikke eliminere looping. I stedet må vi gjenkjenne om vi har nådd samme rad igjen mens du følger en bestemt bane av lenker. Vi legger til to kolonner path og cycle i loop-utsatt spørring:

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 fra å hindre sykluser, er matriseverdien ofte nyttig i seg selv som representerer “banen” tatt for å nå en bestemt rad.

i det generelle tilfellet der mer enn ett felt må kontrolleres for å gjenkjenne en syklus, bruk en rekke rader. For eksempel, hvis vi trengte å sammenligne felt 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 nyttig triks for å teste spørringer når du ikke er sikker på om de kan sløyfe, er å plassere en LIMIT i den overordnede spørringen. For eksempel vil denne spørringen sløyfe for alltid uten LIMIT:

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

Dette fungerer fordi PostgreSQL implementering evaluerer bare så mange rader av en WITH spørring som faktisk hentet av den overordnede spørringen. Bruk av dette trikset i produksjon anbefales ikke, fordi andre systemer kan fungere annerledes. Dessuten vil det vanligvis ikke fungere hvis du gjør den ytre spørringen sortere rekursive spørringen resultater eller bli med dem til en annen tabell, fordi i slike tilfeller den ytre spørringen vil vanligvis prøve å hente alle WITH spørringen utdata uansett.

en nyttig egenskap med WITH spørringer er at de vanligvis evalueres bare en gang per kjøring av den overordnede spørringen, selv om de refereres til mer enn en gang av den overordnede spørringen eller søsken WITH spørringer. Dermed kan dyre beregninger som trengs på flere steder plasseres i en WITH spørring for å unngå overflødig arbeid. En annen mulig anvendelse er å hindre uønskede flere evalueringer av funksjoner med bivirkninger. Den andre siden av denne mynten er imidlertid at optimizer ikke er i stand til å presse restriksjoner fra den overordnede spørringen ned i en multiply-referert WITH spørring, siden det kan påvirke alle bruksområder av WITH spørringen utdata når det skal påvirke bare en. Den multipliser refererte WITH – spørringen vil bli evaluert som skrevet, uten undertrykkelse av rader som den overordnede spørringen kan forkaste etterpå. (Men som nevnt ovenfor kan evalueringen stoppe tidlig hvis referansen (e) til spørringen bare krever et begrenset antall rader.)

men hvis en WITH spørring er ikke-rekursiv og bivirkning fri (det vil si at den er en SELECT som ikke inneholder flyktige funksjoner), kan den brettes inn i overordnet spørring, slik at felles optimalisering av de to spørringsnivåene. Som standard skjer dette hvis den overordnede spørringen refererer til WITH – spørringen bare en gang, men ikke hvis den refererer til WITH – spørringen mer enn en gang. Du kan overstyre avgjørelsen ved å angi MATERIALIZED for å tvinge separat beregning av WITH – spørringen, eller ved å angi NOT MATERIALIZED for å tvinge den til å slås sammen i den overordnede spørringen. Det sistnevnte valget risikerer å duplisere beregning av WITH – spørringen, men det kan fortsatt gi en nettobesparelse hvis hver bruk av WITH – spørringen bare trenger en liten del av WITH – spørringens fullstendige utdata.

et enkelt eksempel på disse reglene er

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

denne WITH spørringen vil bli brettet, og produserer den samme utførelsesplanen som

SELECT * FROM big_table WHERE key = 123;

spesielt, hvis det er en indeks på key, vil den sannsynligvis bli brukt til å hente bare radene som har key = 123. På den annen side, 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;

WITH spørringen vil bli materialisert, produsere en midlertidig kopi av big_table som deretter slås sammen med seg selv — uten fordel av noen indeks. Denne spørringen vil bli utført mye mer effektivt hvis den skrives 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;

, slik at begrensningene for den overordnede spørringen kan brukes direkte på skanninger av big_table.

et eksempel der NOT MATERIALIZED kan være uønsket er

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;

her sikrer materialisering av WITH – spørringen at very_expensive_function bare evalueres en gang per tabellrad, ikke to ganger.

eksemplene ovenfor viser bare at WITH brukes med SELECT , men det kan festes på samme måte til INSERT, UPDATE eller DELETE. I hvert tilfelle gir det effektivt midlertidige bord (er) som kan henvises til i hovedkommandoen.