SQL avancé : CTE, requêtes récursives et fonctions de fenêtre
SQL avancé (CTE, fonctions de fenêtre) est un cours de niveau avancé de 5 leçons (environ 2 h 47), avec 17 exercices et 15 questions de quiz. Prérequis : SQL intermédiaire (jointures, sous-requêtes). Les exercices s'exécutent dans le navigateur, sans installation.
- SQL
- Avancé
- 5 leçons
- 2 h 47
- 17 exercices
- Exercices exécutés dans le navigateur
Le cours en bref
Ce cours avancé vous donne les outils du SQL analytique utilisés au quotidien par les data analysts. Vous structurerez vos requêtes avec les CTE, parcourrez des hiérarchies et générerez des calendriers avec WITH RECURSIVE, puis maîtriserez les fonctions de fenêtre : classements, top N par groupe, cumuls, moyennes mobiles et comparaisons période sur période.
Ce que tu sauras faire
- Découper une analyse complexe en CTE lisibles et testables
- Générer des séries et parcourir des hiérarchies avec WITH RECURSIVE
- Calculer des agrégats par partition sans perdre le détail des lignes
- Classer des lignes et extraire un top N par groupe avec ROW_NUMBER, RANK et DENSE_RANK
- Calculer des cumuls et des moyennes mobiles en maîtrisant les cadres de fenêtre
- Mesurer des évolutions et des délais avec LAG et LEAD, et segmenter avec NTILE
Programme des 5 leçons
- Écrire une expression de table commune (CTE) avec WITH
- Enchaîner plusieurs CTE pour découper un calcul en étapes
- Choisir entre CTE, sous-requête et vue
- 02
Les CTE récursives : séries et hiérarchies
- Comprendre la structure d'une CTE récursive (partie initiale, partie récursive, condition d'arrêt)
- Générer une série de nombres ou de dates
- Parcourir une hiérarchie (organigramme, catégories) en calculant le niveau et le chemin
- 03
Fonctions de fenêtre : OVER, PARTITION BY et classements
- Comprendre la différence entre un agrégat classique et une fonction de fenêtre
- Calculer un agrégat par partition sans réduire le nombre de lignes
- Classer des lignes avec ROW_NUMBER, RANK et DENSE_RANK
- Extraire le top N par groupe
Clause OVERPARTITION BYROW_NUMBER, RANK, DENSE_RANKTop N par groupe
- 04
Cumuls et moyennes mobiles : ORDER BY et cadres de fenêtre
- Calculer un cumul (running total) avec SUM ... OVER (ORDER BY ...)
- Réinitialiser un cumul par groupe avec PARTITION BY
- Définir un cadre de fenêtre avec ROWS BETWEEN pour une moyenne mobile
- Comprendre le cadre par défaut et la différence ROWS / RANGE
- 05
Comparer dans le temps : LAG, LEAD et NTILE
- Accéder à la ligne précédente ou suivante avec LAG et LEAD
- Calculer des évolutions (écart et pourcentage) d'une période à l'autre
- Mesurer des délais entre événements d'un même client ou vendeur
- Répartir des lignes en groupes de taille égale avec NTILE
Projet final
Rapport analytique mensuel des ventes
À partir d'une table de ventes sur 18 mois (date, région, vendeur, client, montant) et d'une table de hiérarchie commerciale, produisez en SQL les données d'un rapport mensuel : série mensuelle complète, évolutions, cumuls annuels, classements et segmentation des clients.
Livrables
- Un fichier .sql structuré en CTE nommées et commentées
- Une série mensuelle complète générée par CTE récursive (mois sans vente à 0)
- Le chiffre d'affaires mensuel avec cumul annuel, moyenne mobile sur 3 mois et évolution en pourcentage par rapport au mois précédent
- Le top 3 des vendeurs par région avec gestion explicite des ex aequo (RANK ou DENSE_RANK justifié)
- La segmentation des clients en quartiles de dépense (NTILE) et le délai moyen entre deux achats par segment
- Le chiffre d'affaires consolidé par responsable commercial grâce au parcours de la hiérarchie
Critères d'évaluation
- Exactitude des résultats, vérifiée sur un petit échantillon calculé à la main
- Cadres de fenêtre explicites et tris déterministes
- Gestion des cas limites : premier mois, division par zéro, ex aequo, mois manquants
- Lisibilité : CTE bien nommées, une étape par CTE, commentaires
- Portabilité : différences SQLite / PostgreSQL signalées en commentaire
Questions fréquentes
Qu'est-ce qu'une fonction de fenêtre en SQL ?
C'est une fonction qui calcule une valeur pour chaque ligne à partir d'un ensemble de lignes liées (la fenêtre), définie par la clause OVER, sans regrouper les lignes comme le ferait GROUP BY. Elle sert aux classements, cumuls et comparaisons.
Quelle différence entre une CTE et une sous-requête ?
Le résultat est le même, mais une CTE est nommée, écrite avant la requête principale et réutilisable plusieurs fois dans celle-ci. Elle rend les analyses en plusieurs étapes bien plus lisibles.
Les fonctions de fenêtre fonctionnent-elles dans tous les moteurs ?
Oui pour les moteurs actuels : PostgreSQL, SQLite (depuis 3.25), MySQL 8, MariaDB, SQL Server, Oracle et les entrepôts cloud. Le cours utilise SQLite et signale les différences avec PostgreSQL.
Ce cours est-il utile pour Power BI ?
Oui. Préparer des données propres en SQL (calendriers, classements, cumuls) simplifie le modèle Power BI, et les notions de fenêtre aident à comprendre les fonctions DAX équivalentes.
Ce cours, raconté pour ton métier
Dis ce que tu veux savoir faire : « SQL avancé (CTE, fonctions de fenêtre) » prend sa place dans ton parcours, avec des exemples de ton secteur. Les notions restent celles du cours.