Comment utiliser CASE WHEN dans GROUP BY

Découvrez comment combiner les instructions SQL CASE WHEN et GROUP BY pour créer des catégories personnalisées dans vos requêtes SQL.

Les données brutes, de par leur nature même, ne sont pas toujours lisibles par l'homme. Souvent, les données que vous interrogez sont dans leur forme la plus brute. C'est le cas, par exemple, des codes des différents départements de l'entreprise ou des UGS qui représentent des produits spécifiques. À l'œil nu, ces codes ne signifient rien, et les intégrer dans un rapport n'est donc pas utile pour la personne qui les lit.

Il existe également des situations où les données brutes doivent être regroupées à des niveaux plus élevés afin de les rendre plus faciles à assimiler. Par exemple, une liste de 50 produits peut être ramenée à 5 catégories de produits, ce qui rend le rapport beaucoup plus facile à lire et à comprendre.

Dans ce type de situation, nous pouvons utiliser les déclarations CASE WHEN et GROUP BY pour formater les données et ajouter des métadonnées à notre ensemble de données original. Nous étudierons le fonctionnement de ces déclarations tout au long de cet article. Pour un examen plus approfondi de ces concepts, nous vous invitons à consulter notre site Web Création de rapports basiques en SQL qui vous expliquera toutes les nuances de CASE et GROUP BY. Il s'agit d'une excellente suite à cet article.

Tout d'abord, examinons la déclaration CASE WHEN à l'aide de quelques exemples. Si vous souhaitez obtenir plus d'informations sur l'instruction CASE, lisez notre article Comment utiliser CASE en SQL.

Bref aperçu de CASE WHEN

Vous pouvez considérer l'instruction CASE WHEN comme la logique if..then de votre requête. Elle évalue les conditions et si la condition est vraie, elle renvoie un résultat spécifique défini.

Les instructions CASE en SQL comportent trois éléments importants : CASE WHEN, THEN, et END. Chacun de ces éléments est nécessaire, faute de quoi votre requête renverra une erreur.

Vous commencez l'instruction par CASE WHEN pour définir votre condition logique. Ensuite, vous utilisez THEN pour définir la valeur si cette condition est vraie. Après la dernière instruction THEN, vous utilisez END pour fermer la clause.

La clause ELSE est une autre clause facultative. Si toutes les conditions logiques de l'instruction CASE WHEN échouent, vous pouvez utiliser ELSE pour attribuer une valeur à ces données. Il s'agit en fait d'une clause "fourre-tout". Si vos données échouent à toutes les conditions et que vous n'utilisez pas ELSE, les données renverront une valeur NULL.

Voici à quoi ressemble la syntaxe de CASE WHEN:

CASE WHEN product = ‘Shirt’ THEN ‘Clothing’ 
     WHEN product = ‘Hat’ THEN ‘Accessories’ 
     ELSE ‘Other’
END 

Si le produit est une chemise, CASE WHEN l'affecte à la catégorie Clothing. Si le produit est un chapeau, CASE WHEN l'affecte à la catégorie Accessories. Dans le cas contraire, CASE WHEN attribue le produit à la catégorie Other.

Pour illustrer davantage, considérons les données suivantes trouvées dans le tableau cities dans le tableau :

Si nous voulions classer la population de chaque ville en trois catégories (faible, moyenne ou élevée), nous lancerions une requête :

SELECT 
  city,
  population, 
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END as population_level
FROM cities

Dans cette requête, nous ajoutons une logique conditionnelle basée sur le champ population. Si la population est inférieure à 500 000 habitants, nous attribuons la valeur Low. Si la population est comprise entre 500 000 et 1 500 000 habitants, nous attribuons la valeur Medium. Enfin, si la population est supérieure à 1 500 000 habitants, nous attribuons la valeur High. La colonne est alors aliasée population_level. Le résultat obtenu ressemble à ceci :

Et si nous voulions connaître la population moyenne pour chaque site population_level? Dans ce scénario, nous pouvons y parvenir en incluant une clause d'agrégation et GROUP BY. Nous en ferons la démonstration dans la section suivante.

Utilisation de CASE WHEN avec GROUP BY

Exemple 1 : agrégations de catégories personnalisées

Maintenant que nous disposons de la catégorie personnalisée population_level, nous pouvons calculer différentes mesures pour cette catégorie. Dans cet exemple, nous allons calculer la population moyenne pour chaque site population_level.

Nous utiliserons la même déclaration CASE WHEN que ci-dessus, ajouterons une agrégation pour la moyenne, puis GROUP BY en utilisant la même syntaxe.

Exécutons cette requête :

SELECT  
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END as population_level, 
  AVG(population) as average_population
FROM cities
GROUP BY
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END

Nous avons utilisé la même catégorie de population que dans la requête ci-dessus. Nous avons ajouté un calcul pour la population moyenne. Notez que nous avons répété la même expression CASE WHEN dans SELECT et dans GROUP BY.

Lorsque cette requête est exécutée, les données sont d'abord évaluées dans les conditions logiques de l'instruction CASE WHEN et une valeur leur est attribuée pour population_level. La moyenne est ensuite calculée pour chacun de ces niveaux à l'aide de l'expression GROUP BY.

Les résultats obtenus ressemblent à ceci :

Si vous avez besoin d'un rappel sur la clause GROUP BY, lisez cet article sur le GROUP BY en SQL.

Exemple 2 : CASE WHEN avec ELSE dans GROUP BY

Une autre façon d'écrire cette requête serait d'utiliser la clause ELSE. Vous définiriez les deux premiers niveaux de population et utiliseriez ensuite ELSE pour regrouper toutes les autres villes dans high. Cette requête ressemblerait à la suivante :

SELECT  
  CASE 
    WHEN population < 500000 THEN ‘Low’ 
    WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
    ELSE ‘High’ 
  END as population_level, 
  AVG(population) as average_population
FROM cities
GROUP BY 
  CASE 
   WHEN population < 500000 THEN ‘Low’ 
   WHEN population >= 500000 and population < 1500000 THEN ‘Medium’ 
   WHEN population >= 1500000 THEN ‘High’ 
  END

Nous n'avons pas nécessairement besoin de définir la logique pour les villes à forte population ; si la ville échoue aux deux premiers énoncés logiques, c'est que sa population est supérieure à 1 500 000 habitants.

Exemple 3 : déclarations CASE WHEN à l'intérieur d'agrégations

Nous pouvons également placer les instructions CASE WHEN à l'intérieur des fonctions d'agrégation pour compter le nombre d'enregistrements qui remplissent certaines conditions. En reprenant le même exemple, examinons comment nous pourrions compter le nombre de villes sur chaque site population_level.

Pour cet exemple, nous aurons besoin de trois instructions CASE WHEN distinctes, une pour chaque condition définie dans notre instruction CASE WHEN: low, medium, et high.

SELECT  
  SUM(CASE 
      WHEN population < 500000 
      THEN 1 
      ELSE 0 END) as low_pop_ct,
  SUM(CASE 
      WHEN population >= 500000 and population < 1500000 
      THEN 1   
      ELSE 0 END) as medium_pop_ct, 
  SUM(CASE 
      WHEN population >= 1500000 
      THEN 1 
      ELSE 0 END) as high_pop_ct
FROM cities

Voyons cela ligne par ligne. Dans la première ligne, nous évaluons si la ville a une population inférieure à 500 000 habitants. Si c'est le cas, nous lui attribuons la valeur 1. Dans le cas contraire, la clause ELSE lui attribue la valeur 0. Nous avons ensuite enveloppé l'ensemble de l'instruction CASE WHEN dans une clause SUM(), ce qui a pour effet de compter les lignes des villes à faible population.

Nous répétons ensuite ce schéma dans les deux lignes suivantes, en utilisant les mêmes conditions logiques que précédemment pour attribuer la valeur de medium et high aux villes en fonction de leur population.

Cette requête renvoie l'information suivante :

Nous disposons à présent d'un décompte du nombre de villes entrant dans chaque catégorie.

Vous souhaitez en savoir plus ? Consultez la rubrique Comment utiliser CASE WHEN avec SUM() pour plus d'exemples.

Exemple 4 : CASE WHEN dans GROUP BY

Voyons quelques autres exemples d'utilisation de CASE WHEN dans GROUP BY. Les données ci-dessous sont contenues dans le tableau products.

Ajoutons à ces données une nouvelle colonne contenant un product_category. Cela nous permettra de regrouper les produits individuels dans une catégorie de niveau supérieur afin de pouvoir calculer les agrégations.

Afin d'ajouter une colonne pour product_category, nous allons exécuter :

SELECT *, 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END as product_category 
FROM products

Nous utilisons l'opérateur LIKE pour rechercher dans le champ de description chaque chaîne entre guillemets. L'opérateur % signifie simplement qu'il peut y avoir quelque chose avant ou après. Nous recherchons en fait dans chaque description l'un des mots clés énumérés dans les déclarations WHEN. Les résultats sont présentés ci-dessous :

Maintenant que nous avons notre nouveau champ product_category, nous pouvons compter le nombre de produits dans chaque catégorie en utilisant GROUP BY:

SELECT
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
   END as product_category, 
   COUNT(DISTINCT description) as number_of_products
FROM products 
GROUP BY 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END 

Dans cet exemple, nous avons utilisé CASE WHEN dans la clause SELECT, mais cela n'est pas toujours nécessaire. Vous pouvez également exécuter la requête ci-dessus sans cette clause :

SELECT COUNT(DISTINCT description) as number_of_products
FROM products 
GROUP BY 
  CASE WHEN description LIKE '%sweater%' 
         OR description LIKE '%blazer%' 
         OR description LIKE '%jacket%' THEN  'Outerwear'
       WHEN description LIKE '%dress%' 
         OR description LIKE '%jumpsuit%' THEN  'Dresses' 
       WHEN description LIKE '%shirt%' 
         OR description LIKE '%blouse%' THEN 'Tops'
  END 

Que se passe-t-il ensuite avec CASE WHEN et GROUP BY ?

Comme vous pouvez le constater, il existe de nombreux scénarios dans lesquels la combinaison des instructions CASE WHEN et GROUP BY est extrêmement utile. Elles vous permettent d'ajouter une logique métier à vos données, puis de calculer des mesures basées sur les champs de données nouvellement définis. Vous pouvez utiliser les instructions CASE WHEN aussi bien en dehors qu'à l'intérieur des agrégations ; elles suivent la même syntaxe. Commencez votre déclaration par CASE WHEN pour définir votre logique conditionnelle, puis attribuez des valeurs à l'aide des déclarations THEN/ELSE. Enfin, terminez par END.

N'oubliez pas de consulter notre cours sur Création de rapports basiques en SQL. Vous apprendrez toutes les nuances de CASE et GROUP BY et vous aurez des problèmes pratiques réels à résoudre !