Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Fußball Blitztabelle

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von crz123, 13 September 2015.

  1. crz123

    crz123 Benutzer

    Hallo,
    ich stehe gerade vor einer großen Kopfnuss - ich hoffe hier kann mir jemand weiterhelfen.

    Ich möchte gern mit meinen Fußballergebnissen eine Blitztabelle darstellen.

    Folgende Datenstruktur ist vorhanden:

    Tabelle Spiele
    spiele_id | datum | tore_heim | tore_gast | mannschaft_heim | mannschaft_gast | beendet
    33236 | 2015-08-14T20:30:00 | 5 | 0 | 74 | 103 | 1
    33237 | 2015-08-15T15:30:00 | 2 | 2 | 23 | 71 | 1

    Tabelle Verein
    verein_id | name | liga_id
    74 | Hannover 96 | 1
    103 | Dortmund | 1
    71 | Bielefeld | 2
    23 | Köln | 2

    Wie bekomme ich es hin, dass aus den verschiedenen Ligen eine Blitztabelle entsteht mit folgenden Werten:

    - Platz, Name, Spiele, Siege, Unentschieden, Niederlagen, Tore, Differenz, Punkte

    Folgende Ansätze habe ich, jedoch möchte ich die gerne zusammenführen und nicht alles einzeln machen.


    SELECT SUM(totalGoals) Tore
    FROM
    (
    select sum(tore_heim) totalGoals from spiele WHERE beendet = 1 AND mannschaft_heim = 74
    UNION ALL
    select sum(tore_gast) totalGoals from spiele WHERE beendet = 1 AND mannschaft_gast = 74
    ) s;


    SELECT SUM(againstGoals) Gegentore
    FROM
    (
    select sum(tore_gast) againstGoals from spiele WHERE beendet = 1 AND mannschaft_heim = 74
    UNION ALL
    select sum(tore_heim) againstGoals from spiele WHERE beendet = 1 AND mannschaft_gast = 74
    ) s;


    select mannschaft_heim, mannschaft_gast, tore_heim, tore_gast,
    if((tore_heim > tore_gast),1,0) AS sieg_heim,
    if((tore_heim = tore_gast),1,0) AS remis,
    if((tore_heim < tore_gast),1,0) AS sieg_gast
    from spiele
    where beendet = 1 AND (mannschaft_heim = 74 OR mannschaft_gast = 74);

    Jemand eine Idee?

    Vielen Dank!
     
  2. ukulele

    ukulele Datenbank-Guru

    Hier eine MSSQL-Lösung:

    Code:
    SELECT    v.liga_id AS Liga,
            RANK() OVER (PARTITION BY v.liga_id ORDER BY t.Punkte) AS Rang,
            v.name AS Verein,
            t.Spiele,
            t.Siege,
            t.Unentschieden,
            t.Niederlagen,
            t.Tore,
            t.Differenz,
            t.Punkte
    FROM    (
    
    SELECT    mannschaft_heim,
            count(*) AS Spiele,
            sum(CASE WHEN tore_heim > tore_gast THEN 1 ELSE 0 END) AS Siege,
            sum(CASE WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Unentschieden,
            sum(CASE WHEN tore_heim < tore_gast THEN 1 ELSE 0 END) AS Niederlagen,
            sum(tore_heim) AS Tore,
            sum(tore_heim) - sum(tore_gast) AS Differenz,
            sum(CASE WHEN tore_heim > tore_gast THEN 3 WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Punkte
    FROM    (
    
    SELECT    tore_heim,
            tore_gast,
            mannschaft_heim,
            mannschaft_gast
    FROM    Spiele
    UNION ALL
    SELECT    tore_gast AS tore_heim,
            tore_heim AS tore_gast,
            mannschaft_gast AS mannschaft_heim,
            mannschaft_heim AS mannschaft_gast
    FROM    Spiele
    
            ) s
    GROUP BY mannschaft_heim
    
            ) t
    LEFT JOIN Verein v
    ON        t.mannschaft_heim = v.verein_id
    ORDER BY 1,2
    Bei RANK() OVER (PARTITION BY [...]) weiß ich nicht genau ob MySQL das kann. Wenn nicht musst du dir was basteln. Alles andere sollte aber auch MySQL können.
     
    crz123 gefällt das.
  3. akretschmer

    akretschmer Datenbank-Guru

    Nein.
     
    crz123 gefällt das.
  4. ukulele

    ukulele Datenbank-Guru

    Es müsste natürlich auch noch nach ORDER BY t.Punkte,t.Differenz,t.Tore, etc. gehen.
     
  5. crz123

    crz123 Benutzer

    Hallo Ukulele,
    vielen Dank für die Mühe und den Code!

    In wie weit muss ich den oberen Code anpassen, dass es für MySQL funktioniert?

    Code:
    RANK() OVER (PARTITION BY v.liga_id ORDER BY t.Punkte) AS Rang,
    Vielen lieben Dank nochmal!
     
  6. ukulele

    ukulele Datenbank-Guru

    Also wenn du die Zeile raus nimmst funktioniert er :) Damit hast du ja auch schonmal alle Basisinformationen.

    Eine Alternative könnte das hier sein:
    How to number rows in MySQL · Xaprb
    Aber ich habe kein MySQL zum testen.
     
  7. crz123

    crz123 Benutzer

    Angepasst habe ich es bereits und funktionieren tut es auch soweit so gut (vielen Dank nochmal dafür)

    Jetzt ist es nur so, dass der Rang falschherum ist - sprich die reale 1 ist jetzt z.B. der letzte Wert z.B. die 18

    Die Tabelle habe ich bereits nach Ihrem Status beendet gefiltert, um eine reale Tabelle zu erhalten (ansonsten wären die restlichen Termine die in der Zukunft stehen unentschieden).
    Für eine Blitztabelle müsste ich doch jetzt nur noch das Datum prüfen oder?

    WHERE beendet = 1 AND s.datum <= now () ?

    Code:
    SELECT    v.liga_id AS Liga,
            t.Punkte AS Rang,
            v.name AS Verein,
            t.Spiele,
            t.Siege,
            t.Unentschieden,
            t.Niederlagen,
            t.Tore,
            t.Differenz,
            t.Punkte
    FROM    (
    
    SELECT    mannschaft_heim,
            count(*) AS Spiele,
            sum(CASE WHEN tore_heim > tore_gast THEN 1 ELSE 0 END) AS Siege,
            sum(CASE WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Unentschieden,
            sum(CASE WHEN tore_heim < tore_gast THEN 1 ELSE 0 END) AS Niederlagen,
            sum(tore_heim) AS Tore,
            sum(tore_heim) - sum(tore_gast) AS Differenz,
            sum(CASE WHEN tore_heim > tore_gast THEN 3 WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Punkte
    FROM  (
    
    SELECT  tore_heim,
            tore_gast,
            mannschaft_heim,
            mannschaft_gast
    FROM    spiele
    WHERE beendet = 1
    UNION ALL
    SELECT    tore_gast AS tore_heim,
            tore_heim AS tore_gast,
            mannschaft_gast AS mannschaft_heim,
            mannschaft_heim AS mannschaft_gast
    FROM    spiele
    WHERE beendet = 1
            ) s
    GROUP BY mannschaft_heim
            ) t
    LEFT JOIN verein v
    ON        t.mannschaft_heim = v.verein_id
    ORDER BY 1,2;
     
  8. ukulele

    ukulele Datenbank-Guru

    Also bei ORDER BY kannst du mit ASC und DESC bestimmen, wie der jeweilige Wert (hinter dem ASC oder DESC geschrieben wird) sortiert werden soll. ASC ist default, muss also nicht angegeben werden.

    Ich denke mal beendet steht erst auf 1, wenn das Spiel auch zuende ist. Für eine Blitztabelle musst du also beendet = 1 OR datum <= now() nehmen.
     
  9. crz123

    crz123 Benutzer

    Hallo,
    ich bin immer noch am kniffeln - vllt hast Du ja noch einen Tipp.

    Die Tabelle sieht an sich schon sehr gut aus - jetzt gibt es nur 2x Kleinigkeiten.

    1. Er sortiert noch falsch, da er die Differenz nicht beachtet. Kann man dazu noch einen weiteren ORDER BY einbauen?
    2. In der 2. Bundesliga hat sich Sandhausen -3 Punkte eingefangen, kann man das mit einem CASE eventuell nachbilden?

    Code:
    SELECT  
                        v.liga_id AS Liga,
                        t.Punkte AS Rang,
                        v.name AS Verein,
                        t.Spiele,
                        t.Siege,
                        t.Unentschieden,
                        t.Niederlagen,
                        t.Tore,
                        t.Differenz,
                        t.Punkte
                FROM    (
    
                SELECT    mannschaft_heim,
                        count(*) AS Spiele,
                        sum(CASE WHEN tore_heim > tore_gast THEN 1 ELSE 0 END) AS Siege,
                        sum(CASE WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Unentschieden,
                        sum(CASE WHEN tore_heim < tore_gast THEN 1 ELSE 0 END) AS Niederlagen,
                        sum(tore_heim) AS Tore,
                        sum(tore_heim) - sum(tore_gast) AS Differenz,
                        sum(CASE WHEN tore_heim > tore_gast THEN 3 WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Punkte
                FROM  (
                SELECT    tore_heim,
                        tore_gast,
                        mannschaft_heim,
                        mannschaft_gast
                FROM    spiele
                WHERE beendet = 1 OR datum <= now()
                UNION ALL
                SELECT    tore_gast AS tore_heim,
                        tore_heim AS tore_gast,
                        mannschaft_gast AS mannschaft_heim,
                        mannschaft_heim AS mannschaft_gast
                FROM    spiele
                WHERE beendet = 1 OR datum <= now()
                        ) s
                GROUP BY mannschaft_heim
                        ) t
                LEFT JOIN verein v
                ON        t.mannschaft_heim = v.verein_id
                ORDER BY 1,2 DESC;
    Bild zur Tabelle:
    Bilder-Upload - Kostenlos Fotos hochladen und ins Netz stellen

    Vielen Dank!
     
  10. ukulele

    ukulele Datenbank-Guru

    Du brauchst nur ein ORDER BY, alle Spalten nach denen sortiert werden soll werden in der entsprechenden Reheinfolge angegeben. Also z.B. v.liga_id, t.Punkte DESC, t.Differenz, t.Tore, etc.

    Wenn es sich bei den -3 Punkten um Strafpunkte handelt solltest du das schon sauber im Tabellenmodell abbilden und nicht ins Select einbauen.
     
  11. crz123

    crz123 Benutzer

    Hallo Ukulele,
    vielen Dank für Deinen Hinweiß

    Bei den -3 Punkte handelt es sich um Strafpunkte, da die Berechnung der Blitztabelle an sich aber über die Tabelle Spiele läuft, fehlt mir gerade der Ansatz zum Einbau der Strafpunkte im Tabellenmodell.
    Würde jetzt ungern eine weitere Tabelle für diesen einen Fall anlegen. Hast Du da vllt. einen praktischen Tipp oder Ansatz für?

    Tabelle Spiele
    spiele_id | datum | tore_heim | tore_gast | mannschaft_heim | mannschaft_gast | beendet
    33236 | 2015-08-14T20:30:00 | 5 | 0 | 74 | 103 | 1
    33237 | 2015-08-15T15:30:00 | 2 | 2 | 23 | 71 | 1
     
  12. ukulele

    ukulele Datenbank-Guru

    Also theoretisch ginge das in der Tabelle Verein mit einer Spalte Strafpunkte. Das würde die Strafpunkte allerdings für jede Session berücksichtigen. Wie unterscheidest du denn überhaupt eine neue Session? Derzeit liegen der Wertung alle abgeschlossenen Spiele zugrunde und der Verein ist genau einer Liga zugeordnet, was sich auch ändern kann.
     
  13. crz123

    crz123 Benutzer

    Es wird keine Historie geben - sofern die Saison vorbei ist sind die Spieldaten nicht mehr vorhanden. Das selbe geschieht auch beim Verein.

    Das ganze läuft automatisiert über eine Schnittstelle ab.
     
  14. ukulele

    ukulele Datenbank-Guru

    Dann wäre es einfach: Die Tabelle Verein bekommt eine Spalte Strafpunkte und im äußersten Select ergänzt du t.Punkte - isnull(v.Strafpunkte,0) AS Punkte. Die Funktion zum Abfangen von NULL-Werten heißt glaube ich bei MySQL anders.
     
  15. crz123

    crz123 Benutzer

    Ich habe der Tabelle Spiele jetzt eine Spalte mit Strafpunkten gegeben und bei Sandhauen 3 eingetragen, der Rest ist default 0.

    Jetzt hatte ich mir gedacht, dass das doch so keine Abfrage mehr benötigt (function isnull) sondern nur noch minus gerechnet werden muss.
    Das funktioniert auch soweit, nur ändert sich leider die Platzierung nicht von Sandhausen :-(

    Habe ich im falschen SELECT gearbeitet?

    Code:
    SELECT   
                        v.liga_id AS Liga,
                        t.Punkte AS Rang,
                        v.name AS Verein,
                        t.Spiele,
                        t.Siege,
                        t.Unentschieden,
                        t.Niederlagen,
                        t.Tore,
                        t.Differenz,
                        t.Punkte - v.Strafpunkte
                FROM    (
    
                SELECT    mannschaft_heim,
                        count(*) AS Spiele,
                        sum(CASE WHEN tore_heim > tore_gast THEN 1 ELSE 0 END) AS Siege,
                        sum(CASE WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Unentschieden,
                        sum(CASE WHEN tore_heim < tore_gast THEN 1 ELSE 0 END) AS Niederlagen,
                        sum(tore_heim) AS Tore,
                        sum(tore_heim) - sum(tore_gast) AS Differenz,
                        sum(CASE WHEN tore_heim > tore_gast THEN 3 WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Punkte
                FROM  (
                SELECT    tore_heim,
                        tore_gast,
                        mannschaft_heim,
                        mannschaft_gast
                FROM    spiele
                WHERE beendet = 1 OR datum <= now()
                UNION ALL
                SELECT    tore_gast AS tore_heim,
                        tore_heim AS tore_gast,
                        mannschaft_gast AS mannschaft_heim,
                        mannschaft_heim AS mannschaft_gast
                FROM    spiele
                WHERE beendet = 1 OR datum <= now()
                        ) s
                GROUP BY mannschaft_heim
                        ) t
                LEFT JOIN verein v
                ON        t.mannschaft_heim = v.verein_id
                ORDER BY v.liga_id, t.Punkte DESC, t.Differenz DESC, t.Tore DESC;
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden