La fonction SQL max_by simplifie l’extraction d’une valeur liée au maximum d’une autre colonne, évitant des requêtes complexes avec row_number(). Indispensable en BigQuery, elle optimise la récupération des données les plus récentes ou importantes, comme l’ordre le plus récent d’un utilisateur.
3 principaux points à retenir.
- max_by simplifie les agrégations sur valeurs associées au maximum.
- Elle remplace souvent des CTE complexes ou des fonctions window comme row_number().
- Idéale pour optimiser performance et lisibilité des requêtes BigQuery.
Qu’est-ce que la fonction SQL max_by et à quoi sert-elle
La fonction max_by en SQL est un véritable atout pour quiconque cherche à extraire des données pertinentes de manière efficace. En gros, cette fonction vous permet de récupérer la valeur d’une colonne associée à la valeur maximale d’une autre colonne. Prenons un exemple concret : imaginons que vous souhaitiez obtenir l’ID de la commande la plus récente d’un utilisateur. max_by simplifie cette tâche en passant directement à l’essentiel.
Pour faire simple, vous pouvez utiliser max_by avec deux colonnes en entrée. Supposons que vous ayez une table avec les colonnes user_id, order_id et order_date. En utilisant max_by, vous pouvez obtenir l’order_id qui correspond à la date la plus récente de commande, groupée par user_id. Comparons cela avec une méthode classique utilisant row_number(): cette dernière est souvent plus complexe et nécessite des sous-requêtes, ce qui peut alourdir vos requêtes et réduire leur lisibilité.
Voici un exemple de requête en BigQuery utilisant max_by :
SELECT
user_id,
MAX_BY(order_id, order_date) AS latest_order_id
FROM
orders
GROUP BY
user_id;
Avec cette requête, en un clin d’œil, vous récupérez l’order_id lié à la order_date maximale pour chaque utilisateur, en gardant votre code épuré et facile à comprendre. En effet, la clarté est l’un des plus grands avantages de max_by, surtout lorsqu’il s’agit d’analyse de données complexes où la lisibilité joue un rôle crucial.
De plus, en termes de performance, max_by est souvent plus rapide que l’approche row_number() car elle évite les traitements intermédiaires. En effet, une étude de Google montre que les fonctions analytiques peuvent être intensives sur de grands volumes de données, et max_by réduit considérablement cette charge (source : Jash Bhatt).
Comment intégrer max_by dans une requête SQL BigQuery
Utiliser la fonction max_by dans vos requêtes SQL sur BigQuery peut vraiment faire la différence, surtout lorsque vous cherchez à extraire des données pertinentes. Alors, comment l’intégrer efficacement dans vos requêtes ? C’est assez simple si vous suivez quelques étapes clés.
Commencez par choisir judicieusement vos colonnes pour la fonction max_by(col_to_return, col_for_max). Le premier paramètre, col_to_return, est ce que vous voulez récupérer ; le second, col_for_max, est la colonne à évaluer pour déterminer le maximum. Prenons par exemple une situation où vous voulez extraire le dernier commentaire laissé sur un produit par utilisateur. Imaginez une table comments avec les colonnes user_id, product_id, comment, et timestamp.
Voici à quoi pourrait ressembler votre requête :
SELECT
user_id,
product_id,
max_by(comment, timestamp) as last_comment
FROM
comments
GROUP BY
user_id, product_id
Dans cet exemple, chaque utilisateur et chaque produit sera groupé, et max_by renverra le dernier commentaire basé sur le timestamp.
De la même façon, pour récupérer le dernier événement d’un utilisateur, vous pourriez avoir une table user_events avec user_id, event_type, et event_timestamp. Voici comment vous pourriez écrire cette requête :
SELECT
user_id,
max_by(event_type, event_timestamp) as last_event
FROM
user_events
GROUP BY
user_idÀ ce stade, la fonction max_by brille lorsqu’elle est utilisée avec GROUP BY, vous permettant de réduire les données et de revenir juste avec l’information qui compte le plus. Pour un peu plus de complexité, supposons que vous ayez besoin de plusieurs agrégations, comme la moyenne des notes de produits tout en obtenant le dernier commentaire :
SELECT
product_id,
AVG(rating) as average_rating,
max_by(comment, timestamp) as last_comment
FROM
product_reviews
GROUP BY
product_idCela vous donne une vue complète sur le produit tout en récupérant le dernier commentaire. Maintenant, pour vous donner un aperçu des performances, voici un tableau qui compare max_by et row_number() sur la simplicité et le coût d’exécution :
| Fonction | Simplicité | Coût d’exécution |
|---|---|---|
| max_by | Élevée | Moins coûteux |
| row_number() | Modérée | Plus coûteux |
Ainsi, max_by est un choix logique si votre objectif est d’optimiser à la fois la simplicité et le coût d’exécution dans vos requêtes SQL sur BigQuery. Pour plus d’informations, vous pouvez consulter la documentation officielle ici.
Pourquoi et quand préférer max_by à row_number et autres fonctions window
La fonction max_by est un outil puissant qui mérite d’être mis en avant, surtout lorsqu’on la compare aux fonctions de fenêtre classiques comme row_number(). Lorsque vous cherchez à extraire une valeur maximale rattachée à une autre colonne, max_by simplifie vos requêtes de façon significative.
D’abord, pourquoi opter pour max_by ? Voici quelques points clés :
- Simplicité : Avec max_by, la syntaxe est plus concise. Vous pouvez récupérer un enregistrement entier associé à la valeur maximum en une seule commande, alors qu’avec row_number(), vous devrez probablement effectuer un filtrage supplémentaire.
- Rapidité : max_by peut être plus performant, surtout sur de gros volumes de données. En évitant les calculs de numérotage de lignes pour chaque partition comme le fait row_number(), vous réduisez la charge sur le moteur SQL.
- Lisibilité : Un code moins encombré facilite la maintenance. En évitant des sous-requêtes complexes, votre code reste accessible à d’autres analystes ou développeurs.
Prenons un exemple concret : imaginons que vous ayez une table user_events contenant des interactions de différents utilisateurs. Si vous cherchez à extraire le dernier événement de chaque utilisateur, max_by sera votre allié.
SELECT user_id, MAX_BY(event_name, event_timestamp) AS last_event
FROM user_events
GROUP BY user_id
En revanche, avec row_number(), le code pourrait ressembler à ça :
WITH ranked_events AS (
SELECT user_id, event_name, event_timestamp,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp DESC) AS rn
FROM user_events
)
SELECT user_id, event_name
FROM ranked_events
WHERE rn = 1
Vous voyez la différence ? En termes de plan d’exécution, max_by est généralement plus direct, entraînant moins d’étapes. Cependant, il est essentiel de noter que max_by n’est pas toujours la solution. Si vous avez besoin d’appliquer des logiques plus avancées ou d’effectuer des agrégations sur plusieurs dimensions, row_number() peut s’avérer incontournable.
En conclusion, optez pour max_by lorsque votre besoin est simple et direct, mais ne négligez pas row_number() pour des scénarios plus complexes. La clé est de bien comprendre ce que chaque fonction peut offrir. Pour plus de détails sur ces fonctions, vous pouvez consulter cet article intéressant ici.
Comment optimiser ses requêtes SQL BigQuery avec max_by au quotidien
Pour intégrer efficacement la fonction max_by dans vos workflows BigQuery, quelques bonnes pratiques peuvent faire toute la différence. Tout d’abord, la structuration des requêtes est essentielle. Gardez en tête que max_by se combine très bien avec d’autres fonctions d’agrégation. N’hésitez pas à utiliser GROUP BY correctement afin de réduire le risque d’erreurs de type « group by » qui peuvent survenir si vous oubliez d’inclure toutes les colonnes nécessaires. Par exemple :
SELECT country, MAX_BY(sales, date) AS latest_sale
FROM sales_data
GROUP BY country
Cette requête vous donnera la dernière vente par pays, sans ambiguïté. Prendre soin de structurer la requête permet d’éviter une confusion entre colonnes, notamment si vous travaillez avec de gros jeux de données. Pensez également à vérifier les types de données pour éviter des incohérences qui pourraient fausser vos résultats.
En termes de performance, il est crucial de gérer la quantité de données manipulées. Utilisez des filtres pour réduire le jeu de données avant de l’agréger. Par exemple :
SELECT product_id, MAX_BY(sales, date) AS latest_sale
FROM sales_data
WHERE sales > 1000
GROUP BY product_id
Cela améliore les temps de réponse et optimise l’usage des ressources.
Max_by peut également simplifier des tâches d’automatisation et de reporting. Dans des rapports périodiques, par exemple, vous pouvez calculer la dernière transaction d’un client. Cela réduit la complexité de votre code et améliore sa lisibilité, ce qui est essentiel lorsque vous devez partager votre travail avec d’autres équipes.
Voici quelques astuces concluant sur des pratiques à adopter :
- Optimisez vos GROUP BY : incluez seulement les colonnes nécessaires.
- Filtrez vos données : réduisez les volumes avant d’appliquer MAX_BY.
- Combinez MAX_BY avec d’autres fonctions d’agrégation pour des calculs plus riches.
- Vérifiez vos types de données : cela vous évitera des surprises.
- Documentez vos requêtes : une bonne documentation aide les autres à comprendre vos choix.
Pour plus de détails sur la fonction max_by en BigQuery, vous pouvez consulter cet article ici. Ces conseils devraient vous armer pour utiliser max_by de manière plus efficace au quotidien et intégrer cette pratique dans vos projets professionnels.
Alors, êtes-vous prêt à booster vos requêtes SQL avec max_by ?
La fonction SQL max_by est une véritable épice pour vos requêtes BigQuery. Elle simplifie radicalement l’extraction de valeurs liées aux maximums d’autres colonnes, évitant les requêtes complexes et souvent lourdes de fonctions windows comme row_number(). Utiliser max_by, c’est gagner en clarté, en rapidité d’écriture et souvent aussi en performance. Elle s’intègre naturellement dans les pipelines de données et facilite l’analyse des données temporelles ou ordinales. Son adoption dans la pratique professionnelle assure un gain de temps et un code plus maintenable, à condition de bien comprendre ses cas d’usage et limites. Prêt à l’essayer ?
FAQ
Qu’est-ce que la fonction max_by en SQL ?
Comment utiliser max_by dans une requête BigQuery ?
Max_by est-il plus performant que row_number() ?
Peut-on utiliser max_by pour plusieurs colonnes à la fois ?
Quelles alternatives existe-t-il à max_by en SQL ?
A propos de l’auteur
Franck Scandolera cumule plus de dix ans d’expérience en data engineering et web analytics, avec une expertise pointue en SQL et BigQuery. En tant que responsable de l’agence webAnalyste et formateur, il accompagne de nombreuses équipes dans l’optimisation de leurs requêtes et workflows data. Sa maîtrise approfondie du tracking, cloud data et automatisation no code, ainsi que sa pédagogie claire, font de lui un expert fiable pour démystifier des fonctions avancées comme max_by et améliorer la performance des requêtes analytiques.
⭐ 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.