Combinaison de SUM et d'AVG, pb avec le GROUP BY qui ne marche pas.

10 avr. 2008, 17:49

J'utilise mySQL 5.0.27.

J'ai une base avec des recettes et je voudrais calculer le cout de revient des recettes.
Pour celà, je prends le prix moyen des ingrédiants utilisés x la quantité et je fais la somme des divers ingrédiants.

Dans la base, j'ai :
- une table avec des prix issus de différents magasins ;
- une table avec les ingrédients qui composent de la recette ;
- une table avec la recette.

Lorsque je fais cette requête, je me retrouve avec une ligne par ingrédiant, donc 10 ingrédiants me donnent 10 lignes.

Code : Tout sélectionner

SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(RC.quantite) * AVG(MP.prix / MP.quantite_article) 
FROM magasin_produit MP
INNER JOIN recette_composition RC
ON MP.id_produit = RC.id_produit
INNER JOIN recette RR 
ON RC.id_recette = RR.id_recette
GROUP BY MP.id_produit, RC.id_recette
ORDER BY RR.label_recette

La structure des 3 tables concernées :

Code : Tout sélectionner

-- Structure de la table `magasin_produit`
-- 

CREATE TABLE `magasin_produit` (
  `id_produit` int(11) NOT NULL,
  `id_magasin` int(11) NOT NULL,
  `prix` float(5,2) NOT NULL,
  `quantite_article` float(6,2) NOT NULL,
  `date_maj` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id_produit`,`id_magasin`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

-- 
-- Structure de la table `recette`
-- 

CREATE TABLE `recette` (
  `id_recette` int(10) unsigned NOT NULL auto_increment,
  `type_recette` tinyint(3) unsigned NOT NULL default '0',
  `nb_part` tinyint(4) NOT NULL,
  `label_recette` varchar(40) NOT NULL,
  `origine_recette` varchar(200) NOT NULL,
  `resume_recette` text NOT NULL,
  `url_origine_recette` varchar(200) NOT NULL,
  `description_recette` text,
  `visible_prive` char(1) NOT NULL default 'P',
  PRIMARY KEY  (`id_recette`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

-- 
-- Structure de la table `recette_composition`
-- 

CREATE TABLE `recette_composition` (
  `id_produit` tinyint(4) NOT NULL default '0',
  `id_recette` int(11) NOT NULL,
  `quantite` float(6,3) NOT NULL default '0.000',
  KEY `id_part` (`id_produit`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Merci du coup de main.

11 avr. 2008, 12:47

Si tu ne GROUP pas BY id_produit tu devrais logiquement avoir le total par recette, n'est-ce pas le cas ?

En élaguant la requête pour profiter des raccourcis de SQL, ça nous donne

Code : Tout sélectionner

  SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(RC.quantite) * AVG(MP.prix / MP.quantite_article)  AS prix_moyen
    FROM magasin_produit     MP
    JOIN recette_composition RC USING (id_produit)
    JOIN recette             RR USING (id_recette)
GROUP BY RC.id_recette
ORDER BY RR.label_recette

Les espaces partout c'est cadeaux, ça fait plaisir.

11 avr. 2008, 13:07

Merci.

L'affichage est correct, j'ai bien une ligne par recette.
Je n'ai plus qu'à vérifier que les valeurs sont bonnes.

11 avr. 2008, 13:15

En effet, en y réfléchissant, la façon de calculer le prix moyen ne sera pas la bonne. Est-ce que tu cherches le coût moyen d'une recette en achetant les produits dans différents magasins ou le coût moyen de chaque recette en achetant tout dans le même magasin ? D'ailleurs... je me demande si ça ne revient pas au même

Note que je doute que l'algorithme plus haut donne l'un ou l'autre, puisqu'il multiple le total des quantités par le prix moyen de tous les ingrédients dans tous les magasins. En fait il faudrait calculer la somme de la multiplication du prix moyen par la quantité, ce qui donnerait

Code : Tout sélectionner

  SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(RC.quantite * AVG(MP.prix / MP.quantite_article)) AS prix_moyen
    FROM magasin_produit     MP
    JOIN recette_composition RC USING (id_produit)
    JOIN recette             RR USING (id_recette)
GROUP BY RC.id_recette
ORDER BY RR.label_recette

Ou, en cas d'erreur ci-dessus

Code : Tout sélectionner

  SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(PP.quantite * PP.prix_moyen_produit) AS prix_moyen_recette
    FROM (
            SELECT RC.id_recette, RC.quantite, AVG(MP.prix / MP.quantite_article) AS prix_moyen_produit
              FROM magasin_produit     MP
              JOIN recette_composition RC USING (id_produit)
          GROUP BY RC.id_recette, RC.id_produit
         ) AS PP
    JOIN recette RR USING (id_recette)
GROUP BY RR.id_recette
ORDER BY RR.label_recette

D'ailleurs, dans cette requête on devrait pouvoir calculer le coût de chaque produit (prix moyen * quantite) dans la table dérivée

Code : Tout sélectionner

  SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM(PP.cout_produit) AS prix_moyen_recette
    FROM (
            SELECT RC.id_recette, RC.quantite * AVG(MP.prix / MP.quantite_article) AS cout_produit
              FROM magasin_produit     MP
              JOIN recette_composition RC USING (id_produit)
          GROUP BY RC.id_recette, RC.id_produit
         ) AS PP
    JOIN recette RR USING (id_recette)
GROUP BY RR.id_recette
ORDER BY RR.label_recette

...et c'est à ce moment là que je m'aperçois que cette requête fait la même chose que la première du post, si ce n'est qu'elle fonctionne sous PostgreSQL et qu'elle pourrait être plus rapide sur de grosses recettes (à vérifier).

11 avr. 2008, 13:29

J'avais un doute d'ou ma réponse au dessus.
Je viens de vérifier.
Si j'ai un même produit dans 3 magasins différents, ca me fait le cumul.

En faisant la 2eme requête, que j'avais déjà testé :

SELECT RR.id_recette, RR.label_recette, RR.nb_part, SUM( RC.quantite * AVG( MP.prix / MP.quantite_article ) ) AS prix_moyen
FROM recettes_magasins_prix MP
JOIN recettes_composition RC
USING ( id_produit )
JOIN recettes_recettes RR
USING ( id_recette )
GROUP BY RC.id_recette
ORDER BY RR.label_recette

j'ai comme erreur : Invalid use of group function

PS : j'ai profité du passage en PDO pour changer les noms de mes tables.

11 avr. 2008, 13:36

Je m'aperçois que les requêtes utilisent MP.quantite alors que ça devrait être RC.quantite. J'imagine que MP.quantite correspond au stock de chaque magasin.

En reprenant tout depuis le début :

Prix moyen de chaque produit

Code : Tout sélectionner

  SELECT id_produit, AVG(prix) AS prix
    FROM recettes_magasins_prix
GROUP BY id_produit
ORDER BY NULL

Coût moyen de chaque produit pour chaque recette

Code : Tout sélectionner

SELECT RC.id_recette, RC.id_produit, RC.quantite * PP.prix AS cout_produit
  FROM (
          SELECT id_produit, AVG(prix) AS prix
            FROM recettes_magasins_prix
        GROUP BY id_produit
        ORDER BY NULL
       ) AS PP
  JOIN recettes_composition RC USING (id_produit)

...la même chose en faisant la somme des coûts de tous les produits pour chaque recette

Code : Tout sélectionner

  SELECT RC.id_recette, SUM(RC.quantite * PP.prix) AS cout_recette
    FROM (
            SELECT id_produit, AVG(prix) AS prix
              FROM recettes_magasins_prix
          GROUP BY id_produit
          ORDER BY NULL
         ) AS PP
    JOIN recettes_composition RC USING (id_produit)
GROUP BY RC.id_recette

Normalement, à partir de là tu devrais pouvoir joindre la table des recettes pour avoir leur nom. La requête n'est pas optimale si les recettes n'utilisent pas la majorité des produits disponibles dans les magasins, mais sinon elle devrait être correcte.

11 avr. 2008, 16:43

Je m'aperçois que les requêtes utilisent MP.quantite alors que ça devrait être RC.quantite. J'imagine que MP.quantite correspond au stock de chaque magasin.

MP.quantité correspond à la quantité pour un magasin donné.
Ex : j'achète une boite de Maïzena(id = 2) dans le magasin 1 qui fait 200g et une autre dans le magasin 2 qui fait 400g, dans la table ca donne :
id magasin quantite prix
2 1 0.200 1€50
2 2 0.400 2€50
avec les requêtes, celà devrait donner :
prix moyen = ((1,5/0,2) + (2,5/0,4))/2. (ca me donne le prix moyen au kg, plus facile après pour comparer les prix en magasin).
dans la recette, j'utilise 50g de Maïzena, donc coût de l'ingrédiant dans la recette = prix moyen * 0,05.
Ensuite, je prend la somme de l'ensemble des ingrédiants pour connaître le cout moyen d'une recette.

Le but du jeux, c'est ensuite de pouvoir comparer entre une recette maison et une recette industrielle.

Là, on a la première requête qui est fausse.
Je vais regarder celà tranquillement ce soir pour corriger.
Mais à priori, je ne peux pas échapper aux sous-requêtes.

EDIT
Bon, je viens de regarder, ca donne :

Code : Tout sélectionner

SELECT RC.id_recette, SUM( RC.quantite * PP.prix ) AS cout_recette
FROM (

SELECT MP.id_produit, AVG( MP.prix / MP.quantite_article ) AS prix
FROM recettes_magasins_prix MP
GROUP BY MP.id_produit
ORDER BY NULL 
) AS PP
JOIN recettes_composition RC
USING ( id_produit ) 
GROUP BY RC.id_recette
LIMIT 0 , 30

A vu de nez, ca semble cohérent.

Merci.

Combinaison de SUM et d'AVG, pb avec le GROUP BY qui ne marche pas.

Qui est en ligne