stand competitie uitrekenen
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SELECT
c.clubID,
c.clubnaam,
SUM(IF(c.clubID = u.thuisclubID,
CASE
WHEN u.thuisscore > u.uitscore THEN 3
WHEN u.thuisscore = u.uitscore THEN 1
ELSE 0 END
, /* ELSE */
CASE
WHEN u.thuisscore < u.uitscore THEN 3
WHEN u.thuisscore = u.uitscore THEN 1
ELSE 0 END
)) punt_totaal,
COUNT(u.wedstrijdID) gespeeld,
SUM(IF(c.clubID = u.thuisclubID,
IF(u.thuisscore > u.uitscore, 1, 0),
IF(u.thuisscore < u.uitscore, 1, 0)
)) gewonnen,
SUM(IF(c.clubID = u.thuisclubID,
IF(u.thuisscore < u.uitscore, 1, 0),
IF(u.thuisscore > u.uitscore, 1, 0)
)) verloren,
SUM(IF(u.thuisscore = u.uitscore, 1, 0)) gelijk,
SUM(IF(c.clubID = u.thuisclubID, thuisscore, uitscore)) doelp_voor,
SUM(IF(c.clubID = u.thuisclubID, uitscore, thuisscore)) doelp_tegen,
SUM(IF(c.clubID = u.thuisclubID, thuisscore, uitscore)) -
SUM(IF(c.clubID = u.thuisclubID, uitscore, thuisscore)) doelsaldo
FROM
clubs c
LEFT JOIN
(SELECT
wedstrijdID,
w.thuisclubID,
w.uitclubID,
COUNT(IF(d.thuisclub_uitclub = 'thuis', 1, NULL)) thuisscore,
COUNT(IF(d.thuisclub_uitclub = 'uit', 1, NULL)) uitscore
FROM
tbl_wedstrijden w
LEFT JOIN
tbl_doelpunten d
USING (wedstrijdID)
WHERE w.datumwedstrijd BETWEEN '2012-08-09' AND '2012-08-12'
GROUP BY wedstrijdID, w.thuisclubID, w.uitclubID
) u
ON c.clubID = u.thuisclubID OR c.clubID = u.uitclubID
GROUP BY c.clubID, c.clubnaam
ORDER BY punt_totaal DESC, gespeeld, doelsaldo DESC, doelp_voor DESC
c.clubID,
c.clubnaam,
SUM(IF(c.clubID = u.thuisclubID,
CASE
WHEN u.thuisscore > u.uitscore THEN 3
WHEN u.thuisscore = u.uitscore THEN 1
ELSE 0 END
, /* ELSE */
CASE
WHEN u.thuisscore < u.uitscore THEN 3
WHEN u.thuisscore = u.uitscore THEN 1
ELSE 0 END
)) punt_totaal,
COUNT(u.wedstrijdID) gespeeld,
SUM(IF(c.clubID = u.thuisclubID,
IF(u.thuisscore > u.uitscore, 1, 0),
IF(u.thuisscore < u.uitscore, 1, 0)
)) gewonnen,
SUM(IF(c.clubID = u.thuisclubID,
IF(u.thuisscore < u.uitscore, 1, 0),
IF(u.thuisscore > u.uitscore, 1, 0)
)) verloren,
SUM(IF(u.thuisscore = u.uitscore, 1, 0)) gelijk,
SUM(IF(c.clubID = u.thuisclubID, thuisscore, uitscore)) doelp_voor,
SUM(IF(c.clubID = u.thuisclubID, uitscore, thuisscore)) doelp_tegen,
SUM(IF(c.clubID = u.thuisclubID, thuisscore, uitscore)) -
SUM(IF(c.clubID = u.thuisclubID, uitscore, thuisscore)) doelsaldo
FROM
clubs c
LEFT JOIN
(SELECT
wedstrijdID,
w.thuisclubID,
w.uitclubID,
COUNT(IF(d.thuisclub_uitclub = 'thuis', 1, NULL)) thuisscore,
COUNT(IF(d.thuisclub_uitclub = 'uit', 1, NULL)) uitscore
FROM
tbl_wedstrijden w
LEFT JOIN
tbl_doelpunten d
USING (wedstrijdID)
WHERE w.datumwedstrijd BETWEEN '2012-08-09' AND '2012-08-12'
GROUP BY wedstrijdID, w.thuisclubID, w.uitclubID
) u
ON c.clubID = u.thuisclubID OR c.clubID = u.uitclubID
GROUP BY c.clubID, c.clubnaam
ORDER BY punt_totaal DESC, gespeeld, doelsaldo DESC, doelp_voor DESC
Hij doet het, en geeft alle informatie en is ook nog snel. Ik heb inmiddels de doelpunten voor 18 wedstrijden ingevoerd (57 doelpunten) en de query deed er slechts 0.0023 secondes over. Fantastisch.
Door de subquery loopt die tijd 'exponentioneel' omhoog
Gewijzigd op 12/08/2013 11:51:29 door Ger van Steenderen
Daar kan ik dus heel goed mee leven.
Wat ik inderdaad wel gemerkt heb is dat deze query exponentieel omhoog loopt in tijd. Is het daarom (vanwege die exponentiele stijging) niet toch een goed idee om meerdere tabellen te maken als ik straks ook andere competities invoer?
Dus als ik bijvoorbeeld de knvb-neker in wil voeren dat ik hiervoor een aparte wedstrijdentabel en doelpuntentabel maak? En bij het volgende seizoen van de eredivisie een nieuwe wedstrijden- en doelpuntentabel aan te maken? (En de naam van mijn huidige tabbellen te wijzigen van "tbl_wedstrijden" naar "tbl_wedstrijden_eredivisie_12/13" en "tbl_doelpunten" naar "tbl_doelpunten_eredivisie_12/13".?)
De tabel clubs (waarbij ik, net zoals bij de tabel spelers, niet van plan ben om meerdere tabellen te maken) wordt natuurlijk ook steeds groter en dat heeft dus waarschijnlijk ook negatieve gevolgen voor de tijd die nodig is om de stand van de competitie te berekenen.
Het is mij namelijk eerder aangeraden om dit allemaal in dezelfde tabellen te doen, maar ik zie niet zoveel bezwaren om daar nieuwe tabellen voor aan te maken.
Kun jij daar iets zinnigs over zeggen Ger?
PS: mag ik je nogmaals bedanken voor de fantastische query!
Dit kan je dan gewoon vervangen door een subquery die de clubs van het huidige seizoen eruit filtert, en dat zullen er dan altijd 18 blijven, en heeft dus geen nadelige gevolgen.
Dan voeg je voor de huidige competitie een aantal wedstrijden toe en kijk je of dat van invloed is op het resultaat van de EXPLAIN (hoogst waarschijnlijk niet).
PS.
Geen dank hoor, ik hem zelf ook gebruiken (zonder de subquery dan).
Gewijzigd op 12/08/2013 13:29:59 door Ger van Steenderen
Code (php)
1
2
3
4
5
2
3
4
5
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tbl_clubs ALL NULL NULL NULL NULL 18 Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 306
2 DERIVED w ALL NULL NULL NULL NULL 301 Using where; Using temporary; Using filesort
2 DERIVED d ALL NULL NULL NULL NULL 944
1 PRIMARY tbl_clubs ALL NULL NULL NULL NULL 18 Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 306
2 DERIVED w ALL NULL NULL NULL NULL 301 Using where; Using temporary; Using filesort
2 DERIVED d ALL NULL NULL NULL NULL 944
Dit krijg ik als resultaat als ik EXPLAIN ervoor zet.
Als ik je goed begrijp verwacht je dus dat extra wedstrijden en doelpunten (van andere competities/jaargangen) in de tabellen wedstrijden en doelpunten geen/nauwelijks invloed heeft op de snelheid van de query. En het dus het beste is om 1 wedstrijd- en 1 doelpuntentabel te hanteren voor alle wedstrijden en alle doelpunten uit elke competitie en elke jaargang?
Aan de andere kant: wat is nu eigenlijk het nadeel wanneer je voor elke competitie en jaargang aparte wedstrijd en doelpuntentabellen maakt? Ik zie ze niet. Eigenlijk zie ik alleen maar voordelen van verschillende tabellen:
1. Invoer is toch makkelijker en sneller.
2. Ik denk dat het ook positieve effecten (al is het misschien gering) heeft op de performance/snelheid.
3. Als een seizoen afgelopen is en de informatie staat erin, hoef je er ook niet meer aan te komen (niets meer in te voeren of aan te passen). Kun je er ook niet zo snel iets aan verkloten.
Of zie ik iets over het hoofd?
Die staan volledig los van elkaar, en dan heeft het geen enkel nut om al die data in één tabel te pompen.
Maar om per seizoen/jaargang een tabel te creëren heeft ook geen voordeel, en in mijn situatie zelfs een groot nadeel, want ik wil de mogelijkheid om statistische gegevens op te halen over alle afgelopen seizoenen.
Wat ik alleen niet begrijp: hoezo kun je geen statistische gegevens gebruiken als je voor een competitie voor elke jaargang een aparte wedstrijden- en doelpuntentabel maakt? Je kunt de voorgaande jaren toch ook gewoon opvragen? Ik zie het grote nadeel daarvan niet zo goed.
En stel ik maak wel per competitie voor elke jaargang/seizoen een aparte doelpunten- en wedstrijdentabel. Krijg ik dan straks problemen als ik bijvoorbeeld bij spelers hun statistieken (gespeelde wedstrijden, doelpunten en assists) van voorgaande jaren wil laten zien? En wat voor problemen zijn dat dan?
Stel ik heb een speler en wil de volgende informatie laten zien:
Seizoen Competitie Gespeeld Doelpunten Assist
2013/14 x x x x
2012/13 x x x x
2011/12 x x x x
enz.
Krijg ik dan problemen als ik voor elk seizoen een aparte wedstrijden- en doelpuntentabel maak?
Ik denk dat je in dat geval minder flexibel bent dan alle doelpunten in één tabel met daaraan toegevoegd een foreign key per seizoen/competitie.
Als je de doelpunten van een speler voor seizoen 2012-2013 wilt laten zien die in de eredivisie en de Premier League heeft gespeeld heb je in jouw geval een join nodig om de gegevens bij elkaar te vinden, maar de sql zal niet hetzelfde zijn als die voor een speler die bijvoorbeeld in de Eredivisie en de Eerste divisie (andere tabelnaam) heeft gespeeld.
Wanneer je één tabel voor doelpunten hebt, kun je voor beide spelers wel dezelfde SQL blijven gebruiken.
Nu we hier toch met het voetbal bezig zijn. Ik ben voor mijn eigen site aan het kijken hoe ik zo efficient mogelijk een score-matrix kan maken. Zoiets als
Code (php)
1
2
3
4
5
2
3
4
5
Aja Fey PSV Vit
Ajax xxx 2-1
Feyenoord xxx
PSV xxx 2-0
Vitesse xxx
Ajax xxx 2-1
Feyenoord xxx
PSV xxx 2-0
Vitesse xxx
Hoe zouden jullie dat aanpakken. Eventueel niet gespeelde wedstrijden, zou ik dan graag voorzien van een datum waarop ze staan gepland.
Gewijzigd op 12/08/2013 16:07:04 door Jeroen Jansen
Jeroen Jansen op 12/08/2013 15:59:32:
Je krijgt dan misschien geen probleem, maar zult alle tabellen moeten joinen.
Ik denk dat je in dat geval minder flexibel bent dan alle doelpunten in één tabel met daaraan toegevoegd een foreign key per seizoen/competitie.
Ik denk dat je in dat geval minder flexibel bent dan alle doelpunten in één tabel met daaraan toegevoegd een foreign key per seizoen/competitie.
Ger van Steenderen op 12/08/2013 15:54:09:
Dat heeft als gevolg dat je elk jaar je query moet gaan aanpassen, en dat kan niet de bedoeling zijn (in ieder geval niet de mijne)
Je kan die data wel bij elkaar krijgen, dat is het probleem niet.
Dat onderscheid tussen seizoen/competitie moet je in de wedstrijden tabel aanbrengen anders geeft de query voor de uitslagen een onjuist resultaat.
Jeroen Jansen op 12/08/2013 15:59:32:
Hoe zouden jullie dat aanpakken. Eventueel niet gespeelde wedstrijden, zou ik dan graag voorzien van een datum waarop ze staan gepland.
Als de volgorde van de clubs zoals je ze toont dezelfde is als waarop ze zijn ingevoerd:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
c.clubID,
c.clubnaam,
w.uitID,
IF(u.thuisscore IS NULL, DATE_FORMAT('%d-%m-%y', w.datum),
CONCAT_WS('-', thuisscore, uitscore)) uitslag_cq_datum
FROM
clubs c
JOIN
wedstrijden w ON c.clubID = w.thuisID
LEFT JOIN
uitslagen u USING (wedtrsijdID)
ORDER BY
c.clubID, w.uitID
c.clubID,
c.clubnaam,
w.uitID,
IF(u.thuisscore IS NULL, DATE_FORMAT('%d-%m-%y', w.datum),
CONCAT_WS('-', thuisscore, uitscore)) uitslag_cq_datum
FROM
clubs c
JOIN
wedstrijden w ON c.clubID = w.thuisID
LEFT JOIN
uitslagen u USING (wedtrsijdID)
ORDER BY
c.clubID, w.uitID
Met deze info moet je in PHP die matrix kunnen samenstellen
Maar je reactie, Jeroen, heeft me wel weer aan het denken gezet. Als een speler een transfer heeft gemaakt naar of van een andere competitie dan krijg ik (query)problemen bij het weergeven van de statistieken van die speler.
Wat is wijsheid?
Ik heb 4 verschillende opties:
A: Alle competities en alle jaargangen in 1 tabel wedstrijden en doelpunten.
B: Voor elke competitie een aparte tabel wedstrijden en doelpunten.
C: Voor elke jaargang een aparte tabel wedstrijden en doelpunten.
D: Voor elke competitie en elke jaargang een aparte tabel wedstrijden en doelpunten.
Bij A ben ik toch wel een beetje bang voor performance. Ik merkte namelijk al bij de tabel spelers (waar aardig wat informatie in zit) dat de laadtijd voor ik een nieuwe speler kon invoeren al snel stukken groter werd naarmate er meer spelers in de tabel kwamen. Wat nu straks als ik vijf competities doe en er al vijf jaar om zijn. Dan krijg je enorme grote tabellen niet?
Of is dit gewoon een irreele angst wat in mn hoofdje zit geprent?
En stel ik kies niet voor A, dan maakt dat voor de weergave van de stand niet veel uit (ja, ik moet eens per jaar de tabelnamen van de "stand"-query aanpassen), maar wordt het wat lastiger wat betreft de (historische) statistieken van een speler. Die gaan namelijk over meerdere seizoen en meerdere competities (dus meerdere tabellen).
Poehee. Kan iemand daar iets zinnigs over zeggen?
Toevoeging op 12/08/2013 17:46:36:
PS: @Ger
Dus stel ik maak onderscheid in tabellen voor wat betreft competitie en seizoen, dan moet het onderscheid tussen competitie en seizoen komen uit tabel wedstrijden (en dus niet uit tabel doelpunten).
Maar waar geef ik dat onderscheid dat in de "stand"-query aan?
Door na
Code (php)
1
WHERE w.datumwedstrijd BETWEEN '2012-08-09' AND '2012-08-20'
en voor GROUP BY het volgende:
Code (php)
1
AND w.competitie = 'Eredivisie' AND w.seizoen = '2012/2013'
?
Gewijzigd op 12/08/2013 17:50:08 door Jo Immanuel
En reken zelf eens uit hoeveel records je in de doelpuntentabel krijgt bij 5 competities en 5 seizoenen.
Gebaseerd op 18 teams per competitie en gemiddeld 3 doelpunten per wedstrijd kom ik op ca 23000 records, dat stelt geen ene moer voor.
Je moet gewoon ervoor zorgen dat je de juiste indexen hebt, en als je twijfels hebt gewoon uitproberen via EXPLAIN
Gewijzigd op 12/08/2013 19:59:11 door Ger van Steenderen
En verder moet ik dus alleen competitie (bijv. eredivisie) meenemen in de wedstrijden tabel. En niet seizoen (bijv. 2012/2013) aangezien je deze eigenlijk al meegeeft met (/af kunt leiden uit) de datum van de wedstrijd.
De stand berekenen ik dan volgens de door jou gegeven "stand"-query waarbij ik na
Code (php)
1
WHERE w.datumwedstrijd BETWEEN '2012-08-09' AND '2012-08-20'
en voor GROUP BY de volgende toevoeging doe:
Code (php)
1
AND w.competitie = 'Eredivisie'
?
PS: Hoe weet ik als ik EXPLAIN gebruik voor de query of de query slechter wordt qua performance? Moet ik dan naar de rijen kijken (nu 18, 306, 301, 944) en controleren of wanneer ik extra competities en/of extra jaargangen toevoeg, (en natuurlijk op de juiste manier de subqueries heb gezet in de "stand"-query) deze niet meer worden?
En als deze rijen niet meer worden, dan blijft de performance (nagenoeg) hetzelfde ondanks het grotere aantal records in tabel wedstrijden en doelpunten?
Gewijzigd op 12/08/2013 20:22:29 door Jo Immanuel
Maar wat ik doe is alleen maar inschattingen maken op jouw sitatie, wil het zeker weten is het een kwestie van dat invoeren en dan door de query planner (EXPLAIN) heen te gooien, of echt benchmarks uit te voeren.
Gewijzigd op 12/08/2013 20:55:58 door Ger van Steenderen
1. Bedoel je hiermee dat je adviseert in een tabel helemaal geen (var)chars te gebruiken, maar beter INT? Of geldt dit alleen/voornamelijk voor de PK's, FK's en de zaken waarop gezocht/geselecteerd moet worden?
2. Competitie in de tabel wedstrijden heb ik nu als (var)char. Die kan ik dus (gezien performance) beter als fk (in integer-vorm) binnen halen in tabel wedstrijden?
3. Verder heb ik zojuist een beetje lopen zoeken over indexering, maar nu lijkt het erop dat de tabellen van mij niet helemaal optimaal ingesteld zijn qua indexing volgens mij.
EXPLAIN:
Code (php)
1
2
3
4
5
2
3
4
5
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tbl_clubs ALL NULL NULL NULL NULL 18 Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 306
2 DERIVED w ALL NULL NULL NULL NULL 301 Using where; Using temporary; Using filesort
2 DERIVED d ALL NULL NULL NULL NULL 944
1 PRIMARY tbl_clubs ALL NULL NULL NULL NULL 18 Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 306
2 DERIVED w ALL NULL NULL NULL NULL 301 Using where; Using temporary; Using filesort
2 DERIVED d ALL NULL NULL NULL NULL 944
Daar vallen twee dingen op:
a. Er staat bij kolom 'Extra' de waarde "Using temporary". Dit kost (extra) intern geheugen van de server. Maar daar ontkom ik volgens mij bij de "stand"-query niet aan, omdat er tijdelijke tabellen worden gebruikt in de query om de doelpunten en punten te berekenen.
b. Bij de kolommen 'possible keys', 'keys' (, 'key len' en 'ref') staat "Null". Als er een goede indexing zou zijn dan zouden die toch gevuld moeten zijn?
Gewijzigd op 12/08/2013 22:42:07 door Jo Immanuel
2. Ja :-) en dan dus 1 FK voor competitie/jaargang
3a. Using temporary is het gevolg van een subquery en/of group by, dus daar ontkom je inderdaad niet aan
3b. Een derived table (een subquery) heeft geen indexen, dus NULL possible keys. Bij de rest zou daar altijd iets ingevuld moeten zijn, en ik kan even niet zo snel ontdekken waarom dat bij de clubs tabel niet zo is.
Je moet in ieder geval een index zetten op de datum in de wedstrijd tabel.
Of een key wel of niet gebruikt wordt is afhankelijk van de query, je selecteert bv alle clubs dus hoeft er geen key gebruikt te worden.