Tutoriel sur les fonctions de fenêtrage SQL
Le guide complet pour maîtriser toutes les window functions.
Cumuls, variations journalières, classements, moyennes mobiles, parts de marché et évolution annuelle – découvrez comment analyser un dataset de 62 833 ventes avec les fonctions de fenêtrage SQL.
Dans le précédent cas pratique, nous avons nettoyé un dataset de 63 000 ventes. Nous disposons désormais d'une table clean_sales de qualité, prête pour l'analyse.
Ce second cas pratique explore les fonctions de fenêtrage (window functions) de SQL : SUM() OVER, LAG(), RANK(), AVG() OVER avec fenêtre glissante. Ces fonctions permettent de réaliser des analyses avancées sans sous-requêtes complexes.
À l'issue de ce cas pratique, vous saurez :
- Calculer des cumuls et parts de marché avec SUM() OVER
- Comparer des valeurs entre lignes avec LAG() et LEAD()
- Classer des données avec RANK() et DENSE_RANK()
- Lisser des séries temporelles avec des moyennes mobiles
- Analyser des évolutions annuelles avec LAG(..., 12)
Figure 1 : Aperçu de la table clean_sales après nettoyage.
Pour suivre ce cas pratique, vous avez besoin de :
clean_sales_data.csv ou la base sales_cleaning.dbSQLite ne possède pas de type DATE natif. Les dates sont stockées au format MM-DD-YYYY et triées alphabétiquement, pas chronologiquement. Cela fausse toutes les analyses temporelles.
Figure 2 : Avant correction – les dates ne sont pas triées chronologiquement.
Solution : Ajouter une colonne Sale_Date_ISO au format YYYY-MM-DD.
ALTER TABLE clean_sales ADD COLUMN Sale_Date_ISO TEXT;
UPDATE clean_sales
SET Sale_Date_ISO = SUBSTR(Sale_Date_Clean, 7, 4) || '-' ||
SUBSTR(Sale_Date_Clean, 1, 2) || '-' ||
SUBSTR(Sale_Date_Clean, 4, 2)
WHERE Sale_Date_Clean LIKE '__-__-____';
Figure 3 : Vérification – les dates sont maintenant au format ISO.
Certains produits avaient une catégorie NULL, ce qui perturbait les classements. La solution : imputer la catégorie la plus fréquente pour chaque produit.
-- 1. Table de correspondance (produit → catégorie la plus fréquente)
CREATE TABLE product_category_map AS
SELECT
Product_Name,
Product_Category,
COUNT(*) AS freq,
ROW_NUMBER() OVER (PARTITION BY Product_Name ORDER BY COUNT(*) DESC) AS rn
FROM clean_sales
WHERE Product_Category IS NOT NULL
GROUP BY Product_Name, Product_Category;
-- 2. Garder seulement la catégorie la plus fréquente par produit
CREATE TABLE product_category_final AS
SELECT Product_Name, Product_Category
FROM product_category_map
WHERE rn = 1;
-- 3. Mise à jour des NULL
UPDATE clean_sales
SET Product_Category = (
SELECT Product_Category
FROM product_category_final
WHERE product_category_final.Product_Name = clean_sales.Product_Name
)
WHERE Product_Category IS NULL;
Figure 4 : Après correction – plus de NULL, classement propre par catégorie.
Objectif : suivre l'évolution cumulée du chiffre d'affaires pour chaque région.
SELECT
COALESCE(Store_Location, 'Inconnu') AS Store_Location,
Sale_Date_Clean,
Total_Amount,
SUM(Total_Amount) OVER (
PARTITION BY COALESCE(Store_Location, 'Inconnu')
ORDER BY Sale_Date_Clean
) AS cumul_ventes
FROM clean_sales
ORDER BY COALESCE(Store_Location, 'Inconnu'), Sale_Date_Clean
LIMIT 30;PARTITION BY Store_Location redémarre le cumul à chaque région.ORDER BY Sale_Date_Clean cumule dans l'ordre chronologique.COALESCE(..., 'Inconnu') regroupe les 641 lignes sans localisation sous une étiquette explicite.
Figure 5 : Évolution cumulée des ventes – CHICAGO, IL en exemple.
Objectif : comparer le chiffre d'affaires d'un jour avec celui de la veille.
SELECT
Sale_Date_ISO,
ROUND(SUM(Total_Amount), 2) AS ventes_journalieres,
ROUND(LAG(SUM(Total_Amount), 1) OVER (ORDER BY Sale_Date_ISO), 2) AS ventes_veille,
ROUND(SUM(Total_Amount) - LAG(SUM(Total_Amount), 1) OVER (ORDER BY Sale_Date_ISO), 2) AS variation
FROM clean_sales
WHERE Sale_Date_ISO IS NOT NULL
GROUP BY Sale_Date_ISO
ORDER BY Sale_Date_ISO
LIMIT 30;LAG(Total_Amount, 1) accède à la valeur de la ligne précédente (la veille).
La première ligne a ventes_veille = NULL (pas de jour précédent).
La colonne variation calcule la différence entre aujourd'hui et hier.
Figure 6 : Après correction des dates, la variation est maintenant chronologique.
Objectif : identifier les produits les plus vendus dans chaque catégorie.
SELECT
Product_Category,
Product_Name,
ROUND(SUM(Total_Amount), 2) AS total_ventes,
RANK() OVER (PARTITION BY Product_Category ORDER BY SUM(Total_Amount) DESC) AS rang
FROM clean_sales
GROUP BY Product_Category, Product_Name
ORDER BY Product_Category, rang
LIMIT 30;RANK() OVER (PARTITION BY Product_Category ORDER BY SUM(Total_Amount) DESC)
- Classe les produits au sein de chaque catégorie (partie)
- Le meilleur produit a le rang 1
- En cas d'égalité, RANK saute des numéros (ex: 1,1,3)
Figure 7 : Top produits – dans chaque catégorie, les meilleures ventes sont identifiées.
Objectif : lisser les variations quotidiennes pour visualiser la tendance.
SELECT
Sale_Date_ISO,
ROUND(Total_Amount, 2) AS Total_Amount,
ROUND(AVG(Total_Amount) OVER (
ORDER BY Sale_Date_ISO
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2) AS moyenne_mobile_7j
FROM clean_sales
WHERE Sale_Date_ISO IS NOT NULL
ORDER BY Sale_Date_ISO
LIMIT 30;ROWS BETWEEN 6 PRECEDING AND CURRENT ROW définit une fenêtre glissante de 7 lignes (les 6 précédentes + la ligne courante).
La moyenne mobile "lisse" les pics et creux pour révéler la tendance de fond.
Figure 8 : Moyenne mobile sur 7 jours – tendance plus lisible.
Objectif : calculer la contribution de chaque région au chiffre d'affaires total.
SELECT
COALESCE(Store_Location, 'Inconnu') AS Store_Location,
ROUND(SUM(Total_Amount), 2) AS ventes_region,
ROUND(100.0 * SUM(Total_Amount) / SUM(SUM(Total_Amount)) OVER (), 2) AS part_pourcentage
FROM clean_sales
GROUP BY COALESCE(Store_Location, 'Inconnu')
ORDER BY ventes_region DESC;SUM(SUM(Total_Amount)) OVER () calcule le total général des ventes.
Le ratio SUM(Total_Amount) / total_général * 100 donne la part de marché.
L'absence de PARTITION BY applique le calcul à toute la table.
Figure 9 : Parts de marché – 6 régions se partagent ~99% des ventes, 1% est non localisé.
Objectif : extraire les 3 meilleurs produits de chaque catégorie.
SELECT * FROM (
SELECT
Product_Category,
Product_Name,
ROUND(SUM(Total_Amount), 2) AS total_ventes,
RANK() OVER (PARTITION BY Product_Category ORDER BY SUM(Total_Amount) DESC) AS rang
FROM clean_sales
GROUP BY Product_Category, Product_Name
) AS ranked
WHERE rang <= 3
ORDER BY Product_Category, rang;La sous-requête calcule le rang de chaque produit dans sa catégorie.
La requête externe filtre pour ne garder que les rangs 1, 2 et 3.
On obtient ainsi le "podium" des produits par catégorie.
Figure 10 : Podium des produits – chaque catégorie a son top 3.
| Catégorie | 1er produit | 2e produit | 3e produit |
|---|---|---|---|
| BEAUTY | Shampoo | Toothpaste | Soap |
| CLOTHING | T-shirt | Jeans | Sneakers |
| ELECTRONICS | Smartwatch | Tablet | Smartphone |
| GROCERIES | Cereal | Coffee | Eggs |
| HOME GOODS | Towels | Curtains | Lamp |
Objectif : comparer les ventes d'un mois avec celles du même mois de l'année précédente.
WITH ventes_par_mois AS (
SELECT
strftime('%Y-%m', Sale_Date_ISO) AS mois,
ROUND(SUM(Total_Amount), 2) AS ventes_mois
FROM clean_sales
WHERE Sale_Date_ISO IS NOT NULL
GROUP BY strftime('%Y-%m', Sale_Date_ISO)
)
SELECT
mois,
ventes_mois,
LAG(ventes_mois, 12) OVER (ORDER BY mois) AS ventes_annee_precedente,
ROUND(100.0 * (ventes_mois - LAG(ventes_mois, 12) OVER (ORDER BY mois))
/ NULLIF(LAG(ventes_mois, 12) OVER (ORDER BY mois), 0), 2) AS evolution_pct
FROM ventes_par_mois
ORDER BY mois;Une CTE (Common Table Expression) pré-calcule les ventes par mois.LAG(ventes_mois, 12) OVER (ORDER BY mois) récupère la valeur d'il y a 12 mois.
Les 12 premiers mois ont NULL (pas d'année précédente).NULLIF évite la division par zéro.
Figure 11 : Comparaison année sur année – croissance soutenue jusqu'en mars 2026.
Ce cas pratique a démontré la puissance des fonctions de fenêtrage SQL pour l'analyse de données métier. En une vingtaine de requêtes, nous avons :
SUM() OVERLAG()RANK()LAG(..., 12)SUM() OVER (PARTITION BY ... ORDER BY ...) : cumuls par groupeLAG() / LEAD() : comparer avec lignes précédentes/suivantesRANK() / DENSE_RANK() : classements avec ou sans sautsAVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW) : moyennes mobilesSUM() OVER () : total général (sans PARTITION BY)SQLite ne possède pas de type DATE natif. Les dates stockées en texte sont triées alphabétiquement. MM-DD-YYYY mélange les années. La conversion en YYYY-MM-DD garantit un tri chronologique correct.
RANK() saute des numéros après une égalité (ex: 1,1,3,4). DENSE_RANK() ne saute jamais (ex: 1,1,2,3). Pour un classement sans trous, utilisez DENSE_RANK().
Globalement oui. Les window functions sont standard SQL. Seules quelques différences : strftime devient DATE_FORMAT (MySQL) ou TO_CHAR (PostgreSQL). GLOB est spécifique à SQLite.
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ?Cela définit une fenêtre glissante de 7 lignes : les 6 lignes précédentes + la ligne courante. La moyenne est recalculée à chaque ligne, ce qui "lisse" les variations quotidiennes.
SQL ne permet pas de référencer un alias (mois) dans la même clause OVER. La CTE pré-calcule les ventes mensuelles, puis on applique LAG sur cette table dérivée.
