J'ai une table qui ressemble à cet appelant 'makerar' ;
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Et je veux sélectionner la moyenne maximale pour chaque nom de famille.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
mais j'obtiendrai une erreur,
ERROR: column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
alors je fais ceci
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
mais cela ne donne pas les résultats escomptés, et la sortie incorrecte ci-dessous est affichée.
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Les résultats réels devraient être
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Comment puis-je résoudre ce problème ?
Remarque : Cette table est une VIEW créée à partir d'une opération précédente.
Oui, c'est un problème d'agrégation courant. Avant [SQL3 (1999)][1], les champs sélectionnés doivent apparaître dans la clause GROUP BY
[*].
Pour contourner ce problème, vous devez calculer l'agrégation dans une sous-requête, puis la joindre à elle-même pour obtenir les colonnes supplémentaires que vous devez afficher :
SELECT m.cname, m.wmname, t.mx
FROM (
SELECT cname, MAX(avg) AS mx
FROM makerar
GROUP BY cname
) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Mais vous pouvez également utiliser les fonctions de fenêtre, ce qui semble plus simple :
SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;
Le seul inconvénient de cette méthode est qu'elle affichera tous les enregistrements (les fonctions de fenêtre ne regroupent pas). Mais elle affichera le MAX
correct (c'est-à-dire maximisé au niveau du cname
) pour le pays dans chaque ligne, donc c'est à vous de voir :
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
La solution, sans doute moins élégante, pour afficher les seuls tuples (cname, wmname)
correspondant à la valeur maximale, est la suivante :
SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
m.cname, m.wmname, t.avg AS mx
FROM (
SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn
FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
[1] : http://web.cecs.pdx.edu/~len/sql1999.pdf
[*] : Il est intéressant de noter que, même si la spécification permet en quelque sorte de sélectionner des champs non groupés, les principaux moteurs ne semblent pas vraiment l'apprécier. Oracle et SQLServer ne le permettent pas du tout. Mysql l'autorisait par défaut, mais depuis la version 5.7, l'administrateur doit activer manuellement cette option (ONLY_FULL_GROUP_BY
) dans la configuration du serveur pour que cette fonctionnalité soit prise en charge...
Dans Postgres, vous pouvez également utiliser la syntaxe spéciale [DISTINCT ON (expression)
][1] :
SELECT DISTINCT ON (cname)
cname, wmname, avg
FROM
makerar
ORDER BY
cname, avg DESC ;
[1] : http://www.postgresql.org/docs/9.3/static/sql-select.html#SQL-DISTINCT
SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
SELECT cname, MAX(avg) max
FROM makerar
GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;
Utilisation de rank()
fonction de fenêtre :
SELECT cname, wmname, avg
FROM (
SELECT cname, wmname, avg, rank()
OVER (PARTITION BY cname ORDER BY avg DESC)
FROM makerar) t
WHERE rank = 1;
Note
L'une ou l'autre conservera plusieurs valeurs max par groupe. Si vous ne voulez qu'un seul enregistrement par groupe, même s'il y a plus d'un enregistrement dont la moyenne est égale à la valeur maximale, vous devriez consulter la réponse de @ypercube's.