Muss im GROUP BY -Klausel erscheinen oder in einer Aggregatfunktion verwendet werden.

Question

Ich habe eine Tabelle, die so aussieht, Anrufer 'makerar'

cname

wmname

avg

canada

zoro

2.0000000000000000

spanien

luffy

1.00000000000000000000

spanien

usopp

5.0000000000000000

Und ich möchte den größten Durchschnitt für jeden cname auswählen.

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

aber ich bekomme einen Fehler,

FEHLER: Die Spalte "makerar.wmname" muss in der GROUP BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
ZEILE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

also mache ich das

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

dies wird jedoch nicht die beabsichtigten Ergebnisse liefern, und die falsche Ausgabe unten wird angezeigt.

cname

wmname

max

canada

zoro

2.0000000000000000

spanien

luffy

1.00000000000000000000

spanien

usopp

5.0000000000000000

Die tatsächlichen Ergebnisse sollten wie folgt sein

cname

wmname

max

canada

zoro

2.0000000000000000

spanien

usopp

5.0000000000000000

Wie kann ich dieses Problem beheben?

Hinweis: Diese Tabelle ist eine VIEW, die aus einer früheren Operation erstellt wurde.

Answer 1

Ja, das ist ein häufiges Aggregationsproblem. Vor SQL3 (1999) müssen die ausgewählten Felder im GROUP BY-Klausel erscheinen[*].

Um dieses Problem zu umgehen, müssen Sie das Aggregat in einer Unterabfrage berechnen und dann mit sich selbst verbinden, um die zusätzlichen Spalten zu erhalten, die Sie anzeigen möchten:

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
 spanien  | usopp  |     5.0000000000000000

---

Sie können jedoch auch Fensterfunktionen verwenden, was einfacher aussieht:

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;

Einziger Nachteil bei dieser Methode ist, dass alle Datensätze angezeigt werden (Fensterfunktionen gruppieren nicht). Es wird jedoch das korrekte (d. h. auf cname-Ebene begrenzt) MAX für das Land in jeder Zeile angezeigt, also liegt es bei Ihnen:

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spanien  | luffy  |     5.0000000000000000
 spanien  | usopp  |     5.0000000000000000

Die Lösung, möglicherweise weniger elegant, um nur die (cname, wmname)-Tupel mit dem maximalen Wert anzuzeigen, lautet:

SELECT DISTINCT /* distinct hier ist wichtig, weil es möglicherweise verschiedene Tupel für denselben Maximalwert gibt */
    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
 spanien  | usopp  |     5.0000000000000000

---

[*]: Interessanterweise, obwohl die Spezifikation es irgendwie erlaubt, nicht gruppierte Felder auszuwählen, scheinen die großen Engines dies nicht wirklich zu mögen. Oracle und SQLServer erlauben dies überhaupt nicht. Mysql hat dies standardmäßig erlaubt, aber seit Version 5.7 muss der Administrator diese Option (ONLY_FULL_GROUP_BY) manuell in der Serverkonfiguration aktivieren, damit diese Funktion unterstützt wird...

Answer 2

In Postgres können Sie auch die spezielle DISTINCT ON (expression) Syntax verwenden:

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;

Answer 3

Das Problem bei der Angabe von nicht gruppierten und nicht aggregierten Feldern in group by selects ist, dass der Motor nicht weiß, welches Feld des Datensatzes in diesem Fall zurückgegeben werden soll. Ist es der Erste? Ist es der Letzte? Es gibt normalerweise keinen Datensatz, der natürlich dem aggregierten Ergebnis entspricht (min und max sind Ausnahmen).

Es gibt jedoch einen Workaround: Machen Sie das erforderliche Feld ebenfalls aggregiert. In Postgres sollte das funktionieren:

SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;

Beachten Sie, dass dies ein Array aller wmnames erstellt, sortiert nach avg, und das erste Element zurückgibt (Arrays in Postgres beginnen bei 1).

Answer 4

Für mich geht es nicht um ein "gewöhnliches Aggregationsproblem", sondern einfach um eine falsche SQL-Abfrage. Die einzige richtige Antwort auf "Wählen Sie den maximalen Durchschnitt für jede cname..." ist

SELECT cname, MAX(avg) FROM makerar GROUP BY cname;

Das Ergebnis wird sein:

 cname  |      MAX(avg)
--------+---------------------
 canada | 2.0000000000000000
 spain  | 5.0000000000000000

Dieses Ergebnis beantwortet im Allgemeinen die Frage "Was ist das beste Ergebnis für jede Gruppe?". Wir sehen, dass das beste Ergebnis für Spanien 5 und für Kanada das beste Ergebnis 2 ist. Es ist wahr und es liegt kein Fehler vor. Wenn wir auch wmname anzeigen müssen, müssen wir die Frage beantworten: "Welche REGEL sollen wir verwenden, um wmname aus dem Ergebnis zu wählen?" Lassen Sie uns die Eingabedaten ein wenig ändern, um den Fehler zu verdeutlichen:

  cname | wmname |        avg           
--------+--------+-----------------------
 spain  | zoro   |  1.0000000000000000
 spain  | luffy  |  5.0000000000000000
 spain  | usopp  |  5.0000000000000000

Welches Ergebnis erwarten Sie bei Ausführung dieser Abfrage: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;? Sollte es spain+luffy oder spain+usopp sein? Warum? In der Abfrage ist nicht festgelegt, wie "besserer" wmname ausgewählt werden soll, wenn mehrere geeignet sind, daher ist das Ergebnis auch nicht festgelegt. Aus diesem Grund gibt der SQL-Interpreter einen Fehler zurück - die Abfrage ist nicht korrekt.

Anders ausgedrückt gibt es keine richtige Antwort auf die Frage "Wer ist der Beste in der spain Gruppe?". Luffy ist nicht besser als Usopp, weil Usopp den gleichen "Score" hat.

Answer 5

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;

Mit der Verwendung der rank() Fensterfunktion:

SELECT cname, wmname, avg
FROM (
    SELECT cname, wmname, avg, rank() 
    OVER (PARTITION BY cname ORDER BY avg DESC)
    FROM makerar) t
WHERE rank = 1;

Hinweis

Beides wird mehrere Maximalwerte pro Gruppe beibehalten. Wenn Sie nur einen einzigen Datensatz pro Gruppe wünschen, auch wenn mehr als ein Datensatz mit avg gleich max vorhanden ist, sollten Sie die Antwort von @ypercube überprüfen.