Leverandørspecifik PIVOT- og krydstabssyntaks

Du kender allerede den portable CASE-pivotering. Men interviewere vil også vide, om du kan bruge leverandørspecifikke pivotoperatorer, når de er tilgængelige.

SQL Server leveres med den dedikerede PIVOT-operator. PostgreSQL tilbyder funktionen crosstab i udvidelsen tablefunc. Hvis du kender begge og deres faldgruber, viser det erfaring fra den virkelige verden.

SQL Servers PIVOT tager tre ting:

• Et aggregat over værdikolonnen.
• En FOR-klausul, der angiver kolonnen, hvis værdier skal blive til nye kolonner.
• En IN-liste med de bogstavelige værdier, der skal omdannes til kolonner.

Den skal anvendes på en afledt tabel, der præcis viser nøglen, fordelingskolonnen og værdien – og ikke andet.

En subtil PIVOT-faldgrube, som interviewere undersøger: Grupperingen er implicit. SQL Server grupperer efter hver kolonne i kilden, som ikke er den aggregerede kolonne eller FOR-kolonnen.

Hvis din afledte tabel ved en fejl også indeholder en ekstra kolonne som order_id, grupperer pivoteringen også efter den, og du får langt flere rækker end forventet. Begræns altid den indre forespørgsel til kun nøgle, fordelingskolonne og værdi.

I SQL Server er de pivoterede kolonnenavne de bogstavelige værdier fra dataene, omsluttet af kantede parenteser. Hvis en værdi begynder med et ciffer eller indeholder mellemrum, er kantede parenteser obligatoriske.

Du vælger dem med det samme navn i kantede parenteser i den ydre SELECT. Det er også grunden til, at PIVOT ikke kan håndtere ukendte værdier uden dynamisk SQL: IN-listen er hardkodet.

PostgreSQL har ikke nøgleordet PIVOT. I stedet leverer udvidelsen tablefunc funktionen crosstab, som tager en SQL-streng og omformer dens output.

Du skal først aktivere udvidelsen. crosstab forventer, at kildeforespørgslen returnerer præcis tre kolonner: rækkeidentifikator, kategori og værdi, i den rækkefølge.

Den mest fejlbehæftede del af crosstab er den afsluttende liste over kolonnedefinitioner i AS ct(...). Du skal selv erklære outputkolonnernes navne og typer, og de skal svare til kategoriernes antal og rækkefølge.

Hvis en kategori mangler i en række, udfylder crosstab den efter position. Det kan forskyde dataene, medmindre du bruger formen med to argumenter nedenfor.

Brug formen med to argumenter for at undgå forskydning, når nogle rækker mangler bestemte kategorier. Den anden forespørgsel returnerer den fulde, sorterede liste over kategoriværdier, så crosstab ved præcis, hvilken kolonne hver værdi hører til.

Det er den robuste form, interviewere forventer, når kategorierne er spredte.

Hvis intervieweren spørger til MySQL, er svaret enkelt: MySQL har hverken PIVOT eller crosstab. Din eneste mulighed er betinget aggregering med CASE (eller kortformen SUM(... ) + IF()).

Det er netop derfor, det portable CASE-mønster er så værdsat: Det er den fællesnævner, der virker overalt.

Et rapporteringsbehov kan være: „én række pr. region med en kolonne, der tæller ordrer for hver status“. I SQL Server giver du en beskåret, afledt tabel som input til PIVOT med COUNT.

Fordi du tæller selve statuskolonnen, bliver hver statusrække, der ikke er NULL, talt med i sin gruppe. Den ydre SELECT angiver hver status som en kolonne i kantede parenteser. Det er det korte alternativ til at skrive tre COUNT(CASE ...)-udtryk.

Både PIVOT og crosstab har den samme grundlæggende begrænsning som betinget aggregering: Outputkolonnerne skal være kendt, når du skriver forespørgslen.

• SQL Server: IN-listen består af faste værdier.
• Postgres crosstab: Listen over kolonnedefinitioner er fast angivet.

Ingen af dem kan finde kategorier automatisk under kørsel. Det kræver, at SQL-strengen bygges dynamisk.

Et godt svar til en samtale sammenligner dem ærligt:

• CASE-aggregering: portable, let at læse og fungerer i alle databasemotorer. Standardvalget.
• SQL Server PIVOT: kortfattet ved mange kolonner, men den implicitte gruppering kommer ofte bag på folk.
• Postgres crosstab: kraftfuld, men omstændelig; kræver en udvidelse og en liste over kolonnedefinitioner.

Vælg betinget aggregering, når du er i tvivl, og nævn leverandørernes operatorer som alternativer.

Leverandørspecifik pivoteringssyntaks på én skærm:

• SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])) med en implicit GROUP BY over de resterende kolonner.
• Postgres: crosstab() fra tablefunc, som kræver en liste over kolonnedefinitioner; brug formen med to argumenter til spredte data.
• MySQL: Ingen af delene findes, så brug CASE.
• Alle tre kræver, at kolonnerne er kendt, når forespørgslen skrives.