Tabellenberechnung

Hallo zusammen,

ich habe folgende Tabelle:

CREATE TABLE `Spiel` (
`SPID` int(10) unsigned NOT NULL auto_increment,
`GID` int(10) NOT NULL,
`Rundennr` int(2) default '0',
`PID1` int(10) unsigned default NULL,
`PID2` int(10) unsigned default NULL,
`EErg1` decimal(2,1) NOT NULL default '0.0',
`EErg2` decimal(2,1) NOT NULL default '0.0',
`EAID` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`SPID`),
KEY `GID` (`GID`),
KEY `Rundennr` (`Rundennr`),
KEY `PID1` (`PID1`),
KEY `PID2` (`PID2`),
KEY `EAID` (`EAID`)
)

in dieser Tabelle werden Spielergebnisse gespeichert, also
wie Spieler 1 (PID1) gegen Spieler 2 (PID2) gespielt hat.
Als Ergebniswert in kommen nur die Wert 0, 0.5 und 1.0 in Frage.
EErg1 ist dabei der Wert für den Spieler mit der PID1, EErg2
der für den Spieler mit der PID2. Je nachdem, wie die Spiel-
paarung lautet, steht die ID des Spielers in PID1 oder in
PID2.

Jetzt muss ich daraus eine Tabelle berechnen; neben den
Punkten, die ein Spieler selbst erzielt hat, müssen aber
auch alle Punkte seiner Gegner berechnet werden.
(Für die, die es schon vermuten: ja, es dreht sich um das
sog. Buchholz-System im Schach)

Ich hänge gerade an der Addition der gegnerischen Punkte
und komme nicht weiter. Habt Ihr eine Idee?

Ach ja: MySQL 5.0.32

viele Grüße,
Holger

Re: Tabellenberechnung

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Tabellenberechnung

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Tabellenberechnung

Hallo Andreas,

erstmal vielen Dank für Deinen Mühen, ich weiß das sehr zu
schätzen

Andreas Kretschmer schrieb am Sonntag, 29. April 2007 14:05:

> begin Andreas Kretschmer wrote:
>> begin Holger Schröck wrote:
>>> Jetzt muss ich daraus eine Tabelle berechnen; neben den
>>> Punkten, die ein Spieler selbst erzielt hat, müssen aber
>>> auch alle Punkte seiner Gegner berechnet werden.

> Ich hab Dir mal ein einfaches Beispiel gemacht:
>
> test=*# select * from spiel;
> pid1 | pid2 | e1 | e2
> ------+------+----+-----
> 1 | 2 | 0 | 0.5
> 1 | 3 | 0 | 1
> 2 | 3 | 0 | 1
> 2 | 4 | 1 | 0
> (4 rows)
>
>
> select foo as spieler,
> sum(case when pid1=foo.foo then e1
> when pid2=foo.foo then e2
> else 0 end) as eigene,
> sum(case when pid1=foo.foo then e2
> when pid2=foo.foo then e1
> else 0 end) as gegner
> from spiel, generate_series(1,4) foo
> group by foo.foo
> order by 1;
>
>
> ergibt:
>
> test=*# \i spiel.sql
> spieler | eigene | gegner
> ---------+--------+--------
> 1 | 0 | 1.5
> 2 | 1.5 | 1
> 3 | 2 | 0
> 4 | 0 | 1
> (4 rows)

das ist nicht ganz, was ich brauche... Ausgehend von Deinem Beispiel
sollte bei Spieler 1 das Ergebnis

spieler | eigene | gegner
---------+--------+--------
1 | 0 | 3.5

sein, da er gegen 2 und 3 gespielt habt, und die haben zusammen
3,5 Punkte

viele Grüße,
Holger

Re: Tabellenberechnung

Holger Schröck schrieb:
>
> ich habe folgende Tabelle:
>
> [Tabellendefinition]
>
> in dieser Tabelle werden Spielergebnisse gespeichert, also
> wie Spieler 1 (PID1) gegen Spieler 2 (PID2) gespielt hat.
> Als Ergebniswert in kommen nur die Wert 0, 0.5 und 1.0 in Frage.
> EErg1 ist dabei der Wert für den Spieler mit der PID1, EErg2
> der für den Spieler mit der PID2. Je nachdem, wie die Spiel-
> paarung lautet, steht die ID des Spielers in PID1 oder in
> PID2.
>
> Jetzt muss ich daraus eine Tabelle berechnen; neben den
> Punkten, die ein Spieler selbst erzielt hat, müssen aber
> auch alle Punkte seiner Gegner berechnet werden.
> (Für die, die es schon vermuten: ja, es dreht sich um das
> sog. Buchholz-System im Schach)


Also ich würde die Daten anders speichern. Ins unreine gedacht:
Eine Tabelle mit allen Spielen, den beteiligten Spielern und den
erreichten Punkten:
mysql> DESC ergebnis;
+---------+------------------+------+-----+---------+------- +
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+------- +
| spiel | int(10) unsigned | NO | PRI | | |
| spieler | int(10) unsigned | NO | PRI | | |
| punkte | decimal(2,1) | YES | | NULL | |
+---------+------------------+------+-----+---------+------- +
3 rows in set (0.00 sec)

In deinem Fall wären dort also pro Spiel zwei Einträge drin:
mysql> SELECT * FROM ergebnis;
+-------+---------+--------+
| spiel | spieler | punkte |
+-------+---------+--------+
| 1 | 1 | 1.0 |
| 1 | 2 | 0.0 |
| 2 | 2 | 0.5 |
| 2 | 3 | 0.0 |
| 3 | 3 | 0.5 |
| 3 | 1 | 0.0 |
+-------+---------+--------+
6 rows in set (0.00 sec)

Daraus kannst du dann mit einem einfachen Selfjoin alle Punkte addieren,
die dich interessieren:
SELECT SUM(a.punkte), SUM(b.punkte)
FROM ergebnis a
INNER JOIN ergebnis b ON b.spiel = a.spiel
WHERE a.spieler = 1 AND b.spieler != 1

+---------------+---------------+
| SUM(a.punkte) | SUM(b.punkte) |
+---------------+---------------+
| 1.0 | 0.5 |
+---------------+---------------+
1 row in set (0.00 sec)

Ist es das, was du suchst?

Grüße,
Dominik

Re: Tabellenberechnung

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Tabellenberechnung

Hallo Dominik,

Dominik Echterbruch schrieb am Sonntag, 29. April 2007 16:48:

> Also ich würde die Daten anders speichern. Ins unreine gedacht:
> Eine Tabelle mit allen Spielen, den beteiligten Spielern und den
> erreichten Punkten:
> mysql> DESC ergebnis;
> +---------+------------------+------+-----+---------+------- +
> | Field | Type | Null | Key | Default | Extra |
> +---------+------------------+------+-----+---------+------- +
> | spiel | int(10) unsigned | NO | PRI | | |
> | spieler | int(10) unsigned | NO | PRI | | |
> | punkte | decimal(2,1) | YES | | NULL | |
> +---------+------------------+------+-----+---------+------- +
> 3 rows in set (0.00 sec)

ja, das ist ziemlich unrein :-) ; damit handle ich mir nur Probleme
ein, wenn ich die einzelnen Paarungen auch noch ausgeben will...

> In deinem Fall wären dort also pro Spiel zwei Einträge drin:
> mysql> SELECT * FROM ergebnis;
> +-------+---------+--------+
> | spiel | spieler | punkte |
> +-------+---------+--------+
> | 1 | 1 | 1.0 |
> | 1 | 2 | 0.0 |
> | 2 | 2 | 0.5 |
> | 2 | 3 | 0.0 |
> | 3 | 3 | 0.5 |
> | 3 | 1 | 0.0 |
> +-------+---------+--------+
> 6 rows in set (0.00 sec)
>
> Daraus kannst du dann mit einem einfachen Selfjoin alle Punkte addieren,
> die dich interessieren:
> SELECT SUM(a.punkte), SUM(b.punkte)
> FROM ergebnis a
> INNER JOIN ergebnis b ON b.spiel = a.spiel
> WHERE a.spieler = 1 AND b.spieler != 1
>
> +---------------+---------------+
> | SUM(a.punkte) | SUM(b.punkte) |
> +---------------+---------------+
> | 1.0 | 0.5 |
> +---------------+---------------+
> 1 row in set (0.00 sec)
>
> Ist es das, was du suchst?

ich fürchte, nein; da müsste ich ja für jeden Spieler eine
Query absetzen... ein bisschen aufwändig, wenn die Spielerzahl
irgendwo bei 100 pro Turnier liegt... zudem hat in Deinem Beispiel
Spieler 1 gegen 2 und 3 gespielt und die haben jeweils 0.5
Punkte; Summe über b.Punkte müsste also 1.0 ergeben und nicht 0.5

aber die self joins haben den Knoten in meinem Denken gelöst; danke!

der Punkt ist, dass alle Punkte der Gegner zusammenaddiert werden
müssen. Im Prinzip ist das Vorgehen so, dass zuerst ganz normal
eine Tabelle erstellt wird, indem die erzielten Punkte eines Spielers
addiert werden. Dann wird nachgeschaut, gegen wen der Spieler
gespielt hat, und deren Punkte aus der Tabelle werden dann zu einer
zweiten Zahl addiert.


folgendes scheint zu funktionieren:

create table tabName
select PID, sum(Punkte) as Punkte from (
select
PID1 as PID,
sum(EErg1) as Punkte
from Spiel
where GID=1
group by PID
union
select
PID2 as PID,
sum(EErg2) as Punkte
from Spiel
where GID=1
group by PID) as a
group by a.PID

create temporary table temp1
select
PID1 as PID,
PID2 as PIDG
from Spiel
union all
select
PID2 as PID,
PID1 as PIDG
from Spiel


select
temp1.PID,
concat(Name,', ',Vorname) as Name,
Person.Jahrgang,
t1.Punkte,
sum(t2.Punkte) as Feinwertung
from temp1
inner join tabName t1 on temp1.PID=t1.PID
inner join tabName t2 on temp1.PIDG=t2.PID
inner join Person on temp1.PID=Person.PID
group by temp1.PID
order by Punkte desc, Feinwertung desc, Name asc

dabei muss ich die Tabelle tabName nach Verwendung wieder löschen,
was mir aber auch nicht gefällt...
naja, aber soweit scheint es zu funktionieren...

viele Grüße,
Holger

Re: Tabellenberechnung

Du hast Probleme, weil Deine Tabelle nicht durchnormalisiert ist.
Außerdem gibt es keinen eigenen Datensatz für die einzelne Partie, und
wenn dann irgendwann der Wunsch nach partiespezifischen Daten aufkommt,
wirst Du die Tabellenstruktur umbauen müssen.

Daher dieser Vorschlag (Indexe mal weggelassen):

Tabelle spieler:
sid int(10)
name varchar(50)

Tabelle partie (zwei Datensätze je Partie):
pid int(10)
-- weitere Daten über die Partie, wie z.B. Zeit, Ort, Dauer,
-- Notizen

Tabelle teilnahme (eines Spielers an einer Partie):
pid int(10)
sid int(10)
punkte decimal(2,1)
-- weitere Daten über eine Teilnahme, z.B. Notizen über das
-- Verhalten des Spielers bei der Partie

Punktestand:
SELECT
spieler.name,
SUM(teilname.punkte)
FROM spieler
LEFT JOIN teilnahme ON spieler.sid = teilnahme.sid
GROUP BY spieler.sid

Paarungen:
SELECT spieler.name, partie.pid
FROM partie
JOIN teilnahme ON partie.pid = teilnahme.pid
JOIN spieler ON teilnahme.sid = spieler.sid
ORDER BY partie.pid
Die Paarungen kann man sich zusammenbasteln, indem man das Ergebnis der
Reihe nach durchgeht und die Paarung ausgibt, sobald die pid wechselt.

Wenn mysql in CONCAT_WS auch Subselects zulässt, müsste auch sowas wie
SELECT CONCAT_WS(', ', (SELECT "alle Spieler zur Partie"))
FROM partie JOIN teilnahme
funktionieren. Dann hast Du gleich die Paarungen und musst sie nicht
mehr aus dem Ergebnis zusammenfrickeln.
Allerdings mit Einschränkungen: willst Du separate Spalten für Spieler 1
und Spieler 2 in der Ergebnistabelle, geht das so nicht. Evtl. kannst Du
in der Tabelle teilnahme noch eine Spielernummer eintragen. Oder so ein
Feld:
seite ENUM("Weiß", "Schwarz")
Dann kannst Du für die Ergebnisspalten für Spieler 1 eine
WHERE-Bedingung à la 'WHERE teilnahme.seite = "Weiß"' und entsprechend
für die Ergebnisspalten für Spieler 2 vorsehen.

Grüße
Jo

Re: Tabellenberechnung

Holger Schröck schrieb:
> Hallo Dominik,
>
> Dominik Echterbruch schrieb am Sonntag, 29. April 2007 16:48:
>
>> Also ich würde die Daten anders speichern. Ins unreine gedacht:
>> Eine Tabelle mit allen Spielen, den beteiligten Spielern und den
>> erreichten Punkten:
>> mysql> DESC ergebnis;
>> +---------+------------------+------+-----+---------+------- +
>> | Field | Type | Null | Key | Default | Extra |
>> +---------+------------------+------+-----+---------+------- +
>> | spiel | int(10) unsigned | NO | PRI | | |
>> | spieler | int(10) unsigned | NO | PRI | | |
>> | punkte | decimal(2,1) | YES | | NULL | |
>> +---------+------------------+------+-----+---------+------- +
>> 3 rows in set (0.00 sec)
>
> ja, das ist ziemlich unrein :-) ; damit handle ich mir nur Probleme
> ein, wenn ich die einzelnen Paarungen auch noch ausgeben will...

Wieso? Für Paarungen braucht's doch auch nicht mehr, als einen Selfjoin:
SELECT a.spieler, GROUP_CONCAT(b.spieler) gegner
FROM ergebnis a
INNER JOIN ergebnis b ON b.spiel = a.spiel
WHERE a.spieler = 1 AND b.spieler != 1

+---------+--------+
| spieler | gegner |
+---------+--------+
| 1 | 2,3 |
+---------+--------+

Alternativ:
SELECT a.spieler, b.spieler gegner
FROM ergebnis a
INNER JOIN ergebnis b ON b.spiel = a.spiel
WHERE a.spieler = 1 AND b.spieler != 1

+---------+--------+
| spieler | gegner |
+---------+--------+
| 1 | 2 |
| 1 | 3 |
+---------+--------+

>> Daraus kannst du dann mit einem einfachen Selfjoin alle Punkte addieren,
>> die dich interessieren:
>> SELECT SUM(a.punkte), SUM(b.punkte)
>> FROM ergebnis a
>> INNER JOIN ergebnis b ON b.spiel = a.spiel
>> WHERE a.spieler = 1 AND b.spieler != 1
>
> ich fürchte, nein; da müsste ich ja für jeden Spieler eine
> Query absetzen... ein bisschen aufwändig, wenn die Spielerzahl
> irgendwo bei 100 pro Turnier liegt...

Zugegeben, ich habe da ein wenig auf deine Fantasie gehofft ;)
SELECT a.spieler, SUM(a.punkte), SUM(b.punkte)
FROM ergebnis a
INNER JOIN ergebnis b ON b.spiel = a.spiel
WHERE a.spieler != b.spieler
GROUP BY a.spieler

> zudem hat in Deinem Beispiel
> Spieler 1 gegen 2 und 3 gespielt und die haben jeweils 0.5
> Punkte; Summe über b.Punkte müsste also 1.0 ergeben und nicht 0.5

OK, dann habe ich das Prinzip des Punktezählens falsch verstanden (bin
nicht so der Schachfreak). Wie wäre es also mit:
SELECT a.spieler, SUM(a.punkte), SUM(c.punkte)
FROM ergebnis a
INNER JOIN ergebnis b ON b.spiel = a.spiel
INNER JOIN ergebnis c ON c.spieler = b.spieler
WHERE b.spieler != a.spieler
GROUP BY a.spieler

+---------+---------------+---------------+
| spieler | SUM(a.punkte) | SUM(c.punkte) |
+---------+---------------+---------------+
| 1 | 2.0 | 1.0 |
| 2 | 1.0 | 1.5 |
| 3 | 1.0 | 1.5 |
+---------+---------------+---------------+

Haben wir es jetzt so, wie du es möchtest? Ach nein, dir fehlen ja noch
die Paarungen. Also kombinieren wir mal ein bißchen die Zwischenlösungen
und bauen noch einen DISTINCT ein:
SELECT a.spieler, SUM(a.punkte), SUM(c.punkte),
GROUP_CONCAT(DISTINCT(b.spieler)) gegner
FROM ergebnis a
INNER JOIN ergebnis b ON b.spiel = a.spiel
INNER JOIN ergebnis c ON c.spieler = b.spieler
WHERE b.spieler != a.spieler
GROUP BY a.spieler

+---------+---------------+---------------+--------+
| spieler | SUM(a.punkte) | SUM(c.punkte) | gegner |
+---------+---------------+---------------+--------+
| 1 | 2.0 | 1.0 | 3,2 |
| 2 | 1.0 | 1.5 | 1,3 |
| 3 | 1.0 | 1.5 | 1,2 |
+---------+---------------+---------------+--------+

Zufrieden? :)
Vor allem viel einfacher (und sicherlich deutlich schneller), als deine
Lösung, die ich mal entsorgt habe, um Bandbreite zu sparen ;)
Wenn du mehr als nur ein Turnier in der Tabelle halten möchtest, mußt du
nur noch eine weitere Spalte Turnier einbauen und entsprechend filtern.

So, das soll jetzt aber wirklich mal für's erste reichen. Die Namen der
Spieler oder sonstwas in die Tabelle mit einzubauen, solltest du jetzt
alleine schaffen.

Grüße,
Dominik

Re: Tabellenberechnung

Dominik Echterbruch schrieb:
>
> SELECT a.spieler, SUM(a.punkte), SUM(c.punkte)
> FROM ergebnis a
> INNER JOIN ergebnis b ON b.spiel = a.spiel
> INNER JOIN ergebnis c ON c.spieler = b.spieler
> WHERE b.spieler != a.spieler
> GROUP BY a.spieler

Ich habe mir eben nochmal das Ergebnis angeschaut und festgestellt, daß
die Summe der Punkt für den Spieler zu hoch ist. Das ist auch klar, weil
in der o.g. Konstellation durch den zweiten Selfjoin die Punkte für den
Spieler immer doppel tgezählt werden. Aber auch das ist glücklicherweise
nicht so schwierig zu lösen (habe auch noch ein paar Verschönerungen
eingebaut):

SELECT a.spieler, ROUND(SUM(a.punkte/2), 1) eigene, SUM(c.punkte) fremde
FROM ergebnis a
INNER JOIN ergebnis b ON b.spiel = a.spiel
INNER JOIN ergebnis c ON c.spieler = b.spieler
WHERE b.spieler != a.spieler
GROUP BY a.spieler

Außerdem ist mir noch eine weitere und elegantere Lösung eingefallen:
SELECT a.spieler, SUM(a.punkte) eigene,
(SELECT SUM(b.punkte)
FROM ergebnis b
WHERE b.spieler != a.spieler) fremde
FROM ergebnis a
GROUP BY a.spieler

Damit wäre das Problem der verdoppelten Punkte gelöst und die Gegner zu
ermitteln ist ja jetzt auch nicht mehr so das Problem:
SELECT a.spieler, SUM(a.punkte) eigene,
(SELECT SUM(b.punkte)
FROM ergebnis b
WHERE b.spieler != a.spieler) fremde,
GROUP_CONCAT(c.spieler) gegner
FROM ergebnis a
INNER JOIN ergebnis c ON c.spiel = a.spiel AND c.spieler != a.spieler
GROUP BY a.spieler

Ganz nebenbei haben wir uns also auch noch den unschönen DISTINCT im
GROUP_CONCAT gespart. Sieht so aus, als wäre mein Ansatz doch nicht so
unrein gewesen, wie ursprünglich gedacht ;)

Grüße,
Dominik

Re: Tabellenberechnung

Hallo Dominik,

Dominik Echterbruch schrieb am Dienstag, 1. Mai 2007 12:06:

> SELECT a.spieler, ROUND(SUM(a.punkte/2), 1) eigene, SUM(c.punkte) fremde
> FROM ergebnis a
> INNER JOIN ergebnis b ON b.spiel = a.spiel
> INNER JOIN ergebnis c ON c.spieler = b.spieler
> WHERE b.spieler != a.spieler
> GROUP BY a.spieler

das ist quasi perfekt; je nach Anzahl der gespielten Runden nicht
durch 2 sondern die Anzahl der Runden teilen

> Außerdem ist mir noch eine weitere und elegantere Lösung eingefallen:
> SELECT a.spieler, SUM(a.punkte) eigene,
> (SELECT SUM(b.punkte)
> FROM ergebnis b
> WHERE b.spieler != a.spieler) fremde
> FROM ergebnis a
> GROUP BY a.spieler

das funktioniert nicht ganz, da nicht immer jeder gegen jeden spielt.
Aber für vollrundige Turnieren ist es ein guter Ansatz:

SELECT a.spieler,
ROUND(SUM(a.punkte)/$AnzahlRunden,1) eigene,
SUM(a.punkte*c.punkte) SoBerg
FROM ergebnis a
INNER JOIN ergebnis b ON b.spiel = a.spiel
INNER JOIN ergebnis c ON c.spieler = b.spieler
WHERE b.spieler != a.spieler
GROUP BY a.spieler

herzlichen Dank dafür!
auch Dir, Joachim für Deinen Beitrag!

> Sieht so aus, als wäre mein Ansatz doch nicht so
> unrein gewesen, wie ursprünglich gedacht :-)

ja, sieht so aus :-) ich sollte wohl erst nach einer Kanne Tee
posten und nicht vorher :-)

viele Grüße,
Holger

Re: Tabellenberechnung

Holger Schröck schrieb:
>
>> SELECT a.spieler, SUM(a.punkte) eigene,
>> (SELECT SUM(b.punkte)
>> FROM ergebnis b
>> WHERE b.spieler != a.spieler) fremde
>> FROM ergebnis a
>> GROUP BY a.spieler
>
> das funktioniert nicht ganz, da nicht immer jeder gegen jeden spielt.

Da hast du allerdings recht. Naja, aber als Denkanstoß hat es ja
offensichtlich gereicht ;) Viel Spaß beim Verfeinern!

Grüße,
Dominik