Patterns de fonctions de fenêtrage pour le SQL analytique

Les fonctions de fenêtrage permettent de calculer des valeurs sur des lignes liées à la ligne courante sans réduire le jeu de résultats. Contrairement à GROUP BY, qui réduit les lignes, une fonction de fenêtrage conserve toutes les lignes et ajoute une colonne calculée à côté. Cette distinction les rend indispensables pour le travail analytique où l’on a besoin à la fois du détail et du contexte : classement au sein de groupes, comparaison de lignes adjacentes, accumulation de totaux courants et détection de frontières dans des séquences.

Chaque fonction de fenêtrage a la même anatomie : une fonction (ROW_NUMBER, SUM, LAG, etc.), une clause OVER, et à l’intérieur, un PARTITION BY optionnel (qui regroupe les lignes) et un ORDER BY (qui les ordonne au sein de chaque groupe). La spécification du cadre (par exemple ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) contrôle en outre quelles lignes participent au calcul.

Déduplication avec ROW_NUMBER

Le pattern de fonction de fenêtrage le plus courant en analytics engineering est la déduplication. Les données source arrivent avec des lignes dupliquées — rejeux CDC, corrections tardives, chargements incrémentaux qui se chevauchent — et il faut exactement une ligne par entité.

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM raw_orders
)
WHERE rn = 1

PARTITION BY order_id crée une fenêtre distincte par commande. ORDER BY updated_at DESC place la version la plus récente en premier. Filtrer sur rn = 1 ne conserve que cette dernière version.

BigQuery prend en charge la clause QUALIFY, qui élimine entièrement l’enveloppe de sous-requête :

SELECT *
FROM raw_orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id
  ORDER BY updated_at DESC
) = 1

Même plan d’exécution, code plus propre. QUALIFY filtre après l’évaluation des fonctions de fenêtrage, tout comme HAVING filtre après GROUP BY. Ce pattern apparaît constamment dans les modèles incrémentaux dbt où il faut gérer les arrivées en double lors des opérations de merge.

ROW_NUMBER assigne des entiers séquentiels uniques même en cas d’égalité. RANK et DENSE_RANK gèrent les égalités différemment : RANK laisse des trous (1, 2, 2, 4), DENSE_RANK non (1, 2, 2, 3). Pour la déduplication, toujours utiliser ROW_NUMBER — on veut exactement un gagnant par partition indépendamment des égalités.

LEAD et LAG pour la comparaison de lignes

LAG regarde en arrière et LEAD regarde en avant dans la partition ordonnée. Ils permettent de comparer la ligne courante à ses voisines sans auto-jointures.

Le cas d’usage canonique est la détection des frontières de session à partir de flux d’événements. GA4 exporte des événements bruts sans structure de session — les sessions sont construites en trouvant les trous d’activité. Le modèle d’événements GA4 requiert ce type de reconstruction car les sessions n’existent pas dans les données brutes.

WITH events_with_gaps AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    event_name,
    LAG(event_timestamp) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp
    ) AS prev_event_timestamp,
    CASE
      WHEN LAG(event_timestamp) OVER (
        PARTITION BY user_pseudo_id ORDER BY event_timestamp
      ) IS NULL THEN 1
      WHEN TIMESTAMP_DIFF(
        event_timestamp,
        LAG(event_timestamp) OVER (
          PARTITION BY user_pseudo_id ORDER BY event_timestamp
        ),
        MINUTE
      ) > 30 THEN 1
      ELSE 0
    END AS is_new_session
  FROM events
)

Le premier événement de chaque utilisateur n’a pas de prédécesseur, donc LAG retourne NULL — cela marque une nouvelle session. Tout trou dépassant 30 minutes marque également une nouvelle session. Ce flag devient l’entrée du pattern de total courant décrit dans la section suivante.

LEAD est utile pour calculer le temps avant la prochaine action : combien de temps avant que l’utilisateur fasse quelque chose d’autre ?

SELECT
  event_name,
  event_timestamp,
  TIMESTAMP_DIFF(
    LEAD(event_timestamp) OVER (
      PARTITION BY session_key ORDER BY event_timestamp
    ),
    event_timestamp,
    SECOND
  ) AS seconds_until_next_event
FROM sessionized_events

Les deux fonctions acceptent un paramètre d’offset optionnel (LAG(col, 2) regarde deux lignes en arrière) et une valeur par défaut (LAG(col, 1, 0) retourne 0 au lieu de NULL quand il n’y a pas de ligne précédente).

Totaux courants et agrégation cumulative

Une somme courante des flags de frontière de session issus du pattern LAG ci-dessus assigne chaque événement à sa session :

SUM(is_new_session) OVER (
  PARTITION BY user_pseudo_id
  ORDER BY event_timestamp
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_number

Chaque fois que is_new_session = 1, la somme courante s’incrémente. Les événements entre les frontières partagent la même valeur cumulative, qui devient un identifiant de session. Cette combinaison en deux étapes — LAG pour détecter les frontières, puis SUM pour assigner les numéros de groupe — est la technique de sessionisation standard utilisée dans la construction de sessions GA4 et les définitions de sessions personnalisées.

Les totaux courants s’appliquent largement au-delà des sessions. Le chiffre d’affaires cumulatif, les comptes courants d’utilisateurs et le suivi progressif des métriques utilisent tous le même cadre :

SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM daily_sales

Le cadre explicite ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW est important. Sans lui, BigQuery utilise par défaut RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, ce qui peut produire des résultats inattendus quand plusieurs lignes partagent la même valeur ORDER BY — le cadre basé sur RANGE inclut toutes les égalités, tandis que le cadre basé sur ROWS les traite séquentiellement.

FIRST_VALUE et LAST_VALUE pour l’attribution de session

Quand il faut propager une valeur depuis une position spécifique sur toutes les lignes d’une partition, FIRST_VALUE et LAST_VALUE sont les outils adaptés. L’application la plus courante est l’attribution au niveau session : chaque événement d’une session doit porter la source de trafic du premier événement de la session.

FIRST_VALUE(session_source IGNORE NULLS) OVER (
  PARTITION BY session_key
  ORDER BY event_timestamp
) AS session_source,

FIRST_VALUE(session_medium IGNORE NULLS) OVER (
  PARTITION BY session_key
  ORDER BY event_timestamp
) AS session_medium,

FIRST_VALUE(landing_page IGNORE NULLS) OVER (
  PARTITION BY session_key
  ORDER BY event_timestamp
) AS landing_page

IGNORE NULLS est critique. Tous les événements ne portent pas de données d’attribution — seul l’événement d’entrée a généralement source et medium renseignés. Sans IGNORE NULLS, le premier événement pourrait avoir une source NULL (par exemple si l’événement session_start a été perdu), et chaque ligne hérite de ce NULL.

LAST_VALUE nécessite plus de précautions. Son cadre par défaut (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) signifie qu’il ne voit que les lignes jusqu’à la ligne courante, pas la partition entière. Pour obtenir la dernière valeur réelle de la partition :

LAST_VALUE(event_name IGNORE NULLS) OVER (
  PARTITION BY session_key
  ORDER BY event_timestamp
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_event_in_session

Le cadre explicite UNBOUNDED FOLLOWING s’assure que la fonction considère la partition complète. C’est une source courante de bugs — LAST_VALUE sans spécification de cadre retourne silencieusement la valeur de la ligne courante, pas la dernière valeur de la partition.

Détection de gaps et d’îlots

Identifier des séquences consécutives — séries d’authentification, périodes de disponibilité, plages de dates contiguës — utilise une soustraction astucieuse de ROW_NUMBER. L’intuition : soustraire un entier séquentiel de valeurs consécutives produit la même constante pour chaque groupe ininterrompu.

WITH numbered AS (
  SELECT
    user_id,
    activity_date,
    DATE_SUB(
      activity_date,
      INTERVAL ROW_NUMBER() OVER (
        PARTITION BY user_id ORDER BY activity_date
      ) DAY
    ) AS island_id
  FROM user_activity
)

SELECT
  user_id,
  MIN(activity_date) AS streak_start,
  MAX(activity_date) AS streak_end,
  DATE_DIFF(MAX(activity_date), MIN(activity_date), DAY) + 1 AS streak_days
FROM numbered
GROUP BY user_id, island_id

Si un utilisateur est actif les 1er, 2 et 3 janvier, saute le 4, puis revient les 5 et 6 :

• 1er jan moins ligne 1 = 31 déc (îlot A)
• 2 jan moins ligne 2 = 31 déc (îlot A)
• 3 jan moins ligne 3 = 31 déc (îlot A)
• 5 jan moins ligne 4 = 1er jan (îlot B)
• 6 jan moins ligne 5 = 1er jan (îlot B)

Les trois premières dates partagent un island_id ; les deux dernières en partagent un différent. GROUP BY island_id donne les frontières de série. S’exécute en O(n) contre O(n²) pour les approches par auto-jointure.

Les cas d’usage incluent le suivi de la disponibilité SLA, l’analyse de la continuité des abonnements, les séries de succès dans les jeux, et la détection des trous dans les données de séries temporelles qui indiquent des pannes de pipeline.

Positionnement pour l’attribution avec ROW_NUMBER et COUNT

Les modèles d’attribution multi-touch s’appuient sur les fonctions de fenêtrage pour identifier la position de chaque touchpoint dans un parcours de conversion. La combinaison de ROW_NUMBER pour la position et de COUNT(*) OVER() pour le total de touches permet n’importe quel schéma de pondération :

SELECT
  transaction_id,
  channel,
  revenue,
  ROW_NUMBER() OVER (
    PARTITION BY transaction_id ORDER BY touchpoint_timestamp
  ) AS touch_position,
  ROW_NUMBER() OVER (
    PARTITION BY transaction_id ORDER BY touchpoint_timestamp DESC
  ) AS reverse_position,
  COUNT(*) OVER (
    PARTITION BY transaction_id
  ) AS total_touches
FROM touchpoints

À partir de ces trois colonnes, tout modèle d’attribution est calculable. First-touch : filtrer sur touch_position = 1. Last-touch : filtrer sur reverse_position = 1. Linéaire : diviser le chiffre d’affaires par total_touches. Basé sur la position (40-20-40) : utiliser la logique CASE sur position et total_touches. Pour les implémentations détaillées de chaque modèle, voir first-touch, last-touch et attribution linéaire et modèles basés sur la position et la décroissance temporelle.

Performance : fenêtres nommées et partage de calcul

Quand plusieurs fonctions de fenêtrage partagent le même PARTITION BY et ORDER BY, définir la fenêtre une fois et y faire référence par nom. BigQuery calcule toutes les fonctions partageant une spécification de fenêtre en un seul passage sur les données :

SELECT
  event_timestamp,
  FIRST_VALUE(source IGNORE NULLS) OVER w AS session_source,
  FIRST_VALUE(medium IGNORE NULLS) OVER w AS session_medium,
  FIRST_VALUE(campaign IGNORE NULLS) OVER w AS session_campaign,
  ROW_NUMBER() OVER w AS event_number,
  MIN(event_timestamp) OVER w AS session_start

WINDOW w AS (PARTITION BY session_key ORDER BY event_timestamp)

Cinq fonctions de fenêtrage, un seul scan des données. Sans fenêtres nommées, écrire la clause OVER complète cinq fois produit toujours le même plan d’exécution, mais la syntaxe nommée rend l’intention explicite et le SQL maintenable.

Sur BigQuery spécifiquement, le clustering de la table sur la colonne PARTITION BY apporte une optimisation supplémentaire. Si la fenêtre est partitionnée par session_key et que la table est clusterisée par session_key, BigQuery lit les événements de chaque session depuis des blocs de stockage physiquement adjacents plutôt que de scanner la table entière. Pour les grandes tables d’événements, cela peut réduire significativement le temps de slot.

L’ordre d’exécution à retenir : FROM, WHERE, GROUP BY, HAVING, fonctions de fenêtrage, QUALIFY, DISTINCT, ORDER BY, LIMIT. Les fonctions de fenêtrage s’exécutent après l’agrégation mais avant le filtrage final, c’est pourquoi QUALIFY peut référencer directement les résultats des fenêtres.