Affiner les résultats d'une requête (GROUP BY ?) [Résolu]

[Malikiwi]

Bonjour à tous !

Pour résumer ma situation, je sélectionne des produits et leurs informations propres dans une table produits "T_Prod" ainsi que des éléments se trouvant dans d'autres tables, reliées par des clés étrangères.

Voici, dans un premier temps, ma requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT C.Nom_Cat, C.TxtAlt_Cat, C.Pos_Cat,
L.Classe_Lib,
P.Titre_Prod, P.SousTitre_Prod, P.Prix_Prod, P.PourcentPromo_Prod, P.PrixOkaz_Prod, P.Img_Prod, P.Video_Prod, P.HauteurVideo_Prod,
A.Nom_Aut,
V.Prix_Variante
FROM T_Cat C
JOIN T_Prod P
ON P.Id_Cat = C.Id_Cat
LEFT OUTER JOIN T_Lib L
ON P.Id_Lib = L.Id_Lib
LEFT OUTER JOIN T_Aut A
ON P.Id_Aut = A.Id_Aut
LEFT OUTER JOIN T_Variante V
ON P.Id_Prod = V.Id_Prod
WHERE P.Vignette_Prod = 'True'
ORDER BY C.Pos_Cat

Le problème se situe au niveau de "V.Prix_Variante" :

Mes produits peuvent en faite chacun avoir des déclinaisons : "ce produit est disponible en bleu et en rouge" et chacune de ces variantes est susceptible d'avoir un prix bien distinct : "la version bleu de ce produit coûte 5€, la rouge coûte 6€". Ces prix sont indiqués dans une colonne "Prix_Variante" de la table "T_Variante" à laquelle j'ai affecté un alias "V" lors de la requête (d'où le "V.Prix_Variante").

Pour repérer quelles variantes sont affectées à quel produit (je pars du principe qu'une variante ne peut être affectée qu'à un seul produit et qu'un produit peut avoir plusieurs variantes : relation 1-N), je me base sur la colonne "Id_Prod" de la table "T_Variante", dont la valeur doit correspondre à l'une des entrées de la colonne "Id_Prod" de la table "T_Prod" (voir le schéma relationnel).

La requête fonctionne mais, forcément, si mon produit écope de plusieurs variantes, alors j'aurai autant de lignes qu'il y a des variantes dans mes résultats de requête. Ces lignes seront en tout point identiques, à l'exception du prix de la variante.

Mon souhait est le suivant :
Que je ne récupère qu'une seule entrée pour un produit, avec dans la colonne "Prix_Variante" la valeur la plus petite parmi l'ensemble des prix de variantes recensés.

J'ai déjà repéré la fonction "MIN" et l'instruction "GROUP BY" qui pourraient répondre à mes attentes. J'ai essayé de cette façon, mais sans succès :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT C.Nom_Cat, C.TxtAlt_Cat, C.Pos_Cat,
L.Classe_Lib,
P.Titre_Prod, P.SousTitre_Prod, P.Prix_Prod, P.PourcentPromo_Prod, P.PrixOkaz_Prod, P.Img_Prod, P.Video_Prod, P.HauteurVideo_Prod,
A.Nom_Aut,
MIN(V.Prix_Variante)
FROM T_Cat C
JOIN T_Prod P
ON P.Id_Cat = C.Id_Cat
LEFT OUTER JOIN T_Lib L
ON P.Id_Lib = L.Id_Lib
LEFT OUTER JOIN T_Aut A
ON P.Id_Aut = A.Id_Aut
LEFT OUTER JOIN T_Variante V
ON P.Id_Prod = V.Id_Prod
WHERE P.Vignette_Prod = 'True'
GROUP BY P.Id_Prod
ORDER BY C.Pos_Cat

Merci d'avance pour vos réponses qui, j’espère, me permettront de me sortir de ce bourbier qui me bloque depuis des jours...

[al1_24]

Toutes les colonnes de la clause SELECT qui ne sont pas l'objet d'une fonction de regroupement doivent être reprises dans la clause GROUP BY.

Tu trouveras ici un tutoriel sur les regroupements

[pacmann]

Salut !

La contraposée de ce que dit al1, et qui semble être ce que tu cherches :
Les colonnes qui ne sont pas dans le GROUP BY, il faut les enlever de ton SELECT

En fait, il te suffit de te poser une simple question : si tu veux une ligne par groupe, et que tu demandes à ton SELECT des colonnes qui sont liées au détail, comment peut-on savoir quelle valeur retourner pour le groupe ?

(En fait dans certains SGBD, ça te retourne carrément une erreur de syntaxe)

[al1_24]

Envoyé par pacmann

(En fait dans certains SGBD, ça te retourne carrément une erreur de syntaxe)

C'est le cas pour la majorité des SGBD, ceux qui tentent de respecter la norme du langage SQL.
Pour les autres, ça retourne des valeurs prises pas complètement au hasard mais selon des règles qui n'ont rien à voir avec l'algèbre relationnelle.

[Malikiwi]

Bonjour à vous deux, et d’emblée merci beaucoup pour ces réponses si rapides !

J'avoue avoir un peu de mal en SQL, je suis novice et je m'attaque, je trouve, à des requêtes plutôt maouss-costaud aux vues de mes compétences !

Je pense avoir saisi ce que tu veux dire pacmann. De façon encore plus concrète :
"Si je veux une seule variante de mon produit, comment expliciter quelles valeurs du produit renvoyer pour la variante en question" ?

Je pensai que la question ne se posait pas étant donné que ces valeurs sont "communes" aux deux variantes.

Donc d'un point de vue de la requête, je ne peux pas sélectionner des colonnes qui n'ont rien à voir avec mon regroupement ? Impossible donc de récupérer les infos de ma table "T_Prod" ? Il me faudrait faire deux requêtes distinctes ?

J'excuse par avance mon ignorance et ma lenteur de compréhension...

Edit :
Effectivement j'avais une erreur avec la requête formulée dans mon premier post. Je travaille sur Visual Web Developer 2010 Express avec une BDD SQL.

[ghosn]

Bonjour,
Il suffit de rajouter tout ce qui est dans ta clause SELECT dans la clause GROUP BY.

[Malikiwi]

Merci à toi aussi ghosn pour ta réponse.

J'ai essayé une requête simple de la manière suivante :

Code :

1
2
3
4
5
SELECT V.Id_Prod, MIN(V.Prix_Variante) AS PrixMin_Prod
FROM T_Variante V
JOIN T_Prod P
ON P.Id_Prod = V.Id_Prod
GROUP BY V.Id_Prod, P.Titre_Prod

Il n'y a pas de retour d'erreur, en revanche dans les résultats je n'obtiens que deux colonnes : Id_Prod et PrixMin_Prod. Où est donc passé Titre_Prod ?

[dehorter olivier]

bonjour

Regardes la clause SELECT, la colonne que tu demandes n'y est pas, donc la requete ne l'affiche pas

[al1_24]

Une requête n'affiche que les colonnes présentes dans la clause SELECT.

[Malikiwi]

Ça y est c'est bon... Merci beaucoup pour votre aide, la requête fonctionne impeccablement.

D'un point de vue pratique, c'est ok, je sais désormais comment faire (merci beaucoup à tous ! ), n'empêche que d'un point de vue théorique, j'ai toujours du mal à comprendre pourquoi tous les champs à afficher doivent être à nouveau repris dans la clause GROUP BY, même s'ils ne servent pas à "grouper" à proprement parler les résultats (ils sont groupés selon la colonne "Id_Prod" de la table "T_Variante" uniquement.

Voici la requête fonctionnelle si jamais ça peut aider...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT C.Nom_Cat, C.TxtAlt_Cat, C.Pos_Cat,
L.Classe_Lib,
P.Titre_Prod, P.SousTitre_Prod, P.Prix_Prod, P.PourcentPromo_Prod, P.PrixOkaz_Prod, P.Img_Prod, P.Video_Prod, P.HauteurVideo_Prod,
A.Nom_Aut,
MIN(V.Prix_Variante) AS PrixMin_Prod
FROM T_Cat C
RIGHT OUTER JOIN T_Prod P
ON P.Id_Cat = C.Id_Cat
LEFT OUTER JOIN T_Lib L
ON P.Id_Lib = L.Id_Lib
LEFT OUTER JOIN T_Aut A
ON P.Id_Aut = A.Id_Aut
LEFT OUTER JOIN T_Variante V
ON P.Id_Prod = V.Id_Prod
WHERE P.Vignette_Prod = 'True'
GROUP BY V.Id_Prod,
C.Nom_Cat, C.TxtAlt_Cat, C.Pos_Cat,
L.Classe_Lib,
P.Titre_Prod, P.SousTitre_Prod, P.Prix_Prod, P.PourcentPromo_Prod, P.PrixOkaz_Prod, P.Img_Prod, P.Video_Prod, P.HauteurVideo_Prod,
A.Nom_Aut
ORDER BY C.Pos_Cat

[Malikiwi]

Encore un petit dérangement, désolé.

Je pousse le vice encore plus loin...

Dans la table des variantes "T_Variante", j'ai ajouté deux champs supplémentaires "PourcentPromo_Variante" et "PrixOkaz_Variante". Je souhaite en effet que chaque variante d'un même produit puisse bénéficier d'un pourcentage de promo ou d'un prix fixe d'occasion indépendant.

Comment faire pour que, lors de ma requête, je sélectionne les valeurs de ces deux colonnes qui sont associées au prix minimal de toutes les variantes confondues d'un même produit ?

Plus concrètement, admettons

Code :

1
2
3
4
| Id_Variante     | Id_Prod     | Prix_Variante     | PourcentPromo_Variante     | PrixOkaz_Variante     |
|-----------------|-------------|-------------------|----------------------------|-----------------------|
| 1               | 1           | 5                 | 10                         | NULL                  |
| 2               | 1           | 10                | 15                         | NULL                  |

Dans ce cas, la requête récupèrerai la variante dont le prix est le plus bas, à savoir la 1ère. Il faudrait ensuite que, s'il y a une valeur remplie dans "PourcentPromo_Variante" ou dans "PrixOkaz_Variante", ce soit la valeur associée à la ligne du prix le plus bas qui soit récupérée.

J'espère avoir été suffisamment clair dans ma question... Merci d'avance !

[skuatamad]

pourquoi tous les champs à afficher doivent être à nouveau repris dans la clause GROUP BY, même s'ils ne servent pas à "grouper" à proprement parler les résultats (ils sont groupés selon la colonne "Id_Prod" de la table "T_Variante" uniquement

Non justement ils sont groupés selon toutes les colonnes, à mon avis regarde de plus près ton résultat.

Si tu veux le min(Prix_Variante) par id_prod, puis que tu souhaites afficher d'autres colonnes il est probablement nécessaire de passer par une jointure avec une sous-requête calculant le min :

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
SELECT C.Nom_Cat, 
       C.TxtAlt_Cat, 
       C.Pos_Cat,
       L.Classe_Lib,
       P.Titre_Prod, 
       P.SousTitre_Prod, 
       P.Prix_Prod, 
       P.PourcentPromo_Prod, 
       P.PrixOkaz_Prod, 
       P.Img_Prod, 
       P.Video_Prod, 
       P.HauteurVideo_Prod,
       A.Nom_Aut,
       mini.PrixMin_Prod
  FROM T_Prod P
  LEFT JOIN T_Cat C      ON P.Id_Cat  = C.Id_Cat
  LEFT JOIN T_Lib L      ON P.Id_Lib  = L.Id_Lib
  LEFT JOIN T_Aut A      ON P.Id_Aut  = A.Id_Aut
  LEFT JOIN (SELECT V.id_prod, MIN(V.Prix_Variante) AS PrixMin_Prod
               FROM T_Variante V
              GROUP BY V.id_prod
            ) mini 
         ON p.id_prod = mini.id_prod
 WHERE P.Vignette_Prod = 'True'
 ORDER BY C.Pos_Cat

Pour ta 2eme question, c'est un peu le même concept :

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
27
28
29
30
SELECT C.Nom_Cat, 
       C.TxtAlt_Cat, 
       C.Pos_Cat,
       L.Classe_Lib,
       P.Titre_Prod, 
       P.SousTitre_Prod, 
       P.Prix_Prod, 
       P.PourcentPromo_Prod, 
       P.PrixOkaz_Prod, 
       P.Img_Prod, 
       P.Video_Prod, 
       P.HauteurVideo_Prod,
       A.Nom_Aut,
       mini.PrixMin_Prod,
       mini.PourcentPromo_Variante,
       mini.PrixOkaz_Variante
  FROM T_Prod P
  LEFT JOIN T_Cat C      ON P.Id_Cat  = C.Id_Cat
  LEFT JOIN T_Lib L      ON P.Id_Lib  = L.Id_Lib
  LEFT JOIN T_Aut A      ON P.Id_Aut  = A.Id_Aut
  LEFT JOIN (SELECT t.id_prod, t.PrixMin_Prod, v2.PourcentPromo_Variante, v2.PrixOkaz_Variante
               FROM (SELECT V.id_prod, MIN(V.Prix_Variante) AS PrixMin_Prod
                       FROM T_Variante V
                      GROUP BY V.id_prod
                    ) t 
               JOIN T_Variante V2 ON v2.id_prod = t.id_prod AND v2.Prix_Variante = t.PrixMin_Prod
            ) mini 
         ON p.id_prod = mini.id_prod
 WHERE P.Vignette_Prod = 'True'
 ORDER BY C.Pos_Cat

[Malikiwi]

Waw...

Impressionnant... Je ne pouvais pas espérer une réponse plus précise !

J'avoue ne jamais avoir effectué de sous-requête. C'est incroyable les requêtes complexes que ça permet d'effectuer de manière plutôt intuitive en fin de compte !

Juste une petite précision par rapport à ce code... dans la première requête, "mini" est en faite un alias donné à la sous-requête, ce qui revient à faire ensuite une jointure sur une table "virtuelle" contenant les résultats de la sous-requête. Est-ce bien cela ?

En tout cas, un grand merci ! Je m'empresse de tester cette requête.

[skuatamad]

Envoyé par Malikiwi

Juste une petite précision par rapport à ce code... dans la première requête, "mini" est en faite un alias donné à la sous-requête, ce qui revient à faire ensuite une jointure sur une table "virtuelle" contenant les résultats de la sous-requête. Est-ce bien cela ?

Oui

[Malikiwi]

Je reviens à l'assaut avec une ultime question pour que la requête soit parfaite :

Lorsque j'ai des prix identiques sur deux variantes, la fonction MIN sur la colonne du prix me renvoie les deux... ce qui me pose problème lorsque je vais traiter mes données.

Peut-on ne garder que la variante ayant la plus grande valeur dans la colonne "PourcentPromo_Variante" ou "PrixOkaz_Variante" (si l'une est pleine, l'autre est de toute façon "NULL" ; par contre il se peut que les deux soient "NULL") ?

Quelque chose du genre... :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
...
LEFT JOIN (SELECT VTemp1.Id_Prod,
                  VTemp1.PrixMin_Variante,
                  MAX(VTemp2.PourcentPromo_Variante) AS MaxPPromo_Variante,
                  MAX(VTemp2.PrixOkaz_Variante) AS MaxPOkaz_Variante
           FROM (SELECT V.Id_Prod,
                        MIN(V.Prix_Variante) AS PrixMin_Variante
                 FROM T_Variante V
                 GROUP BY V.Id_Prod
                ) VTemp1
           JOIN T_Variante VTemp2 ON VTemp2.Id_Prod = VTemp1.Id_Prod AND VTemp2.Prix_Variante = VTemp1.PrixMin_Variante
          ) V2
          ON P.Id_Prod = V2.Id_Prod
          GROUP BY VTemp1.Id_Prod
...

Encore un énorme merci à toi skuatamad, la requête fonctionne à la perfection sinon !

[skuatamad]

Oui tu peux prendre le MAX et le fait qu'il n'y ait que des NULLS n'est pas un problème, par contre le GROUP BY doit s'écrire (et bien sûr faire partie de la sous-requête ):

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
...
(SELECT VTemp1.Id_Prod,
        VTemp1.PrixMin_Variante,
        MAX(VTemp2.PourcentPromo_Variante) AS MaxPPromo_Variante,
        MAX(VTemp2.PrixOkaz_Variante) AS MaxPOkaz_Variante
  FROM (SELECT V.Id_Prod,
               MIN(V.Prix_Variante) AS PrixMin_Variante
          FROM T_Variante V
         GROUP BY V.Id_Prod
        ) VTemp1
  JOIN T_Variante VTemp2 ON VTemp2.Id_Prod = VTemp1.Id_Prod AND VTemp2.Prix_Variante = VTemp1.PrixMin_Variante
 GROUP BY VTemp1.Id_Prod, VTemp1.PrixMin_Variante
) V2
ON P.Id_Prod = V2.Id_Prod
...

Même si MySql accepte cette syntaxe (et qu'en l'occurence les résultats seraient juste) c'est une mauvaise habitude à ne surtout pas prendre et qui conduit généralement à des résultats faux.
A lire sur le GROUP BY et MySql
http://cedric-duprez.developpez.com/...fier-group-by/

[Malikiwi]

Encore merci pour ta réponse !

Personnellement je travaille sur SQLServer donc ça devrait être interprété correctement si j'ai bien compris.

[aieeeuuuuu]

Envoyé par Malikiwi

Personnellement je travaille sur SQLServer

Alors autant en profiter... et utiliser un APPLY qui me semble très adapté pour ce cas, et sera je pense plus performant pour peu qu'il dispose d'un index adéquat :

Code SQL :

1
2
3
 
CREATE INDEX IX_IdProdPrixMin 
ON T_Variante(Id_Prod, PrixMin_Variante, PourcentPromo_Variante DESC,PrixOkaz_Variante DESC)

sur le principe, quelque chose comme :

Code SQL :

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
27
28
29
30
31
32
33
34
35
 
SELECT C.Nom_Cat, 
       C.TxtAlt_Cat, 
       C.Pos_Cat,
       L.Classe_Lib,
       P.Titre_Prod, 
       P.SousTitre_Prod, 
       P.Prix_Prod, 
       P.PourcentPromo_Prod, 
       P.PrixOkaz_Prod, 
       P.Img_Prod, 
       P.Video_Prod, 
       P.HauteurVideo_Prod,
       A.Nom_Aut,
       V.PrixMin_Prod,
       V.PourcentPromo_Variante,
       V.PrixOkaz_Variante
  FROM T_Prod P
  LEFT JOIN T_Cat C      ON P.Id_Cat  = C.Id_Cat
  LEFT JOIN T_Lib L      ON P.Id_Lib  = L.Id_Lib
  LEFT JOIN T_Aut A      ON P.Id_Aut  = A.Id_Aut
OUTER APPLY (
    SELECT TOP(1) 
        PrixMin_Variante,
        PourcentPromo_Variante,
        PrixOkaz_Variante
    FROM T_Variante
    WHERE Id_Prod = P.Id_Prod
    ORDER BY 
        PrixMin_Variante,
        PourcentPromo_Variante DESC,
        PrixOkaz_Variante DESC
)V
WHERE P.Vignette_Prod = 'True'
ORDER BY C.Pos_Cat

[skuatamad]

Merci aieeeuuuuu, je ne connais pas APPLY, mais j'avoue avoir fait toutes mes réponses en étant persuadé que Malikiwi était sur MySql...
Avec Oracle j'aurais peut être envisagé keep dense_rank first pour éviter les 2 niveaux de sous-requêtes, pour ma culture personelle est ce que ça fonctionne sur SqlServer ?

[aieeeuuuuu]

pas a ma connaissance, mais les fonctions de fenêtrage sous SQL Server ne sont disponibles que depuis la version 2005, tout comme l'opérateur APPLY. Et pour celui-ci, même si le plus gros intérêt et de pouvoir appliquer des fonctions tables prenant en paramètre une/des valeur(s) de la table externe, il permet également des gains de perfs dans certains cas, comme le cas présent.