Comment utiliser max_by en SQL pour simplifier vos requêtes ?

Par

La fonction max_by en SQL permet de récupérer la valeur d’une colonne correspondant à la plus grande valeur d’une autre colonne, simplifiant ainsi les requêtes habituellement complexes avec row_number(). Très utile en BigQuery, elle optimise vos agrégations en un seul appel clair et efficace.

3 principaux points à retenir.

  • max_by simplifie l’extraction conditionnelle basée sur une valeur maximale en SQL.
  • Elle remplace habilement les requêtes avec row_number() en limitant la complexité.
  • Adaptée à des cas pratiques comme récupérer la dernière commande ou le dernier événement utilisateur.

Qu’est-ce que la fonction max_by en SQL

La fonction max_by en SQL est un outil sous-utilisé mais incroyablement efficace. Son objectif ? Récupérer la valeur d’une colonne associée à la valeur maximale d’une autre colonne dans un ensemble de données. Simple, mais puissant.

Sa syntaxe générale est :

max_by(colonne_à_récupérer, colonne_de_critère)

Pour faire simple : vous passez deux paramètres à la fonction. Le premier est la colonne dont vous voulez récupérer la valeur, et le second est celle selon laquelle vous voulez déterminer le maximum. Ça peut sembler banal, mais croyez-moi, cela offre un raccourci précieux par rapport aux méthodes classiques, comme row_number() ou les sous-requêtes. Plus besoin de se compliquer la vie avec des requêtes imbriquées, max_by vous donne directement ce que vous cherchez.

Découvrez égalementComment prétraiter les données en Python en 8 astuces ?

Pour illustrer cela, prenons un exemple concret en SQL BigQuery. Supposons que nous voulons récupérer le dernier order_id d’un utilisateur, en regroupant par user_id. On pourrait écrire :

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

Vous obtenez instantanément le dernier order_id en fonction de la date de commande. Débarrassé des complexités inutiles.

Alors pourquoi choisir max_by? La réponse est claire : la lisibilité et la performance. En utilisant cette fonction, vous rendez votre code plus compréhensible, ce qui est crucial lorsque d’autres (ou même vous-même dans quelques mois) doivent revenir dessus. De plus, niveau performance, max_by a tendance à être plus efficace que des alternatives, grâce à une simplification des opérations nécessaires lors de l’exécution de la requête. En effet, l’utilisation de sous-requêtes et de méthodes comme row_number() peut alourdir le traitement et rendre le tout moins performant.

Si vous voulez creuser un peu plus, je vous invite à consulter cet article sur les fonctions min et max en SQL ici. Vous verrez que l’ajout de fonctions comme max_by peut vraiment faire la différence.

Dans quels cas utiliser max_by efficacement

Découvrez égalementComment 5 containers Docker transforment votre PME ?

Quand recourir à max_by est pertinent ? La réponse est simple : dès que vous avez besoin d’extraire une valeur liée à un maximum observé. Cela peut s’appliquer à un large éventail de scénarios métiers. Prenons des exemples : vous pouvez vouloir récupérer la dernière commande d’un client sur un webshop, le dernier commentaire d’un utilisateur sur une application mobile, ou encore le dernier événement enregistré dans un système de logs. Voici quelques cas d’usage classiques :

  • Webshops : Imaginez une boutique en ligne qui cherche à connaître le dernier achat effectué par chaque client, facilitant ainsi le ciblage des promotions.
  • Applications mobiles : Sur une app de messagerie, obtenir le dernier message envoyé dans un chat permet d’afficher les notifications les plus récentes.
  • Systèmes de logs : Dans le contexte d’un suivi des opérations système, récupérer l’événement le plus récent peut aider à identifier rapidement des incidents critiques.
  • Reportings en temps réel : Pour les dashboards, il est essentiel d’afficher les informations les plus récentes, comme la dernière vente ou le dernier feedback client.

Utiliser max_by permet d’éviter des requêtes complexes, notamment celles qui impliquent un double regroupement ou plusieurs jointures inutiles. Ainsi, vous simplifiez considérablement le code et améliorez les performances.

Voici quelques exemples en BigQuery :


SELECT user_id, 
       MAX_BY(order_date, purchase_amount) AS last_order
FROM orders
GROUP BY user_id;


SELECT post_id, 
       MAX_BY(comment_date, comment_text) AS latest_comment
FROM comments
GROUP BY post_id;

Ces requêtes vous livrent instantanément les dernières interactions pertinentes sans avoir à jongler avec des groupements compliqués. Toutefois, gardez à l’esprit certaines limites. Par exemple, l’utilisation de max_by nécessite que toutes les données soient bien typées, ce qui peut exclure des valeurs nulles ou des types de données inadéquats. Vérifiez soigneusement vos données avant d’appliquer cette fonction pour éviter des résultats erronés.

Découvrez égalementQuels patterns analytiques un data scientist doit maîtriser?

En résumé, si votre objectif est de repérer rapidement une valeur liée à un maximum dans vos données, max_by est un allié puissant à intégrer dans votre boîte à outils SQL. En gros, faites-en usage mais restez lucide sur ses restrictions !

Comment intégrer max_by dans vos requêtes SQL

Pour intégrer la fonction max_by dans vos requêtes SQL, commençons par observer comment cela fonctionne dans une requête qui utilise GROUP BY. La fonction max_by permet de récupérer la valeur associée à la clé maximale, ce qui peut être très utile pour obtenir la dernière entrée d’un groupe de données. Prenons l’exemple d’une table de commandes, nommons-la commandes. Voici la structure de notre table :


CREATE TABLE commandes (
    id SERIAL PRIMARY KEY,
    utilisateur_id INT,
    montant DECIMAL(10, 2),
    date_commande TIMESTAMP
);

Imaginons que nous voulons récupérer la dernière commande pour chaque utilisateur. La requête SQL utilisant max_by serait la suivante :


SELECT utilisateur_id, max_by(date_commande, montant) AS derniere_commande
FROM commandes
GROUP BY utilisateur_id;

Décomposons cette requête :

  • SELECT utilisateur_id : On sélectionne l’identifiant de l’utilisateur.
  • max_by(date_commande, montant) : Ici, nous récupérons la date de commande maximale, tout en renvoyant le montant de cette commande.
  • FROM commandes : Nous indiquons la table à partir de laquelle nous interrogeons les données.
  • GROUP BY utilisateur_id : Nous groupons les résultats par utilisateur.
Découvrez égalementQuels sont les meilleurs cours SQL gratuits avec certificat ?

Pour une utilisation plus avancée, imaginons que vous souhaitiez également compter le nombre de commandes et calculer le montant total par utilisateur. Voici comment pourrait s’y prendre :


SELECT utilisateur_id, 
       count(*) AS nombre_commandes, 
       sum(montant) AS total_montant,
       max_by(date_commande, montant) AS derniere_commande
FROM commandes
GROUP BY utilisateur_id;

Dans cette requête, nous ajoutons simplement count(*) pour le nombre de commandes et sum(montant) pour le total des montants, tout en gardant max_by pour accéder à la dernière commande.

Pour mieux comprendre, voici un tableau comparatif entre max_by, row_number(), et d’autres méthodes :

Méthode Complexité Performance Lisibilité
max_by Simple Élevée Élevée
row_number() Moyenne Moyenne Moyenne
Subquery Élevée Faible Faible

En résumé, max_by s’avère particulièrement efficace pour simplifier vos requêtes, surtout lorsqu’il s’agit de récupérer les dernières données. Pour un complément d’informations sur GROUP BY, vous pouvez consulter cet article ici.

Quels bénéfices retirer de l’usage de max_by en SQL

Découvrez égalementComment automatiser efficacement le feature engineering en Python ?

L’usage de la fonction max_by dans SQL présente de nombreux bénéfices qui transcendent la simple optimisation. D’abord, la simplicité est un plaisir évident ; en remplaçant des requêtes complexes souvent encombrées de sous-requêtes et de tri, max_by permet d’atteindre des résultats clairs et précis sans se perdre dans le code. Moins de complexité rime avec moins d’erreurs, ce qui est un avantage non négligeable lorsqu’il s’agit de manipuler des données critiques.

En termes de gain de temps, une requête utilisant max_by peut significativement réduire le temps d’exécution. Par exemple, en utilisant cette fonction, vous évitez d’avoir à implémenter des partitions et des fenêtres, ce qui peut alourdir considérablement une consultation. Selon une analyse de Google Cloud, l’optimisation des requêtes peut diminuer de 30 à 40 % le temps de traitement des données dans certains scénarios.[source]

Un autre aspect à considérer est la lisibilité du code. Avec max_by, les requêtes deviennent plus intuitives et plus faciles à comprendre pour les autres membres de l’équipe. Cela facilite non seulement la collaboration, mais également la maintenance et l’évolution des requêtes. Prenons l’exemple d’une équipe de data engineers qui a migré vers max_by pour traiter les retours clients. Cela a permis d’éliminer des blocages liés à la compréhension des requêtes et, par conséquent, d’accélérer le développement d’employés moins expérimentés.

Dans un environnement d’écriture SQL complexe, particulièrement dans le cloud avec des systèmes comme BigQuery ou Snowflake, max_by se présente comme un choix judicieux. Il s’intègre sans peine dans des pratiques d’écriture SQL modernes, rendant le code plus propre et plus maintenable. En adoptant max_by comme outil de base, les analystes et data engineers seront assurés de rédiger des requêtes plus efficaces, moins sujettes aux erreurs et globalement plus faciles à gérer dans le cadre des projets data.

Prêt à intégrer max_by pour booster vos requêtes SQL ?

La fonction max_by s’impose comme une solution élégante pour simplifier vos extractions de données liées à des valeurs maximales, notamment en BigQuery. Elle évite le recours à des requêtes complexes avec row_number(), rendant vos scripts plus courts, lisibles et performants. En adoptant max_by, vous gagnez en efficacité et en qualité de maintenance de vos pipelines SQL, bénéfique aussi bien pour les analystes que pour les data engineers. N’attendez plus pour l’intégrer dans vos pratiques et transformez une tâche rébarbative en un moment de délice SQL.

FAQ

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

max_by est une fonction SQL qui permet de récupérer la valeur d’une colonne correspondant à la valeur maximale d’une autre colonne, facilitant ainsi l’extraction ciblée lors d’agrégations groupées.

Comment max_by simplifie-t-elle les requêtes SQL complexes ?

Elle remplace souvent des constructions avec row_number() ou des sous-requêtes en un appel simple, réduisant la complexité et améliorant la lisibilité des requêtes.

Dans quels contextes utiliser max_by ?

Pour récupérer, par exemple, la dernière commande d’un utilisateur, le dernier commentaire sur un produit, ou le dernier événement dans un parcours utilisateur, particulièrement dans BigQuery ou Snowflake.

Est-ce que max_by gère les valeurs nulles ?

La gestion des valeurs nulles dépend du système SQL utilisé. En général, les valeurs nulles sont ignorées dans la comparaison pour le max, mais il convient de vérifier la documentation spécifique.

max_by est-elle compatible avec tous les moteurs SQL ?

Non. max_by est notamment disponible dans BigQuery et Snowflake, mais pas dans tous les SGBD. Il faut vérifier la compatibilité avant usage.

 

A propos de l’auteur

Franck Scandolera, responsable de l’agence webAnalyste et formateur en Analytics et Data Engineering, accompagne depuis plus d’une décennie des pros du digital à optimiser leurs données et automatisations. Expert en SQL, BigQuery, et écosystèmes Cloud, il partage un savoir-faire pragmatique et centré sur l’usage métier pour accompagner ses clients vers l’excellence data.

Retour en haut
AIgenierie