MySQL: SUM der jeweils besten 3

[Fabienne]

Guten Morgen,

ich stehe vor folgendem Thema: Sportveranstaltung mit Ergbnissen jeden Sportlers, aber nur die besten 3 von 5 Ergebnissen sollen in seine Gesamtsumme einbezogen werden. (sinngemäß):

SELECT SUM(einzelergebnis) AS gesamtergebnis 
FROM table GROUP BY sportler_id
ORDER BY gesamtergebnis DESC

Nun sollten in diese Summe aber jeweils nur die ersten 3 Einzelergebnisse einbezogen werden. Subselect? HAVING? Bin da nicht so firm…

Besten Dank für einen Tipp Fabe

[dedlfix]

Tach!

Nun sollten in diese Summe aber jeweils nur die ersten 3 Einzelergebnisse einbezogen werden. Subselect? HAVING? Bin da nicht so firm…

Ich meine, das lässt sich gar nicht (so einfach) lösen. Bei Bedingungen wird jeder Datensatz einzeln betrachtet. Was davor war oder danach kommt, kann nicht berücksichtigt werden. Fällt dir ein Kriterium ein, wie du anhand des Datensatzes selbst entscheiden kannst, ob er genommen werden soll?

Gruppieren hilft da auch nicht, weil das aus mehreren Datensätzen nur einen einzelnen erzeugt. Having hift auch nicht, weil es nicht vor- und zurückschauen kann.

Man könnte über User-Defined Variables nachdenken, wenn dir dazu einfällt, was man von einem Datensatz speichern müsste, um beim nächsten feststellen zu können, ob er noch genommen werden soll oder nicht. Beachte dazu die Abarbeitungsreihenfolge von SQL-Statements. Die Klauseln werden von links nach rechts in Notationsreihenfolge ausgewertet mit Ausnahme der Feldliste nach dem SELECT, die zwischen GROUP BY und HAVING berechnet wird. Das heißt, im WHERE kann man sich noch nicht darauf beziehen, was erst in der SELECT-Klausel berechnet wird, im HAVING aber schon.

Eine weitere Idee ist, mit GROUP_CONCAT() die IDs der Datensätze einer Gruppe zu einem String zusammenzufassen, sie dann mit Stringverarbeitung auf drei Elemente zu kürzen und den Rest einem IN() zu übergeben. Wobei ich mir da gar nicht mal sicher bin, ob man dem IN() überhaupt den kommaseparierten String schackhaft machen kann. Falls ja, wäre das eine Lösung mit ein paar verschachtelten Subselects.

Und es bleibt immer noch eine Stored Procedure als Option, besonders dann, wenn viele Datensätze nicht genommen werden sollen, die sich nicht anderweitig rausfilten lassen. Wenn es nur 3 von 5 sind, dann kann man die zwei überflüssigen ruhig auch mit abfragen und im Script aussortieren. Die Technik dazu hört in beiden Fällen auf den Namen Gruppenwechsel: man iteriert über die Daten, merkt sich die Daten des vorhergehenden Datensatzes, stellt im Vergleich zum aktuellen fest, ob eine neue Gruppe beginnt, und zählt dann mit, dass nach drei Datensätzen der Rest ignoriert wird, bis eine neue Gruppe beginnt.

dedlfix.

[Fabienne]

Hi again,

vielen Dank für Deine Antwort. Dann lag ich mit meinem Problem ja gar nicht so falsch, dass das ziemlich "tricky" ist.

Idee: Das ist das "Endergebnis" (Summe):

SELECT SUM(einzelergebnis) AS gesamtergebnis 
FROM table GROUP BY sportler_id
ORDER BY gesamtergebnis DESC

Nun ist ja der Inhalt der "table" eigentlich eine Teilmenge, sprich ein Subselect. "Hole alle Daten aus der table a, wobei nur diejenigen ergebnis-Datensätze genommen werden sollen, welche die gleiche Sportler_id haben, sortiert nach einzelergebnise und nur die ersten 3:

SELECT * FROM  table a WHERE ergebnis_id=(SELECT b.einzelergebnis_id FROM table b WHERE b.sportler_id=a.sportler_id ORDER BY b.einzeleregbnise DESC LIMIT 0,3)`

Und das wiederum eingepackt in den ersten SUM-Query:

SELECT SUM(c.einzelergebnis) AS gesamtergebnis 
FROM (SELECT * FROM  table a WHERE ergebnis_id=(SELECT b.einzelergebnis_id FROM table b WHERE b.sportler_id=a.sportler_id ORDER BY b.einzeleregbnise DESC LIMIT 0,3)
)c GROUP BY c.sportler_id
ORDER BY gesamtergebnis DESC

Oder wäre sogar ein JOIN richtiger, mit dem ich jeweils die 3 ersten Datensätze an den "ersten Datensatz" anhänge und dann via GROUP die richtige Summe ziehen kann?

Ist sowas denkbar?

Beste Dank Fabienne

[dedlfix]

Tach!

vielen Dank für Deine Antwort. Dann lag ich mit meinem Problem ja gar nicht so falsch, dass das ziemlich "tricky" ist.

Idee: Das ist das "Endergebnis" (Summe): SELECT SUM(einzelergebnis) AS gesamtergebnis FROM table GROUP BY sportler_id ORDER BY gesamtergebnis DESC

Nun ist ja der Inhalt der "table" eigentlich eine Teilmenge, sprich ein Subselect. "Hole alle Daten aus der table a, wobei nur diejenigen ergebnis-Datensätze genommen werden sollen, welche die gleiche Sportler_id haben, sortiert nach einzelergebnise und nur die ersten 3: SELECT * FROM table a WHERE ergebnis_id=(SELECT b.einzelergebnis_id FROM table b WHERE b.sportler_id=a.sportler_id ORDER BY b.einzeleregbnise DESC LIMIT 0,3)

Wohl eher nicht. Du erzeugst damit zu jeder Zeile der Tabelle - also zu jedem Ergebnis - eins bis drei weitere Ergebniszeilen. Du möchtest aber nur zu jedem Sportler drei haben, soweit ich das verstanden habe.

Oder wäre sogar ein JOIN richtiger, mit dem ich jeweils die 3 ersten Datensätze an den "ersten Datensatz" anhänge und dann via GROUP die richtige Summe ziehen kann?

Ist sowas denkbar?

Das schöne an Subquerys ist, dass man mit relativ wenig Aufwand Hauptquery und Subquery trennen und unabhängig voneinander laufen lassen kann. Man kann damit recht unkompliziert die einzelnen Ergebnismengen zwecks Überprüfung ermitteln lassen. Bei Joins ist das nicht so einfach, weil sie keine abgeschlossene syntaktische Einheit bilden. Du kannst also mit den Subquerys recht gut prüfen, ob deine Ideen zielführend sind.

dedlfix.

[Fabienne]

Hi,

ja, nur die maximal drei besten Ergebnisse (es könnten ja auch weniger sein, wenn ein Sportler an weniger Wettkämfen teilgenommen hat).

Nochmals etwas grafisch

table_id | sportler_id | wettkampf_id | einzelergebnis 1 | 23 | 45 | 35.6 2 | 23 | 46 | 45.4 3 | 23 | 47 | 65.3 4 | 23 | 48 | 48.1 5 | 24 | 45 | 22.2 6 | 24 | 46 | 27.3 7 | 25 | 45 | 2.1 8 | 25 | 46 | 7.3 9 | 25 | 47 | 2.2 10 | 25 | 49 | 7.4

Ergebnis sollte sein: Zähle von jedem Sportler die besten 3 Ergebnisse zusammen und sortiere nach Gesamtergebnis Heißt hier: (habe es nicht ausgerechnet, nur geschätzt) Sportler:

23 | 65.3+48.1+45.4 | =150.99 | GesamtPlatz 1 24 | 22.2+27.3 | =50.2 | GesamtPlatz 2 25 | 7.4+7.3+2.2 | =16.9 | GesamtPlatz 3

Das Ziel soll sein aus der Ergebnisliste die Gesamtplatzierung zu berechnen, aber es zählen eben immer nur maximal die besten 3 Ergebnisse zum Gesamtergebnis dazu....

Any ideas? Grüße Fabe

[dedlfix]

Tach!

ja, nur die maximal drei besten Ergebnisse (es könnten ja auch weniger sein, wenn ein Sportler an weniger Wettkämfen teilgenommen hat).

Der Punkt ist, dass du die Ergebnisse vom jeweiligen Sportler haben möchtest, und nicht von jeder einzelnen Ergebniszeile. Deswegen muss zumindest ein Teil der Query sich auf die Datensätze der Sportler beziehen. Nur darüber kannst du die Teilmengen ihrer Ergebnisse ermittel und auf 3 zusammenkürzen.

Datenbankabfragen arbeiten mengenorientiert. Du kannst nur schlecht aus der Menge aller Ergebnisse drei pro Sportler ermitteln, aber du kannst recht einfach die Menge der Ergebnisse eines Sportlers ermitteln und davon drei nehmen. Wenn du diesen Teilaspekt für einen Spieler ermittelt hast, kannst du das sicher auch recht einfach auf alle ausweiten.

dedlfix.

[Rolf B]

Hallo dedlfix,

Wenn du diesen Teilaspekt für einen Spieler ermittelt hast, kannst du das sicher auch recht einfach auf alle ausweiten.

Ja, habe ich auch geglaubt. Ich krieg's aber mit meinem lokalen MySQL 5.6 (welches ich für bestimmte Tests brauche) überhaupt nicht hin. Er lehnt mir Dinge ab, von denen ich erwartet hätte, dass sie gehen müssten.

Rolf

[Fabienne]

Hi again,

habe eben in einem anderen Forum folgendes gefunden: Dort sollen immer die TOP 3 der Postings je Kategorie angezeigt werden:

SELECT
    p1.postId,
    p1.catId
FROM
    postcategory p1
JOIN postcategory p2 ON p1.catId = p2.catId
AND p2.postId >= p1.postId
GROUP BY
    p1.postId,
    p1.catId
HAVING
    COUNT(*) <= 3
ORDER BY
    catId,
    postId

Problem dabei: ich bräuchte ja nochmals eine übergeordnete GROUP Funktion.

? Ratlos.

[Matthias Apsel]

Hallo Fabienne,

Du kannst Code als Code auszeichnen. Oberhalb des Eingabefeldes gibt es einen Button dafür. Oder du schreibst

 ~~~sql
   sql-code

Bis demnächst  
Matthias

-- 
Pantoffeltierchen haben keine Hobbys.  
¯\\\_(ツ)\_/¯

[Rolf B]

Hallo Fabienne,

ja, das ist das "Ranking für Arme". Du zählst pro Sportler und Ergebnis, wieviele Ergebnisse gleich oder schlechter diesem Ergebnis sind. Der Count entspricht dann dem Rang.

Passe diese Query mal für deinen Bedarf an, so dass sie genau die Zeilen liefert, die Du erwartest, und dann legst Du einen SELECT drumherum, der pro Spieler-ID die Ergebnisse aufsummiert.

Man kann nämlich das Ergebnis einer Query immer als Eingabe eines FROM verwenden, man muss es nur in Klammern setzten und einen Aliasnamen (correlation name) vergeben.

SELECT bla, blub from hui WHERE dings=bums

kann so eingesetzt werden:

SELECT bla, SUM(blub)
FROM (SELECT bla, blub from hui WHERE dings=bums)
GROUP BY bla

Außer mit einem LEFT JOIN kann man diese Technik auch durch einen Subselect realisieren:

SELECT sportler_id,
       einzelergebnis,
       (select count(*) from ergebnisse f 
           where f.sportler_id=e.sportler_id 
             and f.einzelergebnis <= e.einzelergebnis) as rang
FROM   ergebnisse e

Und da kannst Du dann den gruppierenden Select drumherumlegen und nur die Sätze mit Rang 1-3 betrachten.

Eine Lösung ohne diese Ranking-Technik gibt es meines Wissens nach erst am MySQL 8 mit der RANK() OVER PARTITION Funktion.

Eine ganz andere Lösung würde darin bestehen, die Aufgabe nicht im SQL zu lösen sondern in dem System, das die Query-Ergebnisse verarbeitet. Womit geschieht das?

Oder sind wir hier akademisch unterwegs und haben gerade deine Info-Hausaufgaben bearbeitet?

Rolf

[Fabienne]

Hi nochmals,

nein, keine IT-Hausi… sondern für den Sportverein, der bis dato alles papier-händisch macht… Ich teste das heute und geben Rückmeldung.

Besten Dank Fabe

[Rolf B]

Hallo Fabienne,

welche MySQL Version verwendest Du? Wenn es Version 8 ist, gibt es die Window-Funktionen, die Dir das Leben hier sehr erleichtern

Ohne Window-Funktionen bräuchtest du "Ranking für Arme", was aus einem relativ ineffizienten Left Join besteht.

Rolf

[Fabienne]

Hi, leider "nur" die 5.0.12 Beste Grüße Fabe

[Rolf B]

Hallo Fabienne,

urghx - das ist ja antik (von 2005). Keine Chance für ein Update?

Aber das "Ranking für Arme", was Du schon selbst entdeckt hast, sollte damit funktionieren.

Rolf