La fonction SQL max_by facilite l’extraction d’une valeur associée au maximum d’une autre colonne, simplifiant les requêtes complexes. Couramment utilisée pour obtenir la dernière commande ou événement, elle remplace avantageusement des solutions plus lourdes comme row_number().
3 principaux points à retenir.
- max_by simplifie l’extraction conditionnelle: récupère une valeur liée à la valeur max d’une autre colonne.
- Pratique pour données temporelles: idéale pour extraire la dernière commande ou événement d’un utilisateur.
- Économie de code et optimisation: évite des jointures et window functions couteuses comme row_number().
Qu’est-ce que la fonction SQL max_by et à quoi sert-elle
La fonction max_by est une pépite du SQL, surtout dans le cadre de BigQuery. Pour faire simple, elle permet d’extraire la valeur d’une colonne liée à la valeur maximale d’une autre colonne dans un ensemble de données. Imaginez que vous ayez une liste de commandes avec des dates et des montants. Grâce à max_by, vous pouvez facilement trouver l’ID de la commande ayant le montant le plus élevé, tout en récupérant d’autres informations pertinentes. Cela simplifie considérablement ce qui nécessitait auparavant plusieurs étapes.
Voici un exemple concret : disons que vous disposez d’une table orders contenant les colonnes order_id, order_date, et amount. Si vous souhaitez récupérer l’ID de la commande avec le montant maximum pour chaque utilisateur, vous pouvez utiliser la fonction max_by comme suit :
SELECT user_id,
MAX_BY(order_id, amount) AS max_order_id
FROM orders
GROUP BY user_id
Ce code est à la fois clair et efficace. Contrairement aux approches classiques qui pourraient impliquer des sous-requêtes complexes avec ROW_NUMBER() ou une combinaison de MAX() et JOIN, max_by vous offre un moyen direct d’obtenir le résultat souhaité en une seule ligne. Cela se traduit par un gain de temps en termes d’écriture et d’exécution des requêtes.
Les cas d’usage de max_by sont variés, mais on les retrouve souvent dans des analyses de performances commerciales, la surveillance des ventes, ou même l’optimisation des stocks en lien avec les commandes. Que vous soyez analyste de données, développeur ou propriétaire d’entreprise, cette fonction vous permettra d’analyser avec justesse et efficacité divers aspects de votre activité.
En termes de performance, surtout avec de grandes bases de données, max_by peut réduire significativement le temps d’exécution par rapport à des requêtes plus lourdes. En gros, moins de requêtes complexes signifie moins de bande passante gaspillée.
Pour en savoir plus, vous pouvez consulter la documentation officielle de BigQuery sur les fonctions d’agrégation.
Comment exploiter max_by pour extraire les données récentes par groupe
Imaginons qu’on veuille récupérer la dernière commande effectuée par chaque utilisateur dans une table de commandes. Plutôt que d’utiliser des sous-requêtes complexes, la fonction max_by de BigQuery se montre particulièrement efficace pour ce type de requête. Voici comment procéder :
SELECT
user_id,
MAX_BY(order_date, order_id) AS last_order_date
FROM
orders
GROUP BY
user_id
Dans cet exemple, la requête renvoie pour chaque user_id la date de leur dernière commande en se basant sur l’order_date. L’avantage ici est la simplicité : on n’a pas besoin de tri ou de multiples sous-requêtes.
Comparons cela avec l’utilisation de row_number(). Avec cette méthode, on doit d’abord numéroter les lignes pour chaque utilisateur, puis filtrer :
WITH ranked_orders AS (
SELECT
user_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM
orders
)
SELECT
user_id,
order_date
FROM
ranked_orders
WHERE
rn = 1
La méthode avec max_by est donc plus concise et plus lisible, ce qui est un atout précieux en termes de performance et de maintenance.
Mais l’utilisation de max_by ne s’arrête pas aux commandes. Supposons que vous souhaitiez récupérer le dernier commentaire d’un utilisateur dans une table de commentaires. La requête serait presque identique :
SELECT
user_id,
MAX_BY(comment_date, comment_id) AS last_comment_date
FROM
comments
GROUP BY
user_id
De la même manière, pour récupérer le dernier événement lié à un utilisateur, il suffit d’adapter légèrement la requête :
SELECT
user_id,
MAX_BY(event_date, event_id) AS last_event_date
FROM
events
GROUP BY
user_id
Toutefois, attention à quelques limitations : si vos colonnes order_date, comment_date ou event_date contiennent des valeurs nulles, ces dernières seront ignorées, ce qui peut fausser les résultats. De même, en cas d’égalité, max_by peut choisir arbitrairement l’un des enregistrements. Pour une utilisation optimale, assurez-vous de bien gérer ces cas.
Pour approfondir et découvrir d’autres cas d’utilisation de cette fonction puissante, consultez cet article.
Quand utiliser max_by plutôt que d’autres fonctions SQL similaires
La fonction max_by en BigQuery est souvent citée comme la solution élégante pour récupérer des valeurs associées aux maximums. Mais dans quels cas est-elle à privilégier par rapport à d’autres fonctions comme row_number ou first_value ? Observons cela de plus près.
Utilisation de max_by : Cette fonction est idéale quand vous souhaitez récupérer une valeur associée à un maximum dans une colonne, tout en gardant le code lisible et concise. Par exemple, si vous voulez trouver le produit avec le prix le plus élevé, max_by(prix, produit) est une solution directe et claire. De plus, en BigQuery, max_by offre des performances optimales, car elle traite les données de manière centralisée, moins gourmande en ressources, par rapport aux méthodes plus complexes que nous allons explorer.
Alternatives nécessaires : Cependant, il existe des situations où row_number() ou first_value() deviennent requis. Par exemple, row_number() est particulièrement utile si vous devez ordonner des résultats et qu’une numérotation des lignes est nécessaire pour une analyse ultérieure. Cela est utile dans les cas où vous avez besoin de récupérer plusieurs valeurs, pas seulement le maximum. First_value(), quant à elle, sert à extraire la première valeur d’une partition de résultats, quand l’ordre est crucial.
Pour vous aider à choisir la fonction la plus adaptée, voici un tableau comparatif :
| Fonction | Complexité | Performance | Facilité de Lecture | Cas d’Usage Privilégié |
|---|---|---|---|---|
| max_by | Faible | Haute | Élevée | Récupérer une valeur associée au maximum |
| row_number() | Moyenne | Moyenne | Moyenne | Ordonnancer des résultats |
| first_value() | Moyenne | Moyenne | Moyenne | Récupérer la première valeur d’une série |
| max + join | Élevée | Variable | Basse | Comparaisons complexes entre plusieurs jeux de données |
Réfléchissez donc bien : dans quel contexte vous trouvez-vous ? La simplicité et la performance rendent max_by attrayante, mais certains cas nécessitent une approche plus élaborée.
Comment max_by influence la productivité et la maintenance des requêtes SQL
L’utilisation de la fonction SQL max_by dans BigQuery révolutionne la manière dont nous concevons nos requêtes. En simplifiant les instructions SQL, elle réduit la complexité générale et facilite la compréhension des données pour les équipes. Imagine un projet data où nous avons besoin de récupérer rapidement les dernières activités des utilisateurs pour notre dashboard. Au lieu d’écrire une requête compliquée qui jongle entre GROUP BY et des sous-requêtes, on peut simplement utiliser max_by.
Prenons un exemple concret : supposons que nous avons une table d’activités des utilisateurs avec des colonnes pour l’ID de l’utilisateur, le type d’activité et la date. Au lieu de convoluer notre requête, nous pourrions écrire :
SELECT user_id, max_by(activity_type, activity_date) AS latest_activity
FROM user_activities
GROUP BY user_id;Cet exemple est simple, mais il démontre comment max_by permet d’obtenir rapidement les dernières activités de chaque utilisateur. Cela augmente non seulement la productivité des développeurs, mais améliore également la maintenabilité du code. Pourquoi ? Parce qu’une requête claire est plus facile à relire et à modifier. Si les collaborateurs peuvent comprendre immédiatement le but de la requête, ils seront plus efficaces pour résoudre des problèmes ou faire des améliorations.
Au-delà de la lisibilité, max_by peut également avoir un impact significatif sur la performance des requêtes. En réduisant la quantité de traitement nécessaire pour obtenir des résultats, nous canalisons nos ressources vers des calculs plus efficaces. Par exemple, lors d’un chargement de données massives, le temps de réponse sera réduit, ce qui est crucial pour des analyses en temps réel.
Cependant, des bonnes pratiques sont essentielles pour éviter les erreurs. Voici quelques conseils :
- Assurez-vous que les colonnes passées à max_by sont bien indexées pour des performances optimales.
- Évitez les types de données mixtes dans les colonnes pour garantir une cohérence.
- Testez vos requêtes avec des sous-ensembles de données avant de les appliquer à des volumes plus importants.
En intégrant ces principes lors de l’utilisation de max_by, vous maximisez vos chances de succès tout en diminuant le risque d’erreurs dans vos pipelines de données.
Faut-il adopter max_by dans vos requêtes SQL dès aujourd’hui ?
La fonction max_by est une vraie pépite pour qui manipule régulièrement des données groupées en BigQuery. Elle simplifie drastiquement la récupération de valeurs conditionnelles, notamment temporelles, tout en rendant le code plus clair et souvent plus performant. Pour l’essentiel des cas simples comme extraire la dernière commande ou événement, elle fait gagner du temps et évite le recours à des fenêtres complexes. Toutefois, il faut savoir la compléter avec d’autres fonctions selon les cas. Intégrer max_by dans sa boîte à outils SQL est une amélioration pragmatique pour travailler mieux, plus vite et avec plus de fiabilité.
FAQ
Qu’est-ce que la fonction max_by en SQL ?
Pourquoi utiliser max_by plutôt que row_number() ?
Quels sont les cas d’utilisation typiques de max_by ?
max_by est-elle disponible dans tous les moteurs SQL ?
Peut-on utiliser max_by avec des colonnes contenant des valeurs NULL ?
A propos de l’auteur
Je suis Franck Scandolera, analyste et consultant en data engineering et web analytics depuis plus de dix ans. J’accompagne les professionnels pour automatiser, exploiter et valoriser leurs données grâce à des outils comme BigQuery, SQL et l’automatisation. Formateur experienté, je partage régulièrement mes conseils pratiques pour optimiser les requêtes et infrastructures data, rendant la donnée plus accessible et exploitable au quotidien.
⭐ Analytics engineer, Data Analyst et Automatisation IA indépendant ⭐
- Ref clients : Logis Hôtel, Yelloh Village, BazarChic, Fédération Football Français, Texdecor…
Mon terrain de jeu :
- Data Analyst & Analytics engineering : tracking avancé (GTM server, e-commerce, CAPI, RGPD), entrepôt de données (BigQuery, Snowflake, PostgreSQL, ClickHouse), modèles (Airflow, dbt, Dataform), dashboards décisionnels (Looker, Power BI, Metabase, SQL, Python).
- Automatisation IA des taches Data, Marketing, RH, compta etc : conception de workflows intelligents robustes (n8n, 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.