Window Functions SQL - Exercices interactifs et explications en français
Les Window Functions sont le sujet numéro un en entretien technique data. Elles te permettent de faire des calculs sur un ensemble de lignes sans perdre le détail de chaque ligne - contrairement à GROUP BY qui écrase les données.
C'est quoi une Window Function ?
Une Window Function exécute un calcul sur un groupe de lignes (la "fenêtre") tout en conservant chaque ligne dans le résultat. La syntaxe de base est :
fonction() OVER ( PARTITION BY colonne_de_partition ORDER BY colonne_de_tri )
PARTITION BY définit les groupes (comme GROUP BY, mais sans réduction). ORDER BYdéfinit l'ordre à l'intérieur de chaque partition. Tu peux omettre PARTITION BY pour travailler sur toutes les lignes, ou omettre ORDER BY quand l'ordre n'a pas d'importance.
Les 7 Window Functions essentielles
ROW_NUMBER()
Attribue un numéro unique et séquentiel à chaque ligne dans la partition. Pas d'ex aequo : si deux lignes ont la même valeur, elles auront quand même des numéros différents.
SELECT nom, departement, salaire,
ROW_NUMBER() OVER (
PARTITION BY departement
ORDER BY salaire DESC
) AS rang
FROM employes;RANK()
Comme ROW_NUMBER, mais gère les ex aequo : deux lignes avec la même valeur reçoivent le même rang, puis le rang suivant est sauté (1, 2, 2, 4).
SELECT nom, salaire,
RANK() OVER (ORDER BY salaire DESC) AS rang
FROM employes;DENSE_RANK()
Identique à RANK, mais sans saut : les ex aequo reçoivent le même rang et le rang suivant est consécutif (1, 2, 2, 3).
SELECT nom, salaire,
DENSE_RANK() OVER (ORDER BY salaire DESC) AS rang_dense
FROM employes;LAG()
Accède à la valeur de la ligne précédente dans la partition. Idéal pour comparer une ligne avec la précédente (évolution mois par mois, par exemple).
SELECT mois, chiffre_affaires,
LAG(chiffre_affaires) OVER (ORDER BY mois) AS ca_precedent,
chiffre_affaires - LAG(chiffre_affaires) OVER (ORDER BY mois) AS evolution
FROM ventes_mensuelles;LEAD()
L'inverse de LAG : accède à la valeur de la ligne suivante. Utile pour anticiper ou calculer des écarts avec la prochaine valeur.
SELECT nom, date_embauche,
LEAD(date_embauche) OVER (ORDER BY date_embauche) AS prochaine_embauche
FROM employes;SUM() OVER
Calcule une somme cumulative ou un total par partition sans écraser les lignes. Avec ORDER BY, tu obtiens un cumul progressif.
SELECT date_vente, montant,
SUM(montant) OVER (ORDER BY date_vente) AS cumul
FROM ventes;AVG() OVER
Calcule une moyenne glissante ou une moyenne par partition. Combiné avec ROWS BETWEEN pour créer des moyennes mobiles.
SELECT date_vente, montant,
AVG(montant) OVER (
ORDER BY date_vente
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moyenne_mobile_3j
FROM ventes;Exercices pratiques
Pour chaque departement, affiche le nom de l'employe, son salaire et son rang salarial (du plus eleve au plus bas). Utilise DENSE_RANK pour gerer les ex aequo.
Voir la solution
SELECT departement, nom, salaire,
DENSE_RANK() OVER (
PARTITION BY departement
ORDER BY salaire DESC
) AS rang_salarial
FROM employes;A partir de la table ventes_mensuelles (mois, chiffre_affaires), affiche chaque mois avec le CA du mois precedent et le pourcentage d'evolution.
Voir la solution
SELECT mois,
chiffre_affaires,
LAG(chiffre_affaires) OVER (ORDER BY mois) AS ca_precedent,
ROUND(
(chiffre_affaires - LAG(chiffre_affaires) OVER (ORDER BY mois))
* 100.0 / LAG(chiffre_affaires) OVER (ORDER BY mois),
2
) AS evolution_pct
FROM ventes_mensuelles;Affiche les 3 produits les plus vendus par categorie. Utilise ROW_NUMBER dans une sous-requete pour filtrer uniquement le top 3.
Voir la solution
SELECT categorie, nom_produit, total_ventes
FROM (
SELECT categorie, nom_produit, total_ventes,
ROW_NUMBER() OVER (
PARTITION BY categorie
ORDER BY total_ventes DESC
) AS rn
FROM produits
) AS classement
WHERE rn <= 3;Pret a maitriser les Window Functions ?
Sur DataCertification, tu t'entraines avec un editeur SQL integre et tu recois une correction instantanee. Commence par les bases du SQL ou passe directement aux exercices avances.