Détails
doit apparaître dans la clause GROUP BY ou être utilisé dans une fonction d'agrégation.
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.
239
3
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 :
Mais vous pouvez également utiliser les fonctions de fenêtre, ce qui semble plus simple :
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 ducname
) pour le pays dans chaque ligne, donc c'est à vous de voir :La solution, sans doute moins élégante, pour afficher les seuls tuples
(cname, wmname)
correspondant à la valeur maximale, est la suivante :[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] :[1] : http://www.postgresql.org/docs/9.3/static/sql-select.html#SQL-DISTINCT
Utilisation de
rank()
fonction de fenêtre :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.