Discussion :

[BenFett]

Bonjour,

Je me retrouve avec un problème de requêtes SQL.

J'ai 3 tables :
category(id_categ, id_categ_parent, nom)
service (id_service, id_categ, nom)
note (id_note, note)

Je souhaiterais obtenir pour chaque id_categ_parent le service ayant la meilleure moyenne de note.


Pour obtenir pour chaque id_categ le service ayant la meilleure moyenne de note, a priori je n'ai pas de soucis.

Code :

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

1
2
3
4
5
6
7
8
SELECT avg_note.id_categ_parent, avg_note.id_categ, avg_note.id_service, MAX(avg_note.avg_note) AS best_avg_note
FROM (
	SELECT s.id_service, s.id_categ, c.id_categ_parent, AVG(n.note) AS avg_note
	FROM note AS n, service AS s, category AS c
	WHERE s.id_service = n.id_service AND c.id_categ = s.id_categ
	GROUP BY s.id_service, s.id_categ
) AS avg_note
GROUP BY avg_note.id_categ

id_categ_parent	id_categ	id_service	best_avg_note
1	9	7	1.5000
2	16	1	3.5000
2	17	26	4.0000
7	50	13	2.5000

Par contre pour obtenir pour chaque id_categ_parent le service ayant la meilleure moyenne de note, j'ai des résultats incorrects.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT c.id_categ_parent, s.id_service, MAX(avg_note.best_avg_note_categ) AS best_avg_note
FROM (
	SELECT avg_note.id_service, MAX(avg_note.avg_note) AS best_avg_note_categ
	FROM (
		SELECT s.id_service, s.id_categ, AVG(n.note) AS avg_note
		FROM note AS n, service AS s
		WHERE s.id_service = n.id_service
		GROUP BY s.id_service
	) AS avg_note
	GROUP BY avg_note.id_categ
) AS avg_note, service AS s, category AS c
WHERE s.id_service = avg_note.id_service
AND c.id_categ = s.id_categ
GROUP BY c.id_categ_parent

id_categ_parent	id_service	best_avg_note
1	7	1.5000
2	1	4.0000
7	13	2.5000

Ici, pour l'id_categ_parent 2, il me sort l'id_service 1 alors qu'il devrait me sortir l'id_service 26 (mais la meilleure moyenne est bonne cependant)

J'ai essayé de trafiquer ma requête dans tous les sens, j'ai essayé des astuces du web... C'est vendredi aprèm, il doit y avoir une part de fatigue sans doute...

Merci d'avance pour vos lumières.

[punkoff]

Bonjour,


Votre 1ere requête est fausse.
Vous obtenez bien la meilleur moyenne de note, mais en aucun cas ne rattachez le bon id_service à celui-ci.

MySql étant très permissif au niveau des group, celui-ci vous induit en erreur.

La règle est simple :
Toute colonne, dans la clause SELECT, non encadrée par une fonction d'aggrégation (min / max /..) doit être présente dans la clause group by.

Dans votre cas, MySql va remonter un id_service au hasard selon la notion de groupement.

il manque à MySql les fonctions de fenêtrage (supporté par PostGresql au passage) afin de traiter ce problème simplement.


Du coup pour la 1ere requete :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
select s1.id_cat, s1.id_ser, avg(note)
from note n 
inner join service s1 on n.id_ser = s1.id_ser
group by s1.id_cat, s1.id_ser
having avg(note) = (
 select max(avg_note) from (
  select s.id_cat, s.id_ser, avg(note) as avg_note
  from note n
  inner join service s on n.id_ser = s.id_ser
  group by s.id_cat, s.id_ser) as a
 where a.id_cat = s1.id_cat)

Pour la 2eme requete, il faut simplement rajouter un niveau de jointure avec la table catégorie et le resultat sera ok.




Pour la version avec fonction de fenetrage (non supportée par MySql) :

Code :

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

1
2
3
4
5
6
7
8
 
select * from (
 select s.id_cat, s.id_ser, avg(note) as avg_note,
 row_number() over(partition by s.id_cat order by avg(note) desc) as rnk
 from note n
 inner join service s on n.id_ser = s.id_ser
 group by s.id_cat, s.id_ser )as a
where rnk = 1

C'est beaucoup plus simple

[BenFett]

Bonjour,

Merci infiniment pour ton aide.
Effectivement cela fonctionne.
Je ne connaissais pas les sous-requêtes dans les HAVING, et encore moins les fonctions de fenêtrage.

Pour Postgre on m'avait déjà dit que c'était plus complet mais on ne peut/veut changer de SGBD pour le moment.

Merci encore.