[MySQL] Winning strike berekenen over wedstrijden
MySQL data structuur
Players
---
Code (php)
1
Id | Name
Matches
---
Code (php)
1
Id | Created (datetime) | HomePlayerId | AwayPlayerId | HomeGoals | AwayGoals
Nu wil ik graag weten weten hoeveel wedstrijden (uit of thuis) een deelnemer achter elkaar heeft gewonnen (winning strike). Ik weet niet echt waar ik moet beginnen. Kan dit uberhaubt wel rechtstreeks in MySQL?
1) Er moet bepaald worden wie is gewonnen
2) Er moet bepaald worden hoe vaak er achter elkaar is gewonnen (vanaf de laatst gespeelde wedstrijd).
Ik kom niet verder dan;
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
matches.HomePlayer_Id,
matches.AwayPlayer_Id,
matches.HomeGoals,
matches.AwayGoals
FROM
matches
WHERE
matches.HomePlayer_Id = '1' OR
matches.AwayPlayer_Id = '1'
matches.HomePlayer_Id,
matches.AwayPlayer_Id,
matches.HomeGoals,
matches.AwayGoals
FROM
matches
WHERE
matches.HomePlayer_Id = '1' OR
matches.AwayPlayer_Id = '1'
Gewijzigd op 01/01/1970 01:00:00 door Jeroen G
teams:
id | naam | enz..
players
id | naam | teamid
wedstrijden:
id | datum | thuisteam | gastteam | enz...
goals:
id | wedstrijdid | playerid | tegenteam
Ga daar eens mee verder.
edit:
Nog wat beter genormaliseerd.
En als het 1 tegen 1 is dan krijg je:
players
id | naam
wedstrijden:
id | datum | thuisspeler | gastspeler | enz...
goals:
id | wedstrijdid | playerid | tegenplayer
edit:
Je kunt nu dus aangeven wanneer een goal gemaakt is (in welke minuut), je kunt aangeven of het een eigen doelpunt was.. Een genormaliseerde database is verder gemakkelijker uitbreidbaar.
Gewijzigd op 01/01/1970 01:00:00 door PHP Newbie
Waarom haal je de Players tabel uit elkaar en verdeel je deze onder leden en deelnemers met een one-to-many ? Dat is toch nergens voor nodig?
De goals losmaken van de wedstrijden tabel kan ik wel nog iets bij indenken. Alhoewel ik dan nog steeds met hetzelfde probleem zit.
Na jou voorgestelde wijzigingen moet bij een wedstrijd overzicht voor iedere wedstrijduitslag 2x een subquery sum uitgevoerd worden om de doelpunten te berekenen. Ook bij het invoeren van een nieuwe uitslag moet ik voor ieder doelpunt een nieuw record laten aanmaken.
Ik vraag me dus af wat de door jou voorgestelde wijzigingen voor een voordelen hebben.
Gewijzigd op 01/01/1970 01:00:00 door Jeroen G
Ja klopt, ik zie het nu ook.. Maar dit lost mijn probleem toch niet op? Het is gewoon een andere opzet van een DB. Hierbij zou ik hetzelfde probleem hebben om een winning strike te berekenen.
Gewijzigd op 01/01/1970 01:00:00 door Jeroen G
Ik moet er toch wel over nadenken; ik zal het eens proberen.
Gaat het trouwens over voetbal?
EDIT:
@PHP Newbie: ja, dat nieuwe model lijkt me ook beter; het oude was niet geschikt voor voetbal. Ik ga met je nieuw model voort.
Gewijzigd op 01/01/1970 01:00:00 door Emmanuel Delay
Waarom is het trouwens geen correct model wat ik gebruik?
Het model van PHP Newbie gaat er vanuit dat je teams hebt, die meerdere spelers bevatten. Dat is niet mijn bedoeling. Een player is als het ware ook een team bij mij.
Ja het is voor voetbal. Om beter te zeggen, een computer spel.
Gewijzigd op 01/01/1970 01:00:00 door Jeroen G
Om de winnaars te bereken doormiddel van een query heb je een flinke query nodig, maar ik denk niet dat het onmogelijk is.
Het is een stuk gemakkelijker met php. Je haalt de huidige wedstrijd en vorige wedstrijden op. Vervolgens kun je in php de winnaar bepalen en hoeveel wedstrijden hij op een rij gewonnen heeft.
Het nadeel is dat je altijd alle wedstrijden op moet halen.
is er niet zoiets als?
Code (php)
1
2
3
4
5
2
3
4
5
SELECT id, name, date, status
FROM table
WHERE id = $id
ORDER BY date ASC
UNTIL status = 'lost'
FROM table
WHERE id = $id
ORDER BY date ASC
UNTIL status = 'lost'
EDIT:
players
id | naam
wedstrijden:
id | datum | thuisspeler | gastspeler | enz...
goals:
id | wedstrijdid | playerid | tegenplayer
Dit is trouwens wel een goeie opzet. Ik hoef geen doelpunten per min. te registreren. Maar het is inderdaad veel makkelijker uitbreidbaar zo. Even mijn bestaande inner joins aanpassen :S
EDIT2:
Na jou model nauwkeurig te bekijken weet ik trouwens niet zeker of ik dit wel ga gebruiken. Hoe kan ik bijvoorbeeld zien hoeveel thuis doelpunten een deelnemer heeft gescoord? Verder lijkt het berekenen van een winning strike zo nog lastiger.
Gewijzigd op 01/01/1970 01:00:00 door Jeroen G
Eventueel kan je dan bij goals player_id op NULL zetten, voor het geval je niet meer weet wie scoorde.
Je ziet ook dat status een afgeleide eigenschap is.
Het feit dat de SELECT iets moeilijker is, lijkt me niet voldoende reden om de helft van de informatie gewoon te negeren.
Je zal met een query eindigen met meerdere SELECT's
Zo-iets als
Code (php)
1
2
3
4
2
3
4
SELECT id, team, datum,
(SELECT ... FROM ... INNER JOIN ... ) AS status
FROM wedstrijd
...
(SELECT ... FROM ... INNER JOIN ... ) AS status
FROM wedstrijd
...
EDIT:
Kijk, dit heb ik nu
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE TABLE IF NOT EXISTS goals (
id int(11) NOT NULL auto_increment,
wedstrijdid int(11) NOT NULL,
playerid int(11) default NULL,
voorteam int(11) NOT NULL,
tijd time default NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;
CREATE TABLE IF NOT EXISTS players (
id int(11) NOT NULL auto_increment,
naam varchar(100) NOT NULL,
teamid int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
CREATE TABLE IF NOT EXISTS teams (
id int(11) NOT NULL auto_increment,
naam varchar(100) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
CREATE TABLE IF NOT EXISTS wedstrijden (
id int(11) NOT NULL auto_increment,
datum date default NULL,
thuisteam int(11) NOT NULL,
gastteam int(11) NOT NULL,
`week` int(2) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
id int(11) NOT NULL auto_increment,
wedstrijdid int(11) NOT NULL,
playerid int(11) default NULL,
voorteam int(11) NOT NULL,
tijd time default NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;
CREATE TABLE IF NOT EXISTS players (
id int(11) NOT NULL auto_increment,
naam varchar(100) NOT NULL,
teamid int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
CREATE TABLE IF NOT EXISTS teams (
id int(11) NOT NULL auto_increment,
naam varchar(100) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
CREATE TABLE IF NOT EXISTS wedstrijden (
id int(11) NOT NULL auto_increment,
datum date default NULL,
thuisteam int(11) NOT NULL,
gastteam int(11) NOT NULL,
`week` int(2) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
Zo vind je dan de wedstrijduitslagen: (kan je zelf wel uitbreiden)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- wedstrijduitslagen:
SELECT w1 AS wedstrijdnummer, week, thuisteamnaam, gastteamnaam, SUM( thuis ) AS thuisgoals, SUM( gast ) AS gastgoals,
CASE
WHEN SUM( thuis ) > SUM( gast ) THEN thuisteamnaam
WHEN SUM( thuis ) < SUM( gast ) THEN gastteamnaam
END AS winnaar
FROM ( -- alle goals
SELECT wedstrijden.id AS w1, week, thuisteamtabel.naam AS thuisteamnaam, gastteamtabel.naam AS gastteamnaam, voorteam = thuisteam AS thuis, voorteam = gastteam AS gast
FROM wedstrijden
LEFT JOIN goals ON goals.wedstrijdid = wedstrijden.id
INNER JOIN teams AS thuisteamtabel ON thuisteamtabel.id = wedstrijden.thuisteam
INNER JOIN teams AS gastteamtabel ON gastteamtabel.id = wedstrijden.gastteam
ORDER BY wedstrijden.id
) AS t1
GROUP BY w1
ORDER BY w1
SELECT w1 AS wedstrijdnummer, week, thuisteamnaam, gastteamnaam, SUM( thuis ) AS thuisgoals, SUM( gast ) AS gastgoals,
CASE
WHEN SUM( thuis ) > SUM( gast ) THEN thuisteamnaam
WHEN SUM( thuis ) < SUM( gast ) THEN gastteamnaam
END AS winnaar
FROM ( -- alle goals
SELECT wedstrijden.id AS w1, week, thuisteamtabel.naam AS thuisteamnaam, gastteamtabel.naam AS gastteamnaam, voorteam = thuisteam AS thuis, voorteam = gastteam AS gast
FROM wedstrijden
LEFT JOIN goals ON goals.wedstrijdid = wedstrijden.id
INNER JOIN teams AS thuisteamtabel ON thuisteamtabel.id = wedstrijden.thuisteam
INNER JOIN teams AS gastteamtabel ON gastteamtabel.id = wedstrijden.gastteam
ORDER BY wedstrijden.id
) AS t1
GROUP BY w1
ORDER BY w1
Ik laat toe dat een aantal dingen op NULL worden gelaten, zoals speler die de goal maakte, datum van de wedstrijd (wedstrijd-week lijkt me belangrijker), minuut waarop de goal gemaakt is.
Aan de winning streak ben ik niet geraakt. Eerst en vooral moet je een hoop INSERT's doen vooraleer je kan testen.
Ik zou dat toch eerder met php doen ipv met sql.
Maak een array met bv. 18 (Belgische competitie, 19 ploegen in 1e klassse) gegevens, die allemaal op 0 staan. telkens een ploeg wint, verhoog je het nummer met de key van zijn ploeg, telkens de ploeg niet wint, zet je alles weer op 0.
Ik hoop dat je hiermee voort kan.
Gewijzigd op 01/01/1970 01:00:00 door Emmanuel Delay