Stand maken met Mysql
Ik heb een vraag over Mysql. Ik wil op basis van uitslagen een stand opmaken, maar ik kom er niet uit. De puntentelling is enigszins gecompliceerd, en ik weet niet of ik deze het beste in een table kan stoppen of in de query zelf.
Even een korte uiteg. De stand wordt gebaseerd op de punten behaald in het afgelopen jaar, een beetje zoals in het tennis ook gebeurd. Er zijn twee categoriën A en B, en vier niveau's wedstrijden (N1,N2,N3,N4). Daarnaast is er nog een extra moeilijkheid, namelijk dat van de wedstrijden van niveau N3 en N4 alleen de beste 5 resultaten uit het afgelopen jaar tellen.
De code zou er - voor zover mijn kennis reikt - dus ongeveer zo moeten uitzien denk ik:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT * FROM speler AS s
JOIN uitslagen AS u ON s.id = u.uitslag_naam
JOIN kalender AS k ON k.categorie = u.uitslag_categorie AND k.seizoen = u.uitslag_seizoen AND k.kalender_id = u.uitslag_id
....
GROUP BY uitslag_id
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."'
ORDER BY datum DESC
JOIN uitslagen AS u ON s.id = u.uitslag_naam
JOIN kalender AS k ON k.categorie = u.uitslag_categorie AND k.seizoen = u.uitslag_seizoen AND k.kalender_id = u.uitslag_id
....
GROUP BY uitslag_id
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."'
ORDER BY datum DESC
'Datum' komt uit kalender, en $datum haal ik op uit de URL met de GET-functie. Omdat elke wedstrijd maar één keer meetelt moet Group BY uitslag_id ervoor zorgen dat er van elke wedstrijd maar één resultaat is, en door middel van de Order by datum de recentste editie. Al moet de stand uiteindelijk worden 'georderd' op basis van degene met de meeste punten. Ik heb al veel gezocht ivm het rekenwerk, maar ik weet niet hoe dat moet, dus dat heb ik maar even open gelaten.
De puntentelling is als volgt:
uitslag_categorie / uitslag_niveau / puntenverdeling
A - N1 / 20 - 18 - 16 - 14 - 12 - 10 - 8 - 6 - 4 - 2
A - N2 / 10 - 9 - 8 - 7 - 6 - 5 - 4 - 3 - 2 - 1
A - N3 / 5 - 4 - 3 - 2 - 1
B - N4 / 3 - 2 - 1
N1 - 1 van 1
N2 - 3 van 3
N3 - 5 van X (alleen vijf beste resultaten tellen mee)
N4 - 5 van X (alleen vijf beste resultaten tellen mee)
Even uit de vrije hand dus zoiets:
IF uitslag_categorie = A AND uitslag_niveau = N1 AND uitslag_positie = 1 --> 20
Ik heb ook een table 'puntentelling' gemaakt bestaande uit de kolommen categorie - niveau - p1 - p2 - p3 etc. maar ik weet niet of dat de manier is waarop dat zou moeten.
Ik hoop dat jullie mij kunnen helpen!
Gewijzigd op 01/12/2015 19:00:09 door G Jansma
Het script maakt dan eerste de standentabel leeg (waarschijnlijk voor het betreffende seizoen) en loopt vervolgens de andere tabellen af om per speler het aantal punten te berekenen. Dit sla je vervolgens op.
Je kunt dan de standen tabel laten zien gesorteerd op punten van hoog naar laag.
Hoe de berekening precies moet kan ik uit je vraagstelling niet helemaal opmaken, maar misschien geeft je dit een idee hoe verder te gaan.
De standentabel zou iets kunnen zijn als:
- id
- seizoen_id
- speler_id
- punten
En ik zou zelf niet alles in 1 query gooien.
Stuk code om de beste 5 van N3 en N4 uit de database halen.
Stuk code voor N1.
Stuk code voor N2.
Stuk code tussendoor voor A of B? (begrijp de structuur ook niet helemaal).
En dan een stuk code om de uitslagen van die codes op te tellen.
Lijkt me dat het wel te doen is als je de berekening gewoon in hapklare brokjes opbreekt.
Als ik het goed begrijp heb ik dus een PHP-script nodig die gegevens uit een table haalt, en daaruit vervolgens een nieuwe table aanmaakt? Ik heb tot op heden alleen maar tables gebruikt waarin ik zelf de inhoud heb aangegeven, zijn er voorbeelden van hoe zo'n script eruit zou moeten zien om een 'gegenereerde' table te maken?
Is het anders ook mogelijk om een extra kolom in de table 'uitslagen' te genereren voor de punten? In principe staan daar namelijk al alle gegevens die ik nodig denk te hebben, alleen wil ik de punten niet bij elke uitslag handmatig invoeren. Dus iets als: IF uitslag_categorie = A AND uitslag_niveau = N1 AND uitslag_positie = 1 --> kolom punten --> 20?
Over de puntentelling:
A is senioren
B is junioren
Daarbinnen zijn dus diverse niveau's wedstrijden (N1,N2,N3,N4) met een eigen puntentelling.
In uitslagen staat dus welk ID (id/uitslag_naam) in welk uitslag_seizoen, uitslag_categorie, en uitslag_niveau welke uitslag_postie behaalde.
@Jan de Laet: Ik wil alleen dat je per datum kan zien wat de stand op die datum was. Dat kan denk ik niet als je een 'totaal' gebruikt.
@L deB: Hoe moet ik dit voor me zien? Bedoel je met SELECT in SELECT (heb ik wel eens voorbij zien komen)?
En als ik je goed begrijp wil je daarna het totaal van de punten laten zien van de spelers op 1 datum, dus dan heb je een SELECT speler_id, SUM(punten) FROM uitslagen WHERE datum = 20145-12-02' GROUP BY speler_id nodig.
Een eventuele standentabel kun je in het script maken met CREATE TABLE, maar waarschijnlijk is het handiger om de tabel buiten het script te maken en als je de stand berekent voor een datum de oude informatie te verwijderen, bijv DELETE FROM standen WHERE datum='2015-12-02'.
Dat is inderdaad precies wat ik bedoel.
Maar hoe zou ik die punten automatisch als extra veld bij uitslagen kunnen krijgen?
Select in Select: in principe kun je daar waar een 'FROM table' staat, ipv de table ook een '(SELECT a, b, c FROM table2 WHERE a=1)'. Men noemt dat subqueries. Het resultaat tussen de haakjes gedraagt zich dan alsof het een bestaande table was.
Maar dat heeft meestal alleen zin als je met het tussenresultaat nog wat meer wil doen. Bijv.
Code (php)
1
2
2
SELECT * FROM (SELECT a, b, SUM(c) FROM table2 WHERE a=1 GROUP BY a, b ORDER BY SUM(c) DESC) AS t
JOIN spelers ON spelers.id = t.a
JOIN spelers ON spelers.id = t.a
Hier koppel je dus het resultaat t van de subquery aan de spelers tabel.
N3 en N4
Omdat jij van N1 en N2 alles wilt hebben en van N3 en N4 alleen de laatste 5, kun jo overwegen met UNION te werken. Daarmee 'verenig' je meerdere SELECTs tot 1 resultaat.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM uitslagen
WHERE uitslag_niveau in ("N1", "N2") -- alles van N1 en N2
UNION
SELECT * FROM (SELECT * FROM uitslagen
WHERE uitslag_niveau = "N3"
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY datum DESC LIMIT 5) AS u2 -- laatste 5 van N3
UNION
SELECT * FROM (SELECT * FROM uitslagen
WHERE uitslag_niveau = "N4"
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY datum DESC LIMIT 5) AS u3 -- laatste 5 van N4
WHERE uitslag_niveau in ("N1", "N2") -- alles van N1 en N2
UNION
SELECT * FROM (SELECT * FROM uitslagen
WHERE uitslag_niveau = "N3"
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY datum DESC LIMIT 5) AS u2 -- laatste 5 van N3
UNION
SELECT * FROM (SELECT * FROM uitslagen
WHERE uitslag_niveau = "N4"
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY datum DESC LIMIT 5) AS u3 -- laatste 5 van N4
Het zal niet helemaal correct zijn que syntax, maar misschien brengt het je op een idee.
Gewijzigd op 03/12/2015 08:32:13 door Jan de Laet
Jan de Laet op 03/12/2015 08:24:22:
Tabellen maken en velden toevoegen zijn meestal eenmalig, die doe je het beste met een tool als phpMyAdmin (bij mijn server is dat vanuit cPanel, de beheertool, beschikbaar). Je hebt met phpMyAdmin een vrij eenvoudige tool om met hulp de CREATE TABEL en ALTER TABLE uit te voeren, bijv ALTER TABLE `uitslagen` ADD `punten` INT NOT NULL DEFAULT '0';
Ik heb ook phpMyAdmin en heb de kolom punten toegevoegd. Mijn vraag was eigenlijk meer: Hoe kan ik in een rij de kolom 'punten' automatisch gevuld krijgen? In elke rij staan de categorie, het niveau, en de uitslag, maar kan ik met die gegevens het juiste puntentotaal genereren in 'punten'?
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
UPDATE uitslagen
SET punten =
CASE WHEN (uitslag_categorie = "A" AND uitslag_niveau = "N1" AND uitslag_positie = 1) THEN 20
WHEN (uitslag_categorie = "A" AND uitslag_niveau = "N1" AND uitslag_positie = 2) THEN 18
enz
ELSE 0
END
WHERE ...
SET punten =
CASE WHEN (uitslag_categorie = "A" AND uitslag_niveau = "N1" AND uitslag_positie = 1) THEN 20
WHEN (uitslag_categorie = "A" AND uitslag_niveau = "N1" AND uitslag_positie = 2) THEN 18
enz
ELSE 0
END
WHERE ...
Dus zoals Jan aangeeft voor N3 en N4 en op die manier alle juiste berekeningen uitvoeren. Vervolgens per resultaat (of het geheel van die resultaten) in de rij 'punten' plaatsen met een query met INSERT (edit: UPDATE inderdaad).
En even terugkomende op wat ik eerder zei en je naar vroeg: ik bedoelde niet een insert binnen een insert. Ik bedoelde gewoon dat het blijkbaar lastig voor je is om in 1 code alle berekeningen uit te laten voeren dus dan lijkt het me beter om de berekeningen in kleine stukjes op te delen. één die voor N4 berekent. één die voor N3 berekent enzovoort. Heb je een wat langere code dat klopt maar is wel gemakkelijker (althans voor mij).
Gewijzigd op 03/12/2015 10:05:41 door L deB
Ik ben inmiddels alweer wat opgeschoten, en het optellen lukt heel aardig, maar ik loop tegen enkele probleempjes aan.
Ik heb nu dit:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
$sql = "SELECT uitslag_naam, SUM(punten) AS punt FROM uitslagen AS u
JOIN kalender AS k ON u.uitslag_categorie = k.categorie AND u.uitslag_seizoen = k.seizoen AND u.uitslag_id = k.kalender_id
WHERE datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."'
AND uitslag_niveau IN ('N1', 'N2') AND punten > 0
GROUP BY uitslag_naam
ORDER BY punt DESC"
JOIN kalender AS k ON u.uitslag_categorie = k.categorie AND u.uitslag_seizoen = k.seizoen AND u.uitslag_id = k.kalender_id
WHERE datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."'
AND uitslag_niveau IN ('N1', 'N2') AND punten > 0
GROUP BY uitslag_naam
ORDER BY punt DESC"
- Het kan voorkomen dat een wedstrijd het ene jaar in december is, en het andere jaar in november. Ik wil dus eigenlijk zorgen dat alleen de laatste editie (de recentste datum) van de uitslag_id erin komt. Ik doe alleen al Group By uitslag_naam (zodat de ID wordt gegroepeerd), en ook nog Group By uitslag_id werkt niet.
- Hoe krijg ik die Union er goed in voor N3 en N4, en hoe moet ik dat vervolgens optellen?
Gewijzigd op 03/12/2015 14:52:32 door G Jansma
Ik dacht juist dat je van wedstrijden van het laatste jaar de punten wilde optellen (en bij N3 en N4 alleen de beste).
Nav je tweede vraag:
ik denk dat je in 2 stukken moet hakken vanwege de N3 en N4: zoals ik om 8:24 ongeveer aangaf.
Eerst een paar union selects om de juiste wedstrijden op te halen (teruglezend zie ik wel dat je niet de laatste 5, maar de beste 5 wilt. Je zult dus de ORDER BY datum DESC moeten vervangen door ORDER BY punten DESC).
Als je die werken hebt, kun je dat in een subquery stoppen om op te tellen.
Recap: de 1e query zie 8:24, de 2e query wordt dan:
SELECT uitslag_naam, SUM(punten) FROM (.. en hier komt de 1e query)
GROUP BY uitslag_naam
ORDER BY SUM(punten) DESC
Ik hoop dat ik je goed begrijp.
Maar het kan daarnaast dus voorkomen dat een wedstrijd door datumwisseling etc. twee keer in 365 dagen plaatsvindt. Ik wil dus eigenlijk alleen de laatste editie opnemen in de stand.
Bedoel je dit met 8.24?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT uitslag_naam, SUM(punten) FROM (
SELECT * FROM uitslagen
WHERE uitslag_niveau in ("N1", "N2") -- alles van N1 en N2
UNION
SELECT * FROM (SELECT * FROM uitslagen
WHERE uitslag_niveau = "N3"
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY punten DESC LIMIT 5) AS u2
UNION
SELECT * FROM (SELECT * FROM uitslagen
WHERE uitslag_niveau = "N4"
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY punten DESC LIMIT 5) AS u3
)
GROUP BY uitslag_naam
ORDER BY SUM(punten) DESC
SELECT * FROM uitslagen
WHERE uitslag_niveau in ("N1", "N2") -- alles van N1 en N2
UNION
SELECT * FROM (SELECT * FROM uitslagen
WHERE uitslag_niveau = "N3"
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY punten DESC LIMIT 5) AS u2
UNION
SELECT * FROM (SELECT * FROM uitslagen
WHERE uitslag_niveau = "N4"
AND datum BETWEEN '".$datum."' - INTERVAL 1 YEAR AND '".$datum."
ORDER BY punten DESC LIMIT 5) AS u3
)
GROUP BY uitslag_naam
ORDER BY SUM(punten) DESC
Maar ik begrijp jou nog niet.
Hoe staan in uitslagen de 2 wedstrijden waarvan je er maar 1 mee wil nemen?
Is dat 1 per categorie, 1 per nivo?
Misschien dat wat voorbeeld data helpt.
Zal vast ook wel te doen zijn met een code of query die op een bepaalde manier naar de datums kijkt. Maar ik noem dit maar als mogelijkheid.
1 - 2015-12-03 - A - N1 - 2014-2015
1 - 2014-12-02 - A - N1 - 2013-2014
(kalender_id, datum, categorie, niveau, seizoen_
Uitslagen is vrijwel hetzelfde per rij, maar daar is elke rij dus een resultaat van een speler.
1 - A - N1 - 2014-2015 - Jan - 1
1 - A - N1 - 2014-2015 - Piet - 2
1 - A - N1 - 2014-2015 - Klaas - 3
1 - A - N1 - 2013-2014 - Joop - 1
1 - A - N1 - 2013-2014 - Piet - 2
1 - A - N1 - 2013-2014 - Jan - 3
(uitslag_id, uitslag_categorie, uitslag_niveau, uitslag_seizoen, uitslag_naam (eigenlijk id ervan), uitslag_positie)
En leg nu je verzoek van de laatste wedstrijd nog eens uit aan de hand van de data.
Overigens is het niet zo'n goed idee om data te herhalen. Als er velden uit uitslagen afhankelijk zijn van de kalender (zoals categorie, niveau en seizoen) is het beter om ze daar weg te laten en te vervangen door de kalender id. Normaliseren heet dat.
Door middel van de joins haal ik de datum op uit kalender, en vindt hij zeg maar de resultaten die bij die editie horen.
Maar stel dus dat de wedstrijd binnen 365 dagen (obv de datum) twee keer plaatsvindt, dan telt dus het resultaat van beide wedstrijden mee. Maar dat wil ik niet. Dan zou Jan dus 1e en 3e zijn, en 36 punten krijgen. Ik wil dat dan alleen de laatste editie van wedsrijd 1 meetelt, en dan heeft Jan dus 20 punten.
Ik vrees dat ik niet begrijp wat je wilt bereiken.
Ik raak telkens in de war. Aan de ene kant wil je alleen de laatste wedstrijd, aan de andere kant de 5 beste resultaten. Van wie of wat? Van een speler, van de kalender?
Ik denk dat ik hier af moet haken, sorry.