MySQL: SUM der jeweils besten 3 – SELFHTML-Forum Forum als Ergänzung zum SELFHTML-Wiki und zur Dokumentation SELFHTML https://forum.selfhtml.org/self MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 06:44:22 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744555#m1744555 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744555#m1744555 <p>Guten Morgen,</p> <p>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äß):</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>einzelergebnis<span class="token punctuation">)</span> <span class="token keyword">AS</span> gesamtergebnis <span class="token keyword">FROM</span> <span class="token keyword">table</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> sportler_id <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> gesamtergebnis <span class="token keyword">DESC</span> </code></pre> <p>Nun sollten in diese Summe aber jeweils nur die ersten 3 Einzelergebnisse einbezogen werden. Subselect? HAVING? Bin da nicht so firm…</p> <p>Besten Dank für einen Tipp Fabe</p> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 08:02:13 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744560#m1744560 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744560#m1744560 <p>Tach!</p> <blockquote> <p>Nun sollten in diese Summe aber jeweils nur die ersten 3 Einzelergebnisse einbezogen werden. Subselect? HAVING? Bin da nicht so firm…</p> </blockquote> <p>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?</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>dedlfix.</p> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 11:34:04 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744589#m1744589 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744589#m1744589 <p>Hallo Fabienne,</p> <p>welche MySQL Version verwendest Du? Wenn es Version 8 ist, gibt es die Window-Funktionen, die Dir das Leben hier sehr erleichtern</p> <p>Ohne Window-Funktionen bräuchtest du "Ranking für Arme", was aus einem relativ ineffizienten Left Join besteht.</p> <p><em>Rolf</em></p> <div class="signature">-- <br> sumpsi - posui - clusi </div> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 10:27:54 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744581#m1744581 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744581#m1744581 <p>Hi again,</p> <p>vielen Dank für Deine Antwort. Dann lag ich mit meinem Problem ja gar nicht so falsch, dass das ziemlich "tricky" ist.</p> <p>Idee: Das ist das "Endergebnis" (Summe):</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>einzelergebnis<span class="token punctuation">)</span> <span class="token keyword">AS</span> gesamtergebnis <span class="token keyword">FROM</span> <span class="token keyword">table</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> sportler_id <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> gesamtergebnis <span class="token keyword">DESC</span> </code></pre> <p>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:</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">table</span> a <span class="token keyword">WHERE</span> ergebnis_id<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> b<span class="token punctuation">.</span>einzelergebnis_id <span class="token keyword">FROM</span> <span class="token keyword">table</span> b <span class="token keyword">WHERE</span> b<span class="token punctuation">.</span>sportler_id<span class="token operator">=</span>a<span class="token punctuation">.</span>sportler_id <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> b<span class="token punctuation">.</span>einzeleregbnise <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">`</span> </code></pre> <p>Und das wiederum eingepackt in den ersten SUM-Query:</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>c<span class="token punctuation">.</span>einzelergebnis<span class="token punctuation">)</span> <span class="token keyword">AS</span> gesamtergebnis <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">table</span> a <span class="token keyword">WHERE</span> ergebnis_id<span class="token operator">=</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> b<span class="token punctuation">.</span>einzelergebnis_id <span class="token keyword">FROM</span> <span class="token keyword">table</span> b <span class="token keyword">WHERE</span> b<span class="token punctuation">.</span>sportler_id<span class="token operator">=</span>a<span class="token punctuation">.</span>sportler_id <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> b<span class="token punctuation">.</span>einzeleregbnise <span class="token keyword">DESC</span> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token punctuation">)</span>c <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c<span class="token punctuation">.</span>sportler_id <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> gesamtergebnis <span class="token keyword">DESC</span> </code></pre> <p>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?</p> <p>Ist sowas denkbar?</p> <p>Beste Dank Fabienne</p> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 10:51:16 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744585#m1744585 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744585#m1744585 <p>Tach!</p> <blockquote> <p>vielen Dank für Deine Antwort. Dann lag ich mit meinem Problem ja gar nicht so falsch, dass das ziemlich "tricky" ist.</p> <p>Idee: Das ist das "Endergebnis" (Summe): SELECT SUM(einzelergebnis) AS gesamtergebnis FROM table GROUP BY sportler_id ORDER BY gesamtergebnis DESC</p> <p>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)</p> </blockquote> <p>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.</p> <blockquote> <p>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?</p> <p>Ist sowas denkbar?</p> </blockquote> <p>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.</p> <p>dedlfix.</p> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 11:04:05 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744587#m1744587 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744587#m1744587 <p>Hi,</p> <p>ja, nur die maximal drei besten Ergebnisse (es könnten ja auch weniger sein, wenn ein Sportler an weniger Wettkämfen teilgenommen hat).</p> <p>Nochmals etwas grafisch</p> <p>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</p> <p>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:</p> <p>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</p> <p>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....</p> <p>Any ideas? Grüße Fabe</p> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 11:14:49 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744588#m1744588 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744588#m1744588 <p>Tach!</p> <blockquote> <p>ja, nur die maximal drei besten Ergebnisse (es könnten ja auch weniger sein, wenn ein Sportler an weniger Wettkämfen teilgenommen hat).</p> </blockquote> <p>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.</p> <p>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.</p> <p>dedlfix.</p> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 11:36:23 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744590#m1744590 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744590#m1744590 <p>Hallo dedlfix,</p> <blockquote> <p>Wenn du diesen Teilaspekt für einen Spieler ermittelt hast, kannst du das sicher auch recht einfach auf alle ausweiten.</p> </blockquote> <p>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.</p> <p><em>Rolf</em></p> <div class="signature">-- <br> sumpsi - posui - clusi </div> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 11:46:37 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744592#m1744592 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744592#m1744592 <p>Hi, leider "nur" die 5.0.12 Beste Grüße Fabe</p> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 11:48:35 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744593#m1744593 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744593#m1744593 <p>Hi again,</p> <p>habe eben in einem anderen Forum folgendes gefunden: Dort sollen immer die TOP 3 der Postings je Kategorie angezeigt werden:</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> p1<span class="token punctuation">.</span>postId<span class="token punctuation">,</span> p1<span class="token punctuation">.</span>catId <span class="token keyword">FROM</span> postcategory p1 <span class="token keyword">JOIN</span> postcategory p2 <span class="token keyword">ON</span> p1<span class="token punctuation">.</span>catId <span class="token operator">=</span> p2<span class="token punctuation">.</span>catId <span class="token operator">AND</span> p2<span class="token punctuation">.</span>postId <span class="token operator">>=</span> p1<span class="token punctuation">.</span>postId <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> p1<span class="token punctuation">.</span>postId<span class="token punctuation">,</span> p1<span class="token punctuation">.</span>catId <span class="token keyword">HAVING</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator"><=</span> <span class="token number">3</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> catId<span class="token punctuation">,</span> postId </code></pre> <p>Problem dabei: ich bräuchte ja nochmals eine übergeordnete GROUP Funktion.</p> <p>? Ratlos.</p> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 12:09:10 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744598#m1744598 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744598#m1744598 <p>Hallo Fabienne,</p> <p>urghx - das ist ja antik (von 2005). Keine Chance für ein Update?</p> <p>Aber das "Ranking für Arme", was Du schon <a href="/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744593#m1744593" rel="noopener noreferrer">selbst entdeckt hast</a>, sollte damit funktionieren.</p> <p><em>Rolf</em></p> <div class="signature">-- <br> sumpsi - posui - clusi </div> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 11:51:20 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744594#m1744594 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744594#m1744594 <p>Hallo Fabienne,</p> <p>Du kannst Code als Code auszeichnen. Oberhalb des Eingabefeldes gibt es einen Button dafür. Oder du schreibst</p> <pre><code class="block"> ~~~sql sql-code </code></pre> <pre><code class="block"> Bis demnächst Matthias -- Pantoffeltierchen haben keine Hobbys. ¯\\\_(ツ)\_/¯ </code></pre> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 12:06:33 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744597#m1744597 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744597#m1744597 <p>Hallo Fabienne,</p> <p>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.</p> <p>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.</p> <p>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.</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> bla<span class="token punctuation">,</span> blub <span class="token keyword">from</span> hui <span class="token keyword">WHERE</span> dings<span class="token operator">=</span>bums </code></pre> <p>kann so eingesetzt werden:</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> bla<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>blub<span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> bla<span class="token punctuation">,</span> blub <span class="token keyword">from</span> hui <span class="token keyword">WHERE</span> dings<span class="token operator">=</span>bums<span class="token punctuation">)</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> bla </code></pre> <p>Außer mit einem LEFT JOIN kann man diese Technik auch durch einen Subselect realisieren:</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> sportler_id<span class="token punctuation">,</span> einzelergebnis<span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword">select</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">from</span> ergebnisse f <span class="token keyword">where</span> f<span class="token punctuation">.</span>sportler_id<span class="token operator">=</span>e<span class="token punctuation">.</span>sportler_id <span class="token operator">and</span> f<span class="token punctuation">.</span>einzelergebnis <span class="token operator"><=</span> e<span class="token punctuation">.</span>einzelergebnis<span class="token punctuation">)</span> <span class="token keyword">as</span> rang <span class="token keyword">FROM</span> ergebnisse e </code></pre> <p>Und da kannst Du dann den gruppierenden Select drumherumlegen und nur die Sätze mit Rang 1-3 betrachten.</p> <p>Eine Lösung ohne diese Ranking-Technik gibt es meines Wissens nach erst am MySQL 8 mit der RANK() OVER PARTITION Funktion.</p> <p>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?</p> <p>Oder sind wir hier akademisch unterwegs und haben gerade deine Info-Hausaufgaben bearbeitet?</p> <p><em>Rolf</em></p> <div class="signature">-- <br> sumpsi - posui - clusi </div> MySQL: SUM der jeweils besten 3 Sun, 17 Mar 19 12:29:35 Z https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744599#m1744599 https://forum.selfhtml.org/self/2019/mar/17/mysql-sum-der-jeweils-besten-3/1744599#m1744599 <p>Hi nochmals,</p> <p>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.</p> <p>Besten Dank Fabe</p>