Comment optimiser vos requêtes SQL avec la fonction max_by ?

La fonction max_by en SQL simplifie la récupération d’une valeur associée au maximum d’une autre colonne sans recourir à des requêtes complexes. Très utile dans BigQuery, elle remplace avantageusement row_number(), offrant clarté et performance selon Tamas Ujhelyi, expert reconnu en SQL.

3 principaux points à retenir.

max_by facilite l’extraction directe de valeurs liées au max d’une autre colonne.
Elle simplifie et accélère les requêtes en évitant les jointures ou fenêtres complexes.
Compatible avec BigQuery, son usage améliore lisibilité et maintenabilité du code SQL.

Qu’est-ce que la fonction max_by en SQL ?

La fonction max_by est un atout majeur pour quiconque souhaite extraire des données pertinentes d’un ensemble d’enregistrements tout en gardant la lisibilité du code SQL. Alors, qu’est-ce que c’est exactement ? En termes simples, cette fonction d’agrégation permet de renvoyer la valeur d’une colonne associée à la valeur maximale d’une autre colonne. Contrairement à row_number(), qui vient avec son lot de complications liées aux fenêtres et au tri, max_by vous offre une solution directe, pratique et sans chichis.

Son usage basique est limpide : vous en avez besoin pour obtenir le « meilleur » d’un ensemble d’enregistrements, sans convoluer votre requête avec des sous-requêtes ou des jointures. Voici la syntaxe principale dans BigQuery :

SELECT max_by(column_name, order_column) 
FROM your_table
GROUP BY group_column;

Un exemple concret illustrant cette fonction serait de vouloir récupérer l’ID de la commande la plus récente pour chaque utilisateur. Supposons que vous ayez une table de commandes avec les colonnes user_id, order_id et order_date. Voici comment vous pourriez procéder :

SELECT user_id, max_by(order_id, order_date) AS latest_order 
FROM orders 
GROUP BY user_id;

Cette requête retournera pour chaque utilisateur l’ID de la commande récente sans avoir à plonger dans des sous-requêtes ou des jointures ambiguës. Cela simplifie non seulement votre requête, mais rend aussi votre intention beaucoup plus claire. C’est ce genre de clarté qui vous fera gagner du temps dans l’analyse. Vous avez des besoins plus complexes ? Max_by est une solution qui mérite d’être explorée.

Quand et pourquoi utiliser max_by plutôt que row_number() ?

La fonction max_by est souvent sous-estimée, mais elle peut faire toute la différence quand il s’agit d’optimiser vos requêtes SQL, en particulier face à la fonction row_number(). Pourquoi ? Parce qu’elle permet de simplifier les requêtes lourdes et de réduire leur complexité générale. Utiliser row_number() avec des partitions et des ordres augmente non seulement la difficulté de lecture mais aussi la maintenance de vos codes, à long terme, c’est un vrai casse-tête.

Les cas d’utilisation de max_by sont assez courants dans le monde des affaires. Par exemple, si vous devez extraire la dernière commande passée par chaque client, le dernier commentaire sur un produit ou encore le dernier événement lié à un utilisateur, max_by simplifie votre requête.

Voyons cela concrètement avec un exemple SQL. Supposons que vous ayez une table Commandes :


CREATE TABLE Commandes (
    id INT,
    client_id INT,
    date_commande DATE,
    montant DECIMAL
);

Pour obtenir la dernière commande de chaque client avec row_number(), vous feriez :


SELECT id, client_id, date_commande, montant
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_commande DESC) AS rn
    FROM Commandes
) AS subquery
WHERE rn = 1;

Maintenant, avec max_by, cela devient bien plus simple :


SELECT client_id, max_by(date_commande, montant) AS derniere_commande
FROM Commandes
GROUP BY client_id;

Regardez la différence : la version avec max_by est plus succincte et plus facile à comprendre. En termes de performance, il y a aussi un gain notable – moins de computed columns et moins d’opérations en mémoire.

Voici un tableau récapitulatif qui synthétise les forces et les limites de max_by par rapport à row_number() :

Critère	max_by	row_number()
Simplicité	🔝 Très simple à lire	🐢 Plus complexe, nécessite des partitions
Performance	⚡ Meilleur en termes de vitesse	⏳ Peut être lent avec de larges datasets
Flexibilité	👍 Très flexible pour les agrégations	👎 Limitée avec des options plus complexes
Maintenance	✅ Facile à maintenir	❌ Difficile à modifier sans erreurs

En somme, max_by est un choix plus malin dans de nombreux scénarios, surtout quand il s’agit de traiter des données en temps réel et d’améliorer la lisibilité de votre code.

Comment intégrer max_by dans vos requêtes SQL ?

Pour tirer le meilleur parti de la fonction max_by dans vos requêtes SQL, il est essentiel de comprendre comment l’intégrer efficacement, surtout dans des contextes de regroupement avec GROUP BY. Cette fonction vous permet de sélectionner un enregistrement basé sur une valeur maximale dans un contexte spécifique. Prenons un exemple concret pour que tout cela soit plus limpide.

Imaginons que vous disposiez d’une table orders avec les colonnes order_id, user_id, et ordered_at. Si vous souhaitez extraire le dernier order_id par user_id, vous pouvez utiliser la fonction max_by comme suit :

SELECT user_id, 
       max_by(order_id, ordered_at) AS last_order_id
FROM orders
GROUP BY user_id;

Dans cet exemple, nous regroupons nos résultats par user_id. La fonction max_by(order_id, ordered_at) extraira l’order_id qui correspond à la date la plus récente, ordered_at. Cela vous permet d’obtenir le bon enregistrement sans avoir à écrire des sous-requêtes complexes.

Gardez à l’esprit quelques bonnes pratiques lors de l’utilisation de cette fonction :

Vérifiez la cohérence des données : Avant de lancer vos requêtes, assurez-vous que les colonnes utilisées ne contiennent pas de valeurs inattendues. Par exemple, un ordered_at nul pourrait fausser vos résultats.
Comprenez la logique métier : Assurez-vous que le critère de « max » a du sens dans le contexte de votre stratégie d’affaires. Cela vous évitera des erreurs d’interprétation.

En ce qui concerne la compatibilité, la fonction max_by est disponible dans des systèmes comme PostgreSQL ou Hive, mais ne l’est pas forcément dans d’autres systèmes comme MySQL. Vérifiez donc les spécificités de votre base de données à l’avance. Vous éviterez ainsi des erreurs de syntaxe.

Intégrer la fonction max_by dans vos requêtes peut grandement améliorer la lisibilité et la maintenance de votre code SQL. En simplifiant vos requêtes, vous réduisez le risque de bugs et facilitez les futures modifications nécessaires. Recherchez plus d’informations sur l’optimisation de vos requêtes ici.

Quels pièges et limites avec max_by ?

La fonction max_by est souvent saluée pour sa simplicité, mais elle a ses limites. Tout d’abord, qu’en est-il des valeurs nulles ? Si une colonne sur laquelle vous appliquez max_by contient des valeurs nulles, ces dernières peuvent mener à des résultats inattendus. Par exemple, si vous cherchez la ligne maximale par rapport à une valeur, des nulls peuvent fausser cette recherche en la rendant incomplète. En présence de valeurs nulles, il pourrait être plus judicieux d’utiliser les fonctions classiques de filtrage avant d’appliquer max_by.

Autre point crucial : la désambiguïsation. Imaginez un scénario où plusieurs lignes partagent la même valeur maximum. Que se passe-t-il dans ce cas ? max_by renvoie simplement un résultat, sans vous donner de garantie sur la ligne précise choisie. Cela pourrait vous coûter cher en termes de données exactes. Parfois, l’utilisation de row_number() est indispensable pour contrôler quel enregistrement est sélectionné, surtout dans des situations de doublons.

En matière de performances, deux facteurs entrent en jeu : le volume de données et la version de SQL que vous utilisez. Par exemple, dans un environnement de production avec des millions de lignes, la fonction row_number() pourrait être plus optimisée dans certaines implémentations que max_by. Des études indiquent que la performance varie selon le moteur SQL choisi, tel que mentionné dans un article de researchgate.net.

Pour éviter les pièges liés à max_by, voici quelques conseils pratiques :

Vérifiez vos données : Avant d’utiliser max_by, assurez-vous que vos colonnes ne contiennent pas de valeurs nulles ou que vous les gérez correctement.
Utilisez des techniques de désambiguïsation : Si des lignes partagent le même maximum, envisagez d’utiliser row_number() avec une clause ORDER BY pour choisir la ligne que vous souhaitez.
Testez les performances : Comparez les temps d’exécution des différentes fonctions dans vos requêtes pour déterminer ce qui est le plus efficace selon votre volume de données.

Faut-il adopter max_by pour vos prochaines requêtes SQL ?

Max_by est une arme redoutable pour simplifier vos requêtes en SQL lorsque vous devez récupérer la valeur associée au maximum d’une autre colonne. Plus lisible et plus directe que les classiques row_number(), elle allège la complexité de votre code et facilite sa maintenance. Toutefois, restez vigilant aux cas de données égales ou nulles et testez toujours vos requêtes selon le contexte métier. Intégrer max_by dans votre boîte à outils SQL, c’est miser sur l’efficacité et la clarté. Alors, prêt à sauter le pas ?

FAQ

Qu’est-ce que la fonction max_by en SQL et à quoi sert-elle ?

max_by est une fonction d’agrégation SQL qui retourne la valeur d’une colonne correspondant au maximum d’une autre colonne. Elle facilite l’extraction de données liées à un critère maximal, comme l’identifiant de la dernière commande d’un utilisateur, sans utiliser de fonctions fenêtre complexes.

Comment max_by se compare-t-elle à row_number() ?

row_number() génère des numéros de ligne selon un ordre, souvent combiné à une partition, ce qui alourdit les requêtes. max_by récupère directement la valeur liée au maximum d’une autre colonne en une seule fonction, simplifiant la requête et améliorant la lisibilité. Cependant, row_number() reste utile en cas d’égalité de valeurs ou besoin de classer plusieurs lignes.

Dans quels cas doit-on privilégier max_by ?

Lorsque vous souhaitez simplement connaître la valeur d’une colonne associée au plus grand élément d’une autre, comme la dernière date d’activité ou la plus récente commande par utilisateur, max_by est idéale. Elle rend le SQL plus court, clair et performant pour ce type de cas classiques.

Quelles limites rencontre-t-on avec max_by ?

max_by ne gère pas les cas où plusieurs lignes partagent la même valeur maximale ni les valeurs nulles selon les implémentations. Elle peut donc retourner un résultat non déterministe dans ces situations. Pour plus de contrôle sur ces cas, il faut préférer des fonctions fenêtre ou des techniques complémentaire.

max_by est-elle disponible dans toutes les bases SQL ?

Non. max_by est prise en charge dans BigQuery, Snowflake et quelques autres. Elle n’est pas standard SQL et peut manquer dans certains moteurs. Vérifiez toujours la documentation technique de votre base avant utilisation. En cas d’absence, des alternatives comme row_number() restent une solution fiable.

A propos de l’auteur

Je suis Franck Scandolera, consultant expert et formateur en Web Analytics, Data Engineering et SQL depuis plus de dix ans. Mon expérience technique approfondie, notamment sur BigQuery et les pipelines data, me permet de démocratiser des concepts avancés comme la fonction max_by. J’accompagne les professionnels du digital à écrire des requêtes SQL performantes et pérennes, alignées aux besoins métiers, pour exploiter pleinement la puissance de leurs données.

Franck Scandolera

⭐ 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.