Lors d’un audit SQL, j’ai découvert un raccourci qui a simplifié mes requêtes BigQuery du jour au lendemain : les fenêtres nommées (WINDOW). Cette astuce oubliée, pourtant simple, réduit la répétition et clarifie les analyses, un must pour tout analyste data confirmé.
3 principaux points à retenir.
- Les fenêtres nommées simplifient l’écriture des fonctions fenêtres.
- Elles permettent de réutiliser une définition de fenêtre, rendant vos requêtes plus lisibles et plus courtes.
- Pratiques pour corriger des données manquantes dans GA4, elles fonctionnent aussi sur PostgreSQL et T-SQL.
Qu’est-ce qu’une fenêtre nommée en SQL et pourquoi l’utiliser
Imaginez-vous en train de préparer un plat savoureux. Vous avez tous les ingrédients éparpillés sur votre plan de travail. Pour éviter de répéter les mêmes gestes encore et encore, vous décidez d’utiliser un bol pour mélanger tous les épices en une seule fois. Une fois ce mélange prêt, vous ajoutez des cuillerées à votre préparation là où cela est nécessaire. C’est exactement ce que font les fenêtres nommées en SQL !
Les fenêtres nommées (WINDOW clause) en SQL sont comme ce bol à épices. Elles vous permettent de définir une fenêtre, à savoir une partition de vos données et un ordre, que vous pouvez réutiliser tout au long de votre requête. Pourquoi s’embêter à répéter la même définition de fenêtre dans chaque fonction analytique quand on peut le faire une seule fois ?
Par exemple, imaginons que vous souhaitiez calculer le total des ventes pour chaque employé dans une entreprise, tout en ayant besoin de connaître la moyenne des ventes au sein de chaque département. Plutôt que de répétition fastidieuse, voici comment utiliser une fenêtre nommée :
SELECT
employee_id,
department_id,
sales,
SUM(sales) OVER my_window AS total_sales,
AVG(sales) OVER my_window AS average_sales
FROM
sales_data
WINDOW my_window AS (PARTITION BY department_id ORDER BY sales DESC)
Dans cet exemple, nous avons défini une fenêtre nommée « my_window ». Cette fenêtre partitionne par « department_id » et ordonne les ventes par ordre décroissant. En utilisant cette définition une seule fois, on évite de dupliquer le même code pour chaque calcul, ce qui rend notre requête plus claire et rapide à écrire.
Avec cette pratique, non seulement vous gagnez en lisibilité, mais aussi en maintenance. Imaginez devoir changer la définition de votre fenêtre un jour ; avec une fenêtre nommée, il suffit de le faire au même endroit. Pas besoin de fouiller dans votre requête pour modifier chaque occurrence de la définition. En somme, c’est un moyen efficace d’optimiser vos requêtes SQL complexes.
Comment déclarer et utiliser une fenêtre nommée dans une requête BigQuery
Ah, les fenêtres nommées en SQL BigQuery, un véritable trésor pour les analystes de données! Imaginez-vous en train de jongler avec des tableaux gigantesques, à la recherche de tendances, et voilà que la fenêtre nommée vous tend la main pour simplifier la vie. Alors, comment déclarer et utiliser une fenêtre nommée efficacement? Embarquons-nous dans cette aventure.
La première étape consiste à comprendre la syntaxe. La clause WINDOW doit être intégrée après votre clause FROM ou WHERE. Voici comment procéder :
- Vous commencez par définir un alias pour votre fenêtre, par exemple lag_window.
- Ensuite, vous déclarez cette fenêtre avec du contenu comme PARTITION BY et ORDER BY.
Une fois que cela est clair, vous pouvez référencer cet alias dans des fonctions analytiques. Prenons un exemple concret :
SELECT
employee_id,
salary,
LAG(salary) OVER lag_window AS previous_salary
FROM
employees
WINDOW
lag_window AS (PARTITION BY department ORDER BY hire_date ASC);
Dans ce cas, nous avons partitionné les employés par département et ordonné par date d’embauche. La fonction LAG() nous permet ensuite de visualiser le salaire précédent de chaque employé, ce qui est précieux pour des analyses de rémunération.
Un autre exemple, plus simple cette fois, mais qui reste fascinant, est l’utilisation de ROW_NUMBER() :
SELECT
employee_id,
ROW_NUMBER() OVER emp_window AS row_num
FROM
employees
WINDOW
emp_window AS (ORDER BY salary DESC);
Ici, chaque employé se voit attribuer un numéro de ligne basé sur son salaire, trié par ordre décroissant. Pratique, non? Cela rend vos requêtes plus lisibles et faciles à maintenir. En regroupant toutes les définitions de fenêtres dans un espace bien défini, il devient plus simple d’ajuster votre logique sans réécrire le code à chaque fois.
Pour en savoir plus et approfondir votre maîtrise des fenêtres nommées, n’hésitez pas à consulter la documentation officielle sur BigQuery Window Functions. Voilà comment faire des merveilles avec des fenêtres nommées, et vous voilà prêt à démarrer votre prochaine quête d’analytique!
Comment exploiter les fenêtres nommées pour corriger les données GA4 en BigQuery
Ah, l’été 2023 ! Une saison souvent synonyme de bronzage et de vacances. Mais pour nous, analystes de données, cela a aussi été le moment où GA4 a décidé de jouer les trouble-fêtes. De nombreux événements ont commencé à perdre leurs colonnes de source de trafic, nous laissant un peu comme des naufragés à la recherche d’une bouée pour nous maintenir à flot dans un océan de données incomplètes. Que faire dans cette situation ? C’est ici que les fenêtres nommées en SQL BigQuery se révèlent être de véritables héroïnes.
Imaginez un instant que vous travaillez sur un rapport d’attribution basé sur le dernier clic. Vous observez une session où la source de trafic est manquante. Que se passe-t-il dans votre analyse ? Cela pourrait bien fausser vos résultats et vous faire perdre des insights précieux. C’est là que les fenêtres nommées, ou « window functions », entrent en jeu. Elles permettent d’effectuer des calculs sur un ensemble de lignes tout en préservant chaque rangée de données distincte. Dans notre cas, elles pourront nous aider à remplir ces données manquantes avec la dernière source non nulle avant l’événement courant.
Pour mettre cela en pratique, imaginons la requête SQL suivante :
WITH traffic_data AS (
SELECT
user_id,
event_time,
traffic_source,
LAST_VALUE(traffic_source IGNORE NULLS) OVER (
PARTITION BY user_id
ORDER BY event_time
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS last_traffic_source
FROM
`your_project.your_dataset.ga4_events`
)
SELECT
user_id,
event_time,
COALESCE(traffic_source, last_traffic_source) AS filled_traffic_source
FROM
traffic_data
ORDER BY
user_id, event_time;Dans cet exemple, nous utilisons la fonction LAST_VALUE pour récupérer la dernière source de trafic non nulle pour chaque utilisateur, classée par le temps de l’événement. Grâce à la clause IGNORE NULLS, nous évitons les interruptions dans les résultats causées par les valeurs nulles. Une fois que le tableau de données est établi, nous remplissons les valeurs manquantes avec COALESCE.
Cette petite astuce peut sembler simple, mais elle est essentielle pour garantir la robustesse de nos analyses et reportings GA4. En unifiant les données provenant de différentes sessions, nous obtenons des informations plus précises et exploitables. En somme, maîtriser les fenêtres nommées en SQL BigQuery, c’est comme avoir une boussole dans cet océan tumultueux de données. Pour aller plus loin, je vous invite à explorer davantage les fenêtres nommées à travers cet article.
Prêt à rendre vos requêtes BigQuery plus propres et efficaces grâce aux fenêtres nommées ?
Les fenêtres nommées en SQL BigQuery sont un outil simple mais puissant pour rendre vos requêtes plus lisibles, plus concises et plus faciles à maintenir. En définissant une fois les critères de partitionnement et d’ordre puis en les réutilisant via des alias, vous évitez la répétition et les erreurs. L’impact est particulièrement notable dans l’analyse des données GA4, où ce mécanisme permet de corriger les pertes de données importantes après 2023. Adopter cette pratique augmente la robustesse et la clarté de vos pipelines BigQuery, un vrai plus pour tout analyste ou data engineer soucieux d’efficacité.
FAQ
Qu’est-ce qu’une fenêtre nommée en SQL ?
Quels avantages apporte la clause WINDOW dans BigQuery ?
Cette technique est-elle compatible avec d’autres SQL que BigQuery ?
Comment cette méthode aide-t-elle à corriger les données GA4 ?
Faut-il une version payante de BigQuery pour utiliser les fenêtres nommées ?
A propos de l’auteur
Je suis Franck Scandolera, Analytics Engineer et formateur freelance depuis plus de 10 ans, spécialiste BigQuery, SQL avancé et automatisation data. À travers mes missions et formations, j’ai aidé des dizaines d’organisations à optimiser leurs requêtes SQL, notamment dans la collecte et analyse GA4. Je développe aussi des solutions visant à fiabiliser les données grâce à des techniques avancées, comme les fenêtres nommées, combinant expertise technique et pédagogie simple.
⭐ Expert et formateur en Tracking avancé, Analytics Engineering et Automatisation IA (n8n, Make) ⭐
Ref clients : Logis Hôtel, Yelloh Village, BazarChic, Fédération Football Français, Texdecor…
Mon terrain de jeu :
Data & 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.