Bertrand Fabre“Je connais les montant max de dépense des clients chaque jour depuis le début du moi 💪”. ”Top 👍. Et quels sont ces clients pour leur envoyer un mail de remerciement et d’un coupon pour les encourager à continuer leurs achats ?”. ”Euh…”.
Pour éviter de faire une requête trop complexe, il existe une jolie fonction en SQL et disponible sur BigQuery qui s’appelle MAX_BY().
Cette fonction permet d’obtenir la valeur d’une colonne associé au max d’une autre colonne.
Quoi ? Ce n’est pas clair ? Bon, sans contexte, c’est peut-être un peu brute 😅.
Commençons par la syntaxe :
MAX_BY(value, expr)- value : c’est la colonne dont nous voulons récupérer la valeur ;
- expr : c’est la colonne pour laquelle nous avons calculé la maximum (mais dans une autre ligne du SELECT).
Cela s’éclairci, mais vous avez besoin d’un exemple ?
WITH sales AS (
SELECT 1 AS sale_id, DATE '2024-07-13' AS sale_date, 100 AS amount, 'C001' AS customer_id UNION ALL
SELECT 2 AS sale_id, DATE '2024-07-13' AS sale_date, 200 AS amount, 'C002' AS customer_id UNION ALL
SELECT 3 AS sale_id, DATE '2024-07-13' AS sale_date, 150 AS amount, 'C003' AS customer_id UNION ALL
SELECT 4 AS sale_id, DATE '2024-07-14' AS sale_date, 250 AS amount, 'C004' AS customer_id UNION ALL
SELECT 5 AS sale_id, DATE '2024-07-14' AS sale_date, 300 AS amount, 'C005' AS customer_id UNION ALL
SELECT 6 AS sale_id, DATE '2024-07-14' AS sale_date, 220 AS amount, 'C006' AS customer_id UNION ALL
SELECT 7 AS sale_id, DATE '2024-07-15' AS sale_date, 180 AS amount, 'C007' AS customer_id UNION ALL
SELECT 8 AS sale_id, DATE '2024-07-15' AS sale_date, 260 AS amount, 'C008' AS customer_id UNION ALL
SELECT 9 AS sale_id, DATE '2024-07-15' AS sale_date, 240 AS amount, 'C009' AS customer_id UNION ALL
SELECT 10 AS sale_id, DATE '2024-07-15' AS sale_date, 280 AS amount, 'C010' AS customer_id
)
SELECT
sale_date,
MAX(amount) AS max_sale_amount,
MAX_BY(customer_id, amount) AS customer_with_max_sale
FROM sales
GROUP BY sale_date
ORDER BY sale_date;Voici le résultat :
sale_date | max_sale_amount | customer_with_max_sale |
2024-07-13 | 200 | C002 |
2024-07-14 | 300 | C005 |
2024-07-15 | 280 | C010 |
Pour expliquer tout ce qui vient de se passer :
Dans les données nous avons trois jours de vente, du 13/07/2024 au 15/07/2024 avec pour chaque ligne une vente et son montant ainsi que le client.
L’attendu était d’avoir pour chacun de ces jours le montant maximale d’une vente et le client ayant fait l’achat.
Le premier champ était donc le jour, la deuxième le maximum du montant (il faut donc penser à ajouter le premier champ dans un GROUP BY).
Le dernier champ est l’utilisation de la fonction MAX_BY() qui va retrouver le client associé au montant de l’achat maximum.
Merci de votre lecture !
