Module 5 : Fonctions Fenêtres & Analytique

🪟 Module 5 – Fonctions Fenêtres & Analytique (OVER, RANK, ROW_NUMBER, etc.)

#

🎯 Objectifs

#

À la fin du module, tu sauras :

• Faire des calculs ligne par ligne avec fenêtre
• Utiliser ROW_NUMBER, RANK, DENSE_RANK pour classer les lignes
• Naviguer dans les lignes (ex. LAG, LEAD, FIRST_VALUE)
• Bien structurer tes clauses OVER (PARTITION BY ... ORDER BY ...)

📌 1. Qu’est-ce qu’une fonction fenêtre ?

#

Une fonction de fenêtre :

• Travaille ligne par ligne
• Accède à un contexte global (tri, partition)
• Ne réduit pas le nombre de lignes (≠ agrégations classiques)

🧠 Syntaxe

#

fonction() OVER (
  PARTITION BY ...  -- optionnel : sous-groupes
  ORDER BY ...      -- optionnel : tri dans chaque groupe
  ROWS ...          -- optionnel : plage de lignes à considérer
)

🧮 2. Fonctions de classement

#

🔹 ROW_NUMBER()

#

Numérote chaque ligne dans chaque groupe.

SELECT client_id, commande_id,
  ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_commande) AS rang
FROM commandes;

🔢 Résultat : 1, 2, 3, ...

🔹 RANK() et DENSE_RANK()

#

Classe selon un ordre, avec gestion des égalités :

• RANK(): saute les rangs en cas d’égalité
• DENSE_RANK(): ne saute pas les rangs

SELECT produit, montant,
  RANK() OVER (PARTITION BY produit ORDER BY montant DESC) AS rang_classique,
  DENSE_RANK() OVER (PARTITION BY produit ORDER BY montant DESC) AS rang_densifié
FROM ventes;

🔁 3. Navigation dans la fenêtre

#

🔹 LAG() et LEAD()

#

Accède à la valeur avant ou après la ligne courante.

SELECT client_id, date_commande,
  LAG(date_commande) OVER (PARTITION BY client_id ORDER BY date_commande) AS prev_date,
  LEAD(date_commande) OVER (PARTITION BY client_id ORDER BY date_commande) AS next_date
FROM commandes;

🔹 FIRST_VALUE() et LAST_VALUE()

#

Renvoie la première ou dernière valeur d’un groupe.

SELECT client_id, montant,
  FIRST_VALUE(montant) OVER (PARTITION BY client_id ORDER BY date_commande) AS montant_initial
FROM commandes;

📊 4. Fonctions cumulées

#

🔹 SUM(...) OVER(...)

#

Cumul progressif (utile pour suivre des évolutions dans le temps).

SELECT client_id, date_commande, montant,
  SUM(montant) OVER (PARTITION BY client_id ORDER BY date_commande) AS cumul_client
FROM commandes;

🧯 5. Pièges fréquents

#

🏋️‍♂️ 6. Exercices

#

Exo 1 – Pour chaque client, numéroter les commandes dans l’ordre chronologique (ROW_NUMBER())

#

Exo 2 – Afficher, pour chaque commande, la date de la commande précédente du même client (LAG())

#

Exo 3 – Pour chaque produit, afficher les 3 ventes les plus chères (RANK() ou DENSE_RANK())

#

Exo 4 – Pour chaque client, calculer le montant cumulé de ses commandes (SUM(...) OVER)

#

🧰 7. Test rapide dans DuckDB (Python)

#

duckdb.sql("""
SELECT client_id, date_commande, montant,
  SUM(montant) OVER (PARTITION BY client_id ORDER BY date_commande) AS montant_cumule,
  ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_commande) AS rang
FROM commandes
""").df()

📚 8. Ressources utiles

#

• 📘 Mode – SQL Window Functions
• 🧠 PostgreSQL – Window Functions
• 🎮 [SQLFiddle ou DuckDB shell] pour tester