MySQL, group by, komplex szempontrendszer alapján a legjobb recordokról lista. Egyszerűbb megoldás?
Adott egy tábla, ami több különböző tesztnek és több különböző felhasználónak a tesztkitöltési eredményeit tárolja, a következő mezőkkel:
testresult
- - - - - - - -
id - a teszteredmény autoinc azonosítója
testid - a teszt azonosítója
uid - felhasználó azonosítója
starttime - a teszt kitöltésének kezdetei időpontja
endtime - a teszt kitöltésének vége (ha elvileg még tart a kitöltés – akár pl. részeredmény mentve lett, de a netkapcsolat megszakadt a teszt befejezése előtt – akkor nulla)
point - pontszám
rating - szöveges értékelés (a tartalma lényegtelen, a lényeg, hogy van extra mező)
cheat - false: nem csalt, true: csalt, ezt a teszteredményt nem szabad figyelembe venni
stb…
Egy felhasználó egy tesztet többször is kitölthet. Ebből kellene kinyernem egy adott azonosítójú tesztnél a userek legjobb eredményeit, sorrendben. Ha egy user többször is kitöltötte a tesztet, akkor a legjobb eredménye az, aminek a legnagyobb a pontszáma. Pontszámegyezés esetén a legrövidebb a kitöltési idő, azonos kitöltési idő esetén a legkorábban kitöltött teszt számít. (Egyéb esetben a sorrendiség lényegtelen, de legyen következetes.)
SELECT *
FROM testresult
WHERE (testid=123) AND NOT cheat
ORDER BY point DESC, IF(endtime=0,CURRENT_TIMESTAMP(),endtime)-starttime, starttime, id
Csak mindezt úgy, hogy egy felhasználónak csak a legjobb eredménye legyen benne a lekérdezésben, ne az összes, meg aztán az egész össze legyen joinolva más táblákkal is esetleg, mondjuk egy users táblával.
Adatbáziskezelő: MariaDB 10.0.28
> mysql --version
mysql Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
~ ~ ~
Eddig csak nagyon nyakatekert megoldást találtam erre. Nincs erre valami általánosabb, átláthatóbb megoldás? Mert így kevésbé áttekinthető, ha módosítani kell, akkor problémás, ha tovább kell cizellálni a lekérdezést, nehezen kezelhetővé válik. Optimalizálás szempontjából is kérdéses, bár azt nem nagyon vizsgálgattam.
Az általam kreált megoldás:
pastebin [pont] com/hxk3jUhR
JOIN-nal is próbálkoztam, az sem tűnt kezelhetőbbnek.
válasza:Köszi, de…
Az első linken sok olyan megoldás van, ami adatbázisspecifikus és történetesen MySQL esetén nem működik.
A második link megoldásainál több probléma van:
1. Csak egy rendezési kritériumról szólnak. Többszörös rendezési kritériumra – főleg, ha szűrni is kell a rekordokat valamilyen feltétel mentén – nem lehet néhány adattal, egyszerűen kiterjeszteni. A lekérdezés az én adatbázisomban így nézne ki
2. Ha két azonos rekord van, akkor vagy mindkettőt beveszi a lekérdezésbe eredményébe.
Pl.:
SELECT A.Page, A.DateStamp as FirstViewed, A.IpAddress
FROM LogTable A
LEFT JOIN LogTable B
ON A.Page = B.Page
AND A.DateStamp > B.DateStamp
WHERE B.DateStamp is null
Ha most a következő van az adatbázisban:
20/06/2011 ; 192.168.0.1 ; home
20/06/2011 ; 192.168.0.2 ; home
akkor a fenti lekérdezésben két olyan record is lesz, amiben page='home'
~ ~ ~
Ha ennek nyomán építkezek, akkor ez lesz belőle:
SELECT t1.*
FROM testresult t1
LEFT JOIN testresult t2
ON (t1.uid = t2.uid) AND (t1.testid=t2.testid) AND (NOT t2.cheat) AND (
(t1.point<t2.point) OR
((t1.point=t2.point) AND (IF(t1.endtime=0,CURRENT_TIMESTAMP(),t1.endtime)-t1.starttime > IF(t2.endtime=0,CURRENT_TIMESTAMP(),t2.endtime)-t2.starttime)) OR
((t1.point=t2.point) AND (IF(t1.endtime=0,CURRENT_TIMESTAMP(),t1.endtime)-t1.starttime = IF(t2.endtime=0,CURRENT_TIMESTAMP(),t2.endtime)-t2.starttime) AND (t1.starttime>t2.starttime))
)
WHERE (t2.point IS NULL) AND (t1.testid=123) AND (NOT t1.cheat)
ORDER BY point desc, IF(t1.endtime=0,CURRENT_TIMESTAMP(),t1.endtime)-t1.starttime, t1.starttime, id
Ez sem sokkal szebb…
Egyesítsd a két táblát, ha a t1 pontja kisebb, mint a t2 pontja,
vagy ha azonos, de a kitöltési idő nagyobb, mint a másiknál,
vagy ha ez is azonos, akkor ha a kezdés dátuma nagyobb…
~ ~ ~
Bár itt most megihletődtem, mert lehetne származtatni egy szöveget, amiben benne vannak a rendezés szempontjából figyelembeveendő értékek. Mondjuk:
- feltételezzük, hogy egy teszt maximum 9999 pontos,
- feltételezzük, hogy a kitöltési idő maximum 999 999 999 másodperc,
- nem érdekel minket, hogy mi történik 2286. november 11. után,
- nem érdekel minket, mi lesz az 1 milliárdodik tesztkitöltés után,
akkor:
CONCAT(1000+point, 1000*1000*1000 + IF(endtime=0,CURRENT_TIMESTAMP(),endtime)-starttime, 10*1000*1000*1000+starttime, 1000*1000*1000+id)
Ez már egy értékben egyesíti a három sorrendezési feltételt, és kicsivel átláthatóbb lekérdezést ad. De még mindig el tudnék képzelni átláthatóbb megoldást, a sorrendiséget két helyen is bele kell írni, a szűrési kritériumot úgyszintén.
Kapcsolódó kérdések:
Minden jog fenntartva © 2024, www.gyakorikerdesek.hu
GYIK | Szabályzat | Jogi nyilatkozat | Adatvédelem | Cookie beállítások | WebMinute Kft. | Facebook | Kapcsolat: info(kukac)gyakorikerdesek.hu
Ha kifogással szeretne élni valamely tartalommal kapcsolatban, kérjük jelezze e-mailes elérhetőségünkön!