Bertrand Fabre“Je voudrai un classement avec le top 4 des ventes des produits pour chacun de mes sites sur 3 mois glissant tous les jours.”
“Oui bien sûr. Je planifie cela.”
😱 Je fais comment se classement des tops 4 ?!
Pas de panique, avec SQL et sur la plupart des bases de données (PostgreSQL, Redshift, BigQuery, Snowflake), il est possible de faire cela avec un fonction : RANK().
Cette fonction est une window function, j’en ferai un article de blog prochainement. Il faut surtout savoir que ce genre de fonctions ont une syntaxe spécifique, que nous allons voir ci-dessous.
Les données
Pour commencer, nous allons regarder ensemble à quoi ressemble les données.
WITH cte_sales AS (
SELECT '001' AS site, 'produitA' AS product, 1000 AS turnover UNION ALL
SELECT '001', 'produitB', 1500 UNION ALL
SELECT '001', 'produitC', 1200 UNION ALL
SELECT '002', 'produitA', 800 UNION ALL
SELECT '002', 'produitB', 950 UNION ALL
SELECT '002', 'produitC', 1100 UNION ALL
SELECT '003', 'produitA', 1300 UNION ALL
SELECT '003', 'produitB', 1200 UNION ALL
SELECT '003', 'produitC', 900 UNION ALL
SELECT '004', 'produitA', 1600 UNION ALL
SELECT '004', 'produitB', 1400 UNION ALL
SELECT '004', 'produitC', 1350
)
SELECT *
FROM cte_sales;Ces données représentent le chiffre d’affaires généré par produit et par magasin. Les magasins sont donc représentés par le champ ‘site’, les produits par le champ ‘product’ et le chiffre d’affaires par le champ ‘turnover’. Pour ceux qui visualisent mieux les données dans la sortie BigQuery voici les résultats :
site | product | turnover |
001 | produitB | 1500 |
001 | produitC | 1200 |
001 | produitA | 1000 |
002 | produitC | 1100 |
002 | produitB | 950 |
002 | produitA | 800 |
003 | produitA | 1300 |
003 | produitB | 1300 |
003 | produitC | 900 |
004 | produitA | 1600 |
004 | produitB | 1400 |
004 | produitC | 1350 |
La window function RANK()
Maintenant, ce que nous souhaitons faire c’est ajouter un champ qui représenterait le classement ou rang des meilleurs chiffres d’affaires de produits par site.
Dans notre cas, nous utilisons donc la fonction RANK().
En sortie, nous souhaitons avoir le numéro du site, le produit vendu, son chiffre d’affaires et le classement du CA de ce produit sur chaque site.
Nous allons donc ajouter ces champs dans le SELECT : site, product, turnover.
C’est à ce moment là que nous allons utiliser RANK().
On écrit donc d’abord la fonction RANK() le mot clé OVER puis la définition du fenêtrage voulu avec entre parenthèse PARTITION BY.
C’est lui qui va faire un découpage en groupe des données mais sans qu’on le voit. Il lui donne donc le champ site car on veut un classement par site.
Puis on veut faire un classement avec les meilleurs chiffre d’affaires en premier. On ajoute donc ORDER BY turnover DESC pour un classement décroissant.
On va lui donner le nom ‘rank’ qui me semble parlant.
Voici la requête SQL :
SELECT
site,
product,
turnover,
RANK() OVER (PARTITION BY site ORDER BY turnover DESC) AS rank
FROM cte_sales;J’exécute donc cette requête pour voir le résultat.
site | product | turnover | rank |
001 | produitB | 1500 | 1 |
001 | produitC | 1200 | 2 |
001 | ProduitA | 1000 | 3 |
002 | produitC | 1100 | 1 |
002 | produitB | 950 | 2 |
002 | produitA | 800 | 3 |
003 | produitA | 1300 | 1 |
003 | produitB | 1300 | 1 |
003 | produitC | 900 | 3 |
004 | produitA | 1600 | 1 |
004 | produitB | 1400 | 2 |
004 | produitC | 1350 | 3 |
✅ La sortie est aussi classée selon les paramètres fournis dans la window function avec pour le 1er magasin, le produitB au 1er rang car c’est celui qui a réalisé le chiffre d’affaires le plus important du magasin.
☝️ Il y a une subtilité sur laquelle je souhaite que l’on s’arrête c’est le classement des produits pour le magasin ‘003’.
Il a 2 produits en 1ère position !
En effet, la fonction RANK() gère les ex-aequo.
La produit qui vient juste après ces ex-aequo prendra bien en compte qu’il y a 2 produits avant lui dans le classement et affichera donc la 3ème position.
Merci de votre lecture !
