Forberedelse til SQL-intervju · leksjon

Dynamiske pivottabeller med ukjente kolonner

Generering av pivotkolonner når kategoriene ikke er kjent på forhånd.

Leksjon 4 av 413 trinn

Dynamiske pivottabeller med ukjente kolonner er en gratis leksjon i Forberedelse til SQL-intervju på CoddyKit. Dette er leksjon 4 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.

Det vanskelige pivotspørsmålet

Alle statiske pivoteringer, enten det gjelder aggregering med CASE, SQL Server PIVOT eller Postgres crosstab, har én felles begrensning: De må liste opp resultatkolonnene når spørringen skrives.

Men hva om kategoriene er ukjente, for eksempel produktnavn som endres hver uke, eller én kolonne per aktiv måned? Det er en dynamisk pivotering, og dette er et intervjuspørsmål på seniornivå fordi vanlig SQL ikke kan returnere et resultat der kolonnelisten bestemmes under kjøring.

Hvorfor SQL alene ikke kan gjøre det

SQL er statisk typet på resultatsett-nivå: planleggeren må kjenne kolonnene og datatypene deres før kjøringen. En enkelt spørring kan ikke si lag én kolonne for hver verdi som finnes.

Den universelle teknikken er derfor å generere SQL-teksten i to trinn: først henter De de unike kategoriene, deretter bygger De en pivotspørringsstreng fra dem og kjører denne strengen.

Trinn 1: Hent kategoriene

Det første trinnet er en vanlig spørring som lister de unike verdiene som skal bli kolonner. Vanligvis sorteres de for å få en stabil kolonnelayout.

Dette resultatet brukes i trinnet der strengen bygges. I et virkelig system kjører De denne spørringen, tar vare på radene og setter sammen den neste spørringen fra dem.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

Trinn 2: Bygg kolonnelisten

Deretter gjør De disse verdiene om til en kommaseparert liste med CASE-uttrykk (eller navn i hakeparenteser for PIVOT). Databaser tilbyr funksjoner for strengaggregering, slik at dette kan gjøres direkte i SQL.

I Postgres er funksjonen string_agg, i MySQL GROUP_CONCAT, og i SQL Server STRING_AGG eller det eldre trikset med FOR XML PATH.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

Trinn 3: Sett sammen og kjør

Sett det genererte fragmentet sammen til en full spørringsstreng, og kjør den deretter med dynamisk kjøring: EXECUTE i PL/pgSQL, sp_executesql i SQL Server eller PREPARE/EXECUTE i MySQL.

Dette er kjernen i en dynamisk pivotering: SQL skriver SQL og kjører den deretter.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

Fullstendig PostgreSQL-eksempel

I Postgres pakker De de tre trinnene inn i en DO-blokk eller en funksjon. Bygg kolonnelisten med string_agg, sett den inn i spørringen og kjør den med EXECUTE.

Siden resultatkolonnene er ukjente frem til kjøringen, bruker en funksjon som returnerer dette ofte RETURNS SETOF record, eller returnerer radene som json, slik at den som kaller funksjonen kan utvide dem.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

MySQL med preparerte setninger

MySQL har ingen pivotoperator, så dynamiske pivoteringer bygger en streng for betinget aggregering med GROUP_CONCAT, og kjører den deretter via en preparert setning.

GROUP_CONCAT har en lengdegrense (group_concat_max_len) som intervjuere kan nevne. Øk den hvis De har mange kategorier.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

Risikoen for SQL-injeksjon

Fordi dataverdier settes sammen til kjørbar SQL, innebærer dynamiske pivoteringer en injeksjonsrisiko. Hvis en kategoriverdi inneholder et anførselstegn eller skadelig tekst, kan den ødelegge eller kapre den genererte spørringen.

Escap alltid identifikatorer og litteraler med databasemotorens sikre hjelpefunksjoner: format('%I', ...) og %L i Postgres, QUOTENAME i SQL Server. Lim aldri råverdier direkte inn i strengen.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

Returnere ukjente kolonner

En annen utfordring er at den som kaller spørringen, ikke kan kjenne resultatets form på forhånd. Vanlige strategier som intervjuere godtar, er:

Returner radene som JSON, og la applikasjonslaget utvide nøklene.
La prosedyren skrive ut eller bygge spørringen, og kjør den som et andre trinn.
Gjør den endelige pivoteringen i applikasjonskoden (pandas, BI-verktøy) når kategoriene er kjent.

Det finnes ingen ryddig måte å returnere vilkårlige kolonner fra ett statisk kall.

Praktisk eksempel: Pivotering etter produkt

Anta at produkter kommer til og forsvinner, og at rapporten trenger én inntektskolonne per produkt som for øyeblikket finnes i sales. Listen kan ikke hardkodes, så den må genereres. Postgres gjør dette oversiktlig: bygg CASE-fragmentet med string_agg og sikker sitering, sett det inn i en spørring, og bruk deretter EXECUTE.

Gå gjennom dette med intervjueren: finn produktene, formater hvert produkt som en sitert kolonne, sett alt sammen og kjør. Den samme strukturen gjelder i alle databasemotorer; det er bare hjelpefunksjonene som endres.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

Når bør De unngå dynamiske pivoteringer

Gode kandidater vet når dette ikke bør gjøres i SQL. Dynamisk SQL er vanskeligere å lese, teste, sikre og mellomlagre. Ofte er det bedre å:

Returnere data i langformat fra SQL og pivotere i applikasjonen eller rapporteringslaget.
Bruke en statisk pivotering hvis kategorisettet er lite og endres sjelden, og oppdatere den av og til.

Reserver dynamiske pivoteringer for kategorisett som faktisk er åpne og stadig endres.

Hurtigsjekk

Test om De forstår den grunnleggende grunnen til at dynamiske pivoteringer finnes.

Besvar den i den interaktive leksjonen

Oppsummering

Dynamiske pivoteringer håndterer ukjente kolonnesett:

Statiske pivoteringer mislykkes fordi resultatkolonnene må være fastsatt før kjøringen.
Mønsteret er: hent unike kategorier, bygg en pivot-SQL-streng og kjør den dynamisk.
Bruk string_agg/GROUP_CONCAT/STRING_AGG til å bygge kolonnelisten.
Escap verdier (%I/%L, QUOTENAME) for å unngå SQL-injeksjon.
Det er ofte ryddigere å returnere data i langformat og pivotere i applikasjonslaget.

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 «Dynamiske pivottabeller med ukjente kolonner» gratis?

Ja – hele teksten i «Dynamiske pivottabeller med ukjente kolonner» 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 «Dynamiske pivottabeller med ukjente kolonner»?

Generering av pivotkolonner når kategoriene ikke er kjent på forhånd. 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 4 av 4.

Hvor lang tid tar leksjonen «Dynamiske pivottabeller med ukjente kolonner»?

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

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

← Tilbake til Forberedelse til SQL-intervju