Ajouter un comptage à chaque ligne avec OVER

Tu veux garder une ligne par vendeur tout en ajoutant, sur chaque ligne, le nombre de vendeurs de son État. Avec GROUP BY, tu sais calculer ce nombre, mais tu perds alors le détail des vendeurs.

Cette fiche fait travailler l'analyse exploratoire avec SQL, une compétence du bloc BC02.

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

Le bac à sable affiche au maximum 200 lignes. Quand il indique que le résultat est tronqué, les lignes visibles ne forment qu'un extrait du résultat complet.

1. Voir ce que GROUP BY conserve

Dans sellers, une ligne représente un vendeur. seller_state contient le code de son État brésilien. Commençons par les États BA et CE.

SELECT seller_state, COUNT(*) AS vendeurs
FROM sellers
WHERE seller_state IN ('BA', 'CE')
GROUP BY seller_state
ORDER BY seller_state;

Le résultat complet de cette requête tient en deux lignes :

Que représente une ligne de ce résultat ? Écris ta réponse avant de continuer.

La ligne BA | 19 représente un groupe : les 19 vendeurs dont l'État est BA. Après le GROUP BY, chaque vendeur n'a plus sa propre ligne.

2. Garder les vendeurs avec OVER

On veut maintenant retrouver une ligne par vendeur et écrire le nombre de son groupe à côté.

Avant d'exécuter la requête, prédis le nombre de lignes pour BA et la valeur que portera la colonne vendeurs_dans_l_etat sur chacune de ces lignes.

SELECT seller_id,
       seller_state,
       COUNT(*) OVER (PARTITION BY seller_state) AS vendeurs_dans_l_etat
FROM sellers
WHERE seller_state IN ('BA', 'CE')
ORDER BY seller_state, seller_id;

Voici un extrait du résultat. Les identifiants sont abrégés dans ce tableau :

La requête renvoie 32 lignes : 19 vendeurs de BA et 13 de CE.

OVER transforme ici COUNT(*) en fonction de fenêtrage, c'est-à-dire un calcul qui conserve les lignes du résultat. PARTITION BY seller_state découpe ces lignes en groupes selon leur État. Chaque vendeur de BA reçoit donc 19 et chaque vendeur de CE reçoit 13.

3. Lire la fenêtre

La fenêtre est l'ensemble des lignes utilisées pour calculer la valeur placée sur une ligne.

• OVER () utilise toutes les lignes du résultat, sans les séparer.
• OVER (PARTITION BY seller_state) utilise les lignes qui ont le même État que la ligne courante.

Compare les deux colonnes calculées ci-dessous. Avant d'exécuter, prédis celle qui changera entre une ligne de BA et une ligne de CE.

SELECT seller_id,
       seller_state,
       COUNT(*) OVER () AS lignes_du_resultat,
       COUNT(*) OVER (PARTITION BY seller_state) AS vendeurs_dans_l_etat
FROM sellers
WHERE seller_state IN ('BA', 'CE')
ORDER BY seller_state, seller_id;

lignes_du_resultat vaut 32 sur toutes les lignes. La parenthèse vide ne crée aucun groupe. vendeurs_dans_l_etat vaut 19 pour BA et 13 pour CE, car PARTITION BY crée un groupe par valeur de seller_state.

Le WHERE agit avant les deux calculs de fenêtre. Les fonctions ne voient ici que les vendeurs de BA et de CE.

4. Modifier la fenêtre, puis vérifier

Ajoute l'État DF à la requête de la section 2. Avant d'exécuter, note ce que tu prévois pour les anciennes lignes de BA et de CE : leur compteur doit-il changer ?

Travaille à partir de ce modèle :

SELECT seller_id,
       seller_state,
       COUNT(*) OVER (PARTITION BY seller_state) AS vendeurs_dans_l_etat
FROM sellers
WHERE seller_state IN ('BA', 'CE', ...)
ORDER BY seller_state, seller_id;

Si tu bloques, remplace les points par le code de l'État entre apostrophes. Ne change pas le PARTITION BY : le groupe recherché reste l'État du vendeur.

Pour vérifier ton résultat, exécute ensuite ce comptage avec les trois États et compare ses valeurs à celles répétées dans ta colonne de fenêtre :

SELECT seller_state, COUNT(*) AS vendeurs
FROM sellers
WHERE seller_state IN ('BA', 'CE', 'DF')
GROUP BY seller_state
ORDER BY seller_state;

Tu dois retrouver 19 pour BA, 13 pour CE et 30 pour DF. L'ajout de DF ne change pas les compteurs de BA et de CE : chaque partition reste séparée.

Reprends ensuite une requête à toi. Avant de choisir entre GROUP BY et OVER, écris ce qu'une ligne du résultat doit représenter. Si tu dois encore voir chaque élément, conserve les lignes avec OVER. Si tu veux une ligne par groupe, utilise GROUP BY.

5. À part : calculer un pourcentage par groupe

Cette section ajoute un second niveau de lecture. Commence-la quand la différence entre OVER () et OVER (PARTITION BY ...) est claire.

La requête suivante regroupe d'abord les vendeurs par État. Elle calcule ensuite la part de chaque groupe parmi les deux États retenus.

SELECT seller_state,
       COUNT(*) AS vendeurs,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS part_des_deux_etats
FROM sellers
WHERE seller_state IN ('BA', 'CE')
GROUP BY seller_state
ORDER BY seller_state;

Avant d'exécuter, repère les deux étapes : quel calcul produit 19 et 13, puis quel calcul utilise ces deux nombres ensemble ?

COUNT(*) produit le nombre de vendeurs de chaque État. Après ce regroupement, SUM(COUNT(*)) OVER () additionne les comptes des deux lignes du résultat. Le dénominateur vaut donc 32. Les parts affichées sont 59,38 % pour BA et 40,63 % pour CE.

Additionne les deux pourcentages affichés avant de lire la suite. Retrouves-tu exactement 100 % ? Pour contrôler l’écart, calcule les deux parts avec 19/32 et 13/32, sans les arrondir, puis additionne-les.

Pour revoir une autre manière de placer un total dans un calcul, consulte la section « Utiliser un total dans un pourcentage » de Écrire une requête avec WITH.