Discussion :

[fhbcrea]

Bonjour tout le monde.
Longue vie à Kirk Douglas et à Robert Marchand, entre autre.
Mais surtout bonne année 2017 au plus grand nombre!

Voilà, je souhaite factoriser mon code.
Actuellement j'ai plusieurs requêtes qui fonctionnent très bien.
Mais il s'agit de plusieurs requêtes, là où il pourrait en y avoir qu'une...
Ce qui implique un certain nombre d'actions et de contrôles en PHP en amont.

Bref, venons-en à mon problème par l'exemple:

J'ai plusieurs tables, dont une table 'te_produits_pro' que voici.

Code :

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

PRO_ID|PRO_LABEL|PRO_CATEGORIE_ID|PRO_NB_VOTES

Voilà en bon français la requête voulue:
"Je selectionnne dans chacunes des catégories exisantes les 5 produits ayant reçus le plus de votes."

Pour simplifier, actuellement je fais ça:

Code :

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

'SELECT * FROM te_produits_pro WHERE pro_categorie_id=$i ORDER BY pro_nb_votes LIMIT 5'

En PHP, je récupère N catégories, puis dans une boucle j'execute cette requête.

C'est fonctionnel, mais c'est une béquille.

Alors j'ai essayé d'autres choses, dont entre autre:

Code :

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

'SELECT * FROM te_produits_pro ORDER BY pro_cat_id, pro_nb_votes DESC '

Seulement comment limiter le résultat au 5 premiers de chaque catégorie?


Merci à vous.

[SQLpro]

Simple :

Code :

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

1
2
3
4
5
6
WITH T AS (
SELECT *, RANK() OVER(PARTITION BY pro_categorie_id ORDER BY pro_nb_votes DESC) AS RANG
FROM   te_produits_pro)
SELECT * 
FROM   T
WHERE  RANG < 5;

A +

[escartefigue]

Si toutefois votre SGBD ne connait pas les fonctions de fenêtrage (Access, MySQL), vous pouvez faire comme ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT T0.PRO_ID
     , T0.PRO_LABEL
     , T0.PRO_CATEGORIE_ID
     , T0.PRO_NB_VOTES
     , T2.RANG
FROM te_produits_pro AS T0                                        
INNER JOIN                                                    
     (SELECT  TA.PRO_ID
            , TA.PRO_LABEL
            , TA.PRO_CATEGORIE_ID
            , TA.PRO_NB_VOTES                                       
            , COUNT(CASE WHEN TB.PRO_NB_VOTES > TA.PRO_NB_VOTES
                         THEN 1 ELSE NULL END) + 1 AS RANG     
      FROM  te_produits_pro AS TA                                        
      LEFT OUTER JOIN  te_produits_pro AS TB                             
        ON  TB.PRO_NB_VOTES >= TA.PRO_NB_VOTES                               
      GROUP BY  TA.PRO_ID, TA.PRO_LABEL, TA.PRO_CATEGORIE_ID, TA.PRO_NB_VOTES  
      ORDER BY  TA.PRO_ID, TA.PRO_LABEL, TA.PRO_CATEGORIE_ID, TA.PRO_NB_VOTES  DESC
     ) AS T2                                                  
   ON  T2.PRO_ID = T0.PRO_ID                                  
WHERE RANG < 6
ORDER BY RANG

[fhbcrea]

Merci pour votre aide,


Effectivement je suis sur MySQL et la dernière solution présentée est la plus pertinente.
Après l'avoir testée, celle-ci n'est pas fonctionnelle et ne me retourne pas le résultat souhaité.
Cependant, la méthode mérite réflexion et ne s'est pas faite en 5 mn je pense, alors merci pour y avoir passé du temps.
En tout cas, je me creuse la tête pour comprendre cette requête ;-)

Voilà ce que je souhaite comme résultat:

Code :

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

PRO_LABEL|PRO_NB_VOTES| PRO_CAT_ID

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Léo Ferré|18|1
Joe Dassin|17|1
Edith Piaf |13|1
dalida|8|1
 
2be3|22|2
aliage|21|2
L5|17|2
whatfor|12|2
linkup|10|2
 
Etc ...

Merci à vous.

[escartefigue]

Le principe est relativement simple :

La table T2 utilise une auto-jointure pour calculer le rang en fonction de la présence ou non de lignes dans la table outer dont le critère de classement est supérieur par rapport à la ligne de la table inner. La requete principale utilise ensuite ce résultat.

Quel est votre jeu de données qui justifie le résultat attendu ?
Et quel est le résultat obtenu avec la requete que je vous propose ?

[fhbcrea]

Bonjour, merci pour votre aide.
Voilà le réultat obtenu:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
PRO_ID | PRO_NOM | PRO_CAT_ID | PRO_NB_VOTES | RANG Croissant 
57          nom...         2        31                  1
67          nom...         3        35                  2
82          nom...         4        28                  3
63          nom...         2        21                  4
71          nom...         3        21                  4
68          nom...         3        21                  4
71          nom...         3        21                  4
86          nom...         4        21                  4

Et le résultat attendu

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
PRO_ID | PRO_NOM | PRO_CAT_ID | PRO_NB_VOTES | RANG Croissant 
12          nom...        1         51                  1
16          nom...        1         50                  2
14          nom...        1         43                  3
24          nom...        1         34                  4
11          nom...        1         33                  4
 
28          nom...        2         85                  5
26          nom...        2         55                  6
37          nom...        2         32                  7
33          nom...        2         28                  8
31          nom...        2         11                  9
 
62          nom...        3         75                  10
54          nom...        3         60                  11
59          nom...        3         37                  12
45          nom...        3         20                  13
49          nom...        3         19                  14

Voilà.
Merci à vous.

[escartefigue]

Je vois qu'il y avait des boulettes dans ma requete : jointure incomplète et tri erroné
Ca devrait aller mieux avec cette version :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT T0.PRO_ID
     , T0.PRO_LABEL
     , T0.PRO_CATEGORIE_ID
     , T0.PRO_NB_VOTES
     , T2.RANG
FROM te_produits_pro AS T0                                        
INNER JOIN                                                    
     (SELECT  TA.PRO_CATEGORIE_ID
            , TA.PRO_NB_VOTES                                       
            , COUNT(CASE WHEN TB.PRO_NB_VOTES > TA.PRO_NB_VOTES
                         THEN 1 ELSE NULL END) + 1 AS RANG     
      FROM  te_produits_pro AS TA                                        
      LEFT OUTER JOIN  te_produits_pro AS TB            
         ON  TB.PRO_CATEGORIE_ID = TA.PRO_CATEGORIE_ID              
        AND  TB.PRO_NB_VOTES >= TA.PRO_NB_VOTES                               
      GROUP BY  TA.PRO_ID, TA.PRO_LABEL, TA.PRO_CATEGORIE_ID, TA.PRO_NB_VOTES  
      ORDER BY  TA.PRO_CATEGORIE_ID, TA.PRO_NB_VOTES desc, TA.PRO_ID, TA.PRO_LABEL
     ) AS T2                                                  
   ON  T2.PRO_ID = T0.PRO_ID                                  
WHERE RANG < 6
ORDER BY T0.PRO_CATEGORIE_ID, RANG

[fhbcrea]

Merci escartefigue pour prendre le temps de m'aider.

Je suis un peu désarçonné devant une requête, que j'estime à mon niveau, comme complexe.
Je suis cependant heureux de voir que ça semble d'un usage courant. ça va me booster pour aller de l'avant.
Jusqu'à présent mes requêtes étaient simples et je me servais de PHP pour les "compléter".
Un constat:
J'obtiens d'un coté le résultat souhaité avec une simple requête que je place dans une boucle ( voir ci-dessus).
j'obtiens de l'autre, avec votre aide, le même résultat sans passer par un traitement PHP
Ma question:
Quelle différence et pour quelle efficacité?
Evidemment, la raison de l'ouverture de ce post est que justement je souhaite avoir une requête propre. Donc, tout conseil est bon à prendre. Je suis juste amer d'avoir un peu de mal à comprendre cette requête, ne voyez pas de malice dans ma question.

Question technique:
La différence entre jointure interne et externe est que la jointure interne retourne uniquement les résultats de tables qui matchent entre elles, alors que la jointure externe retourne tout, c'est bien ça? N'aurait-on pas pu utiliser une jointure interne à la place d'une jointure externe, Je ne vois pas de retour NULL, car si NULL = 1?

J'ai testé votre code, j'ai l'erreur suivante:

Champ 'T2.PRO_ID' inconnu dans on clause

Merci à vous

[escartefigue]

J'obtiens d'un coté le résultat souhaité avec une simple requête que je place dans une boucle ( voir ci-dessus).
j'obtiens de l'autre, avec votre aide, le même résultat sans passer par un traitement PHP
Ma question:
Quelle différence et pour quelle efficacité?

Privilégiez systématiquement l'approche ensembliste, qui est toujours beaucoup plus performante

Question technique:
La différence entre jointure interne et externe est que la jointure interne retourne uniquement les résultats de tables qui matchent entre elles, alors que la jointure externe retourne tout, c'est bien ça?

Exact pour la jointure inner
Pour la jointure outer, ca dépend de quelle jointure outer il s'agit
Si T1 FULL OUTER JOIN T2 alors oui on prend tout T1, tout T2, et les liens éventuels entre T1 et T2
Si T1 LEFT OUTER JOIN T2 alors on prend tout T1, et on prend T2 s'il existe des correspondances, sinon null pour les colonnes de T2
Si T1 RIGHT OUTER JOIN T2 c'est l'inverse de la précédente

J'ai testé votre code, j'ai l'erreur suivante:

C'est que décidément je ne suis pas réveillé il manque cette colonne dans le SELECT imbriqué ainsi que la colonne label
Corrigez comme ceci :

Code :

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

1
2
3
4
5
6
7
8
INNER JOIN                                                    
     (SELECT  TA.PRO_ID
            , TA.PRO_LABEL
            , TA.PRO_CATEGORIE_ID
            , TA.PRO_NB_VOTES                                       
            , COUNT(CASE WHEN TB.PRO_NB_VOTES > TA.PRO_NB_VOTES
                         THEN 1 ELSE NULL END) + 1 AS RANG     
[...]

[SQLpro]

Question technique:
La différence entre jointure interne et externe est que la jointure interne retourne uniquement les résultats de tables qui matchent entre elles, alors que la jointure externe retourne tout, c'est bien ça?

NON !
La jointure externe permet de conserver les lignes orphelines de jointure, soit :

• De la table de gauche => LEFT OUTER JOIN
• De la table de droite => RIGHT OUTER JOIN
• Des deux tables => FULL OUTER JOIN

Seule cette dernière approche permet d'obtenir toutes les lignes des deux tables !

La droite et la gauche s'appprécie par rapport au sens de lecture du texte de la requête...

Code :

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

FROM LatableDeGauche JOIN LaTableDeDroit

A +

[fhbcrea]

Bonjour,

Alors oui effectivement il faut être précis quand on parle SQL, il ne permet pas la moindre imprécision ;-)
Donc, pour moi la compréhension des jointures internes/externes est acquise.
SQLPRO, je suis en train de lire votre site de A à Z, merci pour cette ressource !
Merci à ESCARTEFIGUE. La requête est fonctionnelle et retourne le résultat attendu.
Maintenant il faut que j'arrive à bien comprendre cette requête pour pouvoir par la suite aller plus loin.
Des notions pas faciles à assimiler. travailler avec des filtres et des alias dans des sous requêtes que l'on a pas encore déclarées est une gymnastique de l’esprit pas encore acquise et qui fait toute la différence entre vous et moi.
Ca passe par de la pratique et de l'apprentissage ! -)
Merci à vous deux pour votre aide et disponibilité.
Sujet résolu.