Quelles fonctions window SQL avancées maîtriser en pratique ?

Les fonctions window SQL appliquent des calculs sur des groupes tout en conservant chaque ligne. Je détaille la différence avec les agrégats, la clause OVER(), les cadres ROWS/RANGE/GROUPS et les fonctions de classement/navigation avec exemples et bonnes pratiques.

En quoi diffèrent les window functions des agrégats

Je réponds précisément : Les window functions calculent des valeurs sur un ensemble de lignes (la « fenêtre ») tout en retournant un résultat pour chaque ligne d’origine, alors que les agrégats classiques condensent plusieurs lignes en une seule valeur par groupe (GROUP BY). Cette différence change profondément la granularité des résultats et les cas d’usage possibles.

Exemple de table (id, department, amount, date) :

Requête 1 (agrégat classique) :

SELECT department, SUM(amount) AS total
FROM sales
GROUP BY department;

Résultat attendu :

Découvrez égalementComment comparer et diagnostiquer les écarts GA4 BigQuery ?

Requête 2 (window function) :

SELECT id, department, amount,
       SUM(amount) OVER(PARTITION BY department) AS dept_total
FROM sales;

Résultat attendu :

Cas d’usage concrets (explication suivie de la liste) :

• Totaux par ligne : Permet d’afficher le total du groupe à côté de chaque enregistrement, utile pour rapports exportables.
• Running totals : Cumuls progressifs utilisant OVER(ORDER BY) pour suivre l’évolution temporelle.
• Moving averages : Moyennes mobiles via frame specification (ex : RANGE BETWEEN 6 PRECEDING AND CURRENT ROW).
• Comparaison côte-à-côte : Afficher la valeur agrégée du groupe et la valeur individuelle pour détecter outliers.

Implications sur la granularité et la restitution : Utiliser des window functions conserve la granularité ligne par ligne, facilitant l’export détaillé et les visualisations, alors que GROUP BY réduit la table et nécessite des jointures supplémentaires pour revenir au détail.

Sources : PostgreSQL Window Functions docs — https://www.postgresql.org/docs/current/functions-window.html

Découvrez égalementAI.DETECT_ANOMALIES : Détecter des anomalies GA4 dans BigQuery

Sources : BigQuery Window Functions guide — https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-functions

Comment définir une fenêtre avec OVER

La clause OVER() définit la fenêtre d’application d’une fonction de fenêtre en précisant comment partitionner les lignes et selon quel ordre les parcourir.

PARTITION BY indique le groupe sur lequel le calcul redémarre : chaque partition est traitée indépendamment. ORDER BY définit la séquence des lignes à l’intérieur de chaque partition, nécessaire pour les fonctions sensibles à l’ordre (classement, cumuls séquentiels).

Exemples SQL :

SELECT ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn, *
FROM employees;

SELECT SUM(amount) OVER(
  PARTITION BY dept
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total, *
FROM sales;

-- Deux fenêtres différentes dans le même SELECT
SELECT dept, emp, salary, date, amount,
  ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rank_by_salary,
  SUM(amount) OVER(PARTITION BY dept ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount
FROM demo;

Exemple de données (résultat attendu pour 5 lignes) :

Découvrez égalementGuide de style SQL : comment écrire un code lisible

ORDER BY est obligatoire pour les fonctions de classement (ROW_NUMBER, RANK, DENSE_RANK) car le résultat dépend de l’ordre. ORDER BY est optionnel pour les agrégats en fenêtre (SUM, AVG) si on veut une seule valeur par partition ; il devient nécessaire dès qu’on demande un cumul ou une fenêtre bornée.

La clause WINDOW (disponible dans PostgreSQL, par exemple) permet de réutiliser une définition de fenêtre :

SELECT *,
  ROW_NUMBER() OVER w AS rn,
  SUM(amount) OVER w ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS cum
FROM sales
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Nommer et factoriser les fenêtres améliore la lisibilité et réduit les risques d’erreur. Penser à aligner ORDER BY sur des index pour aider l’optimiseur. Garder les OVER() simples et documentés pour faciliter la maintenance.

Références :

PostgreSQL Window Functions: https://www.postgresql.org/docs/current/functions-window.html

Microsoft SQL Server Window Functions: https://docs.microsoft.com/sql/t-sql/functions/ranking-window-functions-transact-sql

À quoi servent ROWS RANGE et GROUPS

Les cadres ROWS, RANGE et GROUPS servent à définir précisément quelles lignes autour de la ligne courante entrent dans le calcul d’une fonction fenêtre : physique, logique par valeur, ou par groupe de valeurs identiques.

• ROWS : Fenêtre basée sur un nombre fixe de lignes précédentes/suivantes (offset physique).
• RANGE : Fenêtre basée sur l’offset d’une valeur ORDER BY (par exemple une plage temporelle ou numérique). RANGE utilise la valeur ORDER BY, pas l’index de ligne.
• GROUPS : Fenêtre qui regroupe logiquement les lignes partageant la même valeur ORDER BY (appelées peers) et adresse les groupes précédents/suivants.

Exemples SQL (même jeu de données ci‑dessous) :

SELECT id, date, amount,
SUM(amount) OVER(ORDER BY date, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_rows,
SUM(amount) OVER(ORDER BY date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) AS sum_range,
SUM(amount) OVER(ORDER BY date GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_groups
FROM sales
ORDER BY date, id;

• Pièges courants : RANGE inclura tous les peers ayant la même valeur ORDER BY, ce qui élargit parfois la fenêtre de façon inattendue.
• Pièges courants : Comportement et support diverge selon le SGBD (Système de Gestion de Base de Données) ; GROUPS n’est pas implémenté partout (par exemple absent de certaines versions de MySQL).
• Pièges courants : RANGE nécessite généralement une ORDER BY sur un type compatible avec l’intervalle (temporal ou numérique).

• Usage recommandé ROWS : Moyennes mobiles précises, décalages physiques.
• Usage recommandé RANGE : Fenêtres temporelles (ex. « les 7 derniers jours »).
• Usage recommandé GROUPS : Séries avec valeurs répétées où l’on veut traiter les peers comme une seule entité.

Pour approfondir, consulter la doc PostgreSQL : https://www.postgresql.org/docs/current/functions-window.html et Microsoft SQL Server : https://learn.microsoft.com/sql/t-sql/functions/over-clause-transact-sql.

Quelles fonctions de classement et de navigation utiliser

Les fonctions de classement et de navigation servent à ordonner, segmenter et accéder aux lignes voisines pour analyses temporelles, segmentation et détection d’anomalies.

ROW_NUMBER : Numérote les lignes dans l’ordre spécifié. Utile quand vous voulez un identifiant de rang unique.

SELECT id, ROW_NUMBER() OVER(ORDER BY amount DESC) AS rn FROM sales;

RANK : Donne le rang en laissant des « trous » en cas d’égalité. À privilégier quand les égalités doivent conserver le même rang numérique.

SELECT id, RANK() OVER(ORDER BY amount DESC) AS r FROM sales;

DENSE_RANK : Comme RANK mais sans trous. Pratique pour catégories ordinales continues.

SELECT id, DENSE_RANK() OVER(ORDER BY amount DESC) AS dr FROM sales;

NTILE(n) : Distribue les lignes en n groupes (quantiles). Idéal pour segmentation marketing.

SELECT id, NTILE(4) OVER(ORDER BY amount DESC) AS tile FROM sales;

PERCENT_RANK : Rang normalisé entre 0 et 1, calcul = (rank-1)/(n-1). Utile pour score relatif.

SELECT id, PERCENT_RANK() OVER(ORDER BY amount DESC) AS pr FROM sales;

LAG / LEAD : Accèdent aux lignes précédentes/suivantes. Parfait pour calculer variations séquentielles.

SELECT id, client, amount, LAG(amount) OVER(PARTITION BY client ORDER BY date) AS prev_amt FROM sales;

FIRST_VALUE / LAST_VALUE : Récupèrent première/dernière valeur dans la fenêtre. Attention au framing pour LAST_VALUE.

SELECT id, FIRST_VALUE(amount) OVER(PARTITION BY client ORDER BY date) AS first_amt,
LAST_VALUE(amount) OVER(PARTITION BY client ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amt
FROM sales;

Cas pratiques : (a) Variations jour à jour avec LAG :

SELECT id, date, amount, amount - LAG(amount) OVER(ORDER BY date, id) AS diff FROM sales;

(b) Quartiles clients avec NTILE(4) après agrégation :

SELECT client, SUM(amount) AS total, NTILE(4) OVER(ORDER BY SUM(amount) DESC) AS quartile FROM sales GROUP BY client;

Valeurs manquantes : LAG/LEAD acceptent un paramètre de défaut LAG(expr, offset, default) pour remplacer NULL. FIRST_VALUE/ LAST_VALUE retournent NULL si la valeur source est NULL; framing peut influer.

Bonnes pratiques & performance : Préférer LAG/LEAD pour lisibilité et performance sur données séquentielles. Éviter les self-joins quand la logique porte sur voisinage : les self-joins doublent ou quadrillent les lignes et empêchent certains optimisations. Optimiser en triant sur index et en limitant PARTITION pour réduire mémoire temporaire.

Prêt à appliquer les window functions SQL à vos jeux de données ?

Les window functions permettent d’obtenir des analyses fines (totaux cumulatifs, rangs, comparaisons temporaires) sans perdre la granularité ligne par ligne. En maîtrisant OVER(), les cadres ROWS/RANGE/GROUPS et les fonctions de classement/navigation, vous pouvez produire des rapports plus riches et réutilisables. Appliquez d’abord des exemples simples (ROW_NUMBER, SUM() OVER, LAG) puis industrialisez. Bénéfice immédiat : diagnostics et segmentations plus précis, sans complexifier vos exports ou perdre d’information.

FAQ

• Qu’est-ce qu’une window function en SQL ?
  Une window function réalise un calcul sur un ensemble de lignes (« fenêtre ») tout en renvoyant un résultat pour chaque ligne source. Elle utilise la clause OVER() pour définir partition et ordre, contrairement aux agrégats classiques qui réduisent le nombre de lignes.
• Quand utiliser OVER() au lieu de GROUP BY ?
  Utilisez OVER() quand vous voulez conserver la granularité ligne par ligne tout en affichant des agrégats ou des rangs. GROUP BY est adapté lorsque vous voulez une seule ligne par groupe (résumé).
• Quelle différence entre ROWS et RANGE dans un window frame ?
  ROWS compte un nombre fixe de lignes autour de la ligne courante; RANGE définit la fenêtre sur une plage logique basée sur la valeur ORDER BY (ex : intervalle temporel). RANGE peut se comporter différemment si ORDER BY contient des valeurs dupliquées.
• Les window functions sont-elles lentes ?
  Elles peuvent être coûteuses sur de très grands volumes si mal utilisées (tri, partitionnement). Optimisez par index sur les colonnes ORDER/BY/PARTITION, limitez la taille des partitions et testez les plans d’exécution. Parfois une table temporaire pré-agrégée est plus efficace.
• Les window functions sont-elles supportées par tous les SGBD ?
  La majorité des SGBD modernes (PostgreSQL, SQL Server, Oracle, BigQuery, MySQL 8+) supportent les window functions, mais certains cadres ou fonctionnalités (ex : GROUPS) et la syntaxe exacte peuvent varier. Vérifiez la documentation de votre SGBD.

A propos de l’auteur

Franck Scandolera — expert & formateur en tracking server-side, Analytics Engineering et automatisation No/Low Code (n8n). J’accompagne l’intégration de l’IA en entreprise et l’optimisation du SEO/GEO. Responsable de l’agence webAnalyste et de l’organisme Formations Analytics. Références clients : Logis Hôtel, Yelloh Village, BazarChic, Fédération Française de Football, Texdecor. Disponible pour aider les entreprises => contactez moi.

Franck Scandolera

⭐ Analytics engineer, Data Analyst et Automatisation IA indépendant ⭐

• Ref clients : Logis Hôtel, Yelloh Village, BazarChic, Fédération Football Français, Texdecor…

Mon terrain de jeu :

• Data Analyst & Analytics engineering : tracking avancé (GA4, Matomo, Piano, GTM server, Tealium, Commander Act, e-commerce, CAPI, RGPD), entrepôt de données (BigQuery, Snowflake, PostgreSQL, ClickHouse), modèles (Airflow, dbt, Dataform), dashboards décisionnels (Looker, Power BI, Metabase, SQL, Python).
• Automatisation IA des taches Data, Marketing, RH, compta etc : conception de workflows intelligents robustes (n8n, App Script, scraping) connectés aux API de vos outils et LLM (OpenAI, Mistral, Claude…).
• Engineering IA pour créer des applications et agent IA sur mesure : intégration de LLM (OpenAI, Mistral…), RAG, assistants métier, génération de documents complexes, APIs, backends Node.js/Python.