Requête avec TOP(n)

**jbdfb** · 27/05/2011, 12h28

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 27/05/2011, 12h39

Bonjour

vous pouvez faire comme ceci :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 27/05/2011, 13h18

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 27/05/2011, 13h28

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 27/05/2011, 13h29

Super, ça fonctionne avec la syntaxe suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 27/05/2011, 13h35

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 27/05/2011, 14h39

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** · 27/05/2011, 15h53

Envoyé par elsuket

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

@++

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** · 27/05/2011, 16h14

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** · 27/05/2011, 16h24

hmmm

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

**aieeeuuuuu** · 27/05/2011, 16h32

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 27/05/2011, 16h43

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** · 27/05/2011, 16h48

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** · 27/05/2011, 17h02

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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** · 27/05/2011, 17h10

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** · 27/05/2011, 17h18

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 !

Requête avec TOP(n)

Développement SQL Server

Discussions similaires

Partager

Partager