[SQL] Group by + MIN werkt niet ?

[Verwijderd]

Voor een klant van mij die online games maakt moet ik de highscores van de games bijhouden en bijv. de hoogste score van elk uur bijhouden.

Nu heb ik wat problemen met het berekenen van de highscore.

Ik gebruik hiervoor de volgende SQL-statement:

SELECT *,time / 1000 AS time, DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, MIN(time),MIN(moves),hour(timestamp) AS HT
FROM game
WHERE DAYOFMONTH(timestamp) = DAYOFMONTH(NOW())
AND MONTH(timestamp) = MONTH(NOW())
AND YEAR(timestamp) = YEAR(NOW())
AND pid = 0
GROUP BY HT
ORDER BY time,moves ASC

Deze SQL moet dus de records ophalen van een dag bijv. woensdag en dan van elk uur de hoogste score. Dus er hebben bijvoorbeeld in uur 22 op woensdag 10 mensen gespeeld waarvan 1 de laagste moves en tijd heeft gehaald (het gaat hier om een memory-game).

De database is als volgt opgezet:

ID, pid, name, phone, email, moves, time, email_sent, timestamp
--

En het gaat bijv. om deze records:

50, 0, naam, tel, email, 30, 3500, 0, 20030924222051
51, 0, naam, tel, email, 50, 3800, 0, 20030924222182
52, 0, naam, tel, email, 80, 4000, 0, 20030924222285
note: in bovenstaande records zijn 30,50,80 de moves en 3500,3800,4000 de tijd.

Bovenstaande records zijn dan van het uur 22, de SQL-statement moet de beste uit dit uur halen qua moves en tijd ophalen, maar dit gebeurt niet helaas om een of andere donkere reden Met mijn SQL-statement haalt die de 2de rij (met ID 51) op wat heel onlogisch is omdat je dan zou denken dat die de hoogste of de laagste dan zou nemen.

Uiteraard vraag ik dit niet aan jullie omdat ik het eventjes niet weet, ik ben hier ondertussen al meer dan 4 uur mee bezig

Mocht iemand het weten dan zou ik heel dankbaar zijn

[vinnux]

"MIN(time),MIN(moves)" moet "MAX(time),MAX(moves)" worden om de hoogste er uit te haleb lijkt mij.
Maar eigenlijk is het tegenstrijdig om twee keer MIN of MAX te gebruiken omdat dan time en moves geen verband meer met elkaar hebben.
row 0 : 10 100
row 1 : 101 10
Dan krijg je terug : 101 100

[ Voor 56% gewijzigd door vinnux op 24-09-2003 23:51 ]

[Verwijderd]

Nee het moet echt MIN zijn, dit omdat diegene met de laagste aantal moves en tijd de winnaar is niet, eigenlijk had ik moet zeggen de lowscores

[vinnux]

En wat nou als iemand de laagste aantal moves heeft en een ander de minste tijd?

[Verwijderd]

Ik geloof dat dat automatisch wordt berekend als je op allebij kolommen een MIN() neerzet, maar dat weet ik niet zeker en moet ik nog gaan testen.

[vinnux]

Verwijderd schreef op 25 September 2003 @ 00:03:
Ik geloof dat dat automatisch wordt berekend als je op allebij kolommen een MIN() neerzet, maar dat weet ik niet zeker en moet ik nog gaan testen.

Ik kan je al verzekeren dat ie dat niet doet.
En als ie het al wel zou doen HOE zou ie dat dan doen? Als je daar over nadenkt zul je tot de conclusie komen dat ie dat niet kan.
A(time) en B(moves) zijn namelijke niet dezelfde grootheid, het is hetzelfde dan als peren en appels optellen en vragen wat is het meeste? Héél misschien telt ie ze bijelkaar op als int, maar dat is ook niet wat je wilt.
Je zult moeten aangeven hoe time en moves zich tot elkaat verhouden om aan te kunnen geven wanner iemand verdient om bovenaan te staan.

[ Voor 54% gewijzigd door vinnux op 25-09-2003 00:28 ]

[vinnux]

SQL:

SELECT 
    *,
    time / 1000 AS time, 
    DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, 
    DISTINCT(HOUR(timestamp)) AS HT 
FROM game
WHERE 
    DAYOFMONTH(timestamp) = DAYOFMONTH(NOW()) 
    AND MONTH(timestamp) = MONTH(NOW()) 
    AND YEAR(timestamp) = YEAR(NOW())
    AND pid = 0
ORDER BY 
    HOUR(timestamp),
    time,
    moves 
    ASC

Met deze code krijg je van de huidige dag per uur het record terug met de minste tijd. Mocht de tijd gelijk zijn, dan krijg je degen terug met de minste moves voor die tijd. Dus om één over twaalf snachts is je lijst leeg, maar dat had je al gezien neem ik aan.

[ Voor 44% gewijzigd door vinnux op 25-09-2003 00:28 ]

[Verwijderd]

Bedankt vgouw, ik ga het even uittesten.

[Verwijderd]

Ik heb het geprobeerd maar eerst kreeg ik een foutmelding dat DISTINCT niet werkt.

[vinnux]

Verwijderd schreef op 25 september 2003 @ 01:01:
Ik heb het geprobeerd maar eerst kreeg ik een foutmelding dat DISTINCT niet werkt.

In Windows SQL Server 2000 betstaat de functie Distinct weldeglijk

[Verwijderd]

.

[ Voor 99% gewijzigd door Verwijderd op 31-10-2023 21:53 ]

[justmental]

Als je een group by in je query hebt, dan mogen alleen de velden waarop je groepeert zonder aggregatie functie in je select list staan.
Alle andere velden moeten een aggregatie functie hebben (MIN/MAX/AVG/etc).

[Verwijderd]

justmental schreef op 25 September 2003 @ 08:54:
Als je een group by in je query hebt, dan mogen alleen de velden waarop je groepeert zonder aggregatie functie in je select list staan.
Alle andere velden moeten een aggregatie functie hebben (MIN/MAX/AVG/etc).

dat is nou het leuke van MySQL, die heeft daar gewoon maling aan je krijgt daardoor vaak de meest onverklaarbare resultaten terug (deze fout komt hier ook echt heel vaak voorbij)
gewoon standaard sql is niet zo moeilijk.....

[Verwijderd]

Ik begrijp er niks meer van, ik heb nu deze SQL:

SELECT *,time / 1000 AS time, DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, MIN(time),MIN(moves),hour(timestamp) AS HT
FROM game
WHERE DAYOFMONTH(timestamp) = DAYOFMONTH(NOW())
AND MONTH(timestamp) = MONTH(NOW())
AND YEAR(timestamp) = YEAR(NOW())
AND pid = 0
GROUP BY HT
ORDER BY time,moves ASC

En nu krijg ik doodleuk de record terug waarbij tijd en moves juist heel hoog zijn en naast die records krijg ik nog een kolom MIN(time) waar de laagste tijd in staat.

Dus eigenlijk zou ik moeten doen WHERE time = MIN(time) maar dat mag niet van mysql. Heeft iemand hier een oplossing voor ? Het moet toch op de een of andere manier mogelijk zijn om alleen die kolommen op te halen met de laagste aantal moves en tijd in bijv. uur 21 en 22 ?

[Janoz]

De manier waarop jij min gebruikt is fout. Als je het record op wil halen dat aan bepaalde eisen voldoet dan moet je gebruik maken van having.

[Janoz]

Verwijderd schreef op 25 september 2003 @ 08:49:
Zo heb ik ook eens 2 dagen besteed aan een query op mySQL, uiteindelijk kon het op mySQL alleen met het aanmaken van een tijdleijke tabel, in MS-SQL Server was mijn eerst opgestelde query al gelijk goed geweest. Dus wat gebruik jij eigenlijk?

Eerste query had een dikke foutmelding opgeleverd van MSSql ..

[Verwijderd]

Janoz, het record moet niet aan bepaalde eisen doen, als ik bijvoorbeeld wilde hebben dat het kolom een waarde groter dan 1000 bevat dan zou ik having goed kunnen gebruiken. Dus SQL-statement + HAVING time > 1000 bijv. Maar hier moet ik geloof ik toch echt MIN() gebruiken hoor.

[Verwijderd]

Ik ben nu iets verder gekomen door een andere post die net is geplaatst met een ongeveer hetzelfde probleem, ik heb nu in mijn GROUP alle columns geplaatst behalve de MIN columns, nu laat die alle resultaten zien met bovenaan met de laagste moves en time. Echter moet het zo zijn dat ik per uur slechts 1 resultaat te zien krijg, en dat deed ik dus met die GROUP functie, nu ik echter meerdere columns in die GROUP heb staan werkt dat niet meer, de SQL ziet er nu zo uit:

SELECT *,time / 1000 AS time, DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, MIN(moves * (time/1000)),hour(timestamp) AS HT
FROM game
WHERE DAYOFMONTH(timestamp) = DAYOFMONTH(NOW())
AND MONTH(timestamp) = MONTH(NOW())
AND YEAR(timestamp) = YEAR(NOW())
AND pid = 0
GROUP BY id,pid,name,phone,time,moves
ORDER BY moves ASC

Iemand enig idee hoe ik een resultaat per uur kan laten zien ? Ik heb DISTINCT HOUR(timestamp) al geprobeerd maar dat werkt niet omdat DISTINCT niet over 1 column werkt..

[Verwijderd]

lees nu eens wat justmental schrijft ...

(select * , ..... group by ...)

[Verwijderd]

Weet ik maar ik heb het ook al zo geprobeerd en dat werkt ook niet:
SELECT id,pid,name,phone,time / 1000 AS time, DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, MIN(moves * (time/1000)),hour(timestamp) AS HT
FROM game
WHERE DAYOFMONTH(timestamp) = DAYOFMONTH(NOW())
AND MONTH(timestamp) = MONTH(NOW())
AND YEAR(timestamp) = YEAR(NOW())
AND pid = 0
GROUP BY HT,id,pid,name,phone,time,moves
ORDER BY moves ASC

[_js_]

time / 1000 as time werkt? Hoe weet je db welke time je bedoelt in WHERE en GROUP BY enzo?

In MySQL heet DISTINCT UNIQUE, dus probeer de query van vgouw eens met UNIQUE.

[vinnux]

Post de volgende gegegevens en we kunnen er naar kijken:
- Welke database gebruik je en welke versie
- create script van tabel
- scripts om aantal records te inserten
- de relatie tussen time en moves (hoe bereken je wie er gewonnen heeft)

[justmental]

Verwijderd schreef op 25 September 2003 @ 15:42:
Weet ik maar ik heb het ook al zo geprobeerd en dat werkt ook niet:
SELECT id,pid,name,phone,time / 1000 AS time, DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, MIN(moves * (time/1000)),hour(timestamp) AS HT
FROM game
WHERE DAYOFMONTH(timestamp) = DAYOFMONTH(NOW())
AND MONTH(timestamp) = MONTH(NOW())
AND YEAR(timestamp) = YEAR(NOW())
AND pid = 0
GROUP BY HT,id,pid,name,phone,time,moves
ORDER BY moves ASC

Werkt niet?
Het geheim van SQL schrijven is goed en gestructureerd nadenken over wat je wil hebben.
Eerst de from, dan de where, de group by, de having en dan de select en de order by.

from: waaruit moet je selecteren
where: hoe moeten de gegevens beperkt worden en hoe ligt de relatie
group by: over welke gegevens wil je aggregatie toepassen
having: hoe moet er na aggregatie beperkt worden
select: welke gegevens wil je terugzien en hoe aggregeer je over de gegevens waar je niet op groepeert
order by: hoe moet het resultaat gesorteerd worden

In dit geval ga je groeperen over een rij kolommen waar je waarschijnlijk juist een aggregatie functie over zou moeten gaan.

[Janoz]

Verwijderd schreef op 25 September 2003 @ 14:31:
Janoz, het record moet niet aan bepaalde eisen doen, als ik bijvoorbeeld wilde hebben dat het kolom een waarde groter dan 1000 bevat dan zou ik having goed kunnen gebruiken. Dus SQL-statement + HAVING time > 1000 bijv. Maar hier moet ik geloof ik toch echt MIN() gebruiken hoor.

Je steld wel degelijk een eis aan een record. bla = MIN(bla) toch? Het moet namelijk de minimale waarde bevatten. Zodra je MIN in het gedeelte van de select gebruikt geldt dit alleen voor die kolom en niet voor het hele record. MySQL is gewoon erg lui en daardoor lijkt het of bepaalde dingen zouden kunnen, maar normalitair zou je alle velden die je niet met een agregate function gerbuikt in de group by clausule opnemen. Hierdoor wordt alleen het minimum genomen van de velden die de ander opgevraagde velden gelijk hebben. Door velden in je select op te nemen waarop niet word gegroepeerd is het ongedefinieerd welke je eigenlijk terug krijgt.

(hmm .. Ik zie dat justmental dit gister iets korter ook al heeft gezegd......)

[ Voor 4% gewijzigd door Janoz op 26-09-2003 08:48 ]

[Verwijderd]

Janoz, dat heb ik al geprobeerd en ik kreeg van PMA een error dat het niet kan.

Ik weet niet of ik het wel goed heb gedaan, de SQL ziet er dan zo ong. uit:

SELECT *,time / 1000 AS time, DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, MIN(moves * (time/1000)) AS score,hour(timestamp) AS HT
FROM game
WHERE DAYOFMONTH(timestamp) = DAYOFMONTH(NOW())
AND MONTH(timestamp) = MONTH(NOW())
AND YEAR(timestamp) = YEAR(NOW())
AND pid = 0
HAVING time = MIN(time)
GROUP BY HT
ORDER BY moves ASC;

Ik krijg dan vervolgens deze foutmelding:
You have an error in your SQL syntax near 'GROUP BY HT ORDER BY moves ASC LIMIT 0, 30' at line 1

[justmental]

Verwijderd schreef op 26 September 2003 @ 12:11:
Janoz, dat heb ik al geprobeerd en ik kreeg van PMA een error dat het niet kan.

Ik weet niet of ik het wel goed heb gedaan, de SQL ziet er dan zo ong. uit:

SELECT *,time / 1000 AS time, DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, MIN(moves * (time/1000)) AS score,hour(timestamp) AS HT
*knip*
GROUP BY HT

lees nou eens wat er gezegd wordt!
Waar groepeer je op -> HT
Wat staat er in je select list? -> *, time/1000 etc
Daar groepeer je niet op en die mogen dus ook niet zonder aggregatie functie in je select list staan.
En dan maar op meer dingen groeperen is natuurlijk niet de oplossing.

[Verwijderd]

Justmental, sorry dat ik het niet goed heb gelezen. Ik heb het nu zoals jij zegt:

SELECT *, hour(timestamp) AS HT, MIN(moves * time)
FROM game
WHERE DAYOFMONTH(timestamp) = DAYOFMONTH(NOW())
AND MONTH(timestamp) = MONTH(NOW())
AND YEAR(timestamp) = YEAR(NOW())
AND pid = 0
GROUP BY HT
ORDER BY moves ASC

Alleen heb ik nu het probleem dat ik wel een keurig lijstje krijg met de laagste scores, alleen de MIN-kolom is niet gekoppeld aan de overige velden, name,phone etc. het staat er los van, nu krijg ik dus willekeurige velden bij de MIN-kolom.
En daarvoor moet ik inderdaad iets van HAVING time = MIN(time) en HAVING moves = MIN(moves) doen maar dan krijg ik errors.

[justmental]

Dat * kan zo niet in je select list, d'r staat geen aggregatie functie omheen.
Welke versie van MySQL gebruik je?

[Verwijderd]

Op mijn lokale server gebruik ik versie 4.0.0-alpha-nt, maar ik weet niet welke versie mijn klant op zijn server heeft staan.
Justmental wat voor functie zou ik om * heen kunnen zetten ?

[justmental]

Voor je probleem heb je zo te zien een subquery nodig en die wordt pas in de latere versies ondersteund.
Dat * kan niet omdat je per uur aan het groeperen bent en je in hetzelfde uur meerdere records kunt hebben.

[Verwijderd]

Dus het is echt onmogelijk om dit in mysql te doen ?

[justmental]

Het kan altijd met meerdere queries, dus dat je eerst de maximale score per uur ophaalt en daar doorheen loopt in je programma en binnen de loop een query doet op de rest van de gegevens bij de hoogste score.

[Verwijderd]

Ok dan doe ik dat maar, erg vreemd van mysql dit, nooit echt problemen gehad met mysql maar dit vindt ik toch een redelijk grote limitatie. Maar ja mysql is gratis en voor dat bedrag is het een zeer goede database

Allemaal hartelijk dank voor de goede hulp, maar ik ga het nu toch maar doen zoals justmental het zegt, lijkt me de beste oplossing.