[MySql] Update resultaten tabel met ranking

[mdewildt]

Ik heb samen met een vriend van mij een hobby projectje *snip*
Op deze site plaatsen we allerlei info over wedstrijdzwemmen in Nederland (en soms ook uit het buitenland).
Verder komt er op deze site ook een ranglijsten systeem, te vinden onder de tab Zwemrank

Nu staan er al iets van 2000 uitslagen in de database, maar bij het importeren bleek er een foutje in geslopen te zijn. Doordat het bestandsformaat van de uitslagen was gewijzigd is in de uitslagen tabel de plaats (1e, 2e, 3e, etc) niet goed ingevuld, er staat nu overal 0.
Daardoor is de sortering in de uitslag niet goed.
Natuurlijk zouden we de ranglijsten op kunnen bouwen aan de hand van de tijden, maar dan komen we later in de problemen met diskwalificaties en wat andere zaken.

Dus waar we naar op zoek zijn is een mogelijkheid om de plaats-kolom in de result-table aan te passen.
De gegevens :

Tabelnaam : aton_result
Kolommen :
id - bigint (20)
swimtime - varchar (11)
place - smallint (5)
event_id - int (10)
athlete_id - int (10)
club_id - int (10)
event_agegroup_id - bigint (10)
points - int (11)
reactiontime - varchar (6)
status - enum ('exh', 'dsq', 'dns', 'dnf', 'wdr')
comment - varchar (255)

Zoals gezegd zijn alle kolommen behalve place goed ingevuld.
We willen dus aan de hand van de swimtime en event_id de kolom place vullen met een volgnummer, waarbij de snelste tijd uit een bepaald event een 1 krijgt, de 2e tijd een 2, etc.
En dat dan voor alle events opnieuw vanaf 1.

Onderzoek op internet leverde me op dat het waarschijnlijk met een cursor moet/kan, maar daar gaat het echt boven mijn pet.
Natuurlijk wordt de importer voor de uitslagen ook aangepast, zodat dit niet meer voorkomt, maar dat kan door tijdgebrek nog wel even duren

Mocht diegene die de oplossing aandraagt trouwens genoemd willen worden op onze site, dan is dat uiteraard mogelijk

Martin

[ Voor 0% gewijzigd door Creepy op 22-09-2009 12:30 ]

[Creepy]

En i.p.v. een cursor er een stukje code er tegenaan gooien die de resultaten ophaalt, plaats berekend en vervolgens een update doet in de DB? Lijkt me een kleine moeite voor iemand met een beetje PHP/MySQL kennis.

[Johnny Goodbye]

Ik heb het een tijdje geleden iets vergelijkbaars gemaakt voor mijn (joomla) voetbal toto component.

het moet er zo ongeveer uit zien:

SQL:

SET @rownum=0;update aton_result  as a,(SELECT id,swimtime,@rownum:=@rownum+1 place1
                    FROM aton_result
                    where event_id=1
                    order by even_id,swimtime desc) as b
                    set a.place=b.place1
                    where a.id=b.id

volgens mij moet je het wel per event doen. Anders probeer even de where weg te halen.

[Wokkels]

Ja en zorg dat je even test op een kopietje van je tabel. Met dit soort queries kan je ook je hele tabel vernaggelen

[cariolive23]

Hou wel in de gaten dat meerdere personen dezelfde tijd kunnen hebben en dus op een gedeelde plaats komen. Diegene die daarna komt, krijgt dan plaats (X + aantal_personen_met_dezelfde_tijd).

Maar waarom zou je dit eigenlijk willen opslaan? Je kunt dit toch berekenen wanneer je dit gegeven wilt opvragen? Dat maakt het herstellen van fouten ook eenvoudiger.

Ps. Met bv. SQL Server of PostgreSQL kun je dit eenvoudig oplossen met een WINDOW FUNCTION, RANK() OVER() berekent dit on the fly en houdt ook rekening met gedeelde plaatsen.

[Johnny Goodbye]

@cariolive Het klopt dat je ook vaak met functies als rank() on the fly dit kan doen of d.mv. een simple order by + php code. Maar in het kader van o.a. betere performance kan je ook op deze manier doen.

[cariolive23]

Een ORDER BY houdt geen rekening met dezelfde scores (zet ze gewoon op een rij), in PHP zul je dat dus ook nog moeten bouwen. En performance, waar hebben we het over. Dit is een hele simpele functie die nauwelijks invloed heeft op de performance en er is nog helemaal geen performance probleem gemeld. Ik hou het dan liever eenvoudig, dat is snel te bouwen en goed te onderhouden. De bouwer levert dan betere performance...

[_js_]

Een self join of subquery is volgens mij nog simpeler en kan rekening houden met gedeelde plaatsen zonder veel code.

SELECT a.*, COUNT(b.id) + 1 AS place
FROM tabel AS a INNER JOIN tabel AS b ON a.event_id = b.event_id AND a.swimtime > b.swimtime
GROUP BY a.id -- en andere kolommen uit a als je verouderde ansi regels wilt opvolgen

Alleen swimtime als varchar kolom komt niet echt handig over voor deze oplossing, want de vraag is of je de verschillende waarden kunt vergelijken met >.

[cariolive23]

GROUP BY a.id -- en andere kolommen uit a als je verouderde ansi regels wilt opvolgen

Hap!

Mag ik even heel hard lachen? Wanneer jij denkt dat de ANSI-regels zijn bedacht om maar wat zinloze regels te kunnen hanteren, dan heb je blijkbaar niet opgelet bij wiskunde. Er zit enige logica achter, ga je maar eens verdiepen in de verzamelingenleer. Daarin worden geen random resultaten opgehoest. Of vind jij een random resultaat die iedere keer anders kan zijn, ondanks dezelfde data, ook een geldig resultaat? Hoe zou je het vinden als jouw bank dat zo gaat toepassen op jouw bankrekening? Of is ANSI-SQL dan ineens wel gewenst?

Hou jezelf voor de gek, maar doe niet voorkomen alsof deze regels kant noch wal raken. Daar is beter over nagedacht dan over jouw zinloze opmerking.

[_js_]

cariolive23 schreef op dinsdag 22 september 2009 @ 21:14:
[...]


Hap!

Mag ik even heel hard lachen? Wanneer jij denkt dat de ANSI-regels zijn bedacht om maar wat zinloze regels te kunnen hanteren, dan heb je blijkbaar niet opgelet bij wiskunde. Er zit enige logica achter, ga je maar eens verdiepen in de verzamelingenleer. Daarin worden geen random resultaten opgehoest. Of vind jij een random resultaat die iedere keer anders kan zijn, ondanks dezelfde data, ook een geldig resultaat? Hoe zou je het vinden als jouw bank dat zo gaat toepassen op jouw bankrekening? Of is ANSI-SQL dan ineens wel gewenst?

Hou jezelf voor de gek, maar doe niet voorkomen alsof deze regels kant noch wal raken. Daar is beter over nagedacht dan over jouw zinloze opmerking.

Ken jij die specifieke ANSI regel ook? Als je hem zou kennen dan zou je weten dat in dit geval geen random data wordt teruggegeven en daarom dus ook de rest van de velden niet in de group by hoeft.

De regel zegt iets over dat velden--die functioneel afhankelijk zijn van een ander veld dat al in de group by staat--niet in de group by hoeven worden opgenomen.

Aangezien a.id een unieke waarde is zijn de andere waarden uit a per definitie daar functioneel van afhankelijk en hoeven volgens de huidige ANSI regels niet in de group by.