MySQL Grouping-Problem (erledigt)

[joschilein]

Ich habe eine Tabelle die ungefähr so aussieht

id|subid|name1|name2|val
1|1|a|a1|1
1|2|a|a2|0,9
1|3|b|b1|0,8
2|1|f|f1|1
3|1|b|NULL|1
3|2|a|a3|0,5
4|..|..|..|..
..|..|..|..|..

Nun möchte ich eine Abfrage machen, die pro name1 eine Summe errechnet, wobei aber jeweils nur der größte Wert pro id berücksichtigt werden soll. Der Ausschnitt aus obigem Beispiel soll also liefern:

name1|summe
b|1,8
a|1,5
f|1

Die bisherige Abfrage macht das natürlich nicht richtig.

SELECT     name1
              ,ROUND(SUM(val),2) as summe
FROM        table
GROUP BY  name1
ORDER BY  summe DESC, name ASC

name1|summe
a|2,4
b|1,8
f|1

Wie bekomme ich das also geschickt hin? Ich habe schon verschiedene Kombinationen mit WHERE und MAX versucht, aber das wird irgendwie nichts. Das muss doch aber irgendwie gehen ?!

Einzige Alternative die ich sehe wäre eine Aufspaltung auf zwei Tabellen, einmal mit name1 (ohne Doppel) und einmal mit name2, aber das gefällt mir irgendwie gar nicht.

 

[joschilein]

Jetzt ist mir doch noch eine Variante eingefallen:

SELECT     name1
              ,ROUND(SUM(val),2) as summe
FROM        table AS A
WHERE      A.val = (SELECT max(B.val) FROM table AS B WHERE A.id = B.id AND A.name1 = B.name1)
GROUP BY  name1
ORDER BY  summe DESC, name ASC

Aber das geht doch bestimmt noch besser, oder?

Dann mache ich daraus noch die Erweiterung, in der nur die Zeilen mit subid=1 berücksichtigt werden:

SELECT     name1
              ,ROUND(SUM(val),2) as summe2
FROM        table AS A
WHERE      A.val = (SELECT max(B.val) FROM table AS B WHERE A.id = B.id AND A.name1 = B.name1)
AND          subid = 1
GROUP BY  name1
ORDER BY  summe2 DESC, name ASC

Gibt es jetzt noch eine Möglichkeit summe und summe2 gleichzeitig in ein Abfrageergebnis zu packen? Vermutlich nicht. Ist dann eh nur noch ein Schönheitsproblem.

 

[tleilax]

Ich habe selbst noch nie wirklich damit gearbeitet, aber MySQL hat die GROUP BY-Option WITH ROLLUP.

Da sich die beiden Queries ja nur um die Einschränkung auf subid=1 unterscheiden, würde es sich ja fast anbieten, einfach nach allen subid zu gruppieren und mittels WITH ROLLUP auch die Gesamtsumme direkt mit zu ermitteln. Kannst dann ja in der Logik entscheiden, was Du brauchst und was verworfen wird.

Würde das Ergebnis nicht in Spalten sondern in Zeilen liefern, aber das ist ja letztendlich auch nicht wirklich schlimm.

Wie's mit der Performance vom Ganzen aussieht, wird Dir und mir bestimmt jemand anderes beantworten können.

 

[joschilein]

Über das Rollup bin ich auch schon gestolpert, habe aber bisher nicht einordnen können, ob oder wie das helfen könnte. Klingt aber plausibel, werde ich ausprobieren.

Performance ist in dem Fall sekundär, da es nur um eine selten benutzte Stelle für ausgewählte Benutzer geht bei der man für notfalls auch ein paar Sekunden warten mehrere Minuten Denkerei sparen kann.

 

[transversalis]

unter DB2 for z/OS würde das hier funktionieren:

SELECT name1, SUM ( max_val ) as sum_max_val
FROM
( SELECT id , name1 , max(val) as max_val
FROM table
GROUP BY id, name1 ) x
GROUP BY name1
ORDER BY sum_max_val DESC

ich weiss allerdings nicht, ob MySQL schon nested tables beherrscht

 

[joschilein]

Da meine realen Datensätze etwas umfangreicher sind, als im vereinfachten Beispiel ganz oben, habe ich heute erstmal geprüft, was ich eigentlich als Ergebnis erwarten würde. Bisher hat nichts wirklich gepasst, auch das Rollup hat nicht so richtig weiter geholfen.

Ich habe nun zwei Varianten gefunden, die ich einfach als zwei Abfragen laufen lasse und das nicht noch krampfhaft in eine Abfrage quetsche.

Für den größten Wert pro id und name

SELECT     name1
              ,ROUND(SUM(val),2) AS sum
              ,COUNT(*) AS count
FROM        table AS A
WHERE      subid = (SELECT subid FROM table AS B WHERE A.id = B.id AND A.name1 = B.name1 ORDER BY val DESC LIMIT 1)
GROUP BY  name1
ORDER BY  sum DESC, name1 ASC

Für den größten Wert pro id (mit WHERE subid = 1 wäre es nur der erste, aber nicht zwangsweise der größte)

SELECT     name1
              ,ROUND(SUM(val),2) AS sum
              ,COUNT(*) AS count
FROM        table
WHERE      subid = (SELECT subid FROM table AS B WHERE A.id = B.id ORDER BY val DESC, id ASC LIMIT 1)
GROUP BY  name1
ORDER BY  sum DESC, name1 ASC