sql datensätze - muss in der GROUP BY-Klausel vorkommen oder in einer Aggregatfunktion verwendet werden




3 Answers

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

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

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

Sie können aber auch Fensterfunktionen verwenden, die einfacher aussehen:

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

Die einzige Sache mit dieser Methode ist, dass es alle Aufzeichnungen zeigt (Fensterfunktionen gruppieren sich nicht). Aber es wird die korrekte (dh maxed auf cname Ebene) MAX für das Land in jeder Zeile zeigen, also liegt es an dir:

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

Die (cname, wmname) weniger elegante Lösung, die einzigen (cname, wmname) Tupel zu zeigen, die dem Maximalwert entsprechen, ist:

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

[*]: Interessanterweise scheinen die großen Engines das nicht zu gruppieren. Oracle und SQLServer erlauben das überhaupt nicht. Mysql hat es standardmäßig erlaubt, aber seit 5.7 muss der Administrator diese Option ( ONLY_FULL_GROUP_BY ) manuell in der Serverkonfiguration ONLY_FULL_GROUP_BY damit diese Funktion unterstützt wird ...

spalten summe

Ich habe eine Tabelle, die aussieht wie dieser Anrufer 'makerar'

 cname  | wmname |          avg           
--------+-------------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

Und ich möchte das maximale avg für jeden cname auswählen.

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

aber ich werde einen Fehler bekommen,

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;

also mache ich das

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

Dies führt jedoch nicht zu den beabsichtigten Ergebnissen, und die unten angegebene falsche Ausgabe wird angezeigt.

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

Tatsächliche Ergebnisse sollten sein

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

Wie kann ich dieses Problem beheben?

Hinweis: Diese Tabelle ist eine VIEW, die aus einer vorherigen Operation erstellt wurde.




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 Fensterfunktion rank() :

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

Bei beiden werden mehrere Maximalwerte pro Gruppe beibehalten. Wenn Sie nur einen einzelnen Datensatz pro Gruppe haben möchten, sollten Sie die Antwort von @ ypercube überprüfen, auch wenn mehr als ein Datensatz mit einem Durchschnittswert von max.




Ich habe kürzlich auf dieses Problem gestoßen, als ich versuchte, case when while zu zählen, und festgestellt habe, dass das Ändern der Reihenfolge der Anweisungen which und count das Problem behebt:

SELECT date(dateday) as pick_day,
COUNT(CASE WHEN (apples = 'TRUE' OR oranges 'TRUE') THEN fruit END)  AS fruit_counter

FROM pickings

GROUP BY 1

Anstatt - in letzterem, wo ich Fehler habe, dass Äpfel und Orangen in Aggregatfunktionen erscheinen sollten

CASE WHEN ((apples = 'TRUE' OR oranges 'TRUE') THEN COUNT(*) END) END AS fruit_counter



Related

sql group-by aggregate-functions postgresql-9.1