Formations — 1 037 leçons en français

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

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.

Lire l'explication détaillée et faire le quiz →

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.