MAX_BY est une fonction BigQuery qui simplifie l’extraction d’une valeur associée au maximum d’une autre colonne, évitant ainsi des requêtes complexes avec fenêtrage. Pratique pour récupérer des données récentes ou extrêmes en un seul appel, elle optimise la lisibilité et la maintenance SQL.
3 principaux points à retenir.
- MAX_BY simplifie la récupération du max lié à une autre donnée sans requêtes complexes.
- Idéal pour extraire la valeur associée au dernier événement, commande ou commentaire.
- Favorise la clarté, la rapidité et la maintenabilité des requêtes en BigQuery.
Qu’est-ce que la fonction MAX_BY en SQL et à quoi sert-elle
La fonction MAX_BY en SQL, et plus particulièrement dans BigQuery, est un véritable atout pour utiliser efficacement vos données. En quoi cela consiste-t-il ? MAX_BY permet de récupérer la valeur d’une colonne en se basant sur la valeur maximale d’une autre colonne. C’est assez puissant, non ? Il répond à une question fréquente dans l’analyse des données : comment obtenir la valeur associée à la valeur maximale d’un autre champ dans un sous-ensemble de données ?
Prenons quelques applications concrètes. Supposons que vous souhaitiez connaître la dernière commande passée par un utilisateur. Au lieu de jongler avec des requêtes complexes utilisant ROW_NUMBER() pour filtrer, MAX_BY vous simplifie la vie : vous pouvez extraire directement l’identifiant de commande associé à la date de commande la plus récente. De même, pour un suivi applicatif, si vous devez déterminer le dernier événement enregistré pour chaque utilisateur, MAX_BY peut le faire en un tour de main. Finies les convolutions inutiles, le focus est sur ce qui compte !
En termes de performance, MAX_BY offre des avantages clairs par rapport aux méthodes traditionnelles qui combinent MAX avec JOIN. L’utilisation de MAX_BY est généralement plus rapide et permet de réduire la complexité de la requête. En effet, une étude d’utilisation des bonnes pratiques en BigQuery a montré que simplifier les requêtes contribue à améliorer les performances sur des ensembles de données volumineux (source).
L’utilisation de MAX_BY s’inscrit généralement dans une clause GROUP BY. La syntaxe est simple :
SELECT MAX_BY(order_id, ordered_at) FROM orders GROUP BY user_id;. Ce code récupère le dernier identifiant de commande pour chaque utilisateur basé sur la date de commande.
Il y a cependant quelques limitations à garder à l’esprit. MAX_BY ne peut être utilisé que dans des contextes où il y a un nombre assez limité de valeurs à comparer. Si vous essayez de l’utiliser sur des ensembles de données trop larges sans une agrégation préalable, cela peut entraîner des performances dégradées. Par conséquent, il faut bien gérer l’usage de cette fonction pour ne pas en perdre les bénéfices.
Comment utiliser MAX_BY en pratique avec BigQuery
Utiliser la fonction MAX_BY dans BigQuery, c’est comme passer d’une vieille bicyclette à une voiture de sport. On va explorer comment cette fonction vous permet d’obtenir des résultats plus efficaces et clairs dans vos requêtes SQL. Prenons l’exemple concret de l’identifiant de la dernière commande passée par chaque utilisateur.
Voici une requête BigQuery qui fait exactement ça :
SELECT
user_id,
MAX_BY(order_id, ordered_at) AS last_order_id
FROM
orders
GROUP BY
user_id;Voyons chaque partie ensemble :
- SELECT user_id : On sélectionne l’identifiant de l’utilisateur.
- MAX_BY(order_id, ordered_at) : Ici, on récupère l’ID de la commande associée à la date la plus récente (ordered_at). En somme, on dit à BigQuery : « Donne-moi la commande la plus récente pour chaque utilisateur ».
- FROM orders : Ça indique que l’on travaille avec la table « orders ».
- GROUP BY user_id : Essentiel pour grouper les résultats par utilisateur, ce qui nous permet d’appliquer la fonction MAX_BY correctement.
Les cas d’usage de MAX_BY ne manquent pas. En e-commerce, vous pouvez facilement obtenir la commande la plus récente d’un client. Dans le domaine des Web Analytics, il peut s’agir du dernier événement enregistré, et pour le support client, du dernier commentaire déposé par un utilisateur. Ces applications sont cruciales pour le suivi des interactions et la personnalisation.
Comparons rapidement MAX_BY avec l’approche classique de ROW_NUMBER(). Avec ROW_NUMBER(), vous deviez attribuer des rangs à chaque enregistrement et ensuite filtrer pour obtenir le dernier. En revanche, MAX_BY fait le travail en une seule ligne, ce qui réduit la complexité des requêtes. Voici un tableau qui résume la différence :
| Méthode | Complexité | Performance |
|---|---|---|
| MAX_BY | Simple, direct | Plus rapide, moins d’opérations |
| ROW_NUMBER() | Complexe, nécessitant des sous-requêtes | Plus lourd, surtout avec de grandes tables |
En résumé, MAX_BY offre une meilleure lisibilité et une amélioration des performances dans vos requêtes SQL. N’hésitez pas à approfondir vos connaissances sur ce sujet en consultant cet article qui démystifie encore plus cette fonction.
Quelles sont les bonnes pratiques et pièges à éviter avec MAX_BY
Lorsqu’on utilise MAX_BY dans BigQuery, il y a quelques bonnes pratiques essentielles à garder en tête pour éviter les pièges classiques. Tout d’abord, assurez-vous d’associer MAX_BY avec un GROUP BY clair. Pourquoi ? Parce que MAX_BY ne renvoie qu’une seule valeur pour le critère spécifié. Si vous ne l’associez pas à un regroupement adéquat, vous risqueriez d’obtenir des résultats imprévisibles, voire erronés.
Une autre pratique cruciale est de vérifier vos données en amont. Si vos données ne sont pas triées ou si le champ de base n’est pas un critère unique, vous risquez de vous retrouver avec des valeurs inattendues. En effet, le pire scénario serait de voir MAX_BY retourner un résultat qui ne correspond pas à votre attente, par exemple, si vous filtrez sur un ID qui n’est pas unique.
Il est également judicieux de valider vos résultats avec des tests unitaires. Assurez-vous que les valeurs retournées par MAX_BY correspondent à ce que vous attendez, en particulier lorsque vous travaillez avec de grandes bases de données. N’oubliez pas que MAX_BY n’est pas universel : il peut ne pas être disponible dans certains dialectes SQL, alors que vous pouvez toujours compter sur lui dans BigQuery.
Pour des cas plus complexes, vous devez envisager des alternatives. Par exemple, combiner MAX_BY avec ARRAY_AGG ou STRUCT peut offrir des résultats plus riches. Cela ouvre une avenue pour gérer des données où plusieurs valeurs peuvent être pertinentes, tout en gardant une structure claire.
Voici un tableau synthétique pour résumer les do & don’t de l’utilisation de MAX_BY :
- Do :
- Utiliser avec
GROUP BYpour des résultats précis. - Vérifier que le champ utilisé n’est pas ambigu.
- Effectuer des tests unitaires pour valider les résultats.
- Utiliser avec
- Don’t :
- Ne pas l’utiliser sans vérifier l’intégrité des données.
- Ne pas ignorer la disponibilité dans d’autres dialectes SQL.
- Ne pas se fier uniquement à
MAX_BYpour des cas complexes.
Enfin, intégrez MAX_BY dans un workflow SQL robuste et maintenable en documentant clairement son usage et en simplifiant vos requêtes pour faciliter leur compréhension par d’autres développeurs. Cela fera de vos projets des modèles de clarté et d’efficacité.
Alors, pourquoi ne pas adopter MAX_BY dès maintenant dans vos requêtes SQL ?
MAX_BY est une fonction SQL précieuse qui évite la complexité des jointures, des sous-requêtes ou des fenêtrages pour extraire la valeur liée au maximum d’une autre colonne. Grâce à sa simplicité et son efficacité, elle rend vos requêtes plus lisibles et faciles à maintenir, tout en restant performante sur BigQuery. Employée dans des scénarios quotidiens comme trouver la dernière commande ou le dernier événement utilisateur, MAX_BY devient vite indispensable. En suivant les bonnes pratiques évoquées, vous limitez les risques d’erreurs et tirez le meilleur parti de cette fonction, sans compromis sur la rigueur analytique.
FAQ
Qu’est-ce que la fonction MAX_BY en SQL ?
Dans quels cas utiliser MAX_BY ?
Quelle différence avec ROW_NUMBER() ?
MAX_BY est-il disponible dans toutes les bases SQL ?
Quels risques ou erreurs éviter avec MAX_BY ?
A propos de l’auteur
Je suis Franck Scandolera, consultant expert en Data Engineering et Analytics avec plus de 10 ans d’expérience. Responsable de l’agence webAnalyste et formateur reconnu, j’interviens régulièrement sur BigQuery, SQL et automatisation data. J’accompagne les professionnels à exploiter pleinement leurs données via des solutions robustes, simples et performantes, notamment grâce à des fonctions SQL avancées comme MAX_BY.
⭐ 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.