Forberedelse til kodeinterviews · Lektion

Pivotering med betinget aggregering

Det portable CASE-i-SUM-mønster til at omdanne rækker til kolonner.

Lektion 1 af 413 trin

Pivotering med betinget aggregering er en gratis Forberedelse til kodeinterviews-lektion på CoddyKit. Dette er lektion 1 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i Forberedelse til kodeinterviews, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Forberedelse til kodeinterviews-kurset indeholder 4 lektioner i alt.

Interviewopsætningen

En af de mest almindelige opgaver i rapporteringsinterviews er: omdan rækker til kolonner. Du har en lang tabel som sales(region, quarter, amount), og intervieweren vil have en bred rapport med én kolonne pr. kvartal.

Det portabelt svar, som er uafhængigt af SQL-dialekten, er betinget aggregering: Et CASE-udtryk placeret i en aggregeringsfunktion som SUM. Lær dette grundigt, så kan du pivotere i enhver database, selv databaser uden nøgleordet PIVOT.

Langt kontra bredt format

Før du omformer dataene, skal du kende formaterne. Langt format gemmer én oplysning pr. række: hvert par af region og kvartal har sin egen række. Bredt format fordeler en kategori over kolonner.

Langt: nemt at indsætte, svært at læse ved siden af hinanden.
Bredt: velegnet til en rapport, som mennesker skal læse.

En omformning ændrer langt format til bredt format. Interviewere kan godt lide dette, fordi det afprøver, om du forstår aggregering og ikke kun syntaks.

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

Kernemønsteret

Tricket er følgende: For hver resultatkolonne skriver du et CASE-udtryk, der returnerer værdien, når rækken hører til den pågældende kolonne, og NULL ellers. Pak det ind i en aggregeringsfunktion, så gruppen reduceres til én række pr. nøgle.

Læs det som: læg beløbet sammen, men kun for Q1-rækkerne. Fordi SUM ignorerer NULL, bidrager rækker, der ikke passer, ikke med noget.

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ønster virker på grund af én kendsgerning, som interviewere vil spørge ind til: aggregeringsfunktioner springer NULL over. Et CASE-udtryk uden ELSE returnerer NULL, når ingen gren passer, så SUM(CASE WHEN ... THEN amount END) lægger kun de valgte rækker sammen.

Hvis du i stedet skrev ELSE 0, ville det også virke med SUM (at lægge nul til ændrer ingenting), men det ville ødelægge resultatet for 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)

Gennemgået eksempel: Kvartalsrapport

Her er hele forespørgslen mod eksempeldataene. Hver region bliver til én række, og hvert kvartal bliver til én kolonne.

Det er GROUP BY region, der samler de fire inputrækker til to outputrækker. Uden den ville du få én række pr. inputrække med for det meste NULL-værdier.

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

Valg af det rigtige aggregat

Det aggregat, du omslutter CASE med, skal passe til spørgsmålet:

SUM, når hver celle summerer værdier.
MAX eller MIN, når hvert region-/kvartalspar har præcis én værdi, og du blot vil vise den.
COUNT, når hver celle tæller matchende rækker.

Interviewere spørger ofte til COUNT-varianten: Hvor mange ordrer er der pr. status pr. 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 til celler med én værdi

Når hvert nøgle-/kategori-par indeholder én enkelt værdi (en ægte krydstabel og ikke en total), skal du bruge MAX eller MIN. Begge returnerer den eneste værdi, der ikke er NULL, og ignorerer NULL-værdierne fra grene, der ikke matcher.

Det er det sikre valg, når du omformer attributter i stedet for at summere penge, for eksempel når du omdanner en indstillingstabel med nøgler og værdier til én række pr. 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åndtering af NULL i outputceller

Hvis en region ikke havde noget salg i Q2, bliver dens q2-celle til NULL. Interviewere kan bede dig om at vise 0 i stedet. Omslut hele aggregatet med COALESCE.

Placér COALESCE uden på aggregatet, ikke inde i CASE, så du kun erstatter værdien, når hele gruppen ikke har nogen matchende rækker.

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;

Tilføjelse af en kolonne med hovedtotal

Et almindeligt opfølgende spørgsmål er at tilføje en total på tværs af alle de pivoterede kolonner. Du behøver ikke lægge kolonnerne sammen ved navn. En almindelig SUM(amount) over den samme gruppe giver rækketotalen, fordi den helt ignorerer CASE-filtreringen.

Det viser intervieweren, at du forstår, at hvert aggregat i SELECT beregnes uafhængigt over den samme gruppe.

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;

Genvejen med filtreret aggregat

PostgreSQL og SQL-standarden understøtter FILTER (WHERE ...), som er en renere måde at skrive betinget aggregering på. Det er lettere at læse og undgår standardkoden med CASE.

Nævn dette til en samtale for at vise, at du kender flere muligheder, men husk, at MySQL og SQL Server ikke understøtter det, så CASE stadig er det portable svar.

-- 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 begrænsning

Der er én vigtig begrænsning ved betinget aggregering, som interviewere vil presse dig på: Du skal angive hver outputkolonne manuelt. Hvis kvartaler eller kategorier ikke er kendt på forhånd, kan denne statiske forespørgsel ikke tilpasse sig.

Det problem kaldes en dynamic pivot, og det kræver genereret SQL. For et fast, kendt sæt kategorier er betinget aggregering dog den rene, portable løsning.

Hurtig kontrol

Afprøv, hvor godt du forstår mønstret for betinget aggregering.

Besvar den i den interaktive lektion

Opsamling

Betinget aggregering er den portable pivotering, som alle interviewere accepterer:

Én CASE pr. outputkolonne, omsluttet af et aggregat.
SUM til totaler, MAX/MIN til celler med én værdi og COUNT til optællinger.
Det virker, fordi aggregater ignorerer den NULL-værdi, der kommer fra grene, som ikke matcher.
Brug COALESCE til at omdanne tomme celler til 0.
Begrænsning: Kolonnerne skal være hardkodede, hvilket leder videre til dynamisk pivotering.

Gratis at komme i gang

Lær Forberedelse til kodeinterviews med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 90
Lektioner: 360

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Pivotering med betinget aggregering” gratis?

Ja — hele teksten til “Pivotering med betinget aggregering” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af Forberedelse til kodeinterviews-kurset, skal du opgradere til CoddyKit PRO. Forberedelse til kodeinterviews-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Pivotering med betinget aggregering”?

Det portable CASE-i-SUM-mønster til at omdanne rækker til kolonner. Du øver dig i Forberedelse til kodeinterviews med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Forberedelse til kodeinterviews?

Der kræves ingen tidligere erfaring. Forberedelse til kodeinterviews på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 1 af 4.

Hvor lang tid tager lektionen “Pivotering med betinget aggregering”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Forberedelse til kodeinterviews-lektion?

Ja. Alle Forberedelse til kodeinterviews-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Pivotering med betinget aggregering
Leverandørspecifik PIVOT- og krydstabssyntaks
Omdannelse af kolonner til rækker
Dynamiske pivottabeller med ukendte kolonner

← Tilbage til Forberedelse til kodeinterviews