Dynamiske pivottabeller med ukendte kolonner
Generering af pivotkolonner, når kategorierne ikke kendes på forhånd.
Dynamiske pivottabeller med ukendte kolonner er en gratis Forberedelse til kodeinterviews-lektion på CoddyKit. Dette er lektion 4 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.
Det vanskelige pivotspørgsmål
Alle statiske pivoteringer, uanset om de bruger CASE-aggregering, SQL Server-PIVOT eller Postgres-crosstab, har én begrænsning til fælles: Du skal angive resultatkolonnerne, når du skriver forespørgslen.
Men hvad nu, hvis kategorierne er ukendte, f.eks. produktnavne, der ændrer sig hver uge, eller én kolonne pr. aktiv måned? Det kaldes dynamisk pivotering, og det er et spørgsmål på seniorniveau til jobsamtaler, fordi almindelig SQL ikke kan returnere et resultat, hvis kolonneliste først afgøres under kørsel.
Hvorfor SQL alene ikke kan gøre det
SQL er statisk typet på resultatsætniveau: Forespørgselsplanlæggeren skal kende kolonnerne og deres typer før udførelsen. En enkelt forespørgsel kan ikke sige opret én kolonne for hver værdi, du tilfældigvis finder.
Den generelle metode er derfor at generere SQL-teksten i to trin: Først forespørger du de unikke kategorier, derefter bygger du en pivotforespørgselsstreng ud fra dem og kører den.
Trin 1: Indsaml kategorierne
Det første trin er en almindelig forespørgsel, der viser de unikke værdier, som skal blive til kolonner. Du sorterer dem typisk for at få en stabil kolonneopstilling.
Dette resultat bruges i trinnet, hvor strengen bygges. I et rigtigt system kører du denne forespørgsel, gemmer rækkerne og samler den næste forespørgsel ud fra dem.
SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4Trin 2: Opbyg kolonnelisten
Omdan derefter værdierne til en kommasepareret liste med CASE-udtryk (eller navne i kantede parenteser til PIVOT). Databaser tilbyder funktioner til strengaggregering, så du kan gøre dette direkte i SQL.
I Postgres er det string_agg, i MySQL GROUP_CONCAT og i SQL Server STRING_AGG eller det ældre trick 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;Trin 3: Saml og kør
Sammenkæd det genererede fragment til en komplet forespørgselsstreng, og kør den derefter med dynamisk udførelse: EXECUTE i PL/pgSQL, sp_executesql i SQL Server eller PREPARE/EXECUTE i MySQL.
Det er kernen i dynamisk pivotering: SQL skriver SQL og kører det derefter.
-- 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;Komplet eksempel i PostgreSQL
I Postgres samler du de tre trin i en DO-blok eller en funktion. Opbyg kolonnelisten med string_agg, indsæt den i forespørgslen, og kør den med EXECUTE.
Fordi resultatkolonnerne er ukendte indtil kørselstidspunktet, bruger en funktion, der returnerer dette, ofte RETURNS SETOF record eller returnerer rækkerne som json, som kalderen derefter udfolder.
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 forberedte sætninger
MySQL har ingen pivot-operator, så dynamisk pivotering opbygger en streng med betinget aggregering ved hjælp af GROUP_CONCAT og kører den derefter via en forberedt sætning.
GROUP_CONCAT har en længdegrænse (group_concat_max_len), som interviewere måske nævner. Hæv den, hvis du 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-injektion
Fordi du sammenkæder dataværdier med kørbar SQL, medfører dynamisk pivotering en injektionsrisiko. Hvis en kategoriværdi indeholder et anførselstegn eller skadelig tekst, kan den ødelægge eller kapre den genererede forespørgsel.
Undslip altid identifikatorer og literaler med databasemotorens sikre hjælpefunktioner: format('%I', ...) og %L i Postgres samt QUOTENAME i SQL Server. Indsæt aldrig rå værdier direkte 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)Returnering af ukendte kolonner
En anden vanskelighed er, at kalderen ikke kan kende resultatets form på forhånd. Almindelige strategier, som interviewere accepterer, er:
- Returnér rækkerne som
JSON, og lad applikationslaget udfolde nøglerne. - Lad proceduren udskrive eller opbygge forespørgslen, og kør den som et andet trin.
- Udfør den endelige pivotering i applikationskoden (pandas, BI-værktøj), når kategorierne er kendt.
Der findes ingen enkel måde at returnere vilkårlige kolonner fra ét statisk kald.
Praktisk eksempel: Pivotering efter produkt
Antag, at produkter kommer og går, og at rapporten skal have én omsætningskolonne pr. produkt, der aktuelt findes i sales. Du kan ikke skrive listen direkte i koden, så du genererer den. Postgres gør det overskueligt: Opbyg CASE-fragmentet med string_agg og sikker citering, indsæt det i en forespørgsel, og kør derefter EXECUTE.
Forklar det trin for trin til intervieweren: Find produkterne, formatér hvert produkt som en kolonne med anførselstegn, saml forespørgslen, og kør den. Den samme struktur gælder i alle databasemotorer; kun hjælpefunktionerne ændres.
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$;Hvornår du bør undgå dynamisk pivotering
Dygtige kandidater ved, hvornår de ikke skal gøre dette i SQL. Dynamisk SQL er sværere at læse, teste, sikre og cachelagre. Ofte er det bedre at:
- Returnere langt format fra SQL og pivotere i applikations- eller rapporteringslaget.
- Bruge statisk pivotering og opdatere den lejlighedsvis, hvis kategorisættet er lille og ændrer sig langsomt.
Gem dynamisk pivotering til kategorisæt, der reelt er åbne og hele tiden ændrer sig.
Hurtigt tjek
Afprøv den grundlæggende årsag til, at dynamisk pivotering findes.
Opsummering
Dynamisk pivotering håndterer ukendte kolonnemængder:
- Statisk pivotering mislykkes, fordi resultatkolonnerne skal være fastlagt før udførelsen.
- Mønsteret er: forespørg de unikke kategorier, opbyg en pivotering som SQL-streng, og kør den dynamisk.
- Brug
string_agg/GROUP_CONCAT/STRING_AGGtil at opbygge kolonnelisten. - Undslip værdier (
%I/%L,QUOTENAME) for at undgå SQL-injektion. - Det er ofte enklere at returnere langt format og pivotere i applikationslaget.
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
Ofte stillede spørgsmål
Er lektionen “Dynamiske pivottabeller med ukendte kolonner” gratis?
Ja — hele teksten til “Dynamiske pivottabeller med ukendte kolonner” 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 “Dynamiske pivottabeller med ukendte kolonner”?
Generering af pivotkolonner, når kategorierne ikke kendes på forhånd. 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 4 af 4.
Hvor lang tid tager lektionen “Dynamiske pivottabeller med ukendte kolonner”?
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