Niveau 18 : Fonctions de fenêtre : cumuls et moyennes

Leçon

Au niveau 17, les fonctions de fenêtre classaient les lignes. Les agrégats habituels (SUM, AVG, COUNT, MIN, MAX) deviennent eux aussi des fonctions de fenêtre quand on leur ajoute OVER. La différence avec GROUP BY : toutes les lignes restent, et chacune reçoit le résultat de l'agrégat calculé sur sa fenêtre.

Avec PARTITION BY seul, la fenêtre est le groupe entier : SUM(amount) OVER (PARTITION BY region) affiche sur chaque vente le total de sa région, 2600 pour le Nord et 2450 pour le Sud. On peut ainsi comparer chaque ligne à son groupe sur une même ligne : écart à la moyenne, part du total…

OVER () avec des parenthèses vides prend toute la table comme fenêtre. C'est pratique pour une part du total : amount * 100.0 / SUM(amount) OVER () donne le pourcentage que représente chaque vente. Écris 100.0 pour éviter la division entière.

Avec un ORDER BY dans OVER, le calcul devient cumulatif : la fenêtre va de la première ligne jusqu'à la ligne en cours. SUM(amount) OVER (PARTITION BY seller ORDER BY month) donne pour Ana 300 en janvier, 750 en février et 1150 en mars : c'est un total courant, comme un solde bancaire. Grâce à PARTITION BY, le cumul repart de zéro à chaque vendeur.

Attention aux égalités : le cumul avance par valeur de tri, pas par ligne. Si deux lignes ont la même valeur dans le ORDER BY, par exemple deux transactions le même jour, elles sont additionnées ensemble et reçoivent le même cumul. Pour un cumul strictement ligne par ligne, ajoute une colonne de départage (ORDER BY made_on, id). Le niveau 19 explique ce mécanisme et le montre sur un exemple (exercice 19.4).

On peut enfin combiner GROUP BY et fenêtre, puisque la fenêtre est calculée après le regroupement : SUM(SUM(amount)) OVER () additionne les totaux de tous les vendeurs pour obtenir le grand total.

Syntaxe

SELECT col,
       SUM(x) OVER (PARTITION BY g ORDER BY d) AS cumul,
       x * 100.0 / SUM(x) OVER () AS pct
FROM ma_table;

Exemple commenté

SELECT seller,
       month,
       amount,
       SUM(amount) OVER (PARTITION BY seller ORDER BY month) AS running_total
FROM sales;

Pour chaque vendeur, le total cumulé grandit mois après mois et repart de zéro au vendeur suivant.

Résultat de l’exemple

À retenir

• SUM/AVG/COUNT + OVER : un agrégat sur chaque ligne, sans regroupement.
• ORDER BY dans OVER : cumul.
• OVER () : toute la table.

Pièges fréquents

• Oublier PARTITION BY fait un cumul sur toute la table au lieu de chaque groupe.
• Division entière : écris 100.0 pour un pourcentage.
• Deux lignes de même date reçoivent le même cumul : c'est le cadre RANGE par défaut.

Les 5 exercices du niveau

1. Guidé · Affiche chaque vente (vendeur, région, montant) avec, sur la même ligne, le total de sa région (region_total). (Table : sales)
2. Entraînement · Pour le compte 1, affiche chaque transaction (date, montant) et le solde après chaque opération, dans l'ordre chronologique. (Table : transactions)
3. Entraînement · Affiche chaque note (élève, matière, note) avec la moyenne de sa matière arrondie à une décimale (subject_avg). (Table : grades)
4. Entraînement · Pour chaque ville et chaque jour, affiche la pluie du jour et le cumul de pluie depuis le début du mois dans cette ville (cum_rain). (Table : readings)
5. Défi · Affiche le total de chaque vendeur et sa part du total général en pourcentage, arrondie à une décimale (pct). (Table : sales)

Dans SpeedQL, chaque requête est corrigée tout de suite : le résultat est comparé à celui attendu, puis la requête est relancée sur une base de contrôle cachée. Chaque exercice a ses indices écrits, à afficher seulement si tu bloques.

Voir aussi : la fiche de l’aide-mémoire · les exercices SQL corrigés sur cette notion