La fonction max_by en SQL simplifie l’extraction de valeurs corrélées au maximum d’une autre colonne, par exemple récupérer la dernière commande d’un utilisateur sans complexité inutile ni sous-requêtes lourdes (BigQuery Documentation).
3 principaux points à retenir.
- max_by sélectionne une valeur liée au maximum dans une autre colonne en une seule fonction.
- Elle remplace efficacement row_number() ou les sous-requêtes lourdes en simplifiant le code SQL.
- Idéale pour extraire valeurs comme la dernière commande, l’événement le plus récent ou le dernier commentaire.
Qu’est-ce que la fonction max_by en SQL
La fonction max_by est une fonctionnalité puissante en SQL, souvent sous-estimée. En gros, elle permet de retourner la valeur d’une colonne qui est liée à la valeur maximale d’une autre colonne dans un ensemble de données. On lui donne essentiellement deux colonnes et, magie ! Elle vous renvoie celle qui est associée au maximum de l’autre.
Imaginez que vous avez une table de ventes avec des ID de commande et des montants. Si vous voulez savoir quel montant correspond à la dernière commande, max_by est votre meilleur allié. Elle s’intègre parfaitement aux requêtes GROUP BY, où vous pouvez grouper vos données par une colonne et en tirer parti pour obtenir des informations précises dans chaque groupe.
Utilité dans l’agrégation : cette fonction simplifie vos requêtes lorsque vous recherchez des valeurs maximales sans perdre en lisibilité. Contrairement aux méthodes classiques comme row_number() ou les sous-requêtes, qui peuvent rapidement devenir complexes et lourdes, max_by offre une approche plus élégante et performante.
Pour illustrer ce point, prenons un exemple concret : imaginez une table nommée Orders contenant les colonnes order_id et order_date. Voici comment vous utiliseriez max_by pour récupérer le dernier order_id basé sur order_date :
SELECT max_by(order_id, order_date) AS latest_order
FROM Orders;En une seule ligne de code, vous obtenez exactement ce que vous cherchez. Nul besoin de fouiller dans des logique compliquées de sous-requêtes ou de méthodes peu claires.
| Méthode | Lisibilité | Performance |
|---|---|---|
| max_by | Haute | Optimale |
| row_number() | Moyenne | Variable |
| Sous-requêtes | Basse | Défavorable |
Comme on peut le voir dans ce tableau, max_by surpasse largement les autres méthodes en termes de lisibilité et de performance. Si vous cherchez à optimiser vos requêtes et à garder votre code propre et efficace, il est temps d’adopter max_by dans votre arsenal SQL, et pourquoi pas explorer davantage les possibilités qu’elle offre sur des plateformes comme sql.sh.
Comment max_by facilite l’extraction des données temporelles
Oui, max_by est particulièrement taillée pour manipuler des colonnes temporelles. Imaginez que vous devez retrouver la dernière interaction d’un utilisateur sur une plateforme e-commerce. C’est là que cette fonction entre en jeu : elle permet d’extraire des données précises sans une surcharge cognitive inutile et sans avoir à jongler avec plusieurs requêtes complexes.
Prenons un exemple concret. Supposons que vous ayez une table orders qui contient les colonnes user_id, order_date, et order_amount. Si vous voulez récupérer la dernière commande de chaque utilisateur, voici comment utiliser max_by efficacement :
SELECT user_id,
max_by(order_amount, order_date) AS last_order_amount,
max_by(order_date, order_date) AS last_order_date
FROM orders
GROUP BY user_id;
Ce code retourne la dernière commande en termes de valeur et de date pour chaque utilisateur, tout en gardant la requête simple et lisible. Grâce à max_by, vous évitez la verbosité d’un window function classique, qui nécessiterait plusieurs étapes pour parvenir au même résultat. En effet, une approche avec ROW_NUMBER() aurait été beaucoup plus longue :
WITH ranked_orders AS (
SELECT user_id,
order_amount,
order_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM orders
)
SELECT user_id, order_amount, order_date
FROM ranked_orders
WHERE rn = 1;Dans le cadre de l’analyse de données en e-commerce ou en Web Analytics, la simplicité de la fonction max_by permet non seulement d’optimiser les performances des requêtes, mais aussi de faciliter la lecture et la maintenance du code. Vous avez moins de chances de faire des erreurs avec une syntaxe simplifiée et moins encombrante.
Pour des plateformes comme BigQuery ou Snowflake, l’implémentation de cette fonction est aussi rapide qu’efficace. Il ne suffit que de quelques lignes de code pour atteindre vos objectifs analytiques sans vous perdre dans les méandres des fonctions de fenêtres. En somme, max_by est un outil inestimable pour tous ceux qui cherchent à extraire des données temporelles précises et pertinentes avec facilité et efficacité.
Quand et pourquoi privilégier max_by dans vos requêtes SQL
Lorsque vous utilisez SQL pour vos analyses, il est essentiel de choisir la bonne fonction pour le bon contexte. La fonction max_by se révèle particulièrement pertinente dans les situations où vous devez extraire une ligne associée à un maximum dans le cadre de reporting, analytics ou business intelligence. Voici quelques scénarios typiques où cette fonction brille :
- Analyse de Performances : Lorsque vous cherchez à obtenir le meilleur vendeur d’une période donnée, utiliser max_by vous permet de sélectionner rapidement la ligne de l’agent avec les ventes maximales, tout en récupérant des informations supplémentaires comme son ID ou d’autres métriques.
- Optimisation de Reporting : Dans les rapports, récupérer une seule ligne liée à un maximum peut considérablement simplifier les requêtes par rapport à des solutions utilisant des sous-requêtes complexes.
Les avantages sont évidents : la simplicité et la maintenabilité du code sont renforcées, ce qui contribue à un traitement plus efficace des données. Cela se traduit par des temps d’exécution réduits, ce qui est crucial, notamment dans des outils courants tels que BigQuery et Snowflake. Cependant, il y a des limites. Par exemple, si plusieurs valeurs identiques maximales existent, max_by retourne juste une ligne, ce qui peut conduire à une perte d’informations. De même, si votre analyse nécessite une granularité plus fine ou plusieurs mesures simultanées, cette fonction pourrait ne pas suffire.
Voici un tableau qui résume les avantages et limites de max_by :
| Avantages | Limites |
|---|---|
| Simplicité d’utilisation | Perte d’informations en cas de valeurs max identiques |
| Efficacité du traitement | Granularité insuffisante pour certaines analyses |
| Facilité de maintenance du code | Limitée aux contextes simples |
Pour intégrer max_by dans vos pipelines de données, concentrez-vous sur les environnements comme BigQuery ou Snowflake et utilisez des outils tels que dbt. Voici un exemple de requête optimisée :
SELECT
department,
max_by(salary, employee_id) AS top_employee
FROM employees
GROUP BY department
Cette requête extrait le meilleur employé par salaire pour chaque département, tout en conservant les identifiants des employés, rendant ainsi l’analyse à la fois simple et efficace. Pour approfondir votre connaissance sur max_by, consultez cet article ici.
Alors, max_by est-elle la solution idéale pour vos requêtes SQL ?
La fonction max_by s’impose comme une arme redoutable dans votre arsenal SQL quand il s’agit de lier des données à la valeur maximale d’une colonne. Simple, performante et élégante, elle évite des constructions complexes avec row_number() ou sous-requêtes. Pour les analyses orientées temps — dernières commandes, événements, ou commentaires — c’est un gain de temps et une clarté de code assurés. À condition de comprendre ses limites, max_by optimise vos requêtes, facilite la maintenance et accélère les traitements. Adoptée par BigQuery ou Snowflake, elle mérite une place centrale dans vos patterns SQL. La maîtrisez-vous déjà ?
FAQ
Qu’est-ce que la fonction max_by en SQL ?
Dans quels cas utiliser max_by plutôt que row_number() ?
max_by fonctionne-t-elle avec toutes les bases SQL ?
Peut-on utiliser max_by avec plusieurs colonnes ?
Quelles sont les limites de max_by ?
A propos de l’auteur
Je suis Franck Scandolera, consultant indépendant et formateur depuis plus de dix ans, expert en Web Analytics, Data Engineering et automatisation. J’interviens régulièrement sur BigQuery et SQL, aidant entreprises et professionnels à structurer leurs données et automatiser leurs analyses. Ma maîtrise des outils cloud, pipelines data et reporting automatisé m’offre une expertise concrète et opérationnelle, orientée vers l’efficacité métier.
⭐ 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.