Home » Data Marketing » Comment utiliser les named windows SQL dans BigQuery simplement ?

Comment utiliser les named windows SQL dans BigQuery simplement ?

Par

Un jour, en réécrivant une requête BigQuery à rallonge, j’ai découvert les named windows. Ces alias pour fonctions fenêtres rendent les requêtes plus claires et évitent les répétitions. Simple, efficace et peu enseigné, cet outil peut vraiment optimiser vos scripts SQL.

3 principaux points à retenir.

  • Les named windows simplifient la maintenance en évitant la répétition des clauses de fenêtre.
  • Ils améliorent la lisibilité de vos requêtes complexes avec des alias explicites.
  • Compatible avec BigQuery, PostgreSQL et T-SQL, restez vigilant selon votre dialecte SQL.

Qu’est-ce qu’un named window en SQL et comment ça marche

Alors, qu’est-ce qu’un named window en SQL et comment ça marche, me demandez-vous ? Imaginez que vous soyez dans un magasin où chaque section a un nom. La section des fruits, celle des légumes, et ainsi de suite. Cela devient plus simple pour vous de vous déplacer – vous savez exactement où aller. C’est un peu ça, un named window !

Un named window est une fonctionnalité SQL qui vous permet de donner un nom à une clause de fenêtre (comme PARTITION BY et ORDER BY) afin de la réutiliser dans la même requête. Cela facilite grandement la lisibilité et la maintenance de vos requêtes SQL, surtout lorsque vous jonglez avec des fonctions de fenêtres. Voici comment cela fonctionne, étape par étape.

Vous commencez par définir votre fenêtre en ajoutant le mot-clé WINDOW après FROM ou WHERE. Ensuite, vous attribuez un alias, comme ‘lag_window’. Puis, vous définissez les paramètres de votre fenêtre. Enfin, vous pouvez référencer cet alias dans vos fonctions de fenêtre telles que LAG(). Voici un petit exemple pour illustrer tout ça :

SELECT
    date,
    sales,
    SUM(sales) OVER lag_window AS cumulative_sales
FROM
    sales_data
WINDOW lag_window AS (PARTITION BY region ORDER BY date);

Dans cet exemple, on cumule les ventes par région au fil du temps. En utilisant un named window, on évite les répétitions et on rend la requête plus concise. Si, par exemple, vous deviez appliquer plusieurs fonctions de fenêtre à la même partition, sans un named window, vous auriez plusieurs répétitions de la même clause, ce qui non seulement alourdit la requête, mais rend aussi son interpréteur perplexe !

Un dernier détail que vous devez garder à l’esprit : cette fonctionnalité est compatible avec BigQuery, PostgreSQL et T-SQL. Cela signifie que peu importe la plateforme sur laquelle vous travaillez, vous pouvez tirer profit de cette astuce. En résumé, le gain principal ici, c’est la clarté et la facilité de maintenance de vos requêtes SQL. Ne manquez pas de consulter des ressources supplémentaires comme cet article pour approfondir votre compréhension des fonctions de fenêtres.

Pourquoi utiliser les named windows améliore vos requêtes BigQuery

Imaginez-vous, un matin ensoleillé, assis devant votre ordinateur, une tasse de café fumant à la main. Vous êtes en pleine rédaction d’une requête SQL complexe sur BigQuery, et là, c’est le drame : vous découvrez que la même définition de fenêtre doit être répétée, encore et encore. Petit à petit, l’agacement s’installe. À quoi bon ? Ces répétitions, ça fait perdre du temps et, avouons-le, c’est là qu’apparaissent souvent les erreurs. Les dead ends d’un script SQL, vous connaissez ? Eh bien, cette chainette de répétitions en est le meilleur atout.

Découvrez égalementComment créer un agent LinkedIn pour vos contenus B2B ?

Les named windows viennent alors à la rescousse. En leur conférant un nom unique, on évite non seulement la redondance, mais on simplifie également tout le travail collaboratif. Vous l’avez compris, faciliter la vie des collègues qui liront votre code est dans l’air du temps. Gagnez en clarté, améliorez la communication et vous réduisez le risque d’erreurs. Mais ce n’est pas tout ! Grâce à cette approche, vous bénéficiez d’un véritable atout : la performance.

En effet, grâce à l’optimiseur de BigQuery, l’écriture de votre requête devient plus fluide et rapide. En évitant la duplication des mêmes définitions de fenêtre, vous facilitez le travail de l’optimiseur, qui peut ainsi exécuter vos requêtes de manière plus efficace. Cela ne vous rappelle pas une certaine citation de Lao Tseu : « Un voyage de mille lieues commence par un pas » ? Et si ce pas était juste une légère modification dans votre approche SQL ?

Laissez-moi partager une petite anecdote. Sur l’un de mes projets BigQuery, j’ai dû traiter une série de données transactionnelles complexes. Au départ, je multipliais les définitions de fenêtres, pensant que cela m’apporterait de la clarté sur chaque segment d’analyse. Malheureusement, cela m’a coûté du temps à chaque fois que je souhaitais modifier un calcul. Puis, après avoir compris l’utilité des named windows, j’ai pu consolider ma requête. Le résultat était non seulement un code plus lisible, mais aussi un traitement des données d’une rapidité décuplée.

Pour illustrer cela, voici un petit tableau comparatif :

  • Requête avec répétition :
SELECT
   SUM(value) OVER (PARTITION BY category ORDER BY date) AS running_total_category,
   AVG(value) OVER (PARTITION BY category ORDER BY date) AS avg_category
FROM transactions
  • Requête avec named windows :
    • SELECT
   SUM(value) OVER named_window AS running_total_category,
   AVG(value) OVER named_window AS avg_category
FROM transactions
WINDOW named_window AS (PARTITION BY category ORDER BY date)
    Découvrez égalementComment parser efficacement les dates et heures en Python ?

    La prochaine fois que vous vous lancerez dans une requête, posez-vous cette question : « Pourquoi ne pas simplifier ma vie avec des named windows ? » Votre code n’en sera que plus élégant.

    Comment appliquer les named windows dans une analyse GA4 sous BigQuery

    Imaginez un monde où chaque détail de l’expérience utilisateur est tracé avec une précision chirurgicale. C’est exactement ce que permet BigQuery, en particulier lorsqu’il s’agit d’analyser les données de Google Analytics 4 (GA4). Mais voilà, un problème de taille se dresse devant vous : la perte de valeurs de source, medium et campaign sur certains événements. Frustrant, non ? Mais ne vous inquiétez pas, les named windows sont là pour vous sauver la mise !

    Les named windows sont une fonctionnalité puissante dans SQL, idéale pour résoudre ce genre de souci. Ils vous permettent de réaliser des analyses temporelles tout en gardant de l’ordre dans vos requêtes. En fait, en nommant une fenêtre, vous pouvez récupérer la dernière source valable lors d’une session, même lorsque certains événements affichent des NULL. Comment ? Grâce à la fonction LAST_VALUE ou LAG que l’on appliquera à cette fenêtre définie en alias.

    Voici un exemple de requête SQL qui montre comment vous pouvez remplir les colonnes source, medium et campaign :

    
WITH base_data AS (
  SELECT 
    event_timestamp, 
    event_name, 
    source_medium, 
    campaign,
    SESSION_ID,
    ROW_NUMBER() OVER (PARTITION BY SESSION_ID ORDER BY event_timestamp) AS rn
  FROM 
    `your_project.your_dataset.your_table`
)

SELECT 
  event_timestamp, 
  event_name, 
  COALESCE(source_medium, LAST_VALUE(source_medium) OVER w) AS filled_source_medium,
  COALESCE(campaign, LAST_VALUE(campaign) OVER w) AS filled_campaign
FROM 
  base_data
WINDOW w AS (PARTITION BY SESSION_ID ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY 
  event_timestamp;
    Découvrez égalementQuels outils Python choisir pour vos pipelines ETL data ?

    Dans cette requête, la fonction COALESCE va nous permettre de récupérer les dernières valeurs non-nulles de source et campaign par session, en s’appuyant sur la fenêtre définie. Question pertinente : pourquoi cette technique est-elle si essentielle après la mise à jour du tracking GA4 en 2023 ? Parce que GA4 repose sur un modèle d’événements, et tout changement dans la structure de vos données a un impact direct sur votre suivi. C’est donc une manière efficace de rectifier des lacunes qui pourraient autrement passer inaperçues, rendant vos rapports beaucoup plus fiables. En fait, cette méthode est déjà reconnue comme un workaround au sein de la communauté BigQuery Analytics.

    Jetez un coup d’œil à cette vidéo pour en savoir plus sur BigQuery et ses fonctionnalités ici. Les named windows sont vraiment la clé pour naviguer à travers les complexités des données GA4, leur permettant de briller dans votre analyse.

    Quelles bonnes pratiques pour maîtriser les named windows SQL

    Travailler avec les named windows en SQL, c’est un peu comme jongler avec des balles en feu : vous devez être précis, agile et éviter les faux pas. Laissez-moi vous donner quelques astuces pour maîtriser cet art sans se brûler les ailes.

    Premièrement, parlons des erreurs fréquentes qui vous guettent. Imaginez un scénario où vous redéfinissez la même fenêtre plusieurs fois dans une requête. Vous vous retrouvez alors avec un code illisible, et la maintenance peut devenir un véritable casse-tête. Évitez cela à tout prix ! De même, prêter attention à la manière dont vous nommez vos aliases est crucial. Si vous partez dans tous les sens, vous risquez de perdre vos collègues en route. Pas de noms ambigus comme ‘fenêtre1’ : dites plutôt ‘session_order_window’. C’est clair, c’est précis, et ça indique immédiatement la fonction de la fenêtre.

    Découvrez égalementPourquoi le cold email marche quand on cesse de vendre direct ?

    Ensuite, un conseil de pro : organisez vos named windows à la fin de la clause FROM. Pourquoi? Simplement pour que tout soit bien visible et facile à lire. En plus, limitez leur nombre pour éviter toute confusion ; trois ou quatre devraient suffire pour la majorité des requêtes. En parallèle, n’oubliez pas de vérifier la compatibilité de votre syntaxe selon le dialecte SQL que vous utilisez (BigQuery, PostgreSQL, T-SQL). Chaque dialecte a ses particularités, et un petit détail peut changer la donne.

    Et pour finir sur la lecture et la maintenance des requêtes, sachez que l’utilisation judicieuse des aliases peut rendre le tout plus fluide. Pour une organisation optimale, vous pourriez envisager un schéma comme celui-ci :

    SELECT
  user_id,
  order_amount,
  SUM(order_amount) OVER session_order_window AS cumulative_order
FROM
  orders
WINDOW
  session_order_window AS (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    En combinant les named windows avec d’autres fonctions window, vous maximisez votre efficacité. Donc, la prochaine fois que vous vous retrouvez à écrire des requêtes, pensez en termes de clarté, de structure et d’efficacité. Cela pourrait bien transformer vos sessions de codage en moments plaisants et productifs ! Pour en savoir plus sur les meilleures pratiques, n’hésitez pas à consulter cette source ici.

    Prêt à rendre vos requêtes SQL BigQuery plus lisibles et efficaces avec les named windows ?

    Les named windows sont une astuce SQL souvent méconnue qui fait toute la différence dans la clarté, la maintenance et la robustesse de vos requêtes BigQuery. En évitant la répétition des clauses de fenêtres, vous gagnez du temps et réduisez les erreurs. Cette technique, testée en conditions réelles sur des cas complexes d’analyse GA4, s’avère incontournable pour tout analyste ou data engineer sérieux. Intégrez-les dès maintenant pour écrire un SQL plus propre, plus performant et plus facilement partageable.

    FAQ

    Qu’est-ce qu’une named window en SQL ?

    Une named window est un alias que l’on attribue à une clause de fenêtre (avec PARTITION BY et ORDER BY) pour la réutiliser dans plusieurs fonctions de fenêtre, simplifiant ainsi les requêtes.

    Pourquoi utiliser les named windows dans BigQuery ?

    Elles évitent de répéter plusieurs fois la même définition de fenêtre, améliorant la lisibilité, la maintenance et parfois la performance des requêtes SQL complexes.

    Toutes les bases SQL supportent les named windows ?

    Non, la plupart des bases modernes comme BigQuery, PostgreSQL et T-SQL les supportent, mais il faut vérifier selon votre dialecte SQL.

    Comment appliquer named windows pour corriger les sources manquantes en GA4 ?

    En définissant un window alias qui partitionne par session et ordre d’événements, on récupère la dernière source connue via LAST_VALUE ou LAG, permettant de remplir les valeurs NULL générées par GA4.

    Des conseils pour bien nommer ses named windows ?

    Choisissez des noms clairs en lien avec leur usage, limitez leur nombre, placez-les de façon lisible dans la requête et évitez de trop complexifier vos définitions de fenêtres.
    Découvrez égalementQuelles sont les meilleures techniques d’upselling pour un small business ?

     

    A propos de l’auteur

    Franck Scandolera dirige l’agence webAnalyste et forme depuis des années des professionnels au SQL et à BigQuery. Expert en data engineering et analytics, il maîtrise les environnements cloud, l’automatisation et l’analyse avancée GA4. Son approche pédagogique et terrain aide à transformer des requêtes complexes en outils métier lisibles et fiables.

    Retour en haut
    ClickAIpro