Accueil › SQL & bases de données › ROW_NUMBER, RANK et le top N par groupe
ROW_NUMBER, RANK et le top N par groupe
Fonctions de fenêtrageavance
Quatre fonctions de classement, et un motif qui revient sans cesse.
SELECT nom, categorie, prix,
row_number() OVER (PARTITION BY categorie ORDER BY prix DESC) AS numero,
rank() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang,
dense_rank() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang_dense
FROM produit;
-- nom | categorie | prix | numero | rang | rang_dense
-- ----------+-----------+------+--------+------+------------
-- Clavier A| clavier | 100 | 1 | 1 | 1
-- Clavier B| clavier | 100 | 2 | 1 | 1
-- Clavier C| clavier | 90 | 3 | 3 | 2
row_number(): 1, 2, 3 — jamais d'ex æquo.rank(): 1, 1, 3 — ex æquo, puis trou.dense_rank(): 1, 1, 2 — ex æquo, sans trou.
Le motif « top N par groupe » est la raison n°1 d'utiliser ces fonctions :
WITH classement AS (
SELECT cmd.*,
row_number() OVER (PARTITION BY client_id ORDER BY creee_le DESC) AS n
FROM commande cmd
)
SELECT * FROM classement WHERE n <= 3;
LIMIT ne sait pas faire cela : il s'applique au résultat global, pas à chaque groupe.
Attention enfin au tri incomplet : si l'ORDER BY de la fenêtre laisse des ex æquo, la numérotation varie d'une exécution à l'autre. Ajoutez une colonne unique en dernier critère.
Les termes à connaître
- row_number()
Numérote les lignes de 1 à N dans chaque partition, sans ex æquo : deux lignes de même valeur reçoivent des numéros différents, dans un ordre non garanti si le tri ne les départage pas.
- rank()
Classement avec ex æquo et trous : deux premiers ex æquo reçoivent 1 et 1, le suivant reçoit 3.
- dense_rank()
Classement avec ex æquo et sans trou : 1, 1, 2. À utiliser quand on veut « les trois meilleures valeurs » plutôt que « les trois premières lignes ».
- ntile(n)
Découpe la partition en n groupes de taille équivalente : quartiles (
ntile(4)), déciles (ntile(10)). Pratique pour segmenter une clientèle.- Top N par groupe
Le motif le plus courant du fenêtrage : numéroter dans chaque partition, puis filtrer sur le numéro depuis un CTE ou une sous-requête.
- percent_rank() / cume_dist()
Position relative entre 0 et 1 :
percent_rank()donne la proportion de lignes strictement inférieures,cume_dist()la proportion inférieure ou égale.
Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.