SQL — Fonctions de fenêtrage
ROW_NUMBER, RANK, PARTITION BY : classer et comparer des lignes sans perdre le détail, contrairement à GROUP BY
Objectif pédagogique
À la fin de ce module, vous saurez classer et comparer des lignes avec ROW_NUMBER, RANK et DENSE_RANK, isoler un top N par groupe, comparer une ligne à ses voisines avec LAG et LEAD, et combiner ces fonctions de fenêtrage avec CASE et des sous-requêtes.
Chapitres
Classer avec ROW_NUMBER, RANK, DENSE_RANK
réduit vos lignes : N lignes deviennent un nombre de lignes égal au nombre de groupes distincts. Une fonction de fenêtrage () fait…
Voir les détails du chapitre
- Principe général : la différence avec GROUP BY
- ROW_NUMBER()
- ORDER BY à l'intérieur d'une fonction de fenêtrage
- PARTITION BY : appliquer par groupe
- PARTITION BY sur plusieurs colonnes
- RANK() et DENSE_RANK() : gérer les ex-aequo
- Comparer ROW_NUMBER, RANK et DENSE_RANK côte à côte
- NTILE() : répartir en groupes égaux
- Cas pratique : combiner plusieurs classements
Isoler le top N par groupe
Impossible d'écrire directement : dans l'ordre logique d'exécution d'une requête SQL, WHERE s'applique avant que les fonctions de…
Voir les détails du chapitre
- Cas d'usage concret : le membre le plus ancien de chaque équipe
- PERCENT_RANK() : la position relative dans le classement
- CUME_DIST() : la proportion de valeurs à ce niveau ou au-delà
- Le piège du top-N avec ROW_NUMBER en présence d'ex-aequo
- RANK ou ROW_NUMBER pour un top-N : lequel choisir ?
- Sous-requête ou CTE pour isoler un rang : lequel préférer ?
- Filtrer avec PERCENT_RANK : garder le premier quart du classement
- NTILE et filtrage : isoler un quartile précis
- Cas pratique : un top inclusif par équipe
LAG et LEAD approfondis
renvoie la valeur de la ligne précédente, selon l'ordre défini ; renvoie celle de la ligne suivante. Pour la toute première (ou de…
Voir les détails du chapitre
- LAG() et LEAD() : valeur de la ligne précédente/suivante
- LAG/LEAD avec un décalage : accéder à plusieurs lignes en arrière
- LAG/LEAD avec une valeur par défaut
- Détecter un changement : le piège de NULL face à =
- Calculer une variation entre une ligne et la précédente
- Détecter la dernière ligne d'une partition avec LEAD IS NULL
- LAG/LEAD avec un PARTITION BY sur plusieurs colonnes
- Combiner LAG et CASE pour étiqueter une tendance
- Cas pratique : ne garder que les manches en hausse
Agrégats de fenêtre et cadres
Contrairement à avec , qui réduit chaque groupe à une seule ligne, conserve toutes les lignes et calcule, pour chacune, un total c…
Voir les détails du chapitre
- SUM() OVER : un total cumulé, pas un total global
- SUM() OVER sans ORDER BY : le total de toute la partition
- AVG() OVER : une moyenne cumulée
- COUNT() OVER : un compteur cumulé ou un total fixe
- ROWS BETWEEN : une fenêtre glissante à taille fixe
- ROWS vs RANGE : ce qui change en présence d'ex-aequo
- UNBOUNDED FOLLOWING : calculer ce qu'il reste à venir
- Combiner AVG() et ROWS BETWEEN : une moyenne mobile
- Cas pratique : le total cumulé de la dernière ligne égale le total de la partition
FIRST_VALUE, LAST_VALUE, NTH_VALUE et fenêtres nommées
renvoie la valeur de la première ligne de la fenêtre, selon l'ORDER BY précisé — répétée sur toutes les lignes de la partition. Ta…
Voir les détails du chapitre
- FIRST_VALUE() : la première valeur de la fenêtre
- FIRST_VALUE() est insensible au cadre par défaut
- LAST_VALUE() et le piège du cadre par défaut
- Corriger LAST_VALUE() avec un cadre explicite
- NTH_VALUE() : la valeur à une position donnée
- Mesurer un écart par rapport à la première valeur
- La clause WINDOW : nommer une fenêtre pour éviter les répétitions
- Une fenêtre nommée avec un cadre explicite partagé
- Cas pratique : comparer la progression du début à la fin
Combiner fenêtres, CASE et sous-requêtes
Étape par étape : 1. classe chaque équipe séparément : ventes → Amara(1), Baptiste(2), Chloé(2, exaequo) ; support → David(1), Far…
Voir les détails du chapitre
- Cas pratique : fonction de fenêtrage + PARTITION BY combinés
- Étiqueter un rang avec CASE
- Une somme conditionnelle : CASE à l'intérieur d'une fonction de fenêtre
- Filtrer sur un cumul conditionnel via une sous-requête
- Combiner une fenêtre, un CASE et un GROUP BY classique
- Filtrer sur deux conditions de fenêtre à la fois
- Fonction de fenêtre vs GROUP BY : une même moyenne, deux approches
- Cas pratique : rang, étiquette et filtre combinés
- Cas pratique final : synthèse d'un bilan complet
Mise en pratique : sport et compétition
Nouveau secteur pour ce chapitre : un club sportif suit les temps de ses athlètes sur plusieurs éditions d'une compétition. Attent…
Voir les détails du chapitre
- Présentation : ROW_NUMBER pour la meilleure performance d'un club
- LAG et la progression entre deux éditions : le piège du signe
- RANK par édition, tous clubs confondus
- SUM() OVER : le temps total cumulé couru par un athlète
- AVG() OVER : le temps moyen cumulé d'un athlète
- FIRST_VALUE() : le temps de référence et l'écart de progression
- Isoler la meilleure performance de chaque club avec RANK
- Comparer un rang de club à un rang général
- Cas pratique final : qui a le plus progressé ?
Mise en pratique : ventes mensuelles
Nouveau secteur pour ce chapitre : une équipe commerciale suit les ventes mensuelles de ses vendeurs (montants en dollars, plus ha…
Voir les détails du chapitre
- Présentation : RANK pour le classement mensuel des vendeurs
- SUM() OVER : le chiffre d'affaires cumulé sur le trimestre
- La part d'un mois dans le total d'un vendeur
- Classer les vendeurs sur un total agrégé : GROUP BY puis RANK
- NTILE pour répartir les vendeurs en deux groupes de performance
- RANK par région
- LAG pour mesurer la progression mensuelle des ventes
- Cas pratique : étiqueter l'atteinte d'un objectif avec CASE
- Cas pratique final : les meilleurs vendeurs finissant en force
Mise en pratique : résultats académiques
Dernier secteur de ce module : le suivi des notes d'étudiants sur plusieurs sessions d'examen (notes sur 20, plus haut = meilleur)…
Voir les détails du chapitre
- Présentation : RANK pour le classement d'une session d'examen
- PERCENT_RANK : la position relative dans sa filière
- LAG et la progression entre deux sessions
- AVG() OVER : la moyenne cumulée d'un étudiant
- FIRST_VALUE et LAST_VALUE : l'écart entre la première et la dernière note
- NTILE pour répartir les étudiants en groupes de niveau
- CASE et fenêtre : attribuer une mention selon la moyenne finale
- Cas pratique : qui a le moins progressé ?
- Cas pratique final du module : un rapport complet
Progression du module
0/81 terminées
Ce module inclut
- ✓ 81 leçons · durée estimée ~14.5h
- ✓ 164 exercices pratiques corrigés
- ✓ Quiz de révision par chapitre
- ✓ Quiz de fin de module
- ✓ Certificat de réussite vérifiable
- ✓ Accès permanent une fois débloqué
Votre certificat
Terminez ce module pour débloquer votre certification vérifiable.
Accès complet
La première leçon de chaque chapitre est gratuite (9 leçons sur 81).
2 500 XOF / 3,81 € / 4,15 $
Débloquez les 72 leçons restantes de ce module