Window functions

SQL - Cet article fait partie d'une série.

Partie 13: Cet article

Partie 15: Ordre d’écriture & d’exécution

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

Une fonction fenêtre :

Travaille ligne par ligne,
Peut accéder aux lignes voisines,
Ne réduit pas le nombre de lignes (contrairement au GROUP BY).

Elle repose sur la clause :

OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN ...
)

🧭 Navigation dans les lignes
#

LAG(): valeur précédente

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

LEAD(): valeur suivante

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

FIRST_VALUE() / LAST_VALUE():

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

⚠️ LAST_VALUE() nécessite souvent un ROWS BETWEEN UNBOUNDED FOLLOWING selon les moteurs.

📊 Fonctions cumulées
#

Exemple : cumul du montant des commandes

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

⚙️ Options de fenêtre
#

Option	Effet
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Cumul depuis le début
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	Fenêtre glissante autour de chaque ligne
`PARTITION BY`	Réinitialise le calcul par groupe
`ORDER BY`	Définit la progression dans le groupe

🧯 Pièges fréquents
#

⚠️ Piège	✅ Solution
Oublier `ORDER BY` → résultats instables	Toujours ordonner les valeurs
Mélanger `GROUP BY` avec `OVER()`	Comprendre que `OVER` ne réduit pas les lignes
`LAST_VALUE()` renvoie la valeur courante	Ajouter `ROWS BETWEEN UNBOUNDED FOLLOWING`
Fenêtre trop large → résultats absurdes	Ajuster `ROWS BETWEEN ...` selon le besoin

🏋️‍♂️ Entraînement
#

Obtenir la date précédente et suivante de chaque commande (LAG / LEAD).
Calculer l’évolution du montant d’un client (montant courant vs précédent).
Construire le montant cumulé par client.
Calculer la différence entre deux jours successifs pour un produit.

duckdb.sql("""
SELECT
  client_id, date_commande, montant,
  LAG(montant) OVER (PARTITION BY client_id ORDER BY date_commande) AS prev,
  SUM(montant) OVER (PARTITION BY client_id ORDER BY date_commande) AS cumul
FROM commandes
""").df()