Réponse

Les fonctions de fenêtrage permettent d'effectuer des calculs sur une "fenêtre" de lignes sans les grouper en lignes distinctes, ce qui est pratique pour les rapports et l'analytique.

ROW_NUMBER() - attribue un numéro unique et séquentiel aux lignes dans chaque partition (segment) de la fenêtre, les triant selon un critère donné. Peut renvoyer des sauts dans la numérotation en cas de valeurs identiques dans ORDER BY.
RANK() - attribue un rang identique aux lignes avec une valeur identique dans ORDER BY, mais saute les numéros suivants (il y aura un trou).
DENSE_RANK() - attribue également un rang identique aux lignes avec une valeur identique, mais les numéros se suivent sans sauts.

Exemple :

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Table :

name	salary
Vasya	10000
Petya	10000
Masha	9000

Résultat :

name	salary	num	rank	dense_rank
Vasya	10000	1	1	1
Petya	10000	2	1	1
Masha	9000	3	3	2

Pièges à éviter :

Un ORDER BY mal choisi peut entraîner un tri incorrect.
(row_number) Si un champ unique n'est pas choisi dans ORDER BY - il n'y a pas de garantie d'ordre stable.
Utilisation sans PARTITION BY lorsque la segmentation est nécessaire entraîne une numérotation incorrecte pour l'ensemble du jeu de lignes.

Question piège

Si PARTITION BY n'est pas spécifié dans la fonction de fenêtre, comment sera effectuée la numérotation des lignes dans ROW_NUMBER() ?

Réponse : L'ensemble des données sera considéré comme une seule partition. Donc, la numérotation se fera de manière continue sur toutes les lignes, sans tenir compte de groupes quelconques.

Exemple :

SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
-- Tous les employés recevront un numéro unique, commençant par 1 sans segmentation par département

Histoire n°1

Dans le rapport BI, on a oublié d'indiquer PARTITION BY par département. Tous les employés de l'entreprise ont reçu une numérotation continue, alors que l'objectif était d'identifier les meilleurs dans chaque département. Cela a conduit à un TOP-N incorrect des employés par département.

Histoire n°2

Le développeur a choisi RANK() au lieu de ROW_NUMBER() pour déterminer le "meilleur" du groupe - mais avec des indicateurs identiques, des numéros identiques ont été attribués, ce qui a provoqué des doublons implicites de leaders dans l'analyse.

Histoire n°3

Lors de l'utilisation de DENSE_RANK(), on a omis qu'il interdit les sauts dans les rangs, ce qui a faussé le rapport sur le nombre de positions "uniques" significatives lors de l'analyse des ventes. Un contrôle sur la logique commerciale a révélé une erreur de répartition des places.

Comment fonctionnent et en quoi diffèrent les fonctions de fenêtrage ROW_NUMBER(), RANK(), DENSE_RANK() lors de la programmation de rapports en SQL ? Quelles sont les pièges à éviter lors de leur utilisation ?

Réponse

Question piège