TOP Vs RANK() OVER [Résolu]

[Erwan1978]

Bonjour, j'aimerais vous soumettre le problème suivant :

Soit la table (c'est un exemple !) clients_contributions m'indiquant pour une instance de client donné la contribution de ce dernier au chiffre d'affaire de la société à une date donnée

Code :

1
2
3
4
5
CREATE TABLE client_contributions
(id INT IDENTITY(1,1),
client_id  INT NOT NULL,
date SMALLDATETIME NOT NULL,
value DECIMAL(6,4) NOT NULL)

Prenons le jeu de test suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
INSERT INTO client_contributions (client_id, date, value) VALUES (101,'20101231',0.197);
INSERT INTO client_contributions (client_id, date, value) VALUES (103,'20101231',0.19);
INSERT INTO client_contributions (client_id, date, value) VALUES (108,'20101231',0.19);
INSERT INTO client_contributions (client_id, date, value) VALUES (107,'20101231',0.123);
INSERT INTO client_contributions (client_id, date, value) VALUES (114,'20101231',0.08);
INSERT INTO client_contributions (client_id, date, value) VALUES (116,'20101231',0.08);
INSERT INTO client_contributions (client_id, date, value) VALUES (112,'20101231',0.052);
INSERT INTO client_contributions (client_id, date, value) VALUES (117,'20101231',0.041);
INSERT INTO client_contributions (client_id, date, value) VALUES (111,'20101231',0.037);
INSERT INTO client_contributions (client_id, date, value) VALUES (122,'20101231',0.01);

La question posée est : "Quelle est la contribution au chiffre d'affaire de la société au 31/12/2010 des 5 premiers clients ?"

Dans mon exemple, la réponse serait 78% (c'est à dire 0.197 + 0.19 + 0.19 + 0.123 + 0.08)

Pour trouver une réponse SQL au problème, mon premier réflexe a été de passer par une fonction de partitionnement :

Code :

1
2
3
4
5
SELECT SUM(value) AS total
FROM (SELECT value, RANK() OVER (PARTITION BY date ORDER BY value DESC) AS ranking
	  FROM client_contributions
	  WHERE date = '20101231') ranked_contributions
WHERE ranked_contributions.ranking <=5

Sauf qu'ici, les deux contributions de 8% (associés aux clients #114 et #116) sont prises en compte ce qui fait qu'on arrive à une contribution cumulée de 86% (on a en fait les 6 premiers clients)...

Il y aurait-il une solution pour résoudre ce genre de problème ?

Merci !

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

[Waldar]

Vous pouvez utiliser row_number au lieu de rank, mais si vous voulez que votre requête soit cohérente d'une exécution sur l'autre il faut mettre plus de critère de tri, enfin si vous changez les colonnes du résultat sur la valeur ça n'a pas d'importance.

[SQLpro]

La question posée est : "Quelle est la contribution au chiffre d'affaire de la société au 31/12/2010 des 5 premiers clients ?"

Lorsque l'on dit cela le fait que dans la réponse il y en ais 5, 6 ou 7 du fait des ex aequo du dernier rang ne pose aucun problème. En effet que feriez vous dans une compétition pour les ex aequo ? Un tirage au sort ????

A +

[Erwan1978]

Vous pouvez utiliser row_number au lieu de rank

Merci Waldar cela répond à la question posée !

Code :

Lorsque l'on dit cela le fait que dans la réponse il y en ais 5, 6 ou 7 du fait des ex aequo du dernier rang ne pose aucun problème. En effet que feriez vous dans une compétition pour les ex aequo ? Un tirage au sort ????

Sur le principe, je te rejoins SQLPro, il n'y a pas lieu de discriminer le 6ème ou le 7ème ex aequo. Néanmoins, on (comprendre : "le marketing" !) veut ici montrer que le CA n'est pas trop dépendant des plus gros clients, on me demande donc de virer du podium les ex aequo du dernier rang...