[MySQL] Filteren hoogste en laagste bij AVG

[soap]

Ik zit met een probleem dat ik het liefst in MySQL oplos in plaats van naderhand de resultset in php te gaan wijzigen etc.

Stel, ik heb een item dat gewaardeerd wordt door 6 verschillende mensen met een 8, 3, 6, 9, 10, 4. Deze cijfers worden in de db opgeslagen als afzonderlijke rijen met een foreignkey naar het ID van het item.
Hoe kan ik nu in 1 query de hoogste en laagste cijfers eruit filteren (3 en 10) en van de overgebleven cijfers het gemiddelde berekenen?

Dus zoiets als dit (wat niet werkt):

code:

select AVG(avgscore - MIN(avgscore) - MAX(avgscore)) FROM scores GROUP BY item

Dit is uiterst simpel uitgelegd, ik hoop dat het duidelijk is. Googlen op 'remove highest lowest value mysql' etc levert niet het gewenste antwoord op. Iemand hier een idee?

[Soultaker]

Zoiets zou moeten werken (maar pas op voor overflow, en zorg dat je minstens drie waarden hebt):

SQL:

SELECT (SUM(score) - MIN(score) - MAX(score))/(COUNT(score) - 2)..

[ Voor 76% gewijzigd door Soultaker op 27-08-2008 13:46 . Reden: foute formule weggehaald ]

[r0bert]

Edit: Oei, verkeerd gelezen

Eensch met Soultaker

[ Voor 111% gewijzigd door r0bert op 27-08-2008 13:48 ]

[soap]

Soultaker schreef op woensdag 27 augustus 2008 @ 13:44:
Zoiets zou moeten werken (maar pas op voor overflow, en zorg dat je minstens drie waarden hebt):

SQL:

SELECT (SUM(score) - MIN(score) - MAX(score))/(COUNT(score) - 2)..

Top, je hebt helemaal gelijk. Gewoon basis rekenen eigenlijk
Ik filter sowieso op minimaal 6 scores dus dat komt goed.

Bedankt.

[Big Womly]

of je kunt als volgt werken

SQL:

select avg(score)
from scores
where score not in (
  select min(score)
  from scores
  group by item
)
and score not in (
  select max(score)
  from scores
  group by item
)

Nu is het aan jou om te weten of je, indien de minimale of maximale score meer als 1 keer voorkomt, alle minima of maxima wil negeren, of slechts 1 keer, dan is de oplossing van soultaker beter.

[soap]

Bedankt voor deze toevoeging. Het gaat erom om mensen die scores geven die buitengewoon hoge of lage scores geven eruit te filteren. Wat daar de beste manier voor is moet even uitgevogeld worden.
In het 'echte' systeem kent men 6 punten per keer toe, dus dat er meerdere dezelfde min of max averages zijn lijkt niet gauw voor te komen.

[Soultaker]

Het risico van mbsaerens' suggestie is dat als er in totaal minder dan drie verschillende cijfers zijn (wat bij zes stemmen nog wel eens kan voorkomen) alle cijfers weggefilterd worden. Ook filter je per definitie de minimum en maximum cijfers weg: als je cijfers van 1 tot en met 10 lopen, is een gemiddelde lager dan 2 of hoger dan 9 dan onmogelijk geworden.

Of dat wenselijk is of niet, moet je zelf bepalen.

[Voutloos]

Met de query van mbearens filter je misschien 1 max score en 10 min scores (of andersom), dus tenzij die aantallen gelijk getrokken worden is het mogelijk zwaar biased. Korte versie: Soultaker geeft de mooie oplossing, tenzij fancy statistiek eisen nodig.

[Verwijderd]

Is voor wat je zoekt de mediaan van de collectie niet de oplossing? Da's in principe 'de middelste' waarde ipv. het gemiddelde. Als je geen oneven aantal hebt (dus geen midden) pak je 't gemiddelde van de twee middelste waarden.

Zie http://dev.mysql.com/doc/...n/group-by-functions.html voor een voorbeeld (in de commentaren er onder).