Discussion :

[slay1611]

Bonjour,

Je travaille actuellement sur une base de données qui traite les données achats d'une entreprise. Pour simplifier la chose mon problème concerne 4 tables que voici :

• Receip
  

  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
  18
  19
  20
  21
  22
  23
  24
  25
  26
  CREATE TABLE `receip` (
   
  	`re_num` int(10) unsigned NOT NULL AUTO_INCREMENT,
  	`re_date` date NOT NULL,
  	`re_qty` double(15,5) NOT NULL,
  	`re_purite` int(10) unsigned NOT NULL,
   
  	PRIMARY KEY (`re_num`),
  	KEY `re_purite` (`re_purite`),
  	CONSTRAINT `receip_ibfk_1` FOREIGN KEY (`re_purite`) REFERENCES `purite` (`pi_num`)
   
  ) ENGINE=InnoDB AUTO_INCREMENT=532679 DEFAULT CHARSET=latin1;
   
  INSERT INTO receip VALUES(null,'2010-01-04',500,1);
  INSERT INTO receip VALUES(null,'2010-01-04',500,1);
  INSERT INTO receip VALUES(null,'2010-01-04',1000,3);
  INSERT INTO receip VALUES(null,'2010-01-04',10,2);
  INSERT INTO receip VALUES(null,'2010-01-05',50,4);
  INSERT INTO receip VALUES(null,'2010-01-05',10000,4);
  INSERT INTO receip VALUES(null,'2010-01-05',5000,3);
  INSERT INTO receip VALUES(null,'2010-01-08',3000,2);
  INSERT INTO receip VALUES(null,'2010-01-08',3000,5);
  INSERT INTO receip VALUES(null,'2010-01-08',3000,4);
  INSERT INTO receip VALUES(null,'2010-01-08',3000,2);
  INSERT INTO receip VALUES(null,'2010-01-10',2500,2);
  INSERT INTO receip VALUES(null,'2010-01-14',50000,1);

• Purite
  

  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
  CREATE TABLE `purite` (
   
  	`pi_num` int(10) unsigned NOT NULL AUTO_INCREMENT,
  	`pi_price` double(16,10) NOT NULL,
  	`pi_item` char(18) NOT NULL,
   
  	PRIMARY KEY (`pi_num`),
  	KEY `pi_item` (`pi_item`),
  	CONSTRAINT `purite_ibfk_1` FOREIGN KEY (`pi_item`) REFERENCES `item` (`it_code`)
   
  ) ENGINE=InnoDB AUTO_INCREMENT=75777 DEFAULT CHARSET=latin1;
   
  INSERT INTO purite VALUES(1,1.52,'A120152');
  INSERT INTO purite VALUES(2,2.52,'A158751');
  INSERT INTO purite VALUES(3,0.00214,'A203542');
  INSERT INTO purite VALUES(4,0.0034,'A203542');
  INSERT INTO purite VALUES(5,9.54,'A205845');

• Item
  

  Code :

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

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  CREATE TABLE `item` (
   
  	`it_code` char(18) NOT NULL,
  	`it_name` char(70) NOT NULL
   
  	PRIMARY KEY (`it_code`)
   
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   
  INSERT INTO item VALUES('A120152','Resistance');
  INSERT INTO item VALUES('A158751','Condensateur');
  INSERT INTO item VALUES('A203542','relais');
  INSERT INTO item VALUES('A205845','Resistance 2');

• Foreca
  

  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
  CREATE TABLE `foreca` (
   
  	`fc_item` char(18) NOT NULL DEFAULT ''
  	`fc_year` year(4) NOT NULL,
  	`fc_qty` int(10) unsigned NOT NULL,
  	PRIMARY KEY (`fc_item`,`fc_geosit`),
  	KEY `foreca_ibfk_2` (`fc_geosit`),
  	CONSTRAINT `foreca_ibfk_1` FOREIGN KEY (`fc_item`) REFERENCES `item` (`it_code`)
   
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   
  INSERT INTO foreca VALUES('A120152','2010',800000);
  INSERT INTO foreca VALUES('A158751','2010',950000);
  INSERT INTO foreca VALUES('A203542','2010',1510000);
  INSERT INTO foreca VALUES('A205845','2010',15000);

Pour faire simple, je veux comparer mes prévisions (qui sont annuelles) à mes réceptions par mois en 2010.

Voila ma requete pour avoir les réception par mois (et qui fonctionne très bien) :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT
       Date_format(re_date, 'M %Y %m') AS interv, 
       SUM(re_qty) AS qty
FROM  
       receip, purite
WHERE  
       ( re_date BETWEEN '2010-01-01' AND '2010-12-31' ) 
       AND re_purite = pi_num 
GROUP  BY interv 
ORDER  BY interv

Le souci est pour intégrer les prévisions car :
- il y a une quantité prévisionnelle par article (item) et par an
- il peut ( et même il y a très souvant) plusieurs réceptions d'un même article dans un mois
Donc si je fais la requête suivante, il va m'ajouter les prévisions pour un même article réceptionné plusieurs fois. J'espère être clair

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
	date_format(re_date,'M %Y %m') as interv,
	sum(re_qty) as qty,
	sum(fc_qty / 12) as forecast
FROM 
	receip,purite,foreca 
WHERE 
	(re_date between '2010-01-01' and '2010-12-31') 
	and re_purite = pi_num 
	and (fc_item = pi_item and year(re_date) = '2010') 
GROUP BY interv 
ORDER BY interv

Ce que je veux c'est que dans les calculs de la requête il ne compte qu'une seule fois la prévision même si l'article apparaît plusieurs fois dans la table receip.

Je ne veux pas ça :

Code :

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

1
2
3
4
 
interv    qty   forecast
--------- ----- ------------
M 2010 01 81560 1147083.3333

mais ça :

Code :

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

1
2
3
4
 
interv    qty   forecast
--------- ----- ------------
M 2010 01 81560  272916.6667

Merci d'avance

[Jihnn]

Il suffit tout simplement de rajouter le mot-clé DISTINCT dans le SUM. Notez aussi deux choses :
1) Plutôt que de diviser chaque colonne par 12 dans le SUM, diviser le total du SUM par 12. Mathématiquement, c'est identique : 1/2 + 2/2 + 3/2 = (1+2+3)/2.
2) Vous utilisez l'ancienne syntaxe des jointures, dépréciée depuis une bonne dizaine d'année. Préférez la nouvelle pour ainsi départager facilement les conditions de jointures et les conditions de sélection.

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 
	date_format(re_date,'M %Y %m') AS interv,
	sum(re_qty) AS qty,
	sum(DISTINCT fc_qty) / 12 AS forecast
FROM 
	receip
INNER JOIN purite
    ON receip.re_purite = purite.pi_num
INNER JOIN foreca
    ON purite.pi_item = foreca.fc_item
WHERE 
	re_date BETWEEN '2010-01-01' AND '2010-12-31'
	AND year(re_date) = '2010'
GROUP BY interv 
ORDER BY interv;

(Notez que vous pouvez aussi donner des alias à vos tables pour raccourcir un peu la longueur de la requête)

[punkoff]

un distinct sur une quantité ?

Si pour une même date il y a 2 quantités identiques ca risque de ne pas marcher il me semble.

[slay1611]

Il suffit tout simplement de rajouter le mot-clé DISTINCT dans le SUM. Notez aussi deux choses :
1) Plutôt que de diviser chaque colonne par 12 dans le SUM, diviser le total du SUM par 12. Mathématiquement, c'est identique : 1/2 + 2/2 + 3/2 = (1+2+3)/2.
2) Vous utilisez l'ancienne syntaxe des jointures, dépréciée depuis une bonne dizaine d'année. Préférez la nouvelle pour ainsi départager facilement les conditions de jointures et les conditions de sélection.

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 
	date_format(re_date,'M %Y %m') AS interv,
	sum(re_qty) AS qty,
	sum(DISTINCT fc_qty) / 12 AS forecast
FROM 
	receip
INNER JOIN purite
    ON receip.re_purite = purite.pi_num
INNER JOIN foreca
    ON purite.pi_item = foreca.fc_item
WHERE 
	re_date BETWEEN '2010-01-01' AND '2010-12-31'
	AND year(re_date) = '2010'
GROUP BY interv 
ORDER BY interv;

(Notez que vous pouvez aussi donner des alias à vos tables pour raccourcir un peu la longueur de la requête)

Merci pour la réponse et pour l'astuce des jointures. Je vais essayer. Mais il me semble que la dernière remarque est judicieuse. Et ça arrive souvent la même quantité à la même date.
Je vous tiens au courant.

[punkoff]

Je n'arrive pas à trouver de solution simple, ceci dit une piste déjà qui retourne le résultat attendu :

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 month(re_date) as interv, sum(re_qty) as qty, max(e.qtyfc)
from receip a
inner join purite b on a.re_purite = b.pi_num
left outer join (select c.interv, sum(d.fc_qty) / 12 as qtyfc
				from foreca d
				inner join (
					select distinct month(re_date) as interv, pi_item
					from receip a
					inner join purite b on a.re_purite = b.pi_num
					where a.re_date between '2010-01-01' AND '2010-12-31') as c on c.pi_item = d.fc_item
				where year(d.fc_year) = 2010
				group by c.interv) AS e on month(re_date) = e.interv
where a.re_date between '2010-01-01' AND '2010-12-31'
group by month(re_date)

Y a peut-être moyen de simplifier tout ca :/

[soazig]

bonjour,
Je voterai comme punkoff pour une ou deux sous requête.
a+
Soazig