Dokumentation: 13: 7.8. Med forespørgsler (almindelige Tabeludtryk)

grundværdien af SELECT i WITH er at opdele 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 viser pr-produkt salg totaler i kun de øverste salgsområder. Klausulen WITH definerer to hjælpesætninger med navnet regional_sales og top_regions, hvor output på regional_sales bruges i top_regions, og output på top_regions bruges i den primære SELECT forespørgsel. Dette eksempel kunne have været skrevet uden WITH, men vi ville have brug for to niveauer af indlejrede sub-SELECTs. det er lidt lettere at følge denne vej.

den valgfri RECURSIVE modifikator ændrer WITH fra en simpel syntaktisk bekvemmelighed til en funktion, der udfører ting, der ellers ikke er mulige i standardkvm. Ved hjælp af RECURSIVE kan en WITH forespørgsel henvise til sin egen output. Et meget simpelt eksempel er denne forespørgsel for at opsummere heltalene 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 egen output. En sådan forespørgsel udføres som følger:

i eksemplet ovenfor har arbejdsbordet kun en enkelt række i hvert trin, og det tager værdierne fra 1 til 100 i successive trin. I det 100. trin er der ingen output på grund af WHERE – klausulen, og så 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, kun givet en tabel, der viser øjeblikkelige indeslutninger:

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 arbejder med rekursive forespørgsler, er det vigtigt at være sikker på, at den rekursive del af forespørgslen til sidst ikke returnerer nogen tupler, ellers vil forespørgslen løbe på ubestemt tid. Nogle gange kan du bruge UNION i stedet for UNION ALL ved at kassere rækker, der duplikerer tidligere outputrækker. Imidlertid involverer en cyklus ofte ikke outputrækker, der er helt duplikerede: det kan være nødvendigt at kontrollere kun et eller et par felter for at se, om det samme punkt er nået før. Standardmetoden til håndtering af sådanne situationer er at beregne en række af de allerede besøgte værdier. Overvej f. eks. følgende forespørgsel, der søger i en tabel graph 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 løber, hvis link – relationerne indeholder cyklusser. Fordi vi kræver en” dybde ” output, bare ændre UNION ALL til UNION ville ikke fjerne looping. 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 med links. Vi tilføjer to kolonner path og cycle til den loop-tilbøjelige 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;

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

i det generelle tilfælde, hvor mere end et felt skal kontrolleres for at genkende en cyklus, skal du bruge en række rækker. For eksempel, hvis vi havde brug for at sammenligne felter 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 test af forespørgsler, når du ikke er sikker på, om de måske løber, er at placere en LIMIT i den overordnede forespørgsel. For eksempel ville denne forespørgsel løbe 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 implementeringen kun evaluerer så mange rækker af en WITH forespørgsel, som faktisk hentes af den overordnede forespørgsel. Brug af dette trick i produktionen anbefales ikke, fordi andre systemer kan fungere anderledes. Det fungerer normalt ikke, hvis du får den ydre forespørgsel til at sortere den rekursive forespørgsels resultater eller slutte dem til en anden tabel, fordi i sådanne tilfælde vil den ydre forespørgsel normalt forsøge at hente alle WITH forespørgslens output alligevel.

en nyttig egenskab ved WITH forespørgsler er, at de normalt kun evalueres en gang pr.udførelse af den overordnede forespørgsel, selvom de henvises til mere end en gang af den overordnede forespørgsel eller søskende WITH forespørgsler. Således kan dyre beregninger, der er nødvendige flere steder, placeres inden for en WITH forespørgsel for at undgå overflødigt arbejde. En anden mulig anvendelse er at forhindre uønskede flere evalueringer af funktioner med bivirkninger. Den anden side af denne mønt er imidlertid, at optimatoren ikke er i stand til at skubbe begrænsninger fra forældreforespørgslen ned i en multiplicer-refereret WITH forespørgsel, da det kan påvirke alle anvendelser af WITH forespørgslens output, når det kun skal påvirke en. Den multiplikationsrefererede WITH forespørgsel evalueres som skrevet uden undertrykkelse af rækker, som den overordnede forespørgsel muligvis kasserer bagefter. (Men som nævnt ovenfor kan evalueringen stoppe tidligt, hvis henvisningen(E) til forespørgslen kun kræver et begrænset antal rækker.)

men hvis en WITH forespørgsel er ikke-rekursiv og bivirkningsfri (det vil sige, det er en SELECT, der ikke indeholder flygtige funktioner), kan den foldes ind i forældreforespørgslen, hvilket muliggør fælles optimering af de to forespørgselsniveauer. Som standard sker dette, hvis den overordnede forespørgsel refererer til WITH forespørgslen kun en gang, men ikke hvis den refererer til WITH forespørgslen mere end en gang. Du kan tilsidesætte denne beslutning ved at angive MATERIALIZED for at tvinge separat beregning af forespørgslen WITH eller ved at angive NOT MATERIALIZED for at tvinge den til at blive flettet i den overordnede forespørgsel. Sidstnævnte valg risikerer duplikatberegning af WITH forespørgslen, men det kan stadig give en nettobesparelse, hvis hver brug af WITH forespørgslen kun har brug for en lille del af WITH forespørgslens fulde output.

et simpelt eksempel på disse regler er

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

denne WITH forespørgsel vil blive foldet og producerer den samme eksekveringsplan som

SELECT * FROM big_table WHERE key = 123;

især hvis der er et indeks på key, vil det sandsynligvis blive brugt til at hente bare rækkerne med key = 123. På den anden 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 forespørgslen vil blive materialiseret og producere en midlertidig kopi af big_table, der derefter forbindes med sig selv — uden fordel af noget indeks. Denne forespørgsel udføres meget mere 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;

, så forældreforespørgselens begrænsninger kan anvendes direkte på scanninger af big_table.

et eksempel, hvor NOT MATERIALIZED kunne 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 af WITH forespørgslen, at very_expensive_function kun evalueres en gang pr.

eksemplerne ovenfor viser kun, at WITH bruges sammen med SELECT, men det kan fastgøres på samme måde til INSERT, UPDATEeller DELETE. I hvert tilfælde giver det effektivt midlertidige tabeller, der kan henvises til i hovedkommandoen.