Forberedelse til SQL-intervju · leksjon

CTE versus underspørring versus midlertidig tabell

Avveininger innen materialisering, gjenbruk og optimaliseringsatferd.

Leksjon 3 av 413 trinn

CTE versus underspørring versus midlertidig tabell er en gratis leksjon i Forberedelse til SQL-intervju på CoddyKit. Dette er leksjon 3 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Forberedelse til SQL-intervju, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Tre måter å organisere logikk på

Når en spørring trenger et mellomresultat, har De tre vanlige verktøy: en underforespørsel, en CTE og en midlertidig tabell. Intervjuere ber Dem sammenligne dem fordi valget viser om De forstår materialisering og optimalisererens virkemåte.

Denne leksjonen bygger et beslutningsrammeverk De kan gjengi under press.

Underforespørselen

En underforespørsel er en innebygd spørring som er nøstet i en annen, ofte i FROM, WHERE eller SELECT. Den inngår i samme setning, og optimalisereren ser den som én samlet enhet.

Trenger ikke navn (avledede tabeller trenger imidlertid et alias).
Optimalisereren står fritt til å slå den sammen med den ytre spørringen.
Blir omstendelig og vanskelig å lese når den nøstes dypt.

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

CTE-en

En CTE er en navngitt underforespørsel i en WITH-blokk, med virkeområde begrenset til én setning. Den er mer lesbar enn en dypt nøstet underforespørsel og kan refereres til flere ganger.

Den har et navn, slik at hensikten blir dokumentert.
Den kan refereres til mer enn én gang i samme setning.
Den gjelder fortsatt bare for én setning og forsvinner deretter.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

Den midlertidige tabellen

En midlertidig tabell er en reell, fysisk tabell som varer i sesjonen (eller transaksjonen). Den fylles med én setning og brukes i senere, separate setninger.

Den vedvarer på tvers av flere setninger i sesjonen.
Den kan indekseres, og det kan samles inn statistikk for den.
Den medfører disk-I/O og krever eksplisitt opprydding.

CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

SELECT * FROM spend WHERE total > 1000;

Materialisering: det sentrale skillet

Det sentrale begrepet intervjuere undersøker, er materialisering: om mellomresultatet fysisk skrives et sted.

Underforespørsler og CTE-er er vanligvis ikke materialisert; optimalisereren integrerer dem ofte direkte.
En midlertidig tabell er alltid materialisert i lagring.
Noen databaser lar Dem tvinge gjennom eller hindre materialisering av CTE-er ved hjelp av hint.

Optimaliseringssperrer og den gamle Postgres-fellen

Historisk behandlet PostgreSQL alle CTE-er som en optimaliseringssperre, materialiserte dem og hindret predicate push-down. Siden Postgres 12 blir enkle, ikke-rekursive CTE-er som refereres én gang, integrert direkte som standard, med MATERIALIZED og NOT MATERIALIZED som hint for å overstyre dette.

Å nevne denne nyansen signaliserer tydelig seniornivå.

WITH spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;

Gjenbruk i én setning

Hvis De refererer til det samme mellomresultatet flere ganger i én setning, kan en CTE være ryddigere enn å gjenta en underforespørsel. Vær imidlertid oppmerksom på at en CTE som er integrert direkte, kan bli beregnet på nytt for hver referanse.

Når ny beregning er kostbar, unngår tvungen materialisering (eller bruk av en midlertidig tabell) at arbeidet utføres to ganger.

Gjenbruk på tvers av setninger

CTE-er og underforespørsler gjelder bare for én setning. Hvis De trenger det samme resultatet i flere separate spørringer, er en midlertidig tabell det riktige verktøyet.

Et typisk tilfelle er en ETL-prosess eller rapport i flere trinn, der De bygger et mellomdatasett én gang og deretter kjører flere analyser mot det. Indeksering av den midlertidige tabellen kan da fremskynde alle oppfølgende spørringer.

Indeksering og statistikk

Bare en midlertidig tabell kan ha indekser og oppdaterte statistikker. For et enormt mellomdatasett som slås sammen mange ganger, kan det være avgjørende.

CTE/underforespørsel: optimalisereren beregner estimater fra de underliggende tabellene.
Midlertidig tabell: De kan kjøre ANALYZE på den og legge til indekser tilpasset senere sammenføyninger.

For store resultater som gjenbrukes mye, kan en midlertidig tabell derfor gi bedre ytelse til tross for de ekstra trinnene.

Beslutningsrammeverket

Et presist intervjusvar:

Underforespørsel: engangsbruk, lite nøstet, lesbarheten er god nok.
CTE: forbedrer lesbarheten, eller De refererer til den noen ganger i én setning.
Midlertidig tabell: gjenbrukes på tvers av setninger, er svært stor, eller De trenger indekser/statistikk.

Velg som standard en CTE for klarhet, og bruk en midlertidig tabell når materialisering eller gjenbruk på tvers av setninger faktisk er nyttig.

Slik presenterer De avveiningen

Unngå bastante utsagn som «CTE-er er alltid tregere.» Si heller: CTE-er og underforespørsler integreres vanligvis direkte, så de handler først og fremst om lesbarhet; en midlertidig tabell materialiseres og er verdt det når jeg gjenbruker et stort resultat på tvers av setninger eller trenger en indeks.

At De anerkjenner at denne oppførselen er motoravhengig (og versjonsavhengig i Postgres), viser reell dybdeforståelse.

Hurtigsjekk

Velg scenariet der en midlertidig tabell klart er det beste valget.

Besvar den i den interaktive leksjonen

Oppsummering: CTE kontra underforespørsel kontra midlertidig tabell

Valget avhenger av materialisering og virkeområde.

Underforespørsler og CTE-er: vanligvis integrert direkte, gjelder én setning og velges for lesbarhet.
CTE-er gir navngivning og gjenbruk i samme setning.
Midlertidige tabeller: alltid materialisert, vedvarer på tvers av setninger og kan indekseres.
Postgres 12+ integrerer enkle CTE-er direkte; bruk hint med MATERIALIZED for å kontrollere dette.

Neste: refaktorere en sammenfiltret nøstet spørring til ryddige CTE-er.

Gratis å komme i gang

Lær deg SQL med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 30
Leksjoner: 120

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «CTE versus underspørring versus midlertidig tabell» gratis?

Ja – hele teksten i «CTE versus underspørring versus midlertidig tabell» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av Forberedelse til SQL-intervju-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Hva lærer jeg i «CTE versus underspørring versus midlertidig tabell»?

Avveininger innen materialisering, gjenbruk og optimaliseringsatferd. Du øver på Forberedelse til SQL-intervju med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Forberedelse til SQL-intervju?

Ingen tidligere erfaring er nødvendig. Forberedelse til SQL-intervju på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 3 av 4.

Hvor lang tid tar leksjonen «CTE versus underspørring versus midlertidig tabell»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Forberedelse til SQL-intervju-leksjonen?

Ja. Alle Forberedelse til SQL-intervju-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

Skrive din første CTE
Kjede sammen flere CTE-er
CTE versus underspørring versus midlertidig tabell
Refaktorere nestede spørringer til CTE-er

← Tilbake til Forberedelse til SQL-intervju