Forberedelse til kodeintervjuer · leksjon

Pivotering med betinget aggregering

Det portable mønsteret med CASE inni SUM for å gjøre rader om til kolonner.

Leksjon 1 av 413 trinn

Pivotering med betinget aggregering er en gratis leksjon i Forberedelse til kodeintervjuer på CoddyKit. Dette er leksjon 1 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 kodeintervjuer, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til kodeintervjuer inneholder totalt 4 leksjoner.

Intervjuoppgaven

En av de vanligste rapporteringsoppgavene i intervjuer er: gjør rader om til kolonner. De har en lang tabell som sales(region, quarter, amount), og intervjueren ønsker en bred rapport med én kolonne per kvartal.

Det portable, dialektuavhengige svaret de ønsker å høre, er betinget aggregering: et CASE-uttrykk plassert inne i en aggregatfunksjon som SUM. Når De behersker dette, kan De pivotere i alle databaser, også i dem som ikke har nøkkelordet PIVOT.

Langt kontra bredt format

Før De pivoterer, bør De navngi formene. Langt format lagrer ett faktum per rad: hvert par av region og kvartal har sin egen rad. Bredt format fordeler en kategori på tvers av kolonner.

Langt: enkelt å sette inn, vanskelig å lese side om side.
Bredt: godt egnet for en rapport rettet mot mennesker.

En pivotering gjør langt format om til bredt format. Intervjuere liker dette fordi det tester om De forstår aggregering, ikke bare syntaks.

-- Long form (the input)
region | quarter | amount
-------+---------+-------
East   | Q1      | 100
East   | Q2      | 150
West   | Q1      | 200
West   | Q2      | 250

Kjernemønsteret

Trikset er å skrive en CASE for hver resultatkolonne. Den returnerer verdien når raden samsvarer med kolonnen, og NULL ellers. Pakk den inn i en aggregatfunksjon, slik at gruppen reduseres til én rad per nøkkel.

Les det slik: summer amount, men bare for Q1-radene. Fordi SUM ignorerer NULL, bidrar ikke radene som ikke samsvarer, med noe.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

Hvorfor SUM ignorerer NULL

Dette mønsteret fungerer på grunn av ett faktum intervjuere vil undersøke: aggregatfunksjoner hopper over NULL-verdier. En CASE uten ELSE returnerer NULL når ingen gren samsvarer, så SUM(CASE WHEN ... THEN amount END) legger bare sammen radene De har valgt.

Hvis De i stedet skrev ELSE 0, ville det også fungert for SUM (å legge til null endrer ingenting), men det ville ødelagt AVG, MIN og COUNT.

-- Both produce the same SUM result:
SUM(CASE WHEN quarter = 'Q1' THEN amount END)
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END)

Praktisk eksempel: kvartalsrapport

Her er hele spørringen mot eksempeldataene. Hver region blir én rad, og hvert kvartal blir én kolonne.

GROUP BY region er det som reduserer de fire inndataradene til to utdatarader. Uten dette ville man fått én rad per inndatarad, med hovedsakelig NULL-verdier.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

-- Result:
-- region | q1  | q2
-- East   | 100 | 150
-- West   | 200 | 250

Velge riktig aggregat

Aggregatet som CASE pakkes inn i, må samsvare med spørsmålet:

SUM når hver celle summerer verdier.
MAX eller MIN når hvert region-/kvartalspar har nøyaktig én verdi, og man bare vil vise den.
COUNT når hver celle teller samsvarende rader.

Intervjuere spør ofte om COUNT-varianten: hvor mange bestillinger finnes per status per måned?

SELECT
  month,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY month;

MAX for celler med én verdi

Når hvert nøkkel-/kategori-par inneholder én enkelt verdi (en ekte krysstabell, ikke en totalsum), bruker man MAX eller MIN. Begge returnerer den eneste verdien som ikke er NULL, og ignorerer NULL-verdiene fra grener som ikke samsvarer.

Dette er det trygge valget når man omformer attributter i stedet for å summere penger, for eksempel når en tabell med innstillinger i nøkkel-/verdi-format skal gjøres om til én rad per entitet.

-- Turn key/value rows into one wide row per user
SELECT
  user_id,
  MAX(CASE WHEN attr = 'city'  THEN value END) AS city,
  MAX(CASE WHEN attr = 'plan'  THEN value END) AS plan
FROM user_attributes
GROUP BY user_id;

Håndtere NULL-verdier i utdata

Hvis en region ikke hadde noe salg i Q2, blir q2-cellen NULL. Intervjuere kan be om at den skal vise 0 i stedet. Pakk hele aggregatet inn i COALESCE.

Plasser COALESCE utenpå aggregatet, ikke inne i CASE, slik at man bare erstatter verdien når hele gruppen mangler samsvarende rader.

SELECT
  region,
  COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN amount END), 0) AS q1,
  COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN amount END), 0) AS q2
FROM sales
GROUP BY region;

Legge til en totalsumkolonne

Et vanlig oppfølgingsspørsmål er å legge til en totalsum på tvers av alle pivotkolonnene. Man trenger ikke legge sammen kolonnene ved navn. En vanlig SUM(amount) over den samme gruppen gir radtotalen, fordi den ikke påvirkes av CASE-filtreringen.

Dette viser intervjueren at man forstår at hvert aggregat i SELECT beregnes uavhengig over den samme gruppen.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(amount) AS total
FROM sales
GROUP BY region;

Snarveien med filtrert aggregat

PostgreSQL og SQL-standarden støtter FILTER (WHERE ...), som er en ryddigere måte å skrive betinget aggregering på. Det er enklere å lese og unngår standardkoden med CASE.

Nevn dette i et intervju for å vise bredde, men vær klar over at MySQL og SQL Server ikke støtter det. Derfor er CASE fortsatt det portable svaret.

-- Postgres / standard SQL
SELECT
  region,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY region;

Den store begrensningen

Betinget aggregering har én begrensning som intervjuere gjerne presser på: man må liste opp hver utdatakolonne manuelt. Hvis kvartalene eller kategoriene ikke er kjent på forhånd, kan ikke denne statiske spørringen tilpasse seg.

Dette problemet kalles en dynamic pivot, og krever generert SQL. For et fast, kjent sett med kategorier er likevel betinget aggregering det ryddige og portable valget.

Kort kontroll

Test forståelsen av mønsteret for betinget aggregering.

Besvar den i den interaktive leksjonen

Oppsummering

Betinget aggregering er den portable pivotløsningen som alle intervjuere godtar:

Én CASE per utdatakolonne, pakket inn i et aggregat.
SUM for summer, MAX/MIN for celler med én verdi og COUNT for opptelling.
Det fungerer fordi aggregater ignorerer NULL fra grener som ikke samsvarer.
Bruk COALESCE for å gjøre tomme celler om til 0.
Begrensning: kolonnene må skrives inn direkte i spørringen, noe som leder til dynamiske pivoter videre.

Gratis å komme i gang

Lær deg Forberedelse til kodeintervjuer 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: 90
Leksjoner: 360

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Pivotering med betinget aggregering» gratis?

Ja – hele teksten i «Pivotering med betinget aggregering» 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 kodeintervjuer-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til kodeintervjuer inneholder totalt 4 leksjoner.

Hva lærer jeg i «Pivotering med betinget aggregering»?

Det portable mønsteret med CASE inni SUM for å gjøre rader om til kolonner. Du øver på Forberedelse til kodeintervjuer 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 kodeintervjuer?

Ingen tidligere erfaring er nødvendig. Forberedelse til kodeintervjuer 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 1 av 4.

Hvor lang tid tar leksjonen «Pivotering med betinget aggregering»?

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 kodeintervjuer-leksjonen?

Ja. Alle Forberedelse til kodeintervjuer-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

Pivotering med betinget aggregering
Leverandørspesifikk PIVOT- og krysstabellsyntaks
Gjøre kolonner om til rader
Dynamiske pivottabeller med ukjente kolonner

← Tilbake til Forberedelse til kodeintervjuer