CASE-instructie in SQL uitgelegd

Als je met databases hebt gewerkt, heb je vast gemerkt dat data zelden perfect is en vaak bewerking nodig heeft om tot zinvolle inzichten te komen.

Om aan deze behoefte te voldoen, biedt SQL een krachtig construct, de CASE-instructie, waarmee je nieuwe kolommen kunt maken of data kunt transformeren op basis van voorwaarden die je toepast op bestaande kolommen.

In dit artikel leggen we uit wat een CASE-instructie is, waarom die essentieel is en verkennen we verschillende use-cases zodat je het volle potentieel kunt benutten. Aan de slag!

Wat is een CASE-instructie?

De CASE-instructie in SQL is een voorwaardelijke expressie die beslislogica aan je query toevoegt. Het werkt vergelijkbaar met een if-elif-else-instructie in Python, waardoor je meerdere voorwaarden kunt evalueren en specifieke resultaten kunt teruggeven op basis van die voorwaarden. 

De algemene syntaxis is als volgt:

CASE
	WHEN boolean_condition1 THEN result1
	WHEN boolean_condition2 THEN result2
	ELSE result3
END

Zo werkt het:

• Het sleutelwoord CASE markeert het begin van de expressie, terwijl END het einde aangeeft en zo de voorwaardelijke instructie als het ware inkadert.
• Elke WHEN-clausule evalueert een voorwaarde. Als de voorwaarde waar is, geeft de bijbehorende THEN-clausule een opgegeven resultaat terug.
• Als geen van de voorwaarden in de WHEN-clausules wordt gehaald, levert de ELSE-clausule een standaardresultaat.

Waarom is de CASE-instructie belangrijk?

De CASE WHEN-instructie is een waardevol hulpmiddel in SQL-queries en biedt verschillende belangrijke voordelen:

• Datatransformatie: stelt je in staat nieuwe kolommen te maken op basis van de waarden van bestaande kolommen.
• Conditionele aggregatie: maakt het mogelijk geaggregeerde waarden (bijv. sommen, aantallen, maximum) te maken voor specifieke subsets van data op basis van voorwaarden.
• Datafiltering: je kunt rijen opnemen of uitsluiten op basis van specifieke criteria.
• Vereenvoudiging en leesbaarheid van queries: helpt om logica in één query te bundelen, waardoor je minder vaak meerdere queries, tijdelijke tabellen of complexe joins nodig hebt.
• Prestatie-optimalisatie: door de complexiteit van SQL-queries te verminderen, kan de CASE-instructie ook bijdragen aan prestatieoptimalisatie en de uitvoeringstijd minimaliseren, vooral wanneer extra berekeningen en transformaties vermeden worden.

Eenvoudige CASE-instructie

In dit artikel gebruiken we de tabel film van de database cinema. Stel dat we een nieuwe categorische kolom willen maken op basis van de waarden van de ROI (verhouding bruto-opbrengst tot budget):

SELECT 
	title,
	gross,
	budget,
	gross / budget AS ROI,
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END as ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;

Gegeven de bruto-opbrengst en het budget hebben we de ROI berekend, een populaire winstgevendheidsmaatstaf die het inkomen meet dat door een investering wordt gegenereerd.

Naast het ROI-veld hebben we ook een categorisering toegevoegd om de resultaten beter te communiceren:

• Voor films met een ROI lager dan 1 is de waarde "low ROI"
• Films met een ROI tussen 1 en 2 vallen in de categorie "medium ROI"
• "High ROI" is het label voor films met een ROI groter dan of gelijk aan 2.

We hebben ook gefilterd op films met een budget of bruto-opbrengst van nul of negatief. Bovendien hebben we de resultaten beperkt tot films die in 2015 zijn uitgebracht.

CASE gebruiken in de GROUP BY-clausule

In het eerste voorbeeld hebben we een nieuw veld gemaakt dat kan worden gebruikt om de data te aggregeren en extra inzichten te verkrijgen.

Zo kunnen we bijvoorbeeld de gemiddelde bruto-opbrengst en het gemiddelde budget per ROI-groep berekenen:

SELECT 
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END AS ROI_group,
avg(gross) as avg_gross,
avg(budget) as avg_budget
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015
GROUP BY
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END
ORDER BY avg(gross) DESC
;

Om de data te groeperen plaatsen we de CASE-instructie in de GROUP BY-clausule. Voor extra inzicht hebben we de rijen aflopend gesorteerd op de gemiddelde bruto-opbrengst. Zo zie je dat de hoogste gemiddelde bruto-opbrengst hoort bij de hoge ROI en omgekeerd voor de lage ROI.

CASE gebruiken in de WHERE-clausule

Een ander voordeel van de CASE-instructie is dat je er rijen mee kunt filteren op basis van specifieke voorwaarden. Zo kunnen we verschillende ROI-drempels hanteren afhankelijk van de taal van de film. Het is bekend dat Engelstalige films consequent een hoge ROI hebben. De drempel moet dus hoger liggen, terwijl we aannemen dat andere talen een lagere ROI hebben.

SELECT 
    title,
    gross,
    budget,
    language,
    gross / budget AS ROI
FROM cinema.films
WHERE 
    CASE 
        WHEN language = 'English' THEN gross / budget > 2 -- English films need ROI > 2
        WHEN language = 'French' THEN gross / budget > 1.5 -- French films need ROI > 1.5
        ELSE gross / budget > 1.3 -- Default for other languages
    END;

Aggregaties berekenen

We kunnen ook geïnteresseerd zijn in het aantal films met hoge ROI versus lage ROI die in 2015 zijn uitgebracht:

SELECT 
    SUM(CASE WHEN gross / budget > 2 THEN 1 ELSE 0 END) AS high_roi_films,
    SUM(CASE WHEN gross / budget BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS medium_roi_films,
    SUM(CASE WHEN gross / budget < 1 THEN 1 ELSE 0 END) AS low_roi_films
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;

Binnen de SUM-functie maken we als het ware een nieuw veld met waarde 1 wanneer de ROI die drempel haalt, en anders 0. Door de som van deze binaire variabele te nemen, krijgen we het aantal films dat aan dat criterium voldoet.

Uit de output blijkt dat de meeste films een ROI hebben van minder dan 1, terwijl slechts enkele films zeer winstgevend zijn.

Geneste CASE-instructie

Een van de meest geavanceerde toepassingen is de geneste CASE-instructie: een CASE binnen een CASE. Dit is erg handig wanneer je de data in subgroepen moet verdelen en voor elke subgroep andere logica wilt toepassen. 

Net als in het eerste voorbeeld willen we een nieuwe kolom maken die wordt beïnvloed door de waarden van de ROI. Zoals we eerder zeiden, ligt de ROI voor Engelstalige films wereldwijd hoger dan voor films in andere talen. 

Daarom is het beter om films in ROI-groepen in te delen door zowel de ROI als de taal mee te nemen

SELECT 
    title,
    language,
    gross / budget AS ROI,
    CASE
        WHEN language = 'English' THEN 
            CASE 
                WHEN gross / budget < 2 THEN 'low ROI'
                WHEN gross / budget BETWEEN 2 AND 3 THEN 'medium ROI'
                ELSE 'high ROI'
            END
        ELSE 
            CASE
                WHEN gross / budget < 1 THEN 'low ROI'
                WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
                ELSE 'high ROI'
            END
    END AS ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 and release_year = 2006;

In de query wordt de buitenste CASE-instructie gebruikt om te bepalen of de taal Engels is of niet. Afhankelijk van de taal evalueert de binnenste CASE de ROI: de drempels liggen hoger voor Engelstalige films en lager voor niet-Engelstalige films. Deze meerlagige voorwaarde verbetert de scheiding tussen films met een lage, gemiddelde en hoge ROI.

Conclusie

De CASE-instructie is een onmisbaar SQL-construct om zinvolle inzichten uit je data te halen. Theorie wordt afgewisseld met praktische voorbeelden om je te helpen deze krachtige voorwaardelijke expressie goed te begrijpen. Wil je dieper gaan, bekijk dan de cursus Data Manipulation in SQL. Daarin komt de CASE-instructie aan bod, evenals andere belangrijke SQL-onderdelen, zoals subqueries en windowfuncties. Bekijk tot slot de SQL Fundamentals voor een overzicht van cursussen waarmee je SQL onder de knie krijgt.