Die 3 höchsten Ergebnisse addieren

[PHP_Dude]

Hallo zusammen,

ich schreibe gerade an einer Spielstatistik und stoße dabei auf meine SQL-Grenzen.

Die 3 besten Ergebnisse eines Spielers sollen addiert und zusammen mit der Anzahl der gespielten Spiele und dem Datum des letzten Spiels in einer Ergebnisliste gespeichert werden.
Das Ganze wird immer Monatsweise in einem gewissen Zeitraum und pro Spielgruppe berechnet.

Die Tabellen:

[user]
userid(INT),groupid(INT),vorname(VARCHAR),nachname(VARCHAR)

[groups]
groupid(INT),groupname(VARCHAR)

[games]
zeit(DATETIME),userid(INT),score(INT)

[periods]
pid(INT),startzeit(DATETIME),endzeit(DATETIME)

Da ich noch keine Erfahrung mit stored procedures habe und ich momentan keine Zeit habe mir das einzuverleiben wollte ich das Ganze mit einer SQL-Abfrage machen.
Jedoch will das einfach nicht so richtig funktionieren deshalb habe ich das Ganze mal in einzelne Schritte aufgeteilt:

Zuerst schreibe ich die aktuellen eines Monats in eine Tabelle:

[_gametmp]
zeit(DATETIME,userid(INT),score(INT)

INSERT INTO _gametmp (zeit,userid,score)
  SELECT games.zeit,games.userid,games.score
  FROM games WHERE games.zeit BETWEEN
  ( SELECT periods.startzeit FROM periods WHERE NOW() BETWEEN periods.startzeit AND periods.endzeit )
  AND
  ( SELECT periods.endzeit FROM periods WHERE NOW() BETWEEN periods.startzeit AND periods.endzeit )

Dann möchte ich alle oben genannten Daten in eine weitere Tabelle speichern:

[_usertmp]
userid(INT),games(INT),score(INT),lastgame(DATETIME)

Das habe ich schon mit folgendem versucht:

INSERT INTO _usertmp (userid,games,score,lastgame)
  SELECT _gametmp.userid AS aktid,
    (SELECT COUNT( _gametmp.score ) FROM _gametmp WHERE _gametmp.userid=aktid) as games,
    (SELECT SUM( _gametmp.score ) FROM _gametmp WHERE _gametmp.userid=aktid ORDER BY _gametmp.score LIMIT 3) AS score,
    MAX(_gametmp.zeit) AS lastgame
  FROM _gametmp
  GROUP BY _gametmp.userid

Er ignoriert das ORDER BY und LIMIT beim score und zählt alle Spiele zusammen, was mir auch klar ist.

INSERT INTO _usertmp (userid,games,score,lastgame)
  SELECT _gametmp.userid AS aktid,
    (SELECT COUNT( _gametmp.score ) FROM _gametmp WHERE _gametmp.userid=aktid) as games,
    (SELECT SUM(tmp.score) FROM
      (SELECT _gametmp.score FROM _gametmp WHERE _gametmp.userid=aktid ORDER BY _gametmp.score DESC LIMIT 0,3) as tmp) AS score,
    MAX(_gametmp.zeit) AS lastgame
  FROM _gametmp
  GROUP BY _gametmp.uid

Die Fehlermeldung "#1054 - Unknown column 'aktid' in 'where clause'" sagt mir, das er den Alias "aktid" nicht im Sub-SubSelect kennt, obwohl in der Mysql Dokumentation steht das die Datenbank rekursive nach Außen sucht.

Hier nun meine Fragen zu den Lösungsansätzen:

1. Kann ich den Alias irgendwie in den Sub-SubSelect übernehmen?
2. Oder kann ich sonst irgendwie die 3 besten Ergebnisse addieren?
3. Wie würde die SQL-Abfrage aussehen wenn ich gleich nur die 3 besten Spiele in die _gametmp schreiben will?
4. Geht das Ganze auch in einer Abfrage?
5. Ist das auch mit stored procedures möglich und wäre das sinnvoll?

Hoffentlich habe ich nicht einfach nur ne Kleinigkeit übersehn, weil ich probiere schon seit 2 Tagen dieses Problem zu lösen ;)

Gruß
Roland

[Norbert]

Hallo Roland,

am besten Du machst mal einen Dump von Deiner Datenbank, loeschst alles, was wir nicht wissen duerfen, also hoffentlich sehr viel. Und stellst den Rest als Zip-Datei ins Netz. Dann wird sich der eine oder andere das genauer ansehen und sicher schnell einen Loesungsweg vorschlagen koennen.

Z.B.
Du laesst nach allen Spielen eines Users im letzten Monat suchen, sortierst selbige nach dem Score und Limitierst die Ergebnisse auf 3, wobei Du sie in einer extra Spalte gleich summierst. Anzahl der Spiele und letztes Datum sind dabei Nebenergebnisse. Diesen Select steckst Du dann in die Tabelle fuer die Monatsauswertung und gut ists. Man muss diesen Insert natuerlich ueber alle Spieler iterieren, wozu ich dann PHP verwenden wuerde.

Genaueres aber erst nach den Beispieldaten, siehe ganz oben.

Nette Gruesse
Norbert

[PHP_Dude]

Hallo Norbert,

die Zipdatei mit dem Dump und einem Beispielskript findest Du hier.

Ich möchte so viel wie möglich in der Datenbank machen, aber wenn es nicht anders geht dann muss ich wohl die Ergebnisse mit einer php-Schleife in die Tabelle schreiben.

Gruß
Roland

[Norbert]

Hallo Dude,

hat zwar ein bissel gedauert, aber nun ist es fertig:

1. das Summieren braucht ORDER BY und LIMIT, liefert aber trotzdem ein Array-Result,
   aus dem man sich den Wert rauspicken muss.
2. Spieleanzahl und letztes Spieldatum koennen mit einem Query ermittelt werden.
3. da Du sicher einen anderen DB-Layer verwendest, musst Du Dir diese Zeilen erst anpassen.
4. was Du von den Ergebnissen in die Zusammenfassung(Statistik) uebernimmst steht noch voellig frei.

<?php  
  
function ergview($EG) {  
    ?><table cellpadding="1"  cellspacing="0" bordercolor="silver" border="1"><?= "\n";  
    if (is_array($EG)) {  
        foreach($EG as $nr => $AA) {  
            ?><tr><?  
            if (is_array($AA)) {  
                foreach($AA as $val) {  
                    echo ($nr > 0) ? '<td' : '<th';  
                    ?> align="right">&nbsp;<?= $val; ?>&nbsp;</<?  
                    echo ($nr > 0) ? 'td>' : 'th>';  
                }  
            } else {  
                ?><td>&nbsp;<?= $AA; ?>&nbsp;</td><?  
            }  
            ?></tr><?= "\n";  
        }  
    } else {  
        ?><tr><td>&nbsp;<?= $EG; ?>&nbsp;</td></tr><?= "\n";  
    }  
    ?></table><?= "\n";  
}  
  
/* ------------------------------------------------------------------------------ Start */  
$group = 3;  /* ----------------------------------- Gruppen-ID */  
$perid = 8;  /* ----------------------------------- Perioden-ID */  
$ZL = array();  
$ZL[0] = array('grp','prd','usr','sum','play','dat');  
$query = "SELECT user.userid FROM spieldb.user WHERE groupid = 3";  
$USR = $DB->get_data($query, '*');  
unset($USR[0]);  
foreach($USR as $user) {    /* -------------------- User-ID */  
    $query = "SET @msum = 0";  
    $ok = $DB->get_access($query);  
    $query = "  
       SELECT (@msum:=@msum+games.score) AS saldo # sollen addiert werden  
         FROM spieldb.games                       #  
    LEFT JOIN spieldb.periods                     #  
           ON games.zeit                          #  
      BETWEEN periods.startzeit                   #  
          AND periods.endzeit                     #  
        WHERE games.userid = $user                # eines Spielers  
          AND periods.periodid = $perid           # einer Spielperiode  
     ORDER BY games.score DESC                    # besten Ergebnisse  
        LIMIT 3                                   # Die 3  
    ";  
    $ERG = $DB->get_data($query, '*');  
    foreach($ERG as $nr => $val) {  
        $maxsum = $val;  /* ----------------------- MaxSum */  
    }  
    $query = "  
       SELECT COUNT(*) AS plays,                  # Anzahl der Spiele  
              MAX(games.zeit) AS datum            # Datum letztes Spieles  
         FROM spieldb.games                       #  
    LEFT JOIN spieldb.periods                     #  
           ON games.zeit                          #  
      BETWEEN periods.startzeit                   #  
          AND periods.endzeit                     #  
        WHERE games.userid = $user                # eines Spielers  
          AND periods.periodid = $perid           # einer Spielperiode  
    ";  
    $ERG = $DB->get_data($query);  
    unset($ERG[0]);  
    foreach($ERG as $AA) {  
        $plays = $AA[0];  /* ---------------------- Spiele */  
        $datum = $AA[1];  /* ---------------------- LastDay */  
    }  
    $ZL[] = array($group, $perid, $user, $maxsum, $plays, $datum);  
}  
ergview($ZL);  
/* ------------------------------------------------------------------------------ Ende */  
?>

Bei mir kommt dann eine solche Tabelle heraus:
grp prd usr   sum play dat
 3   8    9  1310  21  2007-08-24 01:46:22
 3   8   10  1420  23  2007-08-24 10:10:15
 3   8   14  1474  26  2007-08-24 00:48:04
 3   8   20  1451  20  2007-08-22 18:13:55
 3   8   26  1155  17  2007-08-24 16:45:18
 3   8   31  1317  24  2007-08-21 02:04:19
 3   8   32  1371  22  2007-08-24 16:34:22
 3   8   33  1408  30  2007-08-24 05:02:34
 3   8   38  1451  23  2007-08-23 09:40:40
 3   8   39  1460  27  2007-08-23 13:33:21
 3   8   40  1318  14  2007-08-24 10:17:27
 3   8   42  1277  23  2007-08-24 13:09:08
 3   8   43  1377  24  2007-08-24 17:31:24

HTH

Gruss Norbert

[PHP_Dude]

Danke Norbert, das muss ich jetzt erstmal durchackern ;)