Comment utiliser les fonctions fenêtre SQL en business ?

Résumer ce contenu avec :

Perplexity

ChatGPT

Claude

Grok

Mistral

Les fonctions fenêtre SQL résolvent des analyses business que GROUP BY rend vite illisibles. Cumul de revenus, séries d’activité, sessions utilisateur, classement fiable : je vous montre les patterns à maîtriser pour garder le détail des lignes tout en calculant sur leur contexte.

Pourquoi GROUP BY ne suffit pas ?

GROUP BY ne suffit pas quand on veut agréger des données tout en conservant le détail de chaque ligne. C’est le point clé. Avec GROUP BY, vous obtenez une ligne par groupe. Avec une fonction fenêtre SQL, vous gardez chaque ligne, mais vous ajoutez un calcul basé sur un ensemble de lignes liées.

GROUP BY répond très bien à une question comme : “Quel est le revenu total par mois ?”. Le résultat est compact, mais le détail disparaît. Si vous aviez 10 000 achats, vous n’aurez plus que 12 lignes pour une analyse annuelle par mois.

Une fonction fenêtre répond à une question plus riche : “Pour chaque mois, quel est le revenu du mois, son évolution par rapport au mois précédent, et son rang dans l’année ?”. La ligne reste visible, et le calcul vient l’enrichir.

Comme on dit à Brive, un bon plan de marquage vaut mieux qu’un bon reporting ! Si besoin, consultez moi - faites appel à un super consultant en tracking client et server side.

La clause OVER() définit la fenêtre de calcul. Cette fenêtre correspond à l’ensemble de lignes que SQL doit regarder pour calculer une métrique autour de la ligne courante.

PARTITION BY découpe les données en groupes logiques, par exemple par client, pays ou catégorie.
ORDER BY définit l’ordre de calcul dans chaque groupe, par exemple par mois ou par date d’achat.
La fenêtre est donc le périmètre de lignes utilisé par la fonction, sans supprimer les lignes d’origine.

Les documentations PostgreSQL et Microsoft SQL Server décrivent les fonctions fenêtre comme des calculs effectués sur un ensemble de lignes liées à la ligne courante, défini par la clause OVER(). C’est cette différence qui les rend très utiles en analyse business.

SELECT
  mois,
  revenu_mensuel,
  SUM(revenu_mensuel) OVER (ORDER BY mois) AS revenu_cumule,
  LAG(revenu_mensuel) OVER (ORDER BY mois) AS revenu_mois_precedent,
  LEAD(revenu_mensuel) OVER (ORDER BY mois) AS revenu_mois_suivant,
  ROW_NUMBER() OVER (ORDER BY revenu_mensuel DESC) AS rang_revenu
FROM revenus_mensuels;

Dans cet exemple, SUM() OVER() calcule un cumul, LAG() lit la ligne précédente, LEAD() lit la ligne suivante, et ROW_NUMBER() numérote les lignes selon un ordre donné. Ces fonctions permettent de suivre une chronologie sans casser le niveau de détail.

Besoin d’analyse	GROUP BY	Fonction fenêtre SQL
Calculer le revenu total par mois	Adapté, car une ligne par mois suffit	Possible, mais pas toujours nécessaire
Afficher chaque mois avec un cumul annuel	Insuffisant, car le détail temporel enrichi manque	Adaptée avec SUM() OVER(ORDER BY mois)
Comparer un mois au mois précédent	Difficile sans jointure ou sous-requête	Adaptée avec LAG()
Classer les mois par performance	Limité au résultat agrégé	Adaptée avec ROW_NUMBER() ou RANK()

Comment calculer un cumul fiable ?

Un cumul fiable se calcule avec SUM() OVER(), à condition de définir clairement le niveau d’agrégation et l’ordre chronologique. Pour suivre un chiffre d’affaires cumulé, une consommation, un budget ou l’avancement vers un objectif annuel, le point clé n’est pas la fonction SQL elle-même, mais le grain de calcul.

Le plus souvent, il faut travailler en deux temps. D’abord, les données sont agrégées au bon niveau, par exemple par mois. Ensuite, la fonction fenêtre calcule le cumul sur ces résultats mensuels. Si vous appliquez directement le cumul sur des lignes transactionnelles alors que votre objectif est mensuel, vous risquez d’obtenir un résultat techniquement juste, mais inutilisable pour le pilotage.

CREATE TABLE amazon_purchases (
  created_at TIMESTAMP,
  user_id INT,
  purchase_amt NUMERIC(10, 2)
);

INSERT INTO amazon_purchases (created_at, user_id, purchase_amt) VALUES
('2024-01-05 10:15:00', 1, 120.00),
('2024-01-18 14:20:00', 2, 80.00),
('2024-02-03 09:00:00', 1, 150.00),
('2024-02-22 16:45:00', 3, 50.00),
('2024-03-10 11:30:00', 2, 200.00);

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at)::date AS month,
    SUM(purchase_amt) AS monthly_amount
  FROM amazon_purchases
  GROUP BY DATE_TRUNC('month', created_at)::date
)
SELECT
  month,
  monthly_amount AS monthly_revenue,
  SUM(monthly_amount) OVER (
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM monthly_revenue
ORDER BY month;

Le ORDER BY dans OVER() définit l’ordre dans lequel le cumul avance. Sans ordre chronologique, un running total n’a pas de sens métier, car SQL ne garantit pas naturellement l’ordre des lignes.

Une nuance compte en production. Avec ORDER BY, de nombreux moteurs SQL appliquent par défaut une fenêtre allant du début de la partition jusqu’à la ligne courante. La gestion des lignes ex æquo dépend notamment du mode RANGE ou ROWS. La documentation PostgreSQL indique que le frame par défaut inclut les lignes précédentes et les lignes équivalentes selon l’ordre.

J’écris donc souvent explicitement ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Cette syntaxe rend l’intention lisible et évite les surprises quand plusieurs lignes ont la même date, le même mois ou la même valeur d’ordre.

month	monthly_revenue	cumulative_revenue
2024-01-01	200.00	200.00
2024-02-01	200.00	400.00
2024-03-01	200.00	600.00

Comment détecter des sessions ?

On détecte des sessions en comparant chaque événement au précédent, puis en transformant les ruptures en identifiants de groupes.

Ce pattern s’appelle gaps and islands. Un gap est une rupture dans une suite : par exemple, un utilisateur ne fait rien pendant plus de 30 minutes. Une island est une série continue : plusieurs événements assez proches pour appartenir à la même session.

Deux usages business reviennent souvent :

Repérer des séries de connexions quotidiennes, par exemple 7 jours d’usage consécutifs sur un produit SaaS.
Regrouper des événements web en sessions, par exemple une page vue, un clic, puis une conversion dans une même visite.

Google Analytics utilise historiquement une durée d’inactivité de 30 minutes comme seuil standard de fin de session. GA4 documente aussi les sessions autour des événements utilisateur et de la notion d’engagement. Ce seuil reste un choix produit : 30 minutes peut convenir à un média, mais être trop court pour un outil SaaS ou trop long pour une application mobile.

WITH user_events AS (
    SELECT
        user_id,
        event_time,
        event_name
    FROM analytics.events
),

ordered_events AS (
    SELECT
        user_id,
        event_time,
        event_name,
        LAG(event_time) OVER (
            PARTITION BY user_id
            ORDER BY event_time
        ) AS previous_event_time
    FROM user_events
),

session_flags AS (
    SELECT
        user_id,
        event_time,
        event_name,
        previous_event_time,
        CASE
            WHEN previous_event_time IS NULL THEN 1
            WHEN event_time - previous_event_time > INTERVAL '30 minutes' THEN 1
            ELSE 0
        END AS new_session_flag
    FROM ordered_events
),

session_ids AS (
    SELECT
        user_id,
        event_time,
        event_name,
        previous_event_time,
        new_session_flag,
        SUM(new_session_flag) OVER (
            PARTITION BY user_id
            ORDER BY event_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_id
    FROM session_flags
),

session_summary AS (
    SELECT
        user_id,
        session_id,
        MIN(event_time) AS session_start,
        MAX(event_time) AS session_end,
        COUNT(*) AS event_count
    FROM session_ids
    GROUP BY user_id, session_id
)

SELECT *
FROM session_summary;

user_events isole les données utiles. ordered_events récupère l’événement précédent avec LAG, une fonction fenêtre qui lit une ligne située avant la ligne courante. session_flags marque les ruptures. session_ids cumule ces ruptures pour créer un identifiant stable. session_summary agrège enfin chaque session.

Cette approche reste lisible en entretien technique comme en production analytics, car chaque CTE porte une étape claire du raisonnement.

Colonne	Rôle
previous_event_time	Horodatage de l’événement précédent du même utilisateur.
new_session_flag	Indique si une nouvelle session commence.
session_id	Identifiant de session construit par cumul des ruptures.
session_start	Premier événement de la session.
session_end	Dernier événement de la session.
event_count	Nombre d’événements dans la session.

Comment trouver les meilleures séries ?

Les meilleures séries se trouvent en identifiant des groupes continus, puis en mesurant leur longueur avant de classer les résultats. Après les sessions, qui regroupent des actions proches dans le temps, le même raisonnement s’applique aux streaks d’activité : une visite par jour, plusieurs jours de suite.

Ce cas sert à repérer les utilisateurs les plus réguliers, les plus longues séries de visites, les périodes d’usage intense ou les cohortes les plus engagées. Côté produit, cela aide à comprendre les habitudes réelles. Côté CRM, rétention et analyse comportementale, cela permet de déclencher des messages au bon moment : féliciter une série, prévenir une rupture d’usage, ou segmenter les utilisateurs très engagés.

WITH unique_visits AS (
    SELECT DISTINCT
        user_id,
        CAST(visit_ts AS DATE) AS visit_date
    FROM visits
),

streak_flags AS (
    SELECT
        user_id,
        visit_date,
        previous_visit_date,
        CASE
            WHEN previous_visit_date = visit_date - INTERVAL '1 day' THEN 0
            ELSE 1
        END AS new_streak_flag
    FROM (
        SELECT
            user_id,
            visit_date,
            LAG(visit_date) OVER (
                PARTITION BY user_id
                ORDER BY visit_date
            ) AS previous_visit_date
        FROM unique_visits
    ) v
),

streak_ids AS (
    SELECT
        user_id,
        visit_date,
        SUM(new_streak_flag) OVER (
            PARTITION BY user_id
            ORDER BY visit_date
            ROWS UNBOUNDED PRECEDING
        ) AS streak_id
    FROM streak_flags
),

streak_lengths AS (
    SELECT
        user_id,
        streak_id,
        MIN(visit_date) AS start_date,
        MAX(visit_date) AS end_date,
        COUNT(*) AS streak_length
    FROM streak_ids
    GROUP BY user_id, streak_id
),

ranked_lengths AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY streak_length DESC, end_date DESC
        ) AS row_num,
        RANK() OVER (
            PARTITION BY user_id
            ORDER BY streak_length DESC
        ) AS streak_rank
    FROM streak_lengths
),

top_lengths AS (
    SELECT *
    FROM ranked_lengths
    WHERE row_num = 1
)

SELECT
    user_id,
    start_date,
    end_date,
    streak_length
FROM top_lengths
ORDER BY streak_length DESC;

La première étape déduplique les visites quotidiennes, sinon trois visites le même jour compteraient comme trois jours d’activité. LAG() récupère ensuite le jour précédent pour chaque utilisateur. Dès que l’écart n’est pas de 1 jour, une nouvelle série démarre. SUM() OVER() transforme ces ruptures en identifiant de série.

RANK(), DENSE_RANK() et ROW_NUMBER() ne gèrent pas les égalités de la même façon. ROW_NUMBER() donne toujours un seul gagnant par utilisateur, même en cas d’égalité. RANK() accepte les ex æquo, mais laisse des trous dans le classement. DENSE_RANK() accepte aussi les ex æquo, sans trou. En pratique, utilisez ROW_NUMBER() quand vous voulez une seule ligne gagnante par utilisateur, et RANK() quand plusieurs séries peuvent partager la première place.

Fonction	Usage dans le pattern
LAG()	Comparer chaque jour au jour précédent.
SUM() OVER()	Créer un identifiant de groupe continu.
COUNT()	Mesurer la longueur de chaque série.
RANK() ou ROW_NUMBER()	Sélectionner les meilleures séries.

Comment rendre ces requêtes robustes ?

Ces requêtes deviennent robustes quand le grain, l’ordre, les partitions et les cas limites sont explicitement définis. Une fonction fenêtre SQL peut donner un résultat juste aujourd’hui et faux demain si ces choix restent implicites.

Le grain est le niveau auquel une ligne représente une information : une transaction, un client par jour, un mois, une commande. Avant d’utiliser une fenêtre, je fixe ce niveau. Calculer un cumul sur des transactions n’a pas le même sens que calculer un cumul sur des montants mensuels déjà agrégés.

Point de vigilance	Bonne pratique
Dates dupliquées	Ajouter un critère d’ordre stable, par exemple un identifiant de transaction.
Fuseaux horaires	Convertir les dates dans un fuseau métier unique avant le calcul.
Valeurs nulles	Décider si elles doivent être ignorées, remplacées ou conservées.
Événements arrivés en retard	Prévoir une règle de recalcul ou une date de prise en compte.

L’ordre mérite une attention particulière. Un ORDER BY sur une date seule peut être ambigu si plusieurs événements ont lieu au même instant. Dans ce cas, le moteur SQL peut classer les lignes différemment selon l’exécution. Pour éviter ce flottement, j’ajoute une colonne déterministe : id_event, created_at, numéro de facture, ou toute clé stable.

Le choix entre ROWS et RANGE change aussi le résultat. ROWS compte des lignes physiques. RANGE regroupe les lignes qui ont la même valeur d’ordre. Avec des dates ou des montants dupliqués, RANGE peut inclure plus de lignes que prévu. Quand le besoin métier parle de “3 dernières transactions”, ROWS est souvent plus clair. Quand il parle de “30 derniers jours”, RANGE peut être pertinent selon le moteur.

Les partitions trop larges coûtent cher. PARTITION BY définit les groupes sur lesquels la fenêtre travaille. Si une partition contient des millions de lignes, le tri et le calcul deviennent plus lourds. Il faut filtrer tôt, agréger au bon grain avant la fenêtre, indexer ou clusteriser les colonnes utilisées dans PARTITION BY et ORDER BY quand le moteur le permet, éviter les fenêtres inutiles sur des volumes massifs, puis vérifier le plan d’exécution.

Les CTE, pour Common Table Expressions, sont les blocs WITH utilisés pour découper une requête. Des noms explicites comme ventes_mensuelles ou clients_actifs_30j rendent la logique vérifiable. PostgreSQL, BigQuery, Snowflake et SQL Server supportent les fonctions fenêtre, mais les détails de syntaxe et d’optimisation varient. La documentation du moteur utilisé reste la référence.

Définir le grain.
Choisir la partition.
Choisir l’ordre.
Écrire le frame si nécessaire.
Tester les doublons.
Documenter le seuil métier.
Vérifier les performances.

Et si vos analyses SQL gagnaient en contexte ?

Les fonctions fenêtre SQL ne servent pas seulement à numéroter des lignes. Bien utilisées, elles permettent de répondre à des questions business précises : chiffre d’affaires cumulé, sessions, séries d’activité, classements, rétention. Le point clé reste le même : définir le bon grain, la bonne partition, le bon ordre et le bon frame. GROUP BY résume, la fenêtre contextualise. En combinant CTEs lisibles, LAG(), SUM() OVER() et ROW_NUMBER(), vous obtenez des requêtes plus claires, plus auditables et plus utiles pour décider. Le bénéfice pour vous : transformer SQL en véritable outil d’analyse opérationnelle.

FAQ

Qu’est-ce qu’une fonction fenêtre SQL ?
Une fonction fenêtre SQL calcule une valeur sur un ensemble de lignes liées à la ligne courante, sans supprimer le détail des lignes. Elle s’utilise avec la clause OVER(), souvent accompagnée de PARTITION BY pour découper les groupes et ORDER BY pour définir l’ordre de calcul.
Quelle est la différence entre GROUP BY et une fonction fenêtre ?
GROUP BY agrège les lignes et réduit le résultat à un niveau résumé. Une fonction fenêtre conserve les lignes d’origine et ajoute un calcul contextuel, par exemple un cumul, un rang ou une comparaison avec la ligne précédente.
Pourquoi ORDER BY est-il important dans OVER() ?
ORDER BY définit la chronologie ou la priorité du calcul. Sans ordre clair, un cumul, un classement ou une comparaison entre lignes peut devenir ambigu. Pour les cumuls, il est souvent préférable d’écrire explicitement ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Comment les fonctions fenêtre aident-elles à créer des sessions ?
Elles permettent de comparer chaque événement au précédent avec LAG(), de marquer une nouvelle session quand l’écart dépasse un seuil, puis de créer un identifiant de session avec SUM() OVER(). Le seuil de 30 minutes est courant en analyse web, mais il doit être adapté au produit.
Les fonctions fenêtre SQL sont-elles performantes sur de gros volumes ?
Elles peuvent l’être si la requête est bien conçue. Il faut filtrer tôt, agréger au bon niveau avant d’appliquer la fenêtre, surveiller les colonnes utilisées dans PARTITION BY et ORDER BY, et vérifier le plan d’exécution selon le moteur SQL utilisé.

A propos de l’auteur

Je suis Franck Scandolera, responsable de l’agence webAnalyste et de l’organisme Formations Analytics. J’accompagne des équipes sur le tracking avancé server-side, l’Analytics Engineering, l’automatisation No/Low Code avec n8n, l’intégration de l’IA en entreprise et le SEO/GEO. J’ai travaillé pour des références comme Logis Hôtel, Yelloh Village, BazarChic, la Fédération Française de Football ou Texdecor. Si vous voulez fiabiliser vos données, vos analyses SQL ou vos automatisations business, contactez-moi.

Franck Scandolera

⭐ Data Analyst, Analytics Engineer et expert dans l’automatisation IA ⭐

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

Mon terrain de jeu :

Data Analyst & Analytics engineering : tracking propre RGPD, entrepôt de données (GTM server, BigQuery…), modèles (dbt/Dataform), dashboards décisionnels (Looker, SQL, Python).
Automatisation IA des taches Data, Marketing, RH, compta etc : conception de workflows intelligents robustes (n8n, Make, 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.