SQL query: gewogen gemiddelde vinden

[sip5080]

Hallo,

Ik werk zelf niet zo veel met SQL en ben nu wel erg lang bezig met een vraagstuk, daarom hier een topic.
Ik wil een query trekken welke twee vermenigvuldigde tabellen van (in dit geval) 4 rijen optelt en vervolgens dit delen door het aantal rijen wat uit de initiële query is gekomen.

Praktisch gezien heb ik dit werkend met de volgende query:

SELECT studentnummer, vak, SUM(cijfer*gewicht_cijfer / 4)
FROM cijfer
WHERE studentnummer = 101; AND vak = 'nl'

Nu krijg ik netjes de output met het gemiddelde cijfer. Toelichting: deze query bestaat dus uit 4 cijfers waarvan één 9 met gewicht 1, één 6 met gewicht 1 en één 8 met gewicht 2. Dit maakt 9 + 6 + 8 + 8 / 4 = 7.75, het laatste getal 7.75 maakt dat ook het gemiddelde cijfer.

Probleem: dit is niet schaalbaar, elke keer moet statisch 'het door te delen' getal worden aangepast in de query. Dit wil ik automatiseren.

Concreet: wil ik het aantal rijen van wordt opgehaald uit de query gebruiken ipv de statische 4 welke nu in de query staat. Hoe kan ik dit oplossen?

[Tsurany]

Een aparte select subquery gebruiken die een row count uitvoert?

[nescafe]

Met twee 8en met gewicht 2 zou je een totaalgewicht van 6 hebben: ( 9 + 6 + ( 8 + 8 ) + ( 8 + 8 ) ) / ( 1 + 1 + 2 + 2 ).

Hoe zou je het totaalgewicht kunnen berekenen?

Zou je deze functie in je expressie kunnen meenemen?

Een subquery is hiervoor niet nodig.

[Onbekend]

Je hebt ook de functie AVG in MSSQL.

[sip5080]

nescafe schreef op zondag 13 november 2016 @ 14:14:
Met twee 8en met gewicht 2 zou je een totaalgewicht van 6 hebben: ( 9 + 6 + ( 8 + 8 ) + ( 8 + 8 ) ) / ( 1 + 1 + 2 + 2 ).

Hoe zou je het totaalgewicht kunnen berekenen?

Zou je deze functie in je expressie kunnen meenemen?

Een subquery is hiervoor niet nodig.

Type fout van mij, betreft één 8 met gewicht 2. Als ik die vragen kon beantwoorden had ik dit topic (denk ik) niet geplaatst.

[sip5080]

Onbekend schreef op zondag 13 november 2016 @ 14:15:
Je hebt ook de functie AVG in MSSQL.

Daar heb ik inderdaad veel mee geprobeerd, helaas levert dit een gemiddelde op en niet de door te delen factor. Of mis ik iets? Iets als (AVG(SUM(cijfer*gewicht_cijfer)) werkt niet.

[nescafe]

sip5080 schreef op zondag 13 november 2016 @ 14:16:
Type fout van mij, betreft één 8 met gewicht 2. Als ik die vragen kon beantwoorden had ik dit topic (denk ik) niet geplaatst.

Het zijn wel de vragen die je jezelf moet stellen en waardoor je zelf (ipv iemand anders) het antwoord kunt bedenken.

Nogmaals; hoe zou je het totaalgewicht, de som van de gewichten kunnen bepalen?

[sip5080]

nescafe schreef op zondag 13 november 2016 @ 14:18:
[...]

Het zijn wel de vragen die je jezelf moet stellen en waardoor je zelf (ipv iemand anders) het antwoord kunt bedenken.

Nogmaals; hoe zou je het totaalgewicht, de som van de gewichten kunnen bepalen?

SUM(gewicht_cijfer)

Ik zou alleen niet weten hoe ik deze kan gebruiken in de rekensom.

[nescafe]

Kijk even naar de deling door 4 in je oorspronkelijke query. Deze kun je uit de SUM() halen. SUM(x / 4) is immers gelijk aan SUM(x) / 4. Het getal 4 kun je vervangen door de gegeven functie.

[F_J_K]

CSA --> PRG.

Ik zal er ook een vraagtopic van maken (kan je een volgende keer ook zelf, geef t.z.t. de beste oplossing aan!) en een wat meer informatieve topictitel bedenken: 'vraag' zegt zo weinig

[ Voor 88% gewijzigd door F_J_K op 13-11-2016 16:04 ]

[pedorus]

sip5080 schreef op zondag 13 november 2016 @ 14:05:
Probleem: dit is niet schaalbaar, elke keer moet statisch 'het door te delen' getal worden aangepast in de query. Dit wil ik automatiseren.

Is het ook niet logischer om een group by te doen, zodat je meerdere resultaten kan krijgen? Als in:

SQL:

SELECT studentnummer, vak, COUNT(*), SUM(gewicht_cijfer), AVG(cijfer), MIN(cijfer), MAX(cijfer),
       SUM(cijfer * gewicht_cijfer) / SUM(gewicht_cijfer)
FROM cijfer
GROUP BY studentnummer, vak

[Verwijderd]

pedorus schreef op zondag 13 november 2016 @ 17:21:
[...]

Is het ook niet logischer om een group by te doen, zodat je meerdere resultaten kan krijgen? Als in:

Hij wil maar 1 student en 1 vak selecteren, dus dan is de group by functie overbodig.

Dit zou voldoende moeten zijn:

SQL:

SELECT studentnummer, vak, (SUM(cijfer * gewicht_cijfer) / SUM(gewicht_cijfer)) AS gemiddelde_cijfer 
FROM cijfer
WHERE studentnummer=101 AND vak='NL';

[ Voor 40% gewijzigd door Verwijderd op 14-11-2016 18:36 . Reden: Toevoeging code ]

[[Jules]]

Je zou een window function kunnen proberen:

SQL:

SELECT studentnummer
,vak
,AVG(cijfer) OVER (PARTITION BY studentnummer, vak) AS gemiddelde_cijfer
FROM cijfer

[EngineerCoding]

Het is vrij eenvouding om een gemiddelde berekenen, ik neem als voorbeeld de volgende tabel:

Cijferlijst (niet een werkelijke relatie, aangezien het geen pk heeft maar beside the point)

gewicht	cijfer
5	9.3
2	6.9
9	2.5

Om even handmatig het gemiddelde te berekenen: (5 * 9.3 + 2 * 6.9 + 9 * 2.5) / 16 = 5,175
We kunnen het gemiddelde van alle cijfers nemen met de aggregatie AVG, maar dit is ongewogen en dus onnodig. Men kan met SQL berekenen, dus laten we een gemiddelde nemen van het gewogen gemiddelde:

SQL:

SELECT AVG(gewicht * cijfer) As gemiddelde
FROM Cijferlijst;

We krijgen nu 27,6 als output, waar we eigenlijk vrij weinig aan hebben aangezien dit een gemiddelde van alle cijfers bij elkaar (5 keer het cijfer 9.3, 2 ker 6.9 etc.). Aangezien dit een gemiddelde is, moeten we ook door een gemiddelde delen, en dit geval het gemiddelde van de weging:

SQL:

SELECT AVG(gewicht * cijfer) / AVG(gewicht) As gemiddelde
FROM Cijferlijst;

En wat een verassing, er komt 5,175 uit! Ik heb hier geen degelijke wiskundige uitleg voor, maar het werkt (dit is geen toevalstreffer).

Disclaimer: Deze tests zijn gedaan met PostgreSQL waarbij de kolom gewicht van het datatype INTEGER is en de kolom cijfer van het datatype DECIMAL(3, 1) is. Het zal weinig uitmaken welk DBMS gebruikt wordt, aangezien dit een query is die geen speciale aggregraties bevat. Datatypen kunnen ervoor zorgen dat het antwoord afwijkt.