[MySQL] Ranking weergeven

[mcdronkz]

Ik heb een statistieken script geschreven waarbij ik kan monitoren hoeveel minuten een bepaalde gebruiker op een bepaald forum online is.

Ik haal dus de data als volgt uit de database:

• 50 meest actieve gebruikers
• 50 minst actieve gebruikers
• Moderators/nieuwsposters/etc.

Nu wil ik hier dus een ranking aanhangen. Ik heb nu in totaal 1500 records, en ik zou dus zo'n resultaat willen krijgen als ik moderators bijvoorbeeld weergeef:

#userid ranking: 45
#userid ranking: 332
#userid ranking: 555
#userid ranking: 1440

En als ik een #userid invul wil ik dus direct een ranking gebaseerd op het aantal online minuten kunnen zien.

Het makkelijkste lijkt me om on-the-fly een "veld" aan te maken bij m'n SELECT query, maar ik heb geen flauw idee hoe ik dit moet bewerkstelligen. Ook na tijd gezocht te hebben op google en GoT kwam ik niet verder. Wie weet hier wel een oplossing voor ?

[ Voor 6% gewijzigd door mcdronkz op 22-08-2006 19:16 ]

[Creepy]

Ik snap niet precies wat je bedoeld. Hoe bepaal je nu (query?) hoeveel minuten een bepaalde gebruiker online is geweest en hoe moet dit worden gaan omgezet in een ranking?

[Metalman]

Even een gokje van mijn kant richting de bedoelingen van de TS: in feite heb je een soort ranglijsten (zijn dat gewoon SELECT name FROM members ORDER BY posts LIMIT 50 queries of is het anders opgeslagen?) en nu wil je bij een bepaald user id weten op welke plaats die user staat in het lijstje?

Het antwoord hierop is erg afhankelijk van je DB structuur. Misschien dat je je tabellen eens kan tonen, en iets duidelijker uit kan leggen wat je precies wilt.

[mcdronkz]

CREATE TABLE `stats` (
`id` int(10) NOT NULL auto_increment,
`uID` int(10) NOT NULL,
`uName` varchar(250) default NULL,
`lastSighted` int(100) NOT NULL,
`minutesOnline` int(50) NOT NULL,
`firstSighted` int(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1425 ;

Dit is m'n tabelstructuur, ik weet dus al hoeveel minuten een bepaalde gebruiker online is geweest. Dit doet er dus verder niet meer toe.

Het enige wat ik nu wil is dus kijken wat voor ranking een bepaalde uID heeft, gebaseerd op minutesOnline.

Hier gaat het om, http://mcdronkz.ath.cx:1337/bf/stats/ . Klik maar eens op de eerste button, ik heb al een kolom gemaakt genaamd "ranking".

Ik hoop dat het nu duidelijk is wat ik bedoel

[DexterDee]

SELECT count(*)+1 as rank FROM stats WHERE minutesOnline>(SELECT minutesOnline FROM stats WHERE uID=$user_id)

Waarbij $user_id het user ID is van de gebruiker waar je de rank van wilt weten

Zoiets zou moeten werken

[mcdronkz]

DexterDee schreef op dinsdag 22 augustus 2006 @ 21:40:
SELECT count(*)+1 as rank FROM stats WHERE minutesOnline>(SELECT minutesOnline FROM stats WHERE uID=$user_id)

Waarbij $user_id het user ID is van de gebruiker waar je de rank van wilt weten

Zoiets zou moeten werken

Die werkt inderdaad prima, maar hoe doe ik dat dan als ik ze allemaal uit de database haal ?

Zoiets als dit:

SELECT *,count(*)+1 as rank FROM stats WHERE minutesOnline>(SELECT minutesOnline FROM stats WHERE uID=stats.uID)

[DexterDee]

Die query gaat niet werken bij meerdere personen tegelijk (tenzij je 'm per persoon herhaalt).

Deze query gaat wel werken:

SELECT *, (SELECT count(*)+1 as rank FROM stats WHERE minutesOnline>(SELECT minutesOnline FROM stats s2 WHERE s1.uID=s2.uID LIMIT 1)) as rank FROM stats s1 ORDER BY rank ASC

[Arjen Tempel]

Voor een overzicht moet je gewoon een teller toevoegen voor de ranking en vervolgens de inhoud aflopend sorteren op het aantal minuten online:

SQL:

SET @rownr = 0;
SELECT * , @rownr:= @rownr + 1 as rank FROM stats ORDER BY minutesOnline DESC;

[mcdronkz]

Arjen Tempel schreef op dinsdag 22 augustus 2006 @ 22:33:
Voor een overzicht moet je gewoon een teller toevoegen voor de ranking en vervolgens de inhoud aflopend sorteren op het aantal minuten online:

SQL:

SET @rownr = 0;
SELECT * , @rownr:= @rownr + 1 as rank FROM stats ORDER BY minutesOnline DESC;

Ja oke, maar dat kan ik ook wel met een PHP variabele. Ik wil echt de absolute ranking hebben, en niet alleen de ranking van een bepaald lijstje, maar de ranking in het geheel .

DexterDee schreef op dinsdag 22 augustus 2006 @ 22:04:
Die query gaat niet werken bij meerdere personen tegelijk (tenzij je 'm per persoon herhaalt).

Deze query gaat wel werken:

SELECT *, (SELECT count(*)+1 as rank FROM stats WHERE minutesOnline>(SELECT minutesOnline FROM stats s2 WHERE s1.uID=s2.uID LIMIT 1)) as rank FROM stats s1 ORDER BY rank ASC

Als ik die uitvoer loopt de hele boel vast.

[DexterDee]

Hier werkt die query prima, ik heb jouw structure geimporteerd en 4 testrecords aangemaakt:



Welke versie van MySQL gebruik je?

[mcdronkz]

DexterDee schreef op dinsdag 22 augustus 2006 @ 22:56:
Hier werkt die query prima, ik heb jouw structure geimporteerd en 4 testrecords aangemaakt:

[afbeelding]

Welke versie van MySQL gebruik je?

MySQL - 5.0.21-community-nt

Ondersteund dus gewoon subquries. Ik heb natuurlijk 1425 records, maar aan de andere kant heb ik er wel een limit op staan. Merkwaardig dus !

[DexterDee]

Ik heb MySQL 5.0.18 op Linux. Als die gegevens niet al te "secure" zijn kun je eens een SQL dumpje maken. Die kan ik dan hier importeren en die query nog een keertje draaien. Lijkt me sterk dat 1500 records met *die* query de databaseserver op z'n knieen krijgt

[mcdronkz]

DexterDee schreef op dinsdag 22 augustus 2006 @ 23:11:
Ik heb MySQL 5.0.18 op Linux. Als die gegevens niet al te "secure" zijn kun je eens een SQL dumpje maken. Die kan ik dan hier importeren en die query nog een keertje draaien. Lijkt me sterk dat 1500 records met *die* query de databaseserver op z'n knieen krijgt

Is geen enkel probleem

http://mcdronkz.ath.cx:1337/bf/stats/stats.sql

[Voutloos]

mcdronkz schreef op dinsdag 22 augustus 2006 @ 23:05:Ondersteund dus gewoon subquries. Ik heb natuurlijk 1425 records, maar aan de andere kant heb ik er wel een limit op staan. Merkwaardig dus !

Op zich is 1425 rijen niets. Er wordt echter gesorteerd op de waarde van rank, en die kan je alleen weten als je hem voor alle records bepaald hebt. Limit helpt dus niet echt.

Overigens heb ik een vraag over het datamodel: wat doet uName in de tabel stats? uID verwijst al naar de gegevens van de user in kwestie, dus die data is redundant.

En nog een vraag: een Stat is dus eigenlijk een gegeven van 1 sessie? Maw: voor 1 user kunnen meerdere rijen in Stats staan? Zo ja: dan is de query van DexterDee niet correct, want dan moet er nog iets met bijvoorbeeld SUM() gedaan worden.

[mcdronkz]

Voutloos schreef op dinsdag 22 augustus 2006 @ 23:29:
[...]
Op zich is 1425 rijen niets. Er wordt echter gesorteerd op de waarde van rank, en die kan je alleen weten als je hem voor alle records bepaald hebt. Limit helpt dus niet echt.

Overigens heb ik een vraag over het datamodel: wat doet uName in de tabel stats? uID verwijst al naar de gegevens van de user in kwestie, dus die data is redundant.

En nog een vraag: een Stat is dus eigenlijk een gegeven van 1 sessie? Maw: voor 1 user kunnen meerdere rijen in Stats staan? Zo ja: dan is de query van DexterDee niet correct, want dan moet er nog iets met bijvoorbeeld SUM() gedaan worden.

Nee hoor, voor elke user is er één rij. Wordt gewoon geupdate. Die uName moet ik wel hebben, want ik haal de gegevens uit deze lijst, en heb verder geen toegang tot die database .

[Voutloos]

Maar blijkbaar heb je nu al ergens een where clause om alleen de rijen met modjes te selecteren, voeg die dan ook toe in de reeds gegeven query, dan worden de subqueries niet uitgevoerd voor alle andere users.

[DexterDee]

Ik moet bij die eerste query ook een onacceptabele lange tijd wachten met die 1500 records. Er missen sowieso de juiste indexes (bijv. op minutesOnline en uID)

Deze query doet het bij mij beter:

SELECT *, (SELECT count(*)+1 as rank FROM stats WHERE minutesOnline>(SELECT minutesOnline FROM stats s2 WHERE s1.uID=s2.uID LIMIT 1)) as rank FROM stats s1 ORDER BY minutesOnline DESC limit 50

Met de limit laat hij de top 50 zien. Resultaat binnen een halve seconde hier, dus qua performance geen toppertje maar toch een heel stuk sneller dan de eerste query

[Voutloos]

Het is dat het geen heel serieuze toepassing is, maar een query van 0.5s als de dataset maar uit 1500 kleine rijen bestaat is normaal alles behalve acceptabel.

Verder zal ik wel een minder helder momentje hebben maar ipv de binnenste subquery kan je toch gewoon s1.minutesOnline neerzetten?

[mcdronkz]

DexterDee schreef op dinsdag 22 augustus 2006 @ 23:47:
Ik moet bij die eerste query ook een onacceptabele lange tijd wachten met die 1500 records. Er missen sowieso de juiste indexes (bijv. op minutesOnline en uID)

Deze query doet het bij mij beter:

SELECT *, (SELECT count(*)+1 as rank FROM stats WHERE minutesOnline>(SELECT minutesOnline FROM stats s2 WHERE s1.uID=s2.uID LIMIT 1)) as rank FROM stats s1 ORDER BY minutesOnline DESC limit 50

Met de limit laat hij de top 50 zien. Resultaat binnen een halve seconde hier, dus qua performance geen toppertje maar toch een heel stuk sneller dan de eerste query

Helaas duurt die query hier nog veel langer (trage Windows PC), dus ik denk dat dit nog geen uitkomst biedt, maar super bedankt

[DexterDee]

Voutloos schreef op woensdag 23 augustus 2006 @ 00:02:
Het is dat het geen heel serieuze toepassing is, maar een query van 0.5s als de dataset maar uit 1500 kleine rijen bestaat is normaal alles behalve acceptabel.

Verder zal ik wel een minder helder momentje hebben maar ipv de binnenste subquery kan je toch gewoon s1.minutesOnline neerzetten?

Je hebt helemaal gelijk, ik heb het iets te moeilijk gemaakt. Maar de query wordt (helaas) maar marginaal sneller:

SELECT *, (SELECT count(*)+1 as rank FROM stats s2 WHERE s2.minutesOnline>s1.minutesOnline) as rank FROM stats s1 ORDER BY minutesOnline DESC limit 50

@mcdronkz misschien kun je deze query eens proberen. Hier voert hij in een kleine 0.2 seconden uit.

Als je de query echt niet geoptimaliseerd krijgt, kun je ook een view overwegen. Je gebruikt immers MySQL 5 dus dat mag geen probleem zijn

[mcdronkz]

Deze query werkt eerste klas. Alleen doet 'ie een beetje moeilijk als meerdere gebruikers hetzelfde aantal minuten online zijn.

[DexterDee]

Ja dat heb je bij een gelijk aantal minuten. Aangezien dat het enige criterium is voor de ranking, hebben gebruikers met hetzelfde aantal minuten ook dezelfde ranking.

als de nummer 1 en 2 allebei 1000 minuten hebben dan krijgen ze allebei rank 1, de 3e die dan 999 heeft krijgt wel netjes rank 3 en niet rank 2. Op zich wel een eerlijk systeem, maar unieke ranks worden op deze manier lastig.

Veel succes nog verder met je statistieken