Explication de l'instruction CASE en SQL

Si vous avez travaillé avec des bases de données, vous avez probablement remarqué que les données sont rarement parfaites et qu'il faut souvent les manipuler pour en tirer des informations utiles.

Pour répondre à ce besoin, SQL propose une construction puissante, appelée instruction CASE, qui vous permet de créer de nouvelles colonnes ou de transformer des données en fonction de conditions appliquées à des colonnes existantes.

Dans cet article, nous expliquerons ce qu'est une déclaration CASE, pourquoi elle est essentielle et nous explorerons différents cas d'utilisation pour vous aider à exploiter son potentiel. Commençons !

Qu'est-ce qu'une déclaration CASE ?

L'instruction Case en SQL est une expression conditionnelle qui introduit une logique de prise de décision dans votre requête. Son fonctionnement est similaire à celui d'une instruction instruction if-elif-else en Pythonvous permettant d'évaluer plusieurs conditions et de renvoyer des résultats spécifiques en fonction de ces conditions.

La syntaxe générale est la suivante :

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

Voici comment cela fonctionne :

• Le mot-clé CASE marque le début de l'expression, tandis que le mot-clé END marque sa conclusion, en entourant l'instruction conditionnelle comme un cadre.
• Chaque clause WHEN évalue une condition. Si la condition est vraie, la clause THEN correspondante renvoie le résultat spécifié.
• Si aucune des conditions des clauses WHEN n'est remplie, la clause ELSE fournit un résultat par défaut.

Pourquoi la déclaration CASE est-elle importante ?

L'instruction CASE WHEN est un outil précieux dans les requêtes SQL, offrant plusieurs avantages clés :

• Transformation des donnéesLa transformation des données : vous permet de créer de nouvelles colonnes basées sur les valeurs des colonnes existantes.
• Agrégation conditionnelleL'agrégation conditionnelle vous permet de créer des valeurs agrégées (par exemple, des sommes, des comptages, des maxima) pour des sous-ensembles spécifiques de données en fonction de conditions.
• Filtrage des données: vous pouvez inclure ou exclure des lignes en fonction de critères spécifiques.
• Simplification des requêtes et lisibilité: Il permet de consolider la logique en une seule requête, réduisant ainsi le besoin de requêtes multiples, de tableaux temporaires ou de jointures complexes.
• Optimisation des performancesEn réduisant la complexité des requêtes SQL, l'instruction CASE peut également contribuer à optimiser les performances et à minimiser le temps d'exécution, en particulier lorsqu'elle évite le recours à des calculs et à des transformations supplémentaires.

Déclaration CASE simple

Dans cet article, nous allons utiliser le tableau film de la base de données cinema. Supposons que nous souhaitions créer une nouvelle colonne catégorielle basée sur les valeurs du ROI (ratio brut sur 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;

Compte tenu du montant brut et du budget, nous avons calculé le retour sur investissement (ROI), qui est un indicateur de rentabilité populaire permettant de mesurer le revenu généré par un investissement.

En plus du champ ROI, nous avons également obtenu sa catégorisation pour mieux communiquer :

• Si les films dont le retour sur investissement est inférieur à 1, la valeur est "faible retour sur investissement".
• Les films dont le ROI est compris entre 1 et 2 appartiennent à la catégorie "ROI moyen"
• La mention "ROI élevé" est attribuée aux films dont le ROI est supérieur ou égal à 2.

Nous avons également filtré les films dont le budget ou la valeur brute sont nuls ou négatifs. En outre, nous avons limité les résultats aux films sortis en 2015.

Utilisation de CASE dans la clause Group By

Dans le premier exemple, nous avons créé un nouveau champ qui peut être utilisé pour agréger les données et obtenir des informations supplémentaires.

Par exemple, nous pouvons calculer le montant brut moyen et le budget moyen pour chaque groupe de RCI :

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
;

Pour regrouper les données, il suffit de placer l'instruction case dans la clause GROUP BY. Pour obtenir des informations supplémentaires, nous avons classé les lignes par ordre décroissant en fonction de la valeur brute moyenne. De cette manière, nous sommes en mesure d'attraper la moyenne brute la plus élevée qui est attribuée au ROI élevé et vice versa pour le ROI faible.

Utilisation de CASE dans la clause WHERE

Un autre avantage de l'instruction CASE est qu'elle permet de filtrer les lignes en fonction de conditions spécifiques. Par exemple, nous pouvons appliquer différents seuils de retour sur investissement en fonction de la langue du film. Il est connu que les films anglais ont un retour sur investissement élevé et constant. Le seuil devrait donc avoir une valeur plus élevée, alors que nous supposons que les autres langues ont un retour sur investissement plus faible.

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;

Calcul des agrégations

Nous pouvons également être intéressés par le calcul du nombre de films avec un retour sur investissement élevé par rapport à un retour sur investissement faible sortis en 2015 :

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;

Dans la fonction SUM, c'est comme si nous créions un nouveau champ avec la valeur 1 lorsque les ROI atteignent ce seuil, 0 sinon. Si nous prenons la somme de cette nouvelle variable binaire, nous obtenons le nombre de films qui respectent ce critère.

Les résultats montrent que la plupart des films ont un retour sur investissement inférieur à 1, tandis que peu de films sont très rentables.

Déclaration de cas imbriquée

L'un des cas d'utilisation les plus avancés est la déclaration de cas imbriquée, qui est un cas dans un cas. Il est très utile lorsque vous devez diviser les données en sous-groupes et appliquer une logique différente pour chaque sous-groupe. 

Comme dans le premier exemple, nous voulons créer une nouvelle colonne qui sera influencée par les valeurs de ROI. Comme nous l'avons dit précédemment, le retour sur investissement des films en langue anglaise est globalement élevé par rapport aux films dans d'autres langues. 

C'est pourquoi il serait préférable de classer les films dans des groupes de ROI en tenant compte à la fois du ROI et de la langue.

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;

Dans la requête, l'instruction CASE externe est utilisée pour déterminer si la langue est l'anglais ou non. En fonction de la langue, le CASE interne évalue le retour sur investissement : les seuils sont plus élevés pour les films anglais et plus bas pour les films non anglais. Cette condition à plusieurs niveaux permet d'améliorer la séparation des films à faible, moyenne et forte teneur en chrome.

Conclusion

L'instruction CASE est une construction SQL indispensable pour extraire des informations significatives de vos données. La théorie est alternée avec des exemples pratiques pour vous aider à comprendre en profondeur cette puissante expression conditionnelle. Si vous voulez aller plus loin, jetez un coup d'œil à cours sur la manipulation de données en SQL. Il couvre l'instruction CASE, ainsi que d'autres aspects clés du langage SQL, tels que les sous-requêtes et les fonctions de fenêtre. Enfin, vous devez vérifier les Principes fondamentaux du langage SQL pour avoir une vue d'ensemble des cours qui aident à maîtriser SQL.