Affichage du prix suivant la date incorrect. [Résolu]

[Nnahaa]

Bonsoir à toutes et à tous,

J'ai un petit problème de requete,
J'aimerai afficher des données contenant un prix produit où ce prix varie suivant la date de la commande.

J'arrive à afficher les données mais le prix est toujours le même (le dernier enregistré dans la base de données).

voici la requete :

Code :

1
2
3
4
5
6
7
8
9
SELECT l.CODE_LIGNE, p.CODE_PRODUIT, p.LIBELLE_PRODUIT, l.QT_PRODUIT, l.POIDS_TOTAL_PRODUIT,pr.PRIX_KILO,l.CODE_CMD, c.DATE_CMD, pr.DATE_PRIX, pr.CODE_PRIX 
 
FROM produit p 
INNER JOIN prix pr ON p.CODE_PRODUIT = pr.CODE_PRODUIT
INNER JOIN ligne_commande AS l ON l.CODE_PRODUIT = p.CODE_PRODUIT
INNER JOIN commande AS c ON c.CODE_CMD = l.CODE_CMD 
 
WHERE l.POIDS_TOTAL_PRODUIT IS NOT NULL
AND ( SELECT COUNT(*) FROM prix p1 WHERE  p1.CODE_PRODUIT = pr.CODE_PRODUIT AND p1.DATE_PRIX > pr.DATE_prix ) < 1

Cette requete utilise 4 tables =>
- Ligne_commande

CODE_PRODUIT
LIBELLE_PRODUIT

- commande

CODE_CMD
CODE_PERSONNE
CODE_CLIENT
CODE_LIVRAISON
DATE_CMD

- prix

CODE_PRIX
CODE_PRODUIT
DATE_PRIX
PRIX_KILO

- produit

CODE_PRODUIT
LIBELLE_PRODUIT

J'ai essayé entre autre de mettre cette clause un peu partout mais sans succés :

Code :

AND c.DATE_CMD >= pr.DATE_PRIX

Avez vous une idée pour permettre l'affichage du bon prix? J'ai vraiment plus d'idée.

Merci par avance

[skuatamad]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT l.CODE_LIGNE, p.CODE_PRODUIT, p.LIBELLE_PRODUIT, l.QT_PRODUIT, l.POIDS_TOTAL_PRODUIT,
       pr.PRIX_KILO,l.CODE_CMD, c.DATE_CMD, pr.date_debut_prix, pr.CODE_PRIX 
  FROM produit p   
  JOIN ligne_commande AS l ON l.CODE_PRODUIT = p.CODE_PRODUIT
  JOIN commande       AS c ON c.CODE_CMD     = l.CODE_CMD
  JOIN (SELECT pr1.CODE_PRODUIT, pr1.CODE_PRIX, pr1.PRIX_KILO, pr1.DATE_PRIX AS date_debut_prix,
               (SELECT min(pr2.DATE_PRIX) 
                  FROM prix pr2 
                 WHERE pr2.CODE_PRODUIT = pr1.CODE_PRODUIT 
                   AND pr2.date_prix    > pr1.date_prix) AS date_fin_prix
          FROM prix pr1
        ) pr ON pr.CODE_PRODUIT = p.CODE_PRODUIT 
            AND c.DATE_CMD BETWEEN pr.date_debut_prix AND coalesce(pr.date_fin_prix,current_date)
 WHERE l.POIDS_TOTAL_PRODUIT IS NOT NULL

Je pense qu'il faut déteminer les intervalles de validités des prix, c'est le but de la sous-requête PR, pour pouvoir faire un BETWEEN entre la DATE_CMD et cet intervalle, ensuite tu gardes la bornes inf

Cette sous-requête PR serait une bonne candidate pour une vue, mais je crois que MySql n'aime pas les sous-requêtes dans les vues....

[Nnahaa]

Impressionnant !!

j'aurais jamais trouvé tout seul. Je pense que je me suis aventuré un peu trop loin de chez moi la
J'ai du mettre 15min pour comprendre la requête.

Je te remercie énormément.

J'ai essayé d'en faire une vue mais MySql la refuse.

Encore merci à toi.

[skuatamad]

Content que ça convienne, mais effectivement j'aurais pu ajouter plus de commentaires, je vais le faire maintenant puisque j'ai du temps, ça pourra toujours être utile pour de futurs lecteurs.

La partie intéressante est :

Code :

1
2
3
4
5
6
SELECT pr1.CODE_PRODUIT, pr1.CODE_PRIX, pr1.PRIX_KILO, pr1.DATE_PRIX AS date_debut_prix,
       (SELECT min(pr2.DATE_PRIX) 
          FROM prix pr2 
         WHERE pr2.CODE_PRODUIT = pr1.CODE_PRODUIT 
           AND pr2.date_prix    > pr1.date_prix) AS date_fin_prix
  FROM prix pr1

Comme MySql ne connaît pas les fonctions analytiques, je passe par une requête corrélée, c'est le SELECT dans le SELECT avec correlation de prix pr1 avec prix pr2.
L'idée est de récupérer les lignes des "dates suivantes" (pr2.date_prix > pr1.date_prix) par CODE_PRODUIT (pr2.CODE_PRODUIT = pr1.CODE_PRODUIT).
Et pour récupérer uniquement la "date qui suit", j'utilise MIN. (à noter qu'une requête corrélée dans un SELECT ne peux renvoyer qu'une ligne)
Par "date qui suit" j'entends la ligne suivante lorsqu'on trie la table PRIX sur CODE_PRODUIT,DATE_PRIX.

J'espère que c'est à peu près clair...

Par contre pour avoir de bonne performance sur ce genre de requetête corrélée il faut créer un index sur PRIX(CODE_PRODUIT,DATE_PRIX):

Code :

CREATE INDEX idx_prix_code_date ON prix (CODE_PRODUIT,DATE_PRIX)

Voir même un index couvrant comme (en rajoutant les colonnes sélectionnées mais non filtrantes en fin d'index):

Code :

CREATE INDEX idx_prix_code_date ON prix (CODE_PRODUIT,DATE_PRIX,CODE_PRIX,PRIX_KILO)

Je ne sais pas si MySql fonctionne comme les vrais SGBDR du marché sur ce point mais normalement un index couvrant permet de ne même pas accéder à la table PRIX du tout, seul l'index est utilisé pour cette requête.

Un dernier point concerne COALESCE(PR.DATE_FIN_PRIX,CURRENT_DATE).
Comme pour chaque CODE_PRODUIT la "dernière" DATE_FIN_PRIX sera NULL j'utilise COALESCE pour associer la date du jour (CURRENT_DATE) lorsque DATE_FIN_PRIX est NULL pour que le BETWEEN fonctionne correctement.

[Nnahaa]

merci encore pour ton aide.

Ton explication est intéressante et utile.

Une question me vient à l'esprit, étant débutant dans la programmation, je trouve ce genre de requête assez compliqué. J'aimerai savoir si dans les applications/sites web utilisant une BDD avec prix différent suivant la date (site marchant, logiciel de gestion...) utilisent ce genre de requête ou utilisent une méthode différente peut être plus rapide ?

Merci par avance.

[Maljuna Kris]

Envoyé par Nnahaa

Une question me vient à l'esprit, étant débutant dans la programmation, je trouve ce genre de requête assez compliqué. J'aimerai savoir si dans les applications/sites web utilisant une BDD avec prix différent suivant la date (site marchant, logiciel de gestion...) utilisent ce genre de requete ou utilisent une méthode différente peut être plus rapide ?

Saluton,
Il n'y a rien, en soi, de compliqué dans cette requête. C'est juste que la logique relationnelle peut apparaître très déroutante pour qui n'en est pas familier.

Quant au fond de ta question, une analyse complète de la problématique aurait peut-être conduit à une autre modélisation de la base de données mais, l'expérience prouve que nombre d'entorses aux formes normales, justifiées en amont par des préoccupations d'optimisation, finissent par se montrer très pénalisantes en logique relationnelle sur le long terme.

[skuatamad]

Je suis d'accord avec Maljuna Kris, la requête n'est pas compliquée en soit mais le SQL, comme tout langage, nécessite un apprentissage, d'autant qu'il faut résonner de façon ensembliste plutôt que procédurale, ce qui ne simplifie pas son approche.

Par contre pas de soucis avec avec le modèle, en tout cas SqlPro propose ici un exemple similaire:
http://www.developpez.net/forums/d36...s/#post3256642
Mais la limitation de MySql sur les vues est pénible.

J'aimerai savoir si dans les applications/sites web utilisant une BDD avec prix différent suivant la date (site marchant, logiciel de gestion...) utilisent ce genre de requete ou utilisent une méthode différente peut être plus rapide ?

Ca dépend du SGBDR utilisé, par exemple sur oracle la requête s'écrirait :

Code :

1
2
3
SELECT pr1.CODE_PRODUIT, pr1.CODE_PRIX, pr1.PRIX_KILO, pr1.DATE_PRIX AS date_debut_prix,
       lead (pr1.DATE_PRIX) over (partition BY pr1.CODE_PRODUIT ORDER BY pr1.DATE_PRIX) AS date_fin_prix
  FROM prix pr1

qui est à la fois plus simple et plus performante.

[Maljuna Kris]

Remarque bien que résonner (comme une cloche) c'est comme réfléchir (comme un miroir)

[Nnahaa]

Ok merci des renseignements.
Cela fait 6ans que j'ai pas touché de requetes et déjà à l'époque je n'avais vu que les principes de base.

Si je comprend bien, va falloir que je m'y fasse