Écrire une requête avec WITH

Tu as calculé le nombre de vendeurs par État. Tu veux maintenant compter les États qui n'ont qu'un seul vendeur. Comment réutiliser ton premier résultat ?

WITH permet de donner un nom au résultat d'une requête pour l'utiliser dans la suite de la même instruction. Ce résultat nommé s'appelle une CTEVocabulaire · CTECommon Table Expression : un résultat intermédiaire auquel on donne un nom avec WITH, pour s'en servir ensuite comme d'une table., pour Common Table Expression.

Tu travailles ici l'extraction de données avec SQL, une compétence du bloc BC01.

Exécute les requêtes au fil de la fiche. Si tu lis depuis plus de vingt minutes sans avoir écrit de SQL, reprends l'essai de la section 3.

1. Regarder le résultat qu'on veut réutiliser

Dans Olist, sellers contient une ligne par vendeur. seller_state est le code de son État brésilien.

Exécute ce comptage :

SELECT seller_state, COUNT(*) AS vendeurs
FROM sellers
GROUP BY seller_state
ORDER BY seller_state;

Voici les cinq premières lignes du résultat :

Que représente la ligne BA | 19 ? Réponds avant de continuer.

Elle représente l'État BA, dans lequel la table recense 19 vendeurs. Le résultat complet contient 23 lignes, une par État présent dans sellers.

Pour répondre à notre question, il faut garder les lignes où vendeurs vaut 1, puis les compter. On va donc écrire une requête sur ce résultat.

2. Donner un nom au résultat avec WITH

On appelle ce résultat par_etat. Ce nom est choisi pour cette requête.

WITH par_etat AS (
    SELECT seller_state, COUNT(*) AS vendeurs
    FROM sellers
    GROUP BY seller_state
)
SELECT seller_state, vendeurs
FROM par_etat
WHERE vendeurs = 1
ORDER BY seller_state;

La requête entre parenthèses est celle que tu viens d'exécuter, sans le tri. FROM par_etat donne accès à ses deux colonnes : seller_state et vendeurs. Le WHERE garde les États qui ont un seul vendeur. Le tri s'applique au résultat final.

Exécute la requête et compte les lignes affichées. Puis remplace le SELECT final pour obtenir ce nombre dans une seule cellule :

WITH par_etat AS (
    SELECT seller_state, COUNT(*) AS vendeurs
    FROM sellers
    GROUP BY seller_state
)
SELECT COUNT(*) AS etats_concernes
FROM par_etat
WHERE vendeurs = 1;

Tu dois obtenir 5 États. Si tu obtiens 23, regarde si le filtre est encore présent dans ta requête.

Les deux COUNT(*) comptent des objets différents. Celui dans la CTE compte les vendeurs de chaque État. Celui après la parenthèse compte les lignes de par_etat conservées par le filtre, donc des États.

Pour examiner une CTE dans ton propre code, remplace temporairement le SELECT final par SELECT * FROM nom_de_la_cte;. Tu verras ses colonnes et ses lignes avant d'ajouter le calcul suivant.

3. Modifier la requête, puis vérifier

Combien d'États comptent plus de 100 vendeurs ? Adapte la requête précédente dans ton éditeur SQL.

Avant de l'exécuter, note ce que tu vas modifier : le comptage dans la CTE, le filtre final ou les deux ?

Pour vérifier ton résultat, affiche aussi la liste des États concernés avec leur nombre de vendeurs. Le nombre de lignes de cette liste doit correspondre au nombre calculé.

Si tu bloques, garde la CTE telle quelle. Elle fournit déjà le nombre de vendeurs par État. C'est la condition vendeurs = 1 qu'il faut changer.

Après ton essai, vérifie : tu dois obtenir 6 États. Une réponse « 6 vendeurs » décrirait mal le résultat, même avec une requête correcte.

Reprends ensuite une requête de ton TP qui compte des groupes. Écris en commentaire ce qu'une ligne représente avant et après le regroupement.

4. Enchaîner des CTE et repérer une erreur

Voici la même recherche écrite avec deux résultats nommés. Avant d'exécuter, repère où sont comptés les vendeurs et où sont sélectionnés les États.

WITH par_etat AS (
    SELECT seller_state, COUNT(*) AS vendeurs
    FROM sellers
    GROUP BY seller_state
),
etats_retenus AS (
    SELECT seller_state, vendeurs
    FROM par_etat
    WHERE vendeurs > 100
)
SELECT COUNT(*) AS etats_concernes
FROM etats_retenus;

etats_retenus utilise les colonnes de par_etat, définie au-dessus. Ici, la seconde CTE sert à nommer le filtrage. La version de la section 3 convient aussi.

Si ta requête échoue, vérifie ces points :

WITH ne crée pas de table enregistrée dans ta base. Pour relancer la requête, exécute tout le bloc, depuis WITH jusqu'au point-virgule final.

5. Utiliser un total dans un pourcentage

Cette section sert quand tu as besoin d'un total à côté de chaque groupe. Dans products, chaque ligne représente un produit. On cherche la part de chaque catégorie dans le catalogue.

Exécute d'abord le total seul :

SELECT COUNT(*) AS n
FROM products;

Le résultat contient une ligne et une colonne, n. On peut le nommer total et lire cette valeur dans le calcul du pourcentage :

WITH total AS (
    SELECT COUNT(*) AS n FROM products
)
SELECT product_category_name AS categorie,
       COUNT(*) AS produits,
       ROUND(COUNT(*) * 100.0 / (SELECT n FROM total), 2) AS part
FROM products
GROUP BY product_category_name
ORDER BY produits DESC
LIMIT 5;

product_category_name est le nom de la catégorie. (SELECT n FROM total) fournit le nombre total de produits. ROUND(..., 2) arrondit le pourcentage à deux décimales. Ici, 100.0 permet de conserver les décimales pendant la division.

Le dénominateur compte-t-il les produits des cinq catégories affichées ou ceux de tout le catalogue ? Repère la requête qui le calcule, puis vérifie où se trouve le LIMIT 5.

Le total porte sur tout le catalogue. Le LIMIT 5 réduit seulement le nombre de catégories affichées. Si tu dois calculer un pourcentage dans le TP, écris ce que ton dénominateur compte avant d'adapter cet exemple.