Classement

SQL - Cet article fait partie d'une série.

Partie 12: Cet article

Partie 15: Ordre d’écriture & d’exécution

📌 Qu’est-ce qu’une fonction de classement ?
#

Ce sont des fonctions analytiques qui attribuent un rang aux lignes selon un ordre, avec ou sans gestion des égalités.

Elles utilisent toutes la même structure :

... OVER (
  PARTITION BY ...
  ORDER BY ...
)

🧮 Les 3 fonctions essentielles
#

ROW_NUMBER(): numérote chaque ligne sans tenir compte des égalités.

SELECT
client_id, commande_id,
ROW_NUMBER() OVER (
    PARTITION BY client_id
    ORDER BY date_commande
) AS rang
FROM commandes;

→ Résultat : 1, 2, 3, ...

RANK(): classe en tenant compte des égalités, avec saut de rangs.

SELECT
produit, montant,
RANK() OVER (
    PARTITION BY produit
    ORDER BY montant DESC
) AS rang
FROM ventes;

→ Si deux lignes sont ex aequo au rang 1, la suivante est au rang 3.

DENSE_RANK(): classe avec égalités mais sans sauter de rangs.

SELECT
produit, montant,
DENSE_RANK() OVER (
    PARTITION BY produit
    ORDER BY montant DESC
) AS rang_dense
FROM ventes;

→ Ex æquo au rang 1 → la ligne suivante est au rang 2.

🔍 Exemple : top N par groupe
#

Extrait les 3 meilleures ventes par produit :

SELECT *
FROM (
  SELECT
    produit, montant,
    ROW_NUMBER() OVER (
      PARTITION BY produit
      ORDER BY montant DESC
    ) AS rn
  FROM ventes
)
WHERE rn <= 3;

🧯 Pièges fréquents
#

⚠️ Piège	✅ Solution
Oublier `ORDER BY` → rangs incohérents	Toujours préciser un ordre explicite
Partir sans `PARTITION BY`	Tous les rangs calculés sur l’ensemble des lignes
Confondre `RANK` vs `DENSE_RANK`	RANK saute des valeurs, DENSE_RANK non
Croire que `RANK()` impose l’unicité	Plusieurs lignes peuvent partager un rang

🏋️‍♂️ Exercices
#

Pour chaque client, numéroter les commandes (ROW_NUMBER).
Pour chaque produit, déterminer les 2 ventes les plus chères.
Comparer RANK() vs DENSE_RANK() sur des montants ex aequo.
Trouver la 1ère commande par client (ROW_NUMBER = 1).

WITH ventes AS (
  SELECT *
  FROM (VALUES
    -- client_id, produit, montant, date_commande
    (1, 'A', 100, DATE '2024-01-01'),
    (1, 'A', 150, DATE '2024-01-05'),
    (1, 'B', 150, DATE '2024-01-10'),
    (2, 'A', 200, DATE '2024-01-03'),
    (2, 'A', 200, DATE '2024-01-04'),  -- ex aequo
    (2, 'B',  80, DATE '2024-01-06')
  ) AS t(client_id, produit, montant, date_commande)
),

classements AS (
  SELECT
    client_id,
    produit,
    montant,
    date_commande,

    -- 1) Numéroter les commandes par client
    ROW_NUMBER() OVER (
      PARTITION BY client_id
      ORDER BY date_commande
    ) AS rn_client,

    -- 2) Top ventes par produit (classement décroissant)
    ROW_NUMBER() OVER (
      PARTITION BY produit
      ORDER BY montant DESC
    ) AS rn_produit,

    -- 3) RANK vs DENSE_RANK sur les montants
    RANK() OVER (
      PARTITION BY produit
      ORDER BY montant DESC
    ) AS r_classique,

    DENSE_RANK() OVER (
      PARTITION BY produit
      ORDER BY montant DESC
    ) AS r_dense
  FROM ventes
)

SELECT
  *,
  -- 4) Première commande par client
  CASE WHEN rn_client = 1 THEN TRUE ELSE FALSE END AS est_premiere_commande,
  
  -- 2) Garder aussi les Top 2 par produit (pour visualisation)
  CASE WHEN rn_produit <= 2 THEN TRUE ELSE FALSE END AS top2_produit

FROM classements
ORDER BY client_id, date_commande;