[SQL] Lastige query

[Rhapsody]

Hey,

Ik zit met een sql probleempje waar ik maar niet uit kom.

Ik heb 2 tabellen: modules en cijfers

modules
module_id
naam

cijfers
user_id
module_id
cijfer


Nu wil ik alle users hebben die voor alle modules een voldoende hebben.
Maar ik kom er maar niet uit hoe dit te doen. Ik heb wel gezocht maar (nog) niets bruikbaars gevonden.
Kan iemand mij even in de goede richting schoppen. Het probleem zit hem in het aantal modules. Ik kan best alle users tevoorschijn toveren welke een voldoende hebben, maar ik dan weet ik dus niet of de user voor alle modules een voldoende heeft....

[whoami]

Hmm, wanneer heeft een user een voldoende ? Als hij gewoon minstens een bepaald cijfer heeft ?

Ik zit me af te vragen of het mogelijk is om dit in één query te doen.... Misschien is het mogelijk als je weet hoeveel modules iedere student heeft moeten doen; dan kan je een select doen en alle records returnen waarvoor het cijfer minimum X is, en kijken hoeveel records je per student teruggekregen hebt.

Anders denk ik dat je makkelijkst bent om alle cijfers van alle studenten op te halen, en dan client-side checken.... Het is wellicht niet zo efficient.

edit:
tuurlijk weet je voor iedere student hoeveel modules hij gevolgd heeft. Misschien kan je eens spelen met aggregated subqueries, counts etc...

_{eerst even eten en stuff, daarna test ik misschien wel eens iets uit}

[ Voor 17% gewijzigd door whoami op 27-06-2007 20:20 ]

[Gomez12]

Vraagje : Hoe weet je hoeveel modules een user in gebruik heeft?

Weet je dit niet dan zou je het ranzig kunnen doen met zoiets :

code:

select user_id, count(module_id) as aantal
from cijfers
where cijfer>5,5 and 
count(module_id)=( select count(tmp.module_id) from cijfers as tmp where tmp.user_id = userid_id )
group by user_id

Als je deze pseudo code al aan de praat krijg zou ik het niet aanraden om dit op tabellen met meer dan 10.000 records te draaien...

Maar dit is het 1e idee

[Rhapsody]

het punt is dat het wel om heel veel records kan gaan straks.

Elke student volgt ALLE modules uit de modules tabel. Dus ik weet hoeveel modules elke student volgt.
Verder gaat het inderdaad op cijfer basis (of op vrijstelling) (dus cijfer >55 of vrijstelling = 1)

Wat ik inderdaad zelf had bedacht:

Alle studenten/cijfers ophalen (waarbij cijfer voldoende is) en dan per student kijken hoeveel modules ik voor die student heb teruggekregen.
Dan kan ik de rest in de code afhandelen. Het mooiste zou echter zijn als ik dit in een SP kan gooien.

[ Voor 34% gewijzigd door Rhapsody op 27-06-2007 20:33 ]

[Xander]

Een subquery maken waarin je alle users selecteert die één module onvoldoende hebt? En dan vervolgens in je hoofdquery alle users selecteren en in je WHERE statement een NOT IN gebruiken?

[beetle71]

Het kan ook in 1 keer, zonder dat je weet hoeveel modules een persoon gevolgd heeft.

MySQL:

select user_id,
    sum(if(cijfer>6,1,0)) as aantal_voldoendes,
    count(m.module_id) as aantal_modules
from cijfers as c,
     modules as m
where m.module_id=c.module_id
group by user_id
having aantal_voldoendes=aantal_modules

[whoami]

Deze zou moeten werken:

code:

select tblstudent.studentid, tblstudent.naam, count(1)
from tblstudent
inner join tblcijfer on tblcijfer.studentid = tblstudent.studentid
where tblcijfer.cijfer >= 5
group by tblstudent.studentid, tblstudent.naam
having count(1) = ( select count(1) from tblmodule)

(En in principe zou je de count(1) uit de select-list kunnen halen).

Ik heb even een test DB'tje gemaakt (3 tabellen), en voor mij heeft een student een voldoende voor een module als hij minstens 5 haalt.

Eventueel kan je dit nog optimaliseren door 2 queries te doen (als je 't toch in een SP gaat bakken):
eerst een query die het aantal te volgen modules ophaalt, en dan dit resultaat in de having gebruiken ipv die subquery.

_{hmm, wat Gomez dus zegt.}

[ Voor 35% gewijzigd door whoami op 27-06-2007 21:07 ]

[Rhapsody]

ik ga er eens mee aan de slag. Nu zal ik er toch zeker uit moeten komen.

Het keyword having was nog niet in mij opgekomen....slecht...

Maar goed, ik ga weer ff verder puzzelen. Heel erg bedankt allen tot zover

[Niemand_Anders]

Waarom houd je per user (student) niet gewoon bij wat het laagste cijfer is? Via een trigger op cijfers kun je vervolgens een query als

code:

update users set cijfer=(select min(cijfer) from cijfers where user_id=inserted.user_id) where user_id=inserted.user_id

Op die manier kun je alle studenten met alleen voldoendes simpel ophalen:

code:

select * from users where cijfer > 5.5

Zeker bij grotere databases kan (goede) denormalisatie een flinke performance boost opleveren. Door min(cijfer) te vervangen door avg(cijfer) kun je ook het gemiddelde cijfer opslaan.

De performance boost zit hem in het moment waarop de database server de vergelijkingen moet uitvoeren. Met de trigger verplaatst je het zwaarste gedeelte (het ophalen van het laagste cijfer) naar het moment waarop de database wijzigt. Meestal is de trigger update nauwelijks merkbaar. Bij het genereren van een rapportage of export kun je dan volstaan met een simpele select query. Die hoeft dan alleen maar het cijfer veld te vergelijken met een statische waarde (definitie van voldoende).

Een andere winst is de relatieve leesbaarheid van je queries. Doordat de queries simpeler zijn, is de kans op foutjes ook kleiner. Vermijd waar het kan complexiteit.

*) De trigger query is gebaseerd op een MS SQL database server. 'inserted' is de record welke je zojuist heb aangepast of toegevoegd. In MySQL dien je 'NEW' te gebruiken ipv inserted.

[Grijze Vos]

Als iedereen voor al zijn modules een punt heeft werkt dit ook wel:

code:

SELECT user_id
FROM cijfers
WHERE user_id NOT IN
  (SELECT user_id
   FROM cijfers
   WHERE cijfer < 55)
;

[whoami]

Grijze Vos schreef op donderdag 28 juni 2007 @ 11:37:
Als iedereen voor al zijn modules een punt heeft werkt dit ook wel:

code:

SELECT user_id
FROM cijfers
WHERE user_id NOT IN
  (SELECT user_id
   FROM cijfers
   WHERE cijfer < 55)
;

De vraag is, kan je op deze randvoorwaarde vertrouwen ?
Stel bv dat een bepaalde student door ziekte een bepaald examen voor een bepaalde module niet heeft kunnen afleggen. Wat dan ?

[Greyfox]

Niemand_Anders schreef op donderdag 28 juni 2007 @ 11:23:
Waarom houd je per user (student) niet gewoon bij wat het laagste cijfer is? Via een trigger op cijfers kun je vervolgens een query als

code:

update users set cijfer=(select min(cijfer) from cijfers where user_id=inserted.user_id) where user_id=inserted.user_id

Een andere winst is de relatieve leesbaarheid van je queries. Doordat de queries simpeler zijn, is de kans op foutjes ook kleiner. Vermijd waar het kan complexiteit.

Als iets in mijn ogen evil is, dan zijn het wel triggers.
Je query wordt simpler, maar je ziet helemaal niet meer wat er gebeurd als je iets doet.
Triggers zijn onderhoudstechnisch gesproken een slechte oplossing en zou je alleen moeten toepassen als het echt niet anders kan. (en dan nog zou je ze niet moeten gebruiken )

[Grijze Vos]

whoami schreef op donderdag 28 juni 2007 @ 11:41:
[...]
De vraag is, kan je op deze randvoorwaarde vertrouwen ?
Stel bv dat een bepaalde student door ziekte een bepaald examen voor een bepaalde module niet heeft kunnen afleggen. Wat dan ?

Mja, dat is een beetje de vraag,, vandaar ook mijn opmerking erboven.
Ik vind het sowieso al een apart iets dat iedere student alle modules voor zijn kiezen krijgt, klinkt namelijk niet als een ontwerp wat vooruitkijkt op mogelijke wijzigingen in de toekomst.

Bij mij op de TU/e is het trouwens zo, dat als ik me voor een vak inschrijf, en niet erheen ga, ik een NV als punt krijg. ("Niet Verschenen")

Maar goed, dat is verder speculatie.

[P.O. Box]

en mag een student een module na een onvoldoende herkansen? dan staat hij misschien wel een keer in de db met een voldoende en met een onvoldoende... ook iets om rekening mee te houden lijkt me...

[Dido]

Voor alle modules een voldoende = voor geen enkele module een cijfer lager dan x, toch?

SQL:

select user_id
from cijfers
group by user_id
having min(cijfer)>5.5

Dat zei Niemand_Anders ook al, maar deze oplossing is wat simpeler, lijkt me?

[Rhapsody]

Herkansen wordt niet bijgehouden, het draait om het uiteindelijke cijfer.
Wanneer iemand niet verschijnt, wordt er geen cijfer ingevoerd.

Het is overigens niet voor een school, maar voor een certificaat. Dat certificaat bestaat uit een aantal vaste modules. Iedere student dient die modules te doen. Sommige delen van de database kunnen inderdaad beter, maar die mogelijkheid is er nu helaas niet. Wel speel ik met de gedachte om toch zelf het een en ander te verbeteren.

Enorm bedankt voor de antwoorden, vind de discussie ook leuk om te volgen, zo steek ik er nog meer van op.

[glashio]

Rhapsody schreef op donderdag 28 juni 2007 @ 13:00:
Enorm bedankt voor de antwoorden, vind de discussie ook leuk om te volgen, zo steek ik er nog meer van op.

beetle71 schreef op woensdag 27 juni 2007 @ 20:36:
Het kan ook in 1 keer, zonder dat je weet hoeveel modules een persoon gevolgd heeft.
...

Heb je deze al geprobeerd ? Ik vind hem goed bedacht

[Rhapsody]

Die zal ik vanavond even proberen, heb die van whoami gepakt en verder uitgewerkt en die werkt nu, dus daar kan ik in principe mee verder

[Dido]

Ik vermoed dat ik iets over het hoofd zie, maar waarom voldoet die van mij niet - zonder join etc?

(Niet omdat je per se die moet gebruiken, ik vraag me serieus af wat ik mis - dat kan me een middag kosten als ik daarover blijf piekeren )

[ Voor 42% gewijzigd door Dido op 28-06-2007 13:17 ]

[Rhapsody]

ik kan het hier helaas niet testen Dido.
Ik denk, als ik het zo bekijk, dat je de koppeling mist met het aantal modules.

Maar ik moet het toch nog uitbreiden dan, want een module kan ook nog submodules hebben. Dat wil ik zelf gaan uitvogelen (en das ook niet moeilijk, want alleen submodules hebben een parent)
Verder kan je ook nog vrijstelling hebben, maar ook dat stelt niets voor.

[Dido]

Rhapsody schreef op donderdag 28 juni 2007 @ 13:22:
ik kan het hier helaas niet testen Dido.
Ik denk, als ik het zo bekijk, dat je de koppeling mist met het aantal modules.

Klopt, het aantal is ook niet interessant. Als het minimumcijfer >5.5 is, is de student overl voor geslaagd, lijkt me.

Ik bedenk me nu dat je dan alleen weet of de student geslaagd is voor alle modules waar hij een cijfer voor heeft. Ok, dat is het niet - sorry

[Voutloos]

Dan slinger je een count in de having erbij en weet je of je wel #modules voldoendes hebt.

En het kan ook nog op 1001 andere manieren...

offtopic:
Iets doen met 2 doodeenvoudige tabellen maakt overigens nog geen 'lastige query'.

[Niemand_Anders]

@Dido: Je zou bij de start van de cursus het cijfer voor alle modules op 0 kunnen zetten. Is een module dan nog niet voltooid, dan blijft het laagste cijfer 0. Maar het lijkt mij dat elke 'certificaat' andere modules vereist en dat een count over alle modules niet tot het gewenste resultaat lijdt.

Er zal waarschijnlijk ook een tabel moeten komen welke aangeeft welke modules afgerond moeten worden voordat iemand is geslaagd. Op basis van deze tabel kun je dan een 'correcte' count uitvoeren of bij de start van de cursus alle modules op '0' initialiseren.


@Greyfox: Als triggers evil zijn, dan zullen stored procedures in jouw ogen dat ook zijn. De Coding Standards van onder andere Phillips eisen zelfs dat de integriteit wordt afgedwongen door triggers en foreign keys in databases.

Triggers zijn de meeste eenvoudige vorm om integriteit van je data af te dwingen. Maar de in vraagstelling werd gevraagd naar geslaagde studenen en niet naar hun id. Bij de andere oplossing is nog steeds een query als' select * from users where user_id in (subquery welke geslaagde studenten ophaalt)' om ook de gegevens (bijv naam) van de student op het certificaat te zetten.

Als je elke dag 5 sessies hebt met 40 studenten, 5 dagen per week en 50 weken per jaar met elk 10 modules zal de cijfer lijst flink groeien. Dat zijn per jaar dus 500.000 cijfers. Dan wil je niet elke keer de oude cijfers blijven vergelijken. Het zou mij dan ook niet verbazen dat users een cursus_id of session_id bevat zodat eenvoudig alle studenten voor die cursus kunnen worden opgevraagd.

De oplossing van dido om 'group by user_id having min(cijfer) > 5.5 ' te gebruiken zal steeds trager worden omdat de cijfer tabel blijft groeien. user_id in cijfers zal al een index hebben dus blijft de trigger op die tabel snel en blijft na 5 jaar het ophalen van alle geslaagde studenten voor cursus x nog steeds snel.

Daarbij vind ik persoonlijk denormalisatie een database aangelegenheid (het laagste cijfer opslaan bij de student) en dus een trigger meer dan op zijn plaats. Als je perse een trigger wilt voorkomen, zou je nog een stored procedure 'student_update_module_score(user_id, module_id, cijfer)' kunnen gebruiken welke de insert/update op cijfers doet en daarna het laagste cijfer alsnog opslaat in users.

[whoami]

Niemand_Anders schreef op donderdag 28 juni 2007 @ 16:07:


@Greyfox: Als triggers evil zijn, dan zullen stored procedures in jouw ogen dat ook zijn. De Coding Standards van onder andere Phillips eisen zelfs dat de integriteit wordt afgedwongen door triggers en foreign keys in databases.

Hoe zie je dat ?
Als je een foreign key constraint hebt, waarom dan nog met triggers gaan foefelen om die referentiele integriteit te waarborgen ? (Zowiezo zou je -als alles goed is- geen triggers / cascades nodig moeten hebben, want je PK zou in 99% v/d gevallen niet mogen wijzigen).

[Niemand_Anders]

Omdat je met foreign keys alleen de id's op integriteit kunt bewaken. Een andere waarde uit die tabel (zoals cijfer in users) kun je daarmee niet in 'sync' houden. De trigger staat wordt dan ook niet gebruikt om user_id (primary key) te veranderen, maar om het veld 'cijfer' te updaten zodra er een record in cijfers is gewijzigd of toegevoegd. Bij een grote advertentie site hebben we ook triggers gebruikt om het 'changed' veld te update zodra er een veld in het record wijzigd. Vervolgens gebruikt een ander script dit veld om te bepalen voor welke advertenties opnieuw de html gegenereerd moet worden.

De cascade delete heb je nodig als de user_id 675 uit users wordt verwijderd dan ook de cijferlijst van deze user uit cijfers wordt verwijderd. Als je denormalisatie (wat indien goed toegepast een flinke performance boost kan opleveren) toepast betekend dat informatie 'dubbel' in je database staat. Immers het laagste cijfer staat dan op twee plaatsen. Middels triggers kun je die duplicate waardes up-to-date houden. Stel je voor dat je een keer handmatig een record aan cijfers toevoegt (via MysqlAdmin bijvoorbeeld) en je hebt geen trigger ingesteld dan is het mogelijk dat cijfer uit users niet meer correct is. Op dat moment ben je dus de integriteit van je data kwijt. En hoewel iedereen weet dat je nooit handmatig database inhoud mag wijzigen (omdat je dan gerelateerde queries niet uitvoert of vergeet), gebeurt dit van tijd tot tijd toch.

Iedere database administrator weet de group by queries relatief zwaar zijn. je zult begrijpen dat een query als 'select userid from cijfers where group by user_id having min(cijfer) > 5.5' een stuk zwaarder is dan 'select min(cijfer) from cijfers where user_id=675'. De gehele group by having clausule komt dan te vervallen. Zal ik eens uitleggen hoe een database intern een group by query uitvoert? Dan gebeurt namelijk in twee delen. Het eerste deel doet een distinct op cijfers op basis van de group by velden waarbij de where clausule al direct wordt meegenomen om het eerste deel te optimaliseren. Vervolgens (het tweede deel) wordt er per user (het resultaat van de group by clausule) de aggregate functies uitgevoert waarbij de having clausile op dat moment de where clausule wordt. Daarom staat ook in elke database boek dat een group by op een zo klein mogelijk set van data uitgevoert moet worden. Een group by is zwaar en traag.

Omdat de trigger ervoor zorgt dat het laagste cijfer altijd in het veld cijfer van users staat (afgedwongen) heb je dus bij het bepalen of iemand is geslaagd de cijferlijst niet meer nodig. Met de trigger verkom je dus niet alleen een group by, maar je verplaatst de 'load' om het laagste cijfer te bepalen naar een gunstiger tijdstip (namelijk bij het wijzigen van de cijfer lijst) en je voert hem alleen uit voor een specifieke user.

In mijn vorige post had ik ook al aangegeven dat ik verwacht dat er een course_id oid in users staat. Wil je na 5 jaar nog steeds binnen 1 seconde kunnen bepalen wie geslaagd zijn dan zul je anders couse_id ook moeten toevoegen aan cijfers zodat je geen group by meer doet op de volledige tabel. Want na 5 jaar vind een database een group by op 5 * 500.000 (zie vorige post) = 2.500.000 records niet meer leuk. De query 'select * from users where course_id=87483 and cijfer > 5.5' blijft gewoon snel. Zeker op het moment dat er een multifield index op course_id and cijfer is gelegd.

Ik weet niet met wat voor soort databases GreyFox (heeft ge)werkt en wat de performance requirements van zijn opdrachtgevers zijn, maar triggers 'evil' en technisch slecht onderhoudbaar noemen zegt mij dat GreyFox nog niet met database heeft gewerkt met een minimale grootte van 100GB. Bij dergelijke groottes moet je 100% zeker weten dat de data integrteit 100% is. Om dergelijke database snel te houden ontkom je niet aan denormalisatie en heb je dus de triggers nodig om de integriteit te kunnen waarborgen. Stel je voor dat Rhapsody het exclusieve recht weet te verwerven om alle Microsoft certificeringen af te nemen. Dan heb je echt binnen no-time met een mega database te maken.