Discussion :

[Saten]

Bonjour à tous,

voilà j'aimerais inclure une analyse statistique de ma base de données contenant des produits, mais je ne sais pas comment utiliser mes agrégations là dedans...

Je voudrais récupérer :

• le produit ayant la plus forte hausse de prix
• le produit ayant la plus forte chute de prix
• le produit le plus modifié

et ma base se construit comme cela:

TABLES:

• Produits (référence, désignation)
• prodpxven(reference, date, prix)
• famille(famille,reference)

Pouvez vous m'aider à faire ces requêtes s'il vous plait?
Merci d'avance...

[Chtulus]

Bonjour,

Tu as pris l'apéro non

Est ce que tu aurais la structure complète de tes tables (DDL SQL) ?
Un jeu d'essai ?
Des requêtes que tu aurais commencées ?

[Saten]

Non j'ai pas pris l'apéro, j'ai mangé là. Et vu que manger c'est tricher, je suis contraint par les règles internationales de l'apéritif d'attendre la fin d'après midi


Voilà mes scripts de création de base:

Code :

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

1
2
3
4
5
6
7
8
9
CREATE DATABASE `gestion` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
DROP TABLE IF EXISTS `gestion`.`produits`;
CREATE TABLE  `gestion`.`produits` (
  `Reference` varchar(50) NOT NULL default '',
  `Designation` varchar(100) default NULL,
  `CodeBarre` int(13) NOT NULL,
  PRIMARY KEY  (`Reference`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code :

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

1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS `gestion`.`prodpxven`;
CREATE TABLE  `gestion`.`prodpxven` (
  `Date` date NOT NULL,
  `PrixVenteTTC` double default NULL,
  `Reference` varchar(50) NOT NULL,
  PRIMARY KEY  USING BTREE (`Reference`,`Date`),
  CONSTRAINT `Reference` FOREIGN KEY (`Reference`) REFERENCES `produits` (`Reference`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code :

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

1
2
3
4
5
6
DROP TABLE IF EXISTS `gestion`.`famille`;
CREATE TABLE  `gestion`.`famille` (
  `Famille` varchar(40) NOT NULL,
  `Reference` varchar(50) NOT NULL,
  PRIMARY KEY  USING BTREE (`Reference`,`Famille`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Donc ça c'est fait, après, excepté ces quelques ébauches de requêtes que j'ai faites:

Code :

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

select count(reference) from prodpxven

mais ça m'avance pas trop...

Code :

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

select std(prixventettc) from prodpxven

Le truc c'est que SQL c'est pas mon grand dada, et j'en ai aucune idée de comment faire: "prends tous les produits, et sort moi celui qui a le plus grand écart type".

Enfin est-il possible de coder en SLQ quelque chose qui me sort en plus:

le produit ayant la plus forte hausse de prix?

Merci

[CinePhil]

Pour commencer, si tu es maître de la structure de la BDD, des PRIMARY KEY sur des colonnes en VARCHAR, beurk !
Bonjour les performances ! Surtout que ce que tu cherches à faire va demander des ressources au SGBD.

Ensuite, procédons par étapes...

le produit ayant la plus forte hausse de prix

On peut déjà se demander si c'est entre deux dates précises ou bien dans l'absolu mais ne chipotons pas et prenons ce second cas.

Pour trouver ça, il nous faut, pour chaque produit :
- le prix le plus ancien
- le prix le plus récent
Donc il faut pour chaque produit :
- la date la plus ancienne dans prodpxven
- la date la plus récente dans prodpxven

Code SQL :

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

1
2
3
SELECT reference, MIN(date) AS DateDebut, MAX(date) AS DateFin
FROM prodpxven
GROUP BY reference

Ensuite on va chercher les prix qui correspondent à ces dates, on fait la différence, on classe par ordre inverse et on ne retient que la première ligne retournée :

Code SQL :

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

1
2
3
4
5
6
7
8
SELECT tmp.reference, p1.prix AS PrixDebut, p2.prix AS PrixFin, (p2.prix - p1.prix) AS Difference
FROM (   SELECT reference, MIN(date) AS DateDebut, MAX(date) AS DateFin
   FROM prodpxven
   GROUP BY reference
) tmp
INNER JOIN prodpxven p1 ON tmp.reference = p1.reference AND tmp.DateDebut = p1.date
INNER JOIN prodpxven p2 ON tmp.reference = p2.reference AND tmp.DateFin = p2.date
ORDER BY Difference DESC LIMIT 1

A essayer...
Et ensuite tu essaies de faire les autres en t'inspirant de celle-là.

Au fait, encore un truc : appeler une colonne 'date' peut engendrer des confusions avec un mot de SQL et perturber le SGBD.

[Saten]

Pour les clés, ok, il m'aurait fallu des "ID" en clé non? Je note , j'éviterais prochaine fois!
Pour "Date" en nom de table aussi, je note!

Merci énormément pour ton aide, c'est magique
Je m'y met, et j'essaie!
Une précision, pour les différence entre le prix le plus bas et le prix le plus haut, peut-on utiliser des pourcentages? Car un produit à 100€ chutant de 20€ c'est plus qu'un de 2000€ chutant de 200€...??

Merci encore

[CinePhil]

Pour les clés, ok, il m'aurait fallu des "ID" en clé non?

Oui et de type entier auto-incrémenté.

Une précision, pour les différences entre le prix le plus bas et le prix le plus haut, peut-on utiliser des pourcentages? Car un produit à 100€ chutant de 20€ c'est plus qu'un de 2000€ chutant de 200€...??

Oui bien sûr !

Code :

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

(100 * (p2.prix - p1.prix)/(p1.prix)) AS TauxChgtPrix

[Saten]

Génial! Ça, c'est une super nouvelle, bon allez, je vais aller jouer avec ça maintenant!

Merci beaucoup Cinephil, une grande aide!

[Saten]

J'ai réussi à faire ce que j'ai dit, c'est super, merci encore cinePhil!

Maintenant afin de finaliser cette fonctionnalité, je souhaiterais pouvoir "naviguer" dans l'analyse en déterminant manuellement ou en cliquant sur des boutons "<mois", "mois>", "<trimestre", "trimestre>" la plage de dates concernées par l'analyse.

Donc je me suis dit qu'il suffisait de remplacer les min(date) et max(date) par deux variables, qui changeraient lors des événements. Mais ça ne marche pas...aucun résultat ne sors quand je met en début: '2006-01-01' et en fin '2008-12-01'...

Merci

EDIT: Test avec des between placés après les min(date) et max(date), mais marche pas non plus, je vois pas là...

[CinePhil]

MIN et MAX vont donner, comme leur nom l'indique, les dates mini et maxi.
Ce que tu cherches, ce sont les dates mini et maxi mais comprises dans une plages de dates.
Il faut donc que tu ajoutes à la sous-requête :

Code :

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

WHERE date BETWEEN '2006-01-01' AND '2008-12-01'

Ou plus simplement si tu veux faire ta comparaison sur une année civile :

Code :

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

WHERE YEAR(date) = 2008

[Saten]

Merci beaucoup, c'est vrai qu'en regardant c'est logique en plus...

Par contre n'ayant pas pu résoudre mon problème de nom de table "date", il me dit que "date" est ambigü. Donc je met "prodpxven.date", là il me dit que ça existe pas (??), mais quand je met p1.date ou p2.date ça marche. Seulement p1.date et p2.date ne me sortent que les mêmes résultats que si je n'avais pas mis la ligne

Suis-je obligé de changé la table "date"? Car j'ai beaucoup de code avec des string de requête à changer alors...

edit:

Code :

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

WHERE p1.Date and p2.date  BETWEEN '2006-01-01' AND '2007-08-25'

C'est la solution à mon problème? Ca marche, mais je suis pas sûre de mon truc là^^

[CinePhil]

Il faut mettre le WHERE dans cette partie là :

Code :

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

1
2
3
4
5
6
FROM (   
   SELECT reference, MIN(date) AS DateDebut, MAX(date) AS DateFin
   FROM prodpxven
   WHERE date AND date  BETWEEN '2006-01-01' AND '2007-08-25'   
   GROUP BY reference
) tmp

Et attention ! MySQL est 'case sensitive' : date et Date sont différents !

[Saten]

Alors j'ai mis ta solution, je ne comprend pas pourquoi le "WHERE date AND Date", et en laissant ça, ça fait planter MySql Query Browser! Il me dit que la requête bloque sur le serveur, et bref obligé d'arrêter l'éxécution...

Sinon j'ai enlevé le "date" et ça marche. Normal chef?

Enfin si c'est la bonne solution je prends!

[CinePhil]

Au temps pour moi ! J'avais fait un copier/coller de ton WHERE mais ce n'est pas ça. Tu ne gardes qu'un date bien sûr !

Code :

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

1
2
3
4
5
6
FROM (   
   SELECT reference, MIN(date) AS DateDebut, MAX(date) AS DateFin
   FROM prodpxven
   WHERE date  BETWEEN '2006-01-01' AND '2007-08-25'   
   GROUP BY reference
) tmp

[Saten]

Super ça marche impec! ! Merci infiniment Cinephil! Génial merci pour ton aide!