[MySQL] Rank gebaseerd op punten en tijd

[The.Terminator]

Ik zit met het volgende issue.
Ik heb een tabel waarin gebruikers staan die mee hebben gedaan in een quiz.

Tabel heeft
ID
FIRSTNAME
LASTNAME
POINTS
TIME (in secondes).

Als de gebruiker klaar is met de quiz moet die de score + tijd vergelijken met eerder ingevulde scores en de gebruiker de 2 scores boven- en onder hem terug te geven.

Bijvoorbeeld

Ik heb 5 vragen goed in 60 secondes
Jan heeft 7 vragen goed in 70 secondes
Mike heeft 5 vragen goed in 55 secondes
Stef heeft 4 vragen goed in 35 secondes
Sjaak heeft 1 vragen goed in 35 secondes

Dan moet die bijvoorbeeld terug geven

1. Jan
2. Mike
3. MIJ
4. Stef
5 Sjaak

Nu vond ik via Stackoverflow een query waarbij Rank werd gebruikt, maar dit blijkt niet te bestaan in MySQL. Ook via order by krijg ik het niet voor elkaar. Moet ik gaan rekenen of kan dit gewoon met een query.

[RobIII]

Je kunt gewoon rekenen in een query, dus, bij wijze van, zoiets:

SQL:

select `foo`, `bar`, `points`, (`time` / `points`) * `points` as `score` 
from `mytable` 
order by `score` desc

Implementeer je berekening, sorteer er op en done.
Je hoeft 'score' niet eens in je select op te nemen, je kunt de berekening gewoon in je order by clause zetten. En, als het om veel records gaat, misschien dat het zelfs nog beter is een extra veld in je tabel op te nemen en die 'score' meteen bij 't inserten/updaten in te vullen; dan hoef je er alleen nog maar op te orderen i.p.v. bij elke query die berekening los te laten.

[ Voor 95% gewijzigd door RobIII op 08-04-2014 12:22 ]

[The.Terminator]

thx, ga hiermee aan de slag.
Het moet volgens mij dan zijn meer zijn zoals dit

select `foo`, `bar`, `points`, (`time` / `points`) * `points` as `score`
from `mytable`
order by `points` desc`, `score` desc

Aangezien je bij 1 vraag goed in 35 secondes een hogere waarde krijgt als 4 vragen goed in 35 secondes.
Maar de 4 vragen heeft een hogere rank.

EDIT : ik zie het, gewoon een order op punten desc, en daarna time desc en het werkt ook.
Hoe ik erbij kwam dat het met een moeilijke query moest

[ Voor 17% gewijzigd door The.Terminator op 08-04-2014 12:25 ]

[andydewit]

SQL:

select `foo`, `bar`, `points`, (`time` / `points`) * `points` as `score`  
from `mytable`  
order by `score` desc

Zie je wat er in deze query gebeurd? 'time' deel je door 'points' en doe je daarna keer 'points', waardoor je alsnog 'time' krijgt. Dit is beter:

SQL:

SELECT
    foo,
    bar,
    points,
    (points / totalTime) as score
FROM
    mytable
ORDER BY
    score DESC

Ik heb de punten en tijd omgedraaid, waardoor je nu een hogere score krijgt bij 2 goede antwoorden in 30 seconden dan 1 goed antwoord in 30 seconden. Tevens heb ik 'time' vervangen door 'totalTime', is namelijk wat duidelijker voor SQL. Time is namelijk een functie in SQL. Door het gebruiken van 'totalTime' hoef je geen haakjes er omheen te doen.

[jbdeiman]

Score berekenen hoeft dan toch niet? Volgens mij zegt ts in de post boven andydewit dat ook al: Punten telt eerst, hoogste punten komt vooraan, aflopend gesorteerd, dan komt de tijd met oplopende sortering.

Enige wat (mogelijk) lastiger wordt:
Haal de 2 voor en de 2 na de "nieuwe score" op, dit staat ook in de openingspost. Ik zie op dit moment geen oplossing dit in de query te doen.

[ Voor 0% gewijzigd door jbdeiman op 08-04-2014 17:19 . Reden: typo ]

[RobIII]

andydewit schreef op dinsdag 08 april 2014 @ 17:13:
Zie je wat er in deze query gebeurd? 'time' deel je door 'points' en doe je daarna keer 'points', waardoor je alsnog 'time' krijgt. Dit is beter:

Daarom zei ik ook:

RobIII schreef op dinsdag 08 april 2014 @ 12:11:
dus, bij wijze van, zoiets
[...]
Implementeer je berekening

(Neemt niet weg dat ik niet helemaal wakker was )

Anyhow; onze modus operandi in Programming is niet voor niets:

Give a man a fish and feed him for a day. Teach a man how to fish and feed him for a lifetime.

We kauwen hier dus bij voorkeur niet het complete antwoord voor maar laten nog enig denkwerk over aan de vraagsteller Het ging me dan ook meer om 't principe dan om de daadwerkelijke berekening.

andydewit schreef op dinsdag 08 april 2014 @ 17:13:
Tevens heb ik 'time' vervangen door 'totalTime', is namelijk wat duidelijker voor SQL. Time is namelijk een functie in SQL. Door het gebruiken van 'totalTime' hoef je geen haakjes er omheen te doen.

Dat is een andere discussie; ik (persoonlijk) ben gewoon voorstander van dingen noemen zoals ze zijn en gewoon per definitie alles escapen (be it met `foo` in MySQL of [foo] in MSSQL etc.) zodat je je nooit zorgen hoeft te maken of iets een reserved word is of niet (en belangrijker: in de toekomst mogelijk ooit een reserved word wordt). Over dit specifieke geval: of time een betere/slechtere benaming is dan totaltime kun je betwisten; misschien dat de total...-variant beter is inderdaad.

jbdeiman schreef op dinsdag 08 april 2014 @ 17:19:
Score berekenen hoeft dan toch niet? Volgens mij zegt ts in de post boven andydewit dat ook al: Punten telt eerst, hoogste punten komt vooraan, aflopend gesorteerd, dan komt de tijd met oplopende sortering.

Klopt ook, maar TS wekte in de openingspost wel een beetje de indruk dat de "eindscore" gebaseerd was op een bepaalde "weging" waarbij je dus punten + tijd in ogenschouw zou moeten nemen; achteraf blijkt een sortering op punten (aflopend) en dan tijd (oplopend) voldoende te zijn.

[ Voor 45% gewijzigd door RobIII op 08-04-2014 19:18 ]

[Woy]

Is het in het voorbeeld niet gewoon zo simpel om eerst te sorteren op punten en dan op tijd? In het voorbeeld dat gegeven wordt klopt dat in ieder geval, en dat is ook meestal hoe een score systeem werkt.

Mocht er toch een complexere formule achter zitten kun je inderdaad gewoon met de opmerking van RobIII aan de slag. Alleen meestal mag je niet op een alias sorteren ( In MySQL misschien wel ), en zul je dus de berekening over moeten nemen in de order by.

[P.O. Box]

dit kun je denk ik wel als basis gebruiken...

SQL:

SELECT *
FROM mytable
where userid = 5
union 
(
select
* from mytable
where points > x
order by points asc
limit 2
)
union 
(
select
* from mytable where points < x
order by points desc
limit 2
)

order by points

[jbdeiman]

RobIII schreef op dinsdag 08 april 2014 @ 19:12:
[...]

Klopt ook, maar TS wekte in de openingspost wel een beetje de indruk dat de "eindscore" gebaseerd was op een bepaalde "weging" waarbij je dus punten + tijd in ogenschouw zou moeten nemen; achteraf blijkt een sortering op punten (aflopend) en dan tijd (oplopend) voldoende te zijn.

Was ook niet als "verwijt" richting jou, maar meer iets wat mij uit zijn laatste opmerking opviel. Vandaar dat ik het even aanhaalde, zodat nieuwe reacties wat meer in die richting gaan.

P.O. Box schreef op woensdag 09 april 2014 @ 10:58:
dit kun je denk ik wel als basis gebruiken...

SQL:

SELECT *
FROM mytable
where userid = 5
union 
(
select
* from mytable
where points > x
order by points asc
limit 2
)
union 
(
select
* from mytable where points < x
order by points desc
limit 2
)

order by points

Het lastige krijg je in dit geval bij "gelijk aantal punten en gelijke tijd".

Enige wat ik mij zo kan bedenken is: Bepaal de "positie" van MIJN tijd in de resultatenlijst. Limit de query op "mijn positie -2" tot "mijn positie +2"

[P.O. Box]

jbdeiman schreef op woensdag 09 april 2014 @ 11:15:
[...]

Het lastige krijg je in dit geval bij "gelijk aantal punten en gelijke tijd".

gelijk aantal punten en gelijke tijd? dan zou ik zelf voor alfabetisch gaan als 3e sortering... ipv moeilijk doen om alleen met gelijke punten en tijd weer te geven...

verder is het natuurlijk het makkelijkst om dit niet in 1 query te proppen...

[andydewit]

SQL:

SELECT * 
FROM mytable 
where userid = 5 
union  
( 
select 
* from mytable 
where points >= x  and userid != 5
order by points asc 
limit 2 
) 
union  
( 
select 
* from mytable where points <= x and userid != 5
order by points desc 
limit 2 
) 

order by points

Zo is het toch opgelost? Mocht er iemand zijn met hetzelfde aantal punten, dan wordt deze automatisch gekozen.