Requête avec TOP(n) [Résolu]

[jbdfb]

Bonjour,

je suis nouveau sur le forum mais ce forum est ma "bible" quand j'ai un problème SQL. D'habitude, je recherche si mon problème a déjà été traité et souvent je trouve et je ne pose pas de question. Cette fois, je n'ai pas trouvé de réponse dans les sujets déjà ouvert...

Sous SQLServer 2005, j'ai une table des ventes structurée comme suit (je simplifie)

Magasin
article
Montant
date de vente

Cette table stocke les ventes de tous les magasins pour tous les articles.

Si je cherche les 10 articles les plus vendus pour un magasin donné (par exemple le magasin de Bordeaux), je fais la requete :

Code :

1
2
3
4
5
SELECT top(10) WITH ties article, count(*) AS nombre, sum(montant)
FROM table_des_ventes
WHERE magasin='BORDEAUX'
GROUP BY article
ORDER BY nombre DESC;

Ca fonctionne bien.

Maintenant je voudrais généraliser cette requête pour tous les magasins, c'est à dire avoir pour chaque magasin la liste des 10 articles les plus vendus (mais en ne faisant qu'une seule requête). Bien sûr, si c'est utile, je précise qu'il existe une table des magasins qui donne la liste exhaustive des magasins.

j'ai essayé :

Code :

1
2
3
4
SELECT magasin, top(10) WITH ties article, count(*) AS nombre, sum(montant)
FROM table_des_ventes
GROUP BY magasin, article
ORDER BY nombre DESC;

mais ça ne passe pas au niveau de la syntaxe. J'ai essayé également avec des sous-requête mais sans succès non plus.

Quelqu'un a une idée du comment faire ?
Merci.

[aieeeuuuuu]

Bonjour

vous pouvez faire comme ceci :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT 
    M.Nom AS Magasin,
    A.article,
    A.Nombre,
FROM
    Magasin
CROSS APPLY (
    SELECT TOP(10) WITH TIES 
        article, 
        COUNT(*) AS Nombre
    FROM table_des_ventes T
    WHERE T.idMagasin = M.idMagasin
    ORDER BY Nombre DESC
)T

[jbdfb]

Merci pour la réponse mais la syntaxe ne doit pas être bonne (il doit manquer un group by et je ne vois pas la table A). J'ai rectifié en

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
    M.Nom AS Magasin,
    T.article,
    T.Nombre,
FROM
    Magasin M
CROSS APPLY (
    SELECT TOP(10) WITH TIES 
        article, 
        COUNT(*) AS Nombre
    FROM table_des_ventes T
    WHERE T.idMagasin = M.idMagasin
    GROUP BY article
    ORDER BY Nombre DESC
)T

mais ca ne fonctionne pas non plus...

[elsuket]

Il manque le GROUP BY dans le CROSS APPLY, mais l'idée reste bien vue :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT		M.magasin
		, S.article
		, S.nombre
FROM		T AS M
CROSS APPLY	(
			SELECT	TOP(10) WITH TIES T.article
				, COUNT(*) AS Nombre
			FROM	T
			WHERE	T.Magasin = M.Magasin
			GROUP	BY T.article
			ORDER	BY Nombre DESC
		) AS S

@++

[jbdfb]

Super, ça fonctionne avec la syntaxe suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
M.Nom AS Magasin,
T.article,
T.Nombre,
FROM
Magasin M
CROSS APPLY (
SELECT TOP(10) WITH TIES 
article, 
COUNT(*) AS Nombre
FROM table_des_ventes Tdv
WHERE Tdv.idMagasin = M.idMagasin
GROUP BY article
ORDER BY Nombre DESC
)T

Merci de votre aide !

[elsuket]

On peut aussi écrire, surtout si on veut le rang :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
;WITH
	CTE AS
	(
		SELECT	magasin
			, article
			, COUNT(*) AS quantite
			, SUM(montant) AS montant_total
			, ROW_NUMBER() OVER(PARTITION BY magasin ORDER BY COUNT(*) DESC) AS rang
		FROM	dbo.table_des_ventes
		GROUP	BY magasin, article
	)
SELECT	magasin
	, article
	, quantite
	, montant_total
FROM	CTE
WHERE	rang <= 10

@++

[aieeeuuuuu]

Envoyé par elsuket

Il manque le GROUP BY dans le CROSS APPLY,

Ha oui

en effet sans le GROUP BY, ça doit marcher beaucoup moins bien

[jbdfb]

Envoyé par elsuket

On peut aussi écrire, surtout si on veut le rang :

Code :

@++

Ca marche aussi en effet et c'est plus performant en temps de réponse. Par contre, avec le' rang <=10', on perd la notion d'exaequo il me semble (le 'WITH TIES') ?

[elsuket]

Héhé non : si deux articles ont été vendus autant de fois dans la même magasin, ils auront le même rang

En revanche le rang suivant est sauté : mettons que les articles A et B aient été vendus autant de fois et que ce sont ces deux articles qui ont enregistré le plus grand nombre de ventes : dans ce cas ils prendront tous les deux le rang 1.
L'article qui a le nombre de ventes juste inférieur prendra le rang 3 (il n'y a pas de rang 2).
Comme on filtre sur rang <= 10, on obtient le même "effet" que le WITH TIES de TOP

@++

[aieeeuuuuu]

hmmm

pour avoir ce comportement, il faudrait me semble-t-il utiliser RANK en lieu et place de ROW_NUMBER...

[aieeeuuuuu]

Envoyé par jbdfb

c'est plus performant en temps de réponse.

Pour avoir de bons temps de réponse, il faudrait créer une vue indexée comme suit :

Code SQL :

1
2
3
4
5
6
7
8
9
10
 
CREATE VIEW V_Vente
WITH SCHEMABINDING
AS
SELECT
    idMagasin,
    article, 
    COUNT_BIG(*) AS Nombre
FROM dbo.table_des_ventes 
GROUP BY idmagasin, article

Avec l'index sur (idMagasin, article) :

[elsuket]

Envoyé par aieeeuuuuu

pour avoir ce comportement, il faudrait me semble-t-il utiliser RANK en lieu et place de ROW_NUMBER...

Holàlà le boulet ... fallait vraiment que je sorte du boulot ...
Donc effectivement il faut absolument remplacer ROW_NUMBER() par RANK().

L'idée de la vue indexée est excellente, il est clair que la requête devrait s'exécuter en un temps record ensuite
Dommage que vous n'ayez pas donné le code de création de l'index cluster et de la requête finale

@++

[jbdfb]

Envoyé par elsuket

Holàlà le boulet ... fallait vraiment que je sorte du boulot ...
Donc effectivement il faut absolument remplacer ROW_NUMBER() par RANK().

L'idée de la vue indexée est excellente, il est clair que la requête devrait s'exécuter en un temps record ensuite
Dommage que vous n'ayez pas donné le code de création de l'index cluster et de la requête finale

@++

Je confirme, c'est bien RANK qu'il faut utiliser. Merci !

Pour la vue indexée, je vais essayer mais j'ai considérablement simplifié ma table et créer cette vue n'est pas si simple pour moi (déjà, il faut que je trouve comment on fait ).

[aieeeuuuuu]

Envoyé par elsuket

Dommage que vous n'ayez pas donné le code de création de l'index cluster et de la requête finale

@++

J'ai failli mettre le code de l'index.

Mais comme je ne suis pas sûr du nom des colonnes, et qu'on est vendredi et que le week end approche... je pense que j'étais parti pour mettre plus de boulettes que de bon sens, je me suis donc abstenu
mais ça doit donner quelque chose comme

Code SQL :

1
2
3
 
CREATE UNIQUE CLUSTERED INDEX UCIX_V_Vente_magasinArticle
ON V_Vente(IdMagasin, Article)

Pour la requête, il n'est pas nécessaire de la modifier, elle devrait utiliser l'index cluster sans qu'on ait besoin de le spécifier...

[aieeeuuuuu]

Envoyé par jbdfb

Pour la vue indexée, je vais essayer mais j'ai considérablement simplifié ma table et créer cette vue n'est pas si simple pour moi (déjà, il faut que je trouve comment on fait ).

Vous avez donc modifié les requêtes pour les tester...

Grosso modo, il vous faut donc juste reprendre la sous requête du cross apply, ajouter la colonne idMagasin dans le select et dans le group by.

Enfin, pour pouvoir indexer cette vue, il faut qu'elle soit liée au schéma (WITH SCHEMABINDING ). Pour cela, il vous faut spécifier le schéma dans lequel se trouve vos tables, par defaut, il s'agit de dbo : FROM dbo.MaTableil faut également utiliser COUNT_BIG à la place de COUNT.


Si vous avez des soucis, n'hésitez pas à poster la structure de vos vraies tables...

[jbdfb]

Envoyé par aieeeuuuuu

Vous avez donc modifié les requêtes pour les tester...

Grosso modo, il vous faut donc juste reprendre la sous requête du cross apply, ajouter la colonne idMagasin dans le select et dans le group by.

Enfin, pour pouvoir indexer cette vue, il faut qu'elle soit liée au schéma (WITH SCHEMABINDING ). Pour cela, il vous faut spécifier le schéma dans lequel se trouve vos tables, par defaut, il s'agit de dbo : FROM dbo.MaTableil faut également utiliser COUNT_BIG à la place de COUNT.


Si vous avez des soucis, n'hésitez pas à poster la structure de vos vraies tables...

Je vais tester. Un très grand merci !