Pivots maken met conditionele aggregatie

Een van de meest voorkomende interviewopgaven over rapportage is: zet rijen om in kolommen. Je hebt een lange tabel zoals sales(region, quarter, amount) en de interviewer wil een breed rapport met één kolom per kwartaal.

Het overdraagbare, dialectonafhankelijke antwoord is conditionele aggregatie: een CASE-expressie binnen een aggregatiefunctie zoals SUM. Als je dit beheerst, kun je in elke database een draaitabel maken, zelfs in databases zonder het sleutelwoord PIVOT.

Voordat je een draaitabel maakt, benoem je de vormen. De lange vorm slaat één gegeven per rij op: elk paar regio/kwartaal staat op een eigen rij. De brede vorm verspreidt een categorie over kolommen.

• Lange vorm: eenvoudig in te voegen, lastig naast elkaar te lezen.
• Brede vorm: uitstekend voor een rapport voor eindgebruikers.

Een draaitabel zet lang om in breed. Interviewers waarderen dit omdat het test of je aggregatie begrijpt en niet alleen de syntaxis.

De truc: schrijf voor elke kolom in de uitvoer een CASE die de waarde teruggeeft wanneer de rij bij die kolom hoort, en anders NULL. Plaats die in een aggregatiefunctie, zodat de groep wordt samengevoegd tot één rij per sleutel.

Lees het als: tel het bedrag op, maar alleen voor de Q1-rijen. Omdat SUM NULL negeert, dragen de niet-overeenkomende rijen niets bij.

Dit patroon werkt dankzij één feit waar interviewers op zullen doorvragen: aggregatiefuncties slaan NULL over. Een CASE zonder ELSE geeft NULL terug wanneer geen enkele vertakking overeenkomt. Daarom telt SUM(CASE WHEN ... THEN amount END) alleen de rijen op die je hebt geselecteerd.

Als je in plaats daarvan ELSE 0 schrijft, werkt dat ook voor SUM (het optellen van nul verandert niets), maar het werkt niet goed voor AVG, MIN en COUNT.

Hier staat de volledige query op basis van de voorbeeldgegevens. Elke regio wordt één rij en elk kwartaal één kolom.

Met GROUP BY region worden de vier invoerrijen samengevoegd tot twee uitvoerrijen. Zonder deze clausule krijg je één rij per invoerrij, met voornamelijk NULL-waarden.

De aggregatiefunctie waarin je CASE plaatst, moet passen bij de vraag:

• SUM wanneer elke cel waarden optelt.
• MAX of MIN wanneer elk regio-/kwartaalpaar precies één waarde heeft en je die alleen zichtbaar wilt maken.
• COUNT wanneer elke cel overeenkomende rijen telt.

Interviewers vragen vaak naar de COUNT-variant: hoeveel bestellingen zijn er per status per maand?

Wanneer elk sleutel-/categoriepaar één waarde bevat (een echte kruistabel, geen totaal), gebruik je MAX of MIN. Beide geven de enige niet-NULL-waarde terug en negeren de NULL-waarden uit niet-overeenkomende vertakkingen.

Dit is de veilige keuze wanneer je kenmerken herschikt in plaats van geld op te tellen, bijvoorbeeld wanneer je een tabel met sleutel-waarde-instellingen omzet naar één rij per entiteit.

Als een regio geen verkopen in Q2 had, bevat de cel q2 als resultaat NULL. Interviewers kunnen je vragen om in plaats daarvan 0 te tonen. Plaats de volledige aggregatiefunctie in COALESCE.

Gebruik COALESCE buiten de aggregatiefunctie, niet binnen CASE, zodat je alleen een vervangende waarde gebruikt wanneer de volledige groep geen overeenkomende rijen heeft.

Een veelgestelde vervolgvraag is om een totaal over alle gedraaide kolommen toe te voegen. Je hoeft de kolommen niet op naam op te tellen. Een gewone SUM(amount) over dezelfde groep geeft het rijtotaal, omdat deze functie de filtering van CASE volledig negeert.

Hiermee laat je de interviewer zien dat je begrijpt dat elke aggregatiefunctie in de SELECT onafhankelijk over dezelfde groep wordt berekend.

PostgreSQL en de SQL-standaard ondersteunen FILTER (WHERE ...), een overzichtelijkere manier om voorwaardelijke aggregatie te schrijven. De notatie leest beter en voorkomt de standaardcode rond CASE.

Noem dit in een sollicitatiegesprek om je brede kennis te tonen, maar weet dat MySQL en SQL Server dit niet ondersteunen. Daarom blijft CASE het overdraagbare antwoord.

Voorwaardelijke aggregatie heeft één beperking waar interviewers op zullen doorvragen: je moet elke uitvoerkolom handmatig opsommen. Als kwartalen of categorieën niet vooraf bekend zijn, kan deze statische query zich niet aanpassen.

Dat probleem heet een dynamische draaitabel en vereist gegenereerde SQL. Voor een vaste, bekende verzameling categorieën is voorwaardelijke aggregatie echter de overzichtelijke, platformonafhankelijke winnaar.

Voorwaardelijke aggregatie is de platformonafhankelijke draaitabeloplossing die elke interviewer accepteert:

• Eén CASE per uitvoerkolom, verpakt in een aggregatiefunctie.
• SUM voor totalen, MAX/MIN voor cellen met één waarde, COUNT voor aantallen.
• Dit werkt omdat aggregatiefuncties de NULL-waarde uit niet-overeenkomende vertakkingen negeren.
• Gebruik COALESCE om lege cellen in 0 te veranderen.
• Beperking: kolommen moeten hardgecodeerd zijn, wat leidt tot dynamische draaitabellen in het volgende onderdeel.