Fonctions de fenêtre : classements et calculs par groupe

Une fonction de fenêtre est une fonction qui opère sur un "groupe de lignes" (la fenêtre) mais — contrairement à GROUP BY — ne réduit pas le nombre de rows : une valeur calculée sur la fenêtre est attachée à chaque ligne du résultat final.

C'est le bon outil pour les classements, la numérotation progressive, les pourcentages sur un total de groupe, et bien d'autres opérations délicates ou impossible avec GROUP BY.

La syntaxe : function() OVER (…)

ROW_NUMBER() OVER (PARTITION BY colonna ORDER BY colonna2)

Composants :

• PARTITION BY — définit les "groupes" (comme GROUP BY, mais les lignes rester séparés);
• ORDER BY à l'intérieur de OVER(…) — définit l'ordre dans le partition.

OVER () (parenthèses vides) signifie « la table entière comme une seule fenêtre ». Utile pour les calculs comme « pourcentage du total ».

Fonctions de classement

ROW_NUMBER() OVER (PARTITION BY p ORDER BY o)
RANK()       OVER (PARTITION BY p ORDER BY o)
DENSE_RANK() OVER (PARTITION BY p ORDER BY o)

Différences (avec les valeurs 100, 100, 90, 80) :

Exemple : classement des produits par catégorie

SELECT name,
       category_id,
       price,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM   products
ORDER BY category_id, rank;

Pour chaque category_id le RANK repart de 1 et commande par décroissant prix : vous obtenez le classement des produits au sein de chaque catégorie, en une seule tir.

Numérotation progressive

-- Progressive order number for each customer:
SELECT id,
       customer_id,
       ordered_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS row_num
FROM   orders
ORDER BY customer_id, row_num;

La première commande de chaque client aura le row_num = 1, la seconde le 2, etc.

Essayez-le

Exercice de révision