Utilisation du pivot : problème de requête "complexe"

[Kropernic]

Bonjour à tous,

Je viens solliciter votre aide quant à l'utilisation du pivot dans une requête sql.

La base de donnée sur laquelle je désire faire une requête est une base de données de ventes d'une chaine de 15 magasins.

Voici une requête avec laquelle j'ai testé l'utilisation de l'opérateur (est-ce le bon terme?) pivot :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT sum(a.ca),[009],[011],[012],[015],[017],[020],[022],[024],[026],[029],[042],[043],[050],[054],[056]
 
FROM (SELECT siege, qt
FROM tb2010
WHERE date = '20100102') AS sourceTable
 
pivot
 
(sum(qt)  FOR siege IN ([009],[011],[012],[015],[017],[020],[022],[024],[026],[029],[042],[043],[050],[054],[056])) AS pivotTable,
 
tb2010 AS a
 
WHERE date = '20100102'
 
GROUP BY [009],[011],[012],[015],[017],[020],[022],[024],[026],[029],[042],[043],[050],[054],[056]

Cette requête fonctionne fort bien et dans le cas présent me donne comme résultat :
- dans la première colonne, le chiffre d'affaire global pour la journée du 2 janvier 2010
- dans les 15 colonnes suivantes, la quantité d'articles vendus par magasin (il y en a donc 15) pour la même journée. Les noms des 15 dernières colonnes peuvent paraître étranges mais ce sont les identifiants donnés aux différents magasins.

Partant de cela, j'ai voulu étendre la requête pour coller avec la demande qui m'avait été faite et voici la requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT	gib, ray, substring(eanart,1,12) AS ean, pu, sum(qt) AS 'qt', sum(ca) AS 'ca', sum(rab) AS 'rab', 
		[009],[011],[012],[015],[017],[020],[022],[024],[026],[029],[042],[043],[050],[054],[056]
FROM
		(SELECT	siege, qt
		FROM	tb2010
		WHERE	date >= '20100201' AND date <= '20100630' 
				AND gib IN ('525', '527') 
				AND ray LIKE '4%' 
		) AS sourceTable
pivot	(
		sum(qt) FOR siege IN 
		([009],[011],[012],[015],[017],[020],[022],[024],[026],[029],[042],[043],[050],[054],[056])
		) AS pivotTable,
tb2010
 
WHERE	date >= '20100201' AND date <= '20100630' 
	AND gib IN ('525', '527') 
	AND ray LIKE '4%' 
 
GROUP BY gib, ray, substring(eanart,1,12), pu, 
		[009],[011],[012],[015],[017],[020],[022],[024],[026],[029],[042],[043],[050],[054],[056]
 
ORDER BY ca DESC

Celle-ci est sensé me donner le code gib (données propres à la gestion interne), le numéro de rayon, le code ean de l'article, le prix unitaire, le nombre d'articles vendus, la somme de chiffre d'affaire brut et la somme des rabais par article. A cela, s'ajoute 15 colonnes qui sont sensées contenir le nombre d'articles vendus par magasin.

Malheureusement pour moi, cela ne fonctionne pas et j'avoue volontiers que je patauge joyeusement dans l'utilisation du pivot.

Quelqu'un de compétent pourrait-il m'aider dans la réalisation de cette requête ?

J'espère avoir été clair :-/ . Si ce n'est pas le cas, n'hésitez pas me demander des compléments d'informations.

Merci d'avance.

Griftou.

[aieeeuuuuu]

Envoyé par griftou

Malheureusement pour moi, cela ne fonctionne pas
Griftou.

C'est à dire ? tu as une erreur ? les résultats ne sont pas ceux attendus ?

Est-ce que tu pourrais poster La DDL de ta table, et un jeu d'essai sous forme de INSERT INTO (tu peux faire ca facilement en utilisant l'outil de génération de script, et lui précisant de scripter les données...)

edit : au besoin, remplace les données par des données bidons (enfin pas trop bidons quand même ) et réduit le jeu si tu en as beaucoup !

[Kropernic]

Bonjour,

Merci pour cette réponse. Il est vrai que j'aurais du préciser le "ça ne fonctionne pas". En fait, la requête s'exécute mais elle dure un temps beaucoup trop long (j'ai laissé tourné 15 minutes et je l'ai interrompue). Ce n'est donc pas utilisable dans mon application en l'état actuel (quand bien même les données renvoyées seraient correctes).

Pour ce qui est de ta demande je cite :

Est-ce que tu pourrais poster La DDL de ta table, et un jeu d'essai sous forme de INSERT INTO (tu peux faire ca facilement en utilisant l'outil de génération de script, et lui précisant de scripter les données...)

Je veux bien fournir cela mais j'avoue ne pas savoir comment faire. Perso, sql-server se résume à créer des db avec des tables de dedans, y mettre des clés primaires et qqes liaisons... Ah non, une fois j'ai réussi à créer un trigger

Bref, je suis très loin d'être un DBA. Je ne suis qu'un programmeur qui doit se débrouiller pour ses DB car sa boîte n'engagera jamais de DBA et que la formation que je réclame n'a pas l'air d'être au goût des personnes qui s'occupent du budget ^^.

En conclusion, je peux fournir cela mais il va falloir me dire comment faire.

[aieeeuuuuu]

pour générer les scripts :

clique droit sur ta bdd
tasks->generate scripts.

Dans les options, mets "script data" à true, ensuite sélectionnes ta table dans les étapes suivantes...

Cela va te générer un script du genre

Code sql :

1
2
3
4
 
CREATE TABLE tb2010 ...
--puis
INSERT INTO tb2010 VALUES (...)

Si la deuxième partie est trop importante, ne prend qu'un échantillon(10 ou 20 lignes, mais garde des valeurs représentatives, notamment des "sieges" différents, ...)

Si tu ne veux pas diffuser tes vraies données, tu peux les modifier !

[Kropernic]

Après moultes tentatives pour générer un fichier de taille raisonnable, voici le résultat en pièce jointe.

Attention, j'ai changé l'extension du fichier en .zip car sinon, le fichier était trop grand pour le forum. Donc pensez à renommer le fichier .sql.

Mais même en .zip, il devrait pouvoir s'ouvrir dans notepad ou autre soft du genre.

[aieeeuuuuu]

Re,

Est-ce que ceci vous donne ce que vous voulez ?

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
SELECT *
FROM(
SELECT
	gib,
	ray,
	substring(eanart,1,12) AS ean,
	pu,
	qt,
	SUM(ca) AS SommeCA,
	SUM(rab) AS SommeRab,
	siege
FROM tb2010	
GROUP BY 
	gib,
	ray,
	substring(eanart,1,12),
	pu,
	siege,
	qt
) AS Src
PIVOT(
	SUM(qt) 
	FOR siege IN ([009],[011],[012],[015],[017],[020],[022],[024],[026],[029],[042],[043],[050],[054],[056])
) AS pivotTable		
ORDER BY 
	gib, 
	ray,
	ean,
	pu,
	SommeCA,
	SommeRab

[Kropernic]

Bonjour,

Merci pour votre réponse.

Cependant, il semblerait que ce ne soit pas encore ça.

020 0459 541481000074 79,00 -79,00 -39,50 NULL NULL NULL NULL NULL NULL NULL NULL NULL -1,00 NULL NULL NULL NULL NULL
020 0459 541481000074 79,00 237,00 118,50 NULL NULL NULL NULL NULL NULL NULL NULL NULL 1,00 NULL NULL NULL NULL NULL
020 0459 541481000084 79,00 79,00 39,50 NULL NULL NULL NULL NULL NULL NULL NULL NULL 1,00 NULL NULL NULL NULL NULL

Ci-dessus, 3 lignes du résultat . On peut voir qu'il s'agit du même article grace au code EAN qui est identique.

Première chose, ces 3 lignes n'auraient du en faire qu'une en fait. L'idée étant de regrouper les données par article.

Cela mis à part, il semblerait qu'il y ait des erreurs dans le pivot lui même car dans la colonne SommeCA (la 5e), on peut y voir un montant correspondant à une quantité égale à 3 articles (pour la 2e ligne) alors que dans le pivot, il n'y a qu'un seul article au magasin 029.

Sinon, et je me doute que j'en demande peut-être beaucoup, dans l'idéal, ce que je cherche à faire, ce serait donc de regrouper les records par article (et donc par EAN) et ensuite par prix unitaire car il arrive que des codes barres génériques soient utilisés lorsque l'original est endommagé. Et d'avoir, en plus de la répartition des quantités entre les magasins grâce au pivot, la somme globale des quantités (toujours par article).

Bref, je vais travailler à partir de votre requête car elle a l'avantage de produire un résultat comparé à la mienne. Peut-être arriverai-je à un résultat à force de tâtonnement.

Encore merci,

Griftou.

[Kropernic]

Bon... Après discussion avec le "client", cette idée est en quelque sorte abandonnée.

Le pivot par quantité ne se fera plus que sur les articles sélectionné dans la liste des résultats. Ce qui simplifie grandement les choses.

Encore un tout grand merci pour votre aide.

Griftou.

[aieeeuuuuu]

Une dernière chose, pour ce qui est de vos temps de réponses très longs...

ceci n'a rien d'étonnant, vous avez principalement deux gros défauts dans votre table :
1/ beaucoup de colonnes n'ont pas le types adéquat :
- char(8) pour la date
- char(n) pour des nombres
...
Ceci nuit aux performances, car les différentes opérations (jointure, tri, ...) sont d'autant plus longues à effectuer.

2/ vous semblez n'avoir aucun index.
Je vous conseille de regarder les ressources disponibles sur le net, afin d'apprendre à les placer. Ceci peut améliorer vos temps de réponses dans des proportions énormes.

Bon courage

[Kropernic]

Bonjour,

Je suis bien conscient que la base de données est loin d'être optimal.

Comme dit précédemment, je ne suis qu'un programmeur et en aucun cas un administrateur de base de données. Mes connaissances dans ce domaine sont assez limitées.

Le choix des types de données s'est donc fait en fonction de ce qui me faciliterait la vie au niveau de la programmation. Et je pense que ce n'est clairement pas la bonne méthode mais à défaut d'une autre, elle reste pour l'instant la meilleure à ma disposition.

Bien à vous,

Griftou.

[aieeeuuuuu]

Je suis conscient que le changement des types des colonnes sur une BDD déjà en production peut avoir des conséquences sur les applications...

En revanche, placer des index ne peut en aucun cas impacter les applications, ni même le résultats des requêtes (si ce n'est éventuellement l'ordre des résultats en l'absence de clause ORDER BY explicite...). Ceci ne peut qu'améliorer les performances.

[Kropernic]

Pouvez-vous me recommander une documentation à ce sujet ?

J'ai bien tenté un jour d'en ajouter mais il avait tellement dont j'ignorais tout que dans le doute, j'ai préféré ne rien faire :-/

[aieeeuuuuu]

Vous avez déjà cet article de SQLPro qui fait un tour d'horizon.

Vous avez également cet article de ElSuket, qui vous explique comment SQL Server peut vous indiquer les index manquants.