Comment exploiter efficacement la fonction SQL max_by dans BigQuery

La fonction max_by vous simplifie la vie en SQL en récupérant la valeur d’une colonne liée au maximum d’une autre, comme un order_id du dernier achat. BigQuery l’intègre nativement, évitant les complexités de row_number(). Testée en conditions réelles, elle optimise vos requêtes.

3 principaux points à retenir.

• max_by simplifie la récupération de valeurs associées au max d’une autre colonne.
• Elle évite les requêtes complexes avec row_number() ou sous-requêtes.
• Disponible notamment dans BigQuery, elle améliore lisibilité et performances SQL.

Qu’est-ce que la fonction max_by en SQL

Qu’est-ce que la fonction max_by en SQL ? La fonction max_by est une fonction d’agrégation très utile qui retourne la valeur d’une colonne associée à la valeur maximale d’une autre colonne. C’est un peu comme dire : « Donne-moi ce qui est le meilleur d’un ensemble, et donne-moi aussi ce qui lui est lié ». Dans le cadre de BigQuery et d’autres moteurs SQL comme Presto ou AWS Athena, cette fonction simplifie considérablement l’écriture de requêtes.

La syntaxe est simple : max_by(valeur_à_récupérer, colonne_de_comparaison). Vous indiquez ce que vous voulez récupérer et avec quelle colonne vous souhaitez comparer. Imaginez que vous vouliez récupérer l’ID de commande ayant la date la plus récente pour chaque utilisateur. Grâce à max_by, cela devient un jeu d’enfant.

Voici un exemple concret :

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

Avec cette simple requête, vous obtenez directement l’ID de la commande la plus récente pour chaque utilisateur. Ça, c’est efficace. Pourquoi opter pour max_by plutôt que des méthodes classiques comme row_number() ou des sous-requêtes ? La première raison : la clarté et la concision du code. max_by vous permet d’éviter les manipulations complexes de fenêtres ou de groupes intermédiaires qui rendent souvent le code long et difficile à lire.

Pour illustrer cette différence, voici un tableau récapitulatif qui met en lumière les différences entre max_by et row_number :

Avec des outils comme BigQuery, pourquoi s’encombrer de méthodes compliquées quand une fonction aussi efficace existe ? Pour plus de détails, vous pouvez consulter ce lien : ici.

Pourquoi privilégier max_by plutôt que row_number en BigQuery

Utiliser max_by est souvent la solution la plus judicieuse dans BigQuery lorsqu’on cherche la valeur associée au maximum d’une autre colonne. Pourquoi ? Parce que cette fonction offre une lisibilité et une concision qui la rendent plus attrayante par rapport à row_number(). Ce dernier nécessite souvent des requêtes plus complexes, surtout lorsque vous travaillez avec de grandes tables.

Avec row_number(), vous devez d’abord partitionner vos données et ensuite filtrer pour obtenir uniquement les enregistrements souhaités. Prenons un exemple classique : si vous voulez la dernière commande de chaque utilisateur, vous serez obligé de créer un sous-ensemble avec un CTE ou une sous-requête. Voici un extrait de code pour illustrer ce point :

WITH ranked_orders AS (
  SELECT user_id, order_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rn
  FROM orders
)
SELECT user_id, order_id, order_date
FROM ranked_orders
WHERE rn = 1;

Ce code devient rapidement abscons, surtout si les opérations s’accumulent. En revanche, avec max_by, la syntaxe est plus directe et plus claire :

SELECT user_id,
       MAX_BY(order_id, order_date) AS last_order_id
FROM orders
GROUP BY user_id;

Cette méthode est non seulement plus simple, mais elle réduit également le coût de traitement, ce qui peut être crucial lorsque vous interrogez de grands ensembles de données. En termes de performance, max_by évite le surcoût des opérations de tri et de partitionnement que nécessite row_number().

Maintenant, jetons un œil à un tableau comparatif :

• max_by
  • Clarté et concision
  • Meilleure performance sur grandes tables
  • Moins de complexité dans la requête
• row_number()
  • Plus lourd à écrire et à lire
  • Nécessite des sous-requêtes
  • Potentiellement plus coûteux en termes de ressources

En fin de compte, le choix entre max_by et row_number() est évident dans la plupart des cas où la lisibilité et l’efficacité sont des priorités. Pour en savoir plus sur les fonctions d’agrégation dans BigQuery, vous pouvez consulter cette ressource.

Comment utiliser max_by dans vos cas pratiques courants

La fonction SQL max_by dans BigQuery est une arme redoutable quand il s’agit de récupérer la valeur d’une colonne correspondant au maximum d’une autre. Elle est particulièrement utile dans divers cas d’utilisation, surtout dans le domaine du e-commerce, du tracking web et des analytics applicatifs. Comment ? Voyons cela de plus près.

• Dernière commande d’un client: Supposons que vous souhaitiez connaître la dernière commande effectuée par chaque client. Vous pouvez utiliser max_by pour récupérer la date de commande la plus récente et le montant correspondant. Voici comment cela se présente :

SELECT customer_id,
       MAX_BY(order_date, order_amount) AS last_order_info
    FROM orders
    GROUP BY customer_id;

SELECT user_id,
       MAX_BY(event_date, event_type) AS last_event_info
    FROM user_events
    GROUP BY user_id;

SELECT product_id,
       MAX_BY(comment_date, comment_text) AS last_comment
    FROM product_comments
    GROUP BY product_id;

Dans chacun de ces cas, max_by s’associe parfaitement avec GROUP BY pour segmenter les résultats par clef d’identification, que ce soit un client, un utilisateur ou un produit. Mais attention ! max_by a ses limites. Si vous voulez récupérer plusieurs valeurs ou faire des analyses plus complexes, il vous faudra combiner d’autres fonctions SQL. Par exemple, l’utilisation de JOIN ou de sous-requêtes peut être nécessaires si vous devez détailler les résultats.

Enfin, pour optimiser vos performances et vous assurer que votre base de données BigQuery supporterait les requêtes à grande échelle, consultez les meilleures pratiques. L’utilisation judicieuse de max_by peut transformer la façon dont vous gérez et analysez vos données, à condition d’en saisir les nuances.

Comment aller plus loin avec max_by et alternatives SQL

Bien que max_by soit un excellent outil pour trouver une valeur maximale tout en récupérant une autre valeur associée, il est parfois limitant. Imaginons que vous ayez besoin de gérer plusieurs critères ou de récupérer différentes colonnes selon la valeur maximale. Dans ce cas, vous avez quelques alternatives qui peuvent rendre votre vie beaucoup plus simple.

Parlons d’abord de la fonction first_value() avec les window functions. Cela vous permet de récupérer la première valeur d’une partition que vous avez définie. Par exemple, si vous voulez obtenir le premier projet d’un employé ayant le salaire maximum, voici comment vous pourriez procéder :

SELECT 
  employee_id, 
  first_value(project) OVER (PARTITION BY employee_id ORDER BY salary DESC) AS top_project
FROM 
  employees

Une autre alternative est d’utiliser ARRAY_AGG() avec LIMIT 1. Cela vous donne un contrôle total sur l’ordre des valeurs et vous permet de récupérer plus d’un champ :

SELECT 
  employee_id, 
  (SELECT AS STRUCT * 
   FROM UNNEST(ARRAY_AGG(STRUCT(salary, project) ORDER BY salary DESC)) LIMIT 1) AS best_project
FROM 
  employees
GROUP BY 
  employee_id

Enfin, l’utilisation de STRUCT dans BigQuery pour emballer plusieurs colonnes en une structure peut rendre votre requête encore plus concise. Cela permet de manipuler des ensembles de données complexes plus efficacement.

Pour faciliter vos choix entre ces différentes options, voici un tableau récapitulatif :

Enfin, quelques conseils pratiques pour optimiser vos requêtes avec max_by : utilisez des index pour accélérer l’accès aux données, mettez en place un partitioning pour gérer des ensembles de données massifs et appliquez un filtrage précoce pour ne traiter que les données nécessaires. Ça fait la différence !

Alors, pourquoi ne pas intégrer max_by dès aujourd’hui dans vos requêtes SQL ?

La fonction max_by est un véritable atout pour qui cherche à écrire des requêtes SQL plus efficaces, lisibles et performantes dans BigQuery ou autres plateformes compatibles. Elle simplifie significativement la récupération de valeurs associées au maximum d’une autre colonne, évitant toute gymnastique inutile avec row_number() ou sous-requêtes lourdes. En pratique, elle couvre un large spectre d’utilisations, du suivi des dernières commandes à l’analyse événementielle. Si votre quotidien implique la manipulation régulière de données relationnelles avec SQL, adopter max_by vous fera gagner du temps et limitera les erreurs. Alors, la vraie question est : pourquoi vous en passer ?

FAQ

A propos de l’auteur

Franck Scandolera, expert en Web Analytics et Data Engineering, accompagne depuis plus de dix ans des équipes dans l’optimisation de leurs dispositifs data. Responsable de l’agence webAnalyste et formateur indépendant, j’interviens sur BigQuery et SQL avec une approche pragmatique et orientée usages métier. Maîtriser les fonctions avancées comme max_by est essentiel pour automatiser et fiabiliser vos requêtes et reportings. Mon expérience terrain en analytics et automatisation no-code me permet de vous guider vers les meilleures pratiques pour valoriser vos données sans complexité superflue.