[SQL/PHP] ranking berekenen

[mathijs92]

Voor een stukje software waar ik mee bezig ben, probeer ik een query te schrijven die de uitslag van een setje deelnemers uitrekent. Ik heb een tabel met de volgende gegevens:

id	score
1	140
2	150
3	130
4	140
5	135

Met een query wil ik het volgende resultaat krijgen:

id	score	ranking
2	150	1
1	140	2
4	140	2
5	135	4
3	130	5

De query moet dus de uitslag teruggeven, als 2 deelnemers dezelfde score hebben, bijv 140, dan zijn ze allebei 2e. Degene die daar onder staat die is dan 4e (de ranking telt wel gewoon door).

Ik heb al gezocht op google etc, maar ik kan hier geen query voor schrijven. Ik hoop dat jullie me de goede richting op kunnen sturen. Of moet ik dit in mijn php code regelen?

[Tofu]

Welke SQL server?

[NMe]

Dat ga je inderdaad in PHP moeten doen.

[mathijs92]

Vergeten erbij te zetten. Ik gebruik mysql 5.0.51a-community-log met PHP

[P_de_B]

SQL:

SELECT ID, Score, (SELECT COUNT(*) + 1 FROM Tabel WHERE SCORE > a.Score) as Rank
FROM Tabel a

Of kan MySQL nog steeds geen correlated subqueries?

[OnTracK]

Als dat inderdaad het geval is.

SQL:

            SELECT
                id,
                score,
                @num := @num + 1 AS ranking
            FROM
                (SELECT @num := 0) count,
                tabel
            ORDER BY
                score DESC

Maar ik zou het waarschijnlijk ook in PHP doen.

[ Voor 19% gewijzigd door OnTracK op 15-11-2009 21:23 ]

[mathijs92]

P_de_B schreef op zondag 15 november 2009 @ 21:16:

SQL:

SELECT ID, Score, (SELECT COUNT(*) + 1 FROM Tabel WHERE SCORE > a.Score) as Rank
FROM Tabel a

Of kan MySQL nog steeds geen correlated subqueries?

Die werkt perfect, bedankt.

[P_de_B]

OnTracK schreef op zondag 15 november 2009 @ 21:21:
Als dat inderdaad het geval is.


Maar ik zou het waarschijnlijk ook in PHP doen.

Krijg je nu wel dat twee gelijke score's dezelfde ranking krijgen?

[mathijs92]

Ja, de ranking blijft dan hetzelfde, daarna loopt hij gewoon door. Dit is een gedeelte van het resultaat:

jury1	Rank
234	9
232	10
231	11
231	11
229	13
229	13
229	13
229	13
226	17
225	18

[P_de_B]

mathijs92 schreef op zondag 15 november 2009 @ 21:30:
Ja, de ranking blijft dan hetzelfde, daarna loopt hij gewoon door. Dit is een gedeelte van het resultaat:

Ik doelde op het antwoord van OnTrack

[mathijs92]

Ik heb over de quote heen gelezen
mathijs92 gaat zich opgeven voor een cursus begrijpend lezen

[ Voor 11% gewijzigd door mathijs92 op 15-11-2009 21:38 ]

[OnTracK]

P_de_B schreef op zondag 15 november 2009 @ 21:25:
[...]

Krijg je nu wel dat twee gelijke score's dezelfde ranking krijgen?

Nee, ik had eroverheen gekeken dat dat een eis was.
OnTracK doet mee met de cursus

[ Voor 8% gewijzigd door OnTracK op 15-11-2009 21:47 ]

[cariolive23]

NMe schreef op zondag 15 november 2009 @ 21:12:
Dat ga je inderdaad in PHP moeten doen.

Waarom? De SQL standaard heeft daar de functie RANK() voor in huis, menig DBMS heeft dit geimplementeerd. Maakt het wel zo eenvoudig, hoef je niet zelf het wiel nogmaals uit te vinden.

[P_de_B]

cariolive23 schreef op zondag 15 november 2009 @ 22:02:
[...]

Waarom? De SQL standaard heeft daar de functie RANK() voor in huis, menig DBMS heeft dit geimplementeerd. Maakt het wel zo eenvoudig, hoef je niet zelf het wiel nogmaals uit te vinden.

maar MySQL niet afaik. (en MS SQL niet, en PostgreSQL ook niet volgens mij)

[NMe]

cariolive23 schreef op zondag 15 november 2009 @ 22:02:
[...]

Waarom? De SQL standaard heeft daar de functie RANK() voor in huis, menig DBMS heeft dit geimplementeerd. Maakt het wel zo eenvoudig, hoef je niet zelf het wiel nogmaals uit te vinden.

Wat P_de_B zegt. Overigens had ik niet stilgestaan bij de mogelijkheid die P_de_B hierboven aandraagt, best elegant eigenlijk.

[cariolive23]

P_de_B schreef op zondag 15 november 2009 @ 23:39:
[...]

maar MySQL niet afaik. (en MS SQL niet, en PostgreSQL ook niet volgens mij)

SQL Server en PostgreSQL beschikken beiden over RANK():
SQL Server (vanaf versie 2005)
PostgreSQL (vanaf versie 8.4)

offtopic:
MySQL heeft dit (en vele andere handige zaken) niet, maar dat is bekend. Men neme een verzameling gebreken en noeme het een database...

[pedorus]

P_de_B schreef op zondag 15 november 2009 @ 23:39:
[...]

maar MySQL niet afaik.

Klopt. En deze workaround gaat denk ik traag zijn, en een self-join lijkt me ook wat overdreven, net als iets met 2 user-variabelen. Dus ik zou het gewoon maar in PHP doen...

[NMe]

cariolive23 schreef op maandag 16 november 2009 @ 08:19:
[...]

SQL Server en PostgreSQL beschikken beiden over RANK():
SQL Server (vanaf versie 2005)
PostgreSQL (vanaf versie 8.4)

offtopic:
MySQL heeft dit (en vele andere handige zaken) niet, maar dat is bekend. Men neme een verzameling gebreken en noeme het een database...

Je kan het wel afzeiken maar intussen is het wel de database waar bijna iedereen die hier post mee werkt. Als mensen vergeten te vermelden met welk DBMS ze werken, zoals hier, dan kun je er het beste maar meteen vanuit gaan dat het MySQL betreft en een query bakken die daarin ook werkt.

pedorus schreef op maandag 16 november 2009 @ 09:41:
[...]

Klopt. En deze workaround gaat denk ik traag zijn, en een self-join lijkt me ook wat overdreven, net als iets met 2 user-variabelen. Dus ik zou het gewoon maar in PHP doen...

"Traag" is relatief. Als de tabel klein genoeg is gaat dat best.

[ Voor 18% gewijzigd door NMe op 16-11-2009 12:00 ]

[Soultaker]

pedorus schreef op maandag 16 november 2009 @ 09:41:
Klopt. En deze workaround gaat denk ik traag zijn, en een self-join lijkt me ook wat overdreven, net als iets met 2 user-variabelen. Dus ik zou het gewoon maar in PHP doen...

Lijkt me dat dit met een index op score best snel moet zijn. ('t Hangt er natuurlijk ook vanaf hoeveel rijen je hebt, en hoe vaak je de query uit wil voeren...)

[ Voor 46% gewijzigd door Soultaker op 16-11-2009 12:24 ]

[Mischa_NL]

Het is natuurlijk mogelijk om gewoon te sorteren in je query en vervolgens bij het serverside deel (php) de ranking toe te voegen:

code:

i=1
lastscore = -1
while (i < query.EOF)
    
    write->i + " " + query.score 

    if (query.score <> lastscore){      i++    }
     
    query.next

op deze manier hou je gewoon een counter bij die zelf kijkt of het vorige resltaat gelijk is aan het nieuwe. Is dit niet het geval dan krijgt het de volgende rank (i++), anders blijft hij gelijk.

Uiteraard werkt deze code niet, en loopt hier de i na de gleijke score weer door bij i++. Ook kun je hier maar 1 gelijke score hebben. Maar het gaat om het idee, en ik weet zeker dat dit door de TS zelf is op te lossen!

[Soultaker]

Maar dat geeft niet de gewenste ranking. Je kunt wel zoiets doen:

code:

index = 0
last_score = -1
while score = next_score():
  index = index + 1
  if score <> last_score: rank = index
  last_score = score
  print rank, score

Maar goed, of dat heel veel mooier is dan een subquery...

[ Voor 10% gewijzigd door Soultaker op 16-11-2009 14:42 ]

[NMe]

't Zal marginaal sneller zijn, maar dan zou ik toch liever die subquery gebruiken. Ik ga er niet vanuit dat dat een bottleneck wat betreft snelheid wordt.

[pedorus]

NMe schreef op maandag 16 november 2009 @ 14:52:
't Zal marginaal sneller zijn, maar dan zou ik toch liever die subquery gebruiken. Ik ga er niet vanuit dat dat een bottleneck wat betreft snelheid wordt.

Nah, dat kan met zo'n dependent subquery nog wel eens zwaar tegenvallen. Maar goed, als het om niet te veel deelnemers met weinig veranderingen gaat, zal het allemaal wel meevallen.

[P_de_B]

De query zal inderdaad niet de snelste zijn. De subquery wordt immers voor iedere rij in de resultset geevalueerd, voor relatief kleine sets zou ik er overigens niet over nadenken en het gewoon doen

Dat SQL Server een functie had via over had ik me niet gerealiseerd