Comment simplifier vos requêtes avec named windows en BigQuery SQL ?

Lors d’un audit SQL chez un client, j’ai découvert que l’usage des named windows en BigQuery pouvait réduire la duplication de code et clarifier des requêtes complexes. En moins de 3 minutes, ce truc m’a littéralement sauvé des heures de débogage répétitif, augmentant la lisibilité et la maintenabilité du code.

3 principaux points à retenir.

• Named windows évitent la répétition des définitions de fenêtres dans vos requêtes SQL.
• Ils améliorent la lisibilité et réduisent les erreurs dans les fonctions fenêtrées complexes.
• Compatible avec BigQuery, PostgreSQL, T-SQL mais à vérifier selon le dialecte SQL utilisé.

Qu’est-ce qu’un named window dans SQL ?

Imaginez un monde où vous pouvez donner un alias à vos configurations de fenêtres SQL. En gros, c’est exactement ce que fait un named window, ou fenêtre nommée. C’est une fonctionnalité qui s’avère être un véritable assistant personnel dans le royaume des requêtes SQL. En assignant un nom à une configuration de fenêtre – notamment la partition et l’ordre via la clause WINDOW après FROM – vous pouvez la réutiliser sans avoir à la définir à chaque fois.

Pourquoi voulez-vous révolutionner votre écriture SQL, me direz-vous ? Tout simplement parce que cela rend votre code plus lisible et plus maintenable. Finies les répétitions harassantes ! Pensez à toutes ces lignes de code où vous deviez répéter les mêmes expressions complexes encore et encore. Avec un nom donné, ce qui était une corvée se transforme en un vrai jeu d’enfant. Imaginez un peu, au lieu d’errer dans la jungle des commandes redondantes, vous pouvez juste mentionner votre alias et zapper directement à la fonction qui vous intéresse.

Concrètement, prenons un exemple simple pour mieux comprendre. Supposons que vous ayez une table avec des données de ventes. Vous voulez calculer le rang des ventes par produit. Voici comment vous pourriez le faire avec une fenêtre nommée :

SELECT 
  product_id,
  sales_amount,
  ROW_NUMBER() OVER my_window AS sales_rank
FROM 
  sales_data
WINDOW my_window AS (PARTITION BY product_id ORDER BY sales_amount DESC);

Dans cet exemple, “my_window” est la fenêtre nommée qui regroupe les ventes par produit et qui classe les montants de vente dans l’ordre décroissant. Ce code serait beaucoup plus difficile à lire sans cette simplification. Les fenêtres nommées sont un vrai gain de temps et améliorent la clarté de vos requêtes. En un tournemain, vous naviguez dans vos données comme un pro, même si vous débutez tout juste avec SQL.

Maintenant, imaginez que vous souhaitiez utiliser la fonction LAG() pour analyser les ventes d’un produit par rapport à celles de la période précédente. Pas de souci ! Vous pouvez réutiliser la même fenêtre nommée :

SELECT 
  product_id,
  sales_amount,
  LAG(sales_amount) OVER my_window AS previous_sales
FROM 
  sales_data
WINDOW my_window AS (PARTITION BY product_id ORDER BY sales_date);

Encore une fois, grâce à ce système astucieux, vous évitez la redondance tout en gardant votre code propre et élégant. Voilà comment les named windows dans SQL apportent de la sagesse à votre code, permettant à votre esprit de se concentrer sur l’analyse plutôt que sur la répétition. Alors, prêt à donner un coup de pouce à vos requêtes SQL avec ces fenêtres nommées ? C’est le moment de passer à la vitesse supérieure !

Comment utiliser named windows en pratique dans BigQuery ?

Pour se lancer dans l’implémentation des named windows dans BigQuery, imaginez que vous êtes un chef d’orchestre, guidant chaque musicien avec précision. Ici, chaque note est une requête, et avec les named windows, vous rendez votre partition plus claire et harmonieuse. Comment procéder étape par étape ? Suivez le guide.

Tout commence par la clauses WINDOW. Vous devez d’abord écrire cette clause en donnant un alias significatif à votre fenêtre. Par exemple :

WINDOW my_window AS (PARTITION BY user_id ORDER BY event_time ASC)

Avec le nom de votre fenêtre établi, il est temps de la définir. Ici, vous déterminez comment subdiviser vos données grâce à PARTITION BY et comment les organiser avec ORDER BY. Dans notre exemple, nous avons partitionné les données par user_id et ordonné les événements par event_time.

Vient ensuite la partie la plus excitante : utiliser votre alias dans les fonctions fenêtrées. Prenons une table fictive « events » où nous voulons combiner LAG() pour obtenir l’événement précédent d’un utilisateur et ROW_NUMBER() pour numéroter les événements en fonction du temps :

SELECT 
  user_id,
  event_time,
  event_type,
  ROW_NUMBER() OVER my_window AS event_order,
  LAG(event_type) OVER my_window AS previous_event
FROM 
  events

En utilisant ce setup, vous avez non seulement rendu votre code plus lisible, mais vous avez également gagné en temps. Imaginez le gain lors de mises à jour futures : il vous suffit d’adapter la clause WINDOW sans toucher à toutes les fonctions qui l’exploitent. La clarté du code est un autre bénéfice énorme, surtout lors de travaux collaboratifs.

Un petit rappel : cette syntaxe est compatibles avec PostgreSQL et T-SQL, mais vérifiez toujours si vous travaillez dans d’autres systèmes qui pourraient ne pas supporter cette belle fonctionnalité. En gardant un œil vigilant, vous éviterez bien des maux de tête !

Pour approfondir le sujet et voir quelques exemples supplémentaires, n’hésitez pas à consulter cet article ici.

Comment appliquer named windows pour résoudre des problèmes concrets en GA4 ?

Prenons un instant pour imaginer la scène : vous êtes consultant analytics dans une agence de marketing. Vous venez de rencontrer un client qui se lamente de constater une perte de données collectées au niveau des sources de trafic. Depuis début 2023, de nombreux événements affichent des valeurs NULL, rendant son rapport d’attribution presque illisible. Avec l’effervescence de la transition vers Google Analytics 4 (GA4), cette situation devient encore plus cruciale. Comment solutionner ce casse-tête ? Vous avez entendu parler d’une fonctionnalité prometteuse dans BigQuery, les named windows.

Les named windows, ces fenêtres nommées, peuvent être des alliées puissantes pour résoudre cette problématique. Imaginez-vous en train de recréer une logique d’attribution « last click » en remplissant les colonnes manquantes avec une simple magie SQL. La clé pour récupérer la dernière source connue dans la session est d’utiliser la fonction fenêtrée LAG(). Voici comment cela fonctionne.

Nous allons définir une named window qui va nous permettre de regarder en arrière sur les événements de la session tout en utilisant la puissance de LAG(). Voici un petit exemple de code :

WITH traffic_data AS (
    SELECT
        event_timestamp,
        event_name,
        traffic_source.name AS source,
        LAG(traffic_source.name) OVER (PARTITION BY session_id ORDER BY event_timestamp) AS last_known_source
    FROM
        `your_project.dataset.events_*`
    WHERE 
        _TABLE_SUFFIX BETWEEN '20230101' AND '20230131'
),

named_win AS (
    SELECT
        event_timestamp,
        event_name,
        COALESCE(source, last_known_source) AS final_source
    FROM
        traffic_data
)

SELECT
    final_source,
    COUNT(*) AS event_count
FROM
    named_win
GROUP BY
    final_source;

Ce code commence par récupérer des données d’événements, pour ensuite définir une named window. En utilisant COALESCE(), on remplace les valeurs NULL de la source par la dernière source connue capturée par la fonction LAG(). Cela corrige ce petit « bug » de GA4 et facilite le débogage de votre jeu de données. Vous pouvez ainsi identifier facilement d’où proviennent réellement vos visiteurs, évitant ainsi de laisser des valeurs NULL traîner dans vos rapports.

Il est important de noter que cette astuce n’est pas accessible à tout le monde : elle est réservée aux utilisateurs de la version premium de BigQuery. Mais pour ceux qui peuvent en bénéficier, ces fenêtres nommées peuvent véritablement transformer votre manière d’analyser les données et d’optimiser la maintenance de vos tableaux de bord.

Vous êtes prêt à maîtriser les named windows pour simplifier vos SQL BigQuery ?

Les named windows sont une pépite méconnue qui changent la vie des analystes et développeurs SQL en rendant les requêtes plus claires, maintenables et efficaces. Avec une simple clause WINDOW, vous bannissez la répétition et facilitez les évolutions de code, surtout sur des analyses complexes telles que celles sur GA4. Tester, adopter et intégrer ce réflexe dans vos requêtes SQL vous fera gagner un temps précieux au quotidien et vous assurera une meilleure robustesse face aux évolutions des données. En clair, c’est l’art du SQL qui devient plus élégant et précis, avec un minimum d’effort.

FAQ

A propos de l’auteur

Franck Scandolera, expert en Web Analytics et Data Engineering, accompagne depuis plus de 10 ans agences et entreprises dans l’exploitation avancée des données digitales. Responsable de l’agence webAnalyste et formateur reconnu sur GA4, BigQuery et SQL, il partage sa passion pour automatiser intelligemment les dataflows et optimiser la lisibilité du code SQL au service du business.