[SQL] Scores sorteren op ranking

[MrHighStone]

Hij is vast al vaker langs gekomen, maar de search leverde me geen bruikbare resultaten op.
Hoe voeg ik een kolom Ranking toe aan een SELECT query op een tabel in de vorm van CompetitionScores (UserID, Score)? Volgende stap is sorteren op Ranking DESC, maar dat is wel te doen.

Enig zoekwerk leverde me de oplossing op om twee selects te doen op dezelfde tabel en dan als het ware het aantal overblijvende records te tellen WHERE CS1.Score <= CS2.Score.

Op zich werkt dit wel, maar niet indien gebruikers een gelijke score hebben, ik krijg dan bijvoorbeeld de volgende output:
1 piet 10
2 jan 8
3 klaas 5
5 jaap 4
5 koos 4
8 karel 3
8 sjaak 3
8 hans 3
In plaats hiervan zouden de gebruikers met ranking 5 en 8 een gedeelde 4e resp 5e ranking plaats moeten krijgen...

Iemand een tip?

[whoami]

Waarom sorteren op ranking? Je kan toch sorteren op score, dan heb je ook onmiddellijk de goeie volgorde.
Het toevoegen van een ranking-kolom (zoals in je voorbeeld) dmv SQL is volgens mij niet zo simpel. Misschien is het beter als je daar gewoon een simpel stukje code/script voor schrijft.

[MrHighStone]

Op score sorteren heeft idd hetzelfde effect. Vanwege technische redenen gaat er de voorkeur naar uit om deze query ook een ranking kolom uit te laten spugen. Volgens mij moet het kunnen (zoals ik zei, ik heb het half werkend), alleen de truuk is om gedeelde plaatsen er uit te krijgen...

[klinz]

Hee, die heb ik ook ooit eens gemaakt. Ik heb 'm alleen niet ter hand. Ik zal 'm eerdaags eens posten.

whomai: de resulterende query is niet heel erg ingewikkeld. Een select/inner join/where/count/group/having/orderby IIRC.

[slm]

Als je om te beginnen eens je volledige query zou plaatsen die het resultaat uitspuugt wat je hierboven aangeeft, dan zouden anderen je misschien wat meer kunnen helpen. Tot die tijd ben ik het met whoami eens dat je beter gewoon op score kan sorteren. Of aangeeft wat die technische redenen dan zijn om niet op score te sorteren.

[Bud_s]

eerst een select group op score (zonder userid) desc, hier proberen een ranking aan te hangen (kan je niet zeggen hoe).

Vervolgens adhv deze querie een select incl userid

[Orphix]

Welke SQL server gebruik je?
De bovenstaande oplossing komt ook bij mij op alleen daar zijn subselects wel erg handig bij wat bv MySQL (nog) niet heeft.

[Bud_s]

als subselect niet handig werkt :

resultaat naar een temp. tabel , select group score DESC naar de (nieuwe) tabel met een veld van het type serial (serial zal beginnen met 1)

vervolgens deze weer gebruiken ;-)

dus :


select temp.id , tab1.score , tab1.userid

from temp , tab1

where tab1.score = temp.score

[ Voor 22% gewijzigd door Bud_s op 10-03-2003 00:31 . Reden: typo ]

[Spider.007]

misschien heb je hier iets aan?

[ Voor 9% gewijzigd door Spider.007 op 10-03-2003 00:33 ]

[dusty]

Wat je wilt is te doen dmv een subquery waar je kijkt hoeveel mensen een hogere score heeft.. zijn dat er 5 dan ben je dus plaats 6.

[Bud_s]

kan ook.

zelfde verhaal , select userid, score from tab1 order bij score DESC naar TEMP tabel met een veld van type serial.

zo krijg je dus *alle* records in TEMP

vervolgens

select temp temp.score , min(temp.ID) as rank, tab1.userid

let ff niet op syntax

bij gelijke scores krijgen ze dus de minimale waarde van rank

dus :
userid1 , 300 , rank 1
userid5 , 250 , rank 2
userid3 , 250 , rank 2
userid2 , 200 , rank 4

ongeveer

[Bud_s]

dusty schreef op 10 maart 2003 @ 00:52:
Wat je wilt is te doen dmv een subquery waar je kijkt hoeveel mensen een hogere score heeft.. zijn dat er 5 dan ben je dus plaats 6.

betekent bij 500 records ook 500 queries gegenereerd in de subquerie ??

[ Voor 6% gewijzigd door Bud_s op 10-03-2003 01:04 . Reden: typo ]

[Eskimootje]

Bud_s schreef op 10 March 2003 @ 01:03:
[...]

betekent bij 500 records ook 500 queries gegebereerd in de subquerie ??

Betekend 2 queries, een om te bepalen hoeveel punten iemand heeft en de volgende om te kijken wie er boven hem staan. Als je dit dan voor 500 mensen achter elkaar wil weten zijn dat idd 2 * 500 queries (of in de algoritmiek n queries)
Maar daar kun je het met php oplossen door elke keer de score met de vorige te vergelijken zelfde rank niet verhogen, lager rank wel verhogen.

[ Voor 31% gewijzigd door Eskimootje op 10-03-2003 01:07 ]

[dusty]

Bud_s schreef op 10 March 2003 @ 01:03:
[...]

betekent bij 500 records ook 500 queries gegenereerd in de subquerie ??

Ik zei met een subquery, dus toch echt gewoon alles in een query, dat de database veel werkt moet verrichten valt weinig aan te doen, tenzij dus met de programmeertaal waarmee men de query heeft laten uitvoeren besluit om zelf met een simpel for lusje de rankings te bepalen.

[ Voor 2% gewijzigd door dusty op 10-03-2003 08:52 . Reden: neerlands is moeilijk ]

[MrHighStone]

Precies, zoals jullie het hier beschrijven heb ik het reeds opgelost, alleen (zie 1e post) gaat dit niet op voor gedeelde plaatsen (in dit geval de 4e en 5e plaats, worden nu 5e en resp 8e plaats).
De truuk om de rank te baseren op het aantal overblijvende records in een vergelijking wordt ook op het MSDN geopperd, maar zoals gezegd, gedeelde plaatsen snapt ie niet...
Overigens, ik maak gebruik van SQL Server 2000.

Bud_s: een veld met type serial? bedoel autonumbering? Gedeelde plaatsen werken dan niet helaas...
Spider: dank voor het meedenken, maar hier is de ranking niet afhankelijk van de waarde in een veld alleen, maar ook in relatie tot records onderling.

[ACM]

En dusty's idee?

SQL:

select id, naam, score, 
    (select count(*)+1 from tabel where score > a.score) as rank
from tabel a
order by rank

Je zou ook nog een materialized view kunnen maken waar je bovenstaande select in verwerkt, zodat ie wat minder zwaar hoeft te zijn voor je db, als je dus de select relatief vaak tov wijzigingen in de tabel doet.

[ Voor 38% gewijzigd door ACM op 10-03-2003 09:14 ]

[whoami]

dusty schreef op 10 maart 2003 @ 08:51:
[nohtml]
[...]
[/nohtml]
Ik zei met een subquery, dus toch echt gewoon alles in een query

Dan is het toch nog zo dat die subquery voor ieder record moet uitgevoerd worden toch?
Bv:

code:

SELECT A, (SELECT B .... )
FROM ...

Voor ieder veld A dat hier gereturned wordt, moet die subquery (SELECT B ...) toch nog uitgevoerd worden.

tenzij dus met de programmeertaal waarmee men de query heeft laten uitvoeren besluit om zelf met een simpel for lusje de rankings te bepalen.

Ik denk toch dat dit de simpelste oplossing zal zijn, en wellicht ook de snelste. Imo is het ook het meest logische.
Met SQL haal je de gegevens op (op een gesorteerde manier), en dan ga je de ranking gaan bepalen. Het bepalen van de ranking kan je hier zijn als 'domein-logica' en niet als 'data-logica'.

[MrHighStone]

Heb momenteel even niet mijn db en sprocje bij de hand om te testen.

Voor de liefhebbers, de oplossing van microsoft: http://support.microsoft....?scid=kb%3Ben-us%3B186133
Vooral interessant is de stukje over drawbacks, precies het probleem waarover we het hier hebben...

whoami: hier is de keuze gemaakt om de business logic of, zo je wilt, domein logica, onder te brengen in stored procedures, en dus t-sql. Op meerdere plaatsen in de applicatie wordt deze sproc aangeroepen, doel dus om dergelijke logica op een plek onder te brengen.

acm: eea is ondergebracht in een sproc

dusty: volgens mij is jouw oplossing niet bijzonder veel anders dan de op het msdn geopperde oplossing.

[ Voor 12% gewijzigd door MrHighStone op 10-03-2003 09:42 ]

[ACM]

Een sproc is wel even wat anders dan een materialized view/query table

Maar als de sproc vlot genoeg is maakt dat verder niet zo veel uit.
De voorbeelden die ze op msdn geven zijn idd wel aardig, hoewel je wellicht de '>=' door '>' wilt vervangen om gedeelde 2e plaatsen ipv gedeelde 4e plaatsen te krijgen als je drie records hebt met dezelfde score en één met een hogere.

[slm]

Misschien een domme opmerking, maar ik begrijp echt niet waarom je dit met een query wilt oplossen in plaats van dit op te lossen in een scripting taal zoals ASP / PHP. Ik vraag me echt af of al die (sub)selects sneller zijn dan gewoon x++ te gebruiken bij het ophalen van je rijtjes of bij een for lus als je alles in 1 batch ophaalt.

Nu gebruik ik SQL niet zo vaak, dus het kan zeker aan een gebrek aan kennis liggen, maar ik zou het toch graag willen weten

Je geeft zelf "om technische redenen" aan. Ik zou -nieuwsgierig als ik ben- dan wel willen weten wat die dan zijn.

[whoami]

In dit geval is het imho ook performanter als je de ranking niet bepaald met SQL.

SQL is zeer krachtig, en je moet die 'kracht' ook zoveel mogelijk aanwenden, maar in deze situatie is het imo zeker niet beter om de ranking met SQL te bepalen. Integendeel.

[dusty]

MrHighStone schreef op 10 March 2003 @ 09:40:
dusty: volgens mij is jouw oplossing niet bijzonder veel anders dan de op het msdn geopperde oplossing.

Ligt eraan of die oplossing van MSDN wel gedeelde plaatsen kent of niet.

Op zich is het een logische verklaring, hoe bepaal je dat iemand op de 6e plaats staat? omdat er 5 mensen zijn met een betere score.

hoe bepaal je of iemand op de 8e plaats staat? omdat er 7 mensen zijn met een betere score.

Dus als je kijkt hoeveel resultaten erzijn met een betere score weet je ook het plaats van de persoon die je onderzoekt. (immers is dat het aantal personen met een betere score + 1 )

Betekent ook dat als er er 5 mensen zijn met een score hoger dan 200 en je hebt 2 personen met exact 200 punten krijgen dus beide personen met score 200 een resultaat van 6 eruit , de persoon met 199 punten krijgt er dan een 8 uit.

Er wordt toch een extra query gestart voor elke subquery

Als gebruiker zijnde voer je maar EEN query uit, de sql server splitst het op en optimaliseert het omdat wat er gedaan moet worden reperterend is, ga je dit vergelijken met eerst alle personen uit de database te halen en dan elke "resultaat" apart uit de database te halen is dat duidelijk trager ( althans bij een grotere database ) dit omdat de server minder goed kan optimaliseren, dit komt ook omdat hij resulaten van vorige queries kan gebruiken. (vooral als je groter dan / kleiner dan tekens gebruikt) Dit zal ontzettend goed te merken zijn bij zowel de SQL server van microsoft als die van Oracle.