SQL 3 : Doublon complexe

Version imprimable

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:

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:

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:

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:

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:

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 :roll:

Code:

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:

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

mais ça :

Code:

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

Merci d'avance

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:

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)

03/11/2010, 08h21
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.

Citation:
Envoyé par 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:

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.

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

Code:

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 :/

03/11/2010, 10h30
soazig

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