[Mysql] Gemiddelden toevoegen van waardes uit zelfde tabel

[Verwijderd]

Beste mede tweakers!

Even een (voor pro's) waarschijnlijk erg makkelijk vraagstuk, maar ik ben al uren aan het klooien.

Ik heb een tabel:
groepnummer,totaaltelling,gemiddelde.

entries zien er zo uit:

3300,20,<leeg>
3300,40,<leeg>
1200,10,<leeg>
1200,20,<leeg>
<knip veeeel entries>

Wat ik wil doen is de hele tabel updaten, alle entries, en er PER groepnummer een gemiddelde aan toevoegen, en wel het gemiddelde van de waarde 'totaaltelling' voor alle entries van DIE groep specifiek.

Dus het moet worden:
3300,20,30
3300,40,30
1200,10,15
1200,20,15


Nu heb ik even een vieze hack via bash gemaakt, maar tabellen zijn groot en dit performed totaal niet.

#!/bin/sh
IFS=$'\n'

# lijst met groepnummers maken
for i in `/usr/sbin/mijndb.sh "select distinct groepnummer from tabel;"`
do
# bereken per groep het gemiddelde
gemid=`/usr/sbin/mijndb.sh "select round(avg(totaaltelling)) from tabel where groepnummer='$i';"`
# zet gemiddelde terug in de entries van DIE groep.
/usr/sbin/mijndb.sh "update tabel set gemiddelde='$gemid' where groepnummer='$i';"
done

Kan een van de heren tweakers mij helpen? Ik zit al uren te googlen/fora te lezen, maar het is niet mijn vakgebied ;-(
Dit moet toch binnen SQL op te lossen zijn met een update/join met een of andere recursieve sub-select o.i.d. ??

Thanks
O2.

[Creepy]

Hoe ziet je tabel structuur er precies uit? Welke indexen gebruik je? En wat is "veel" rijen?
Los daarvan is het nogal vreemd om in dezelfde tabel als waar je al die waardes hebt steen, je voor elke rij van 1 groep hetzelfde gemiddelde wilt neerzetten. Je queries opzich lijken me niet het probleem.

[Verwijderd]

Allereerst, ik ben echt beginner in SQL dus het is waarschijnlijk allemaal vloeken in de kerk, maar, de tabel structuur is : groepnummer varchar en totaaltelling en gemiddelde integers.
Ik heb geen indexen.
Entries 200.000+

Wat uiteindelijk de bedoeling is is dat ik de afwijkingen in de variabele 'totaaltelling' kan zien van entries die toegevoegd worden, of deze veel afwijken van het gemiddelde van alle andere antries 'totaaltelling' in de rest van de tabel.

De tabel bestaat eigenlijk uit een history van meetwaardes (zit ook nog een timestamp veld bij, die had ik in het voorbeeld nu even weggelaten).

Ik wil een selectie kunnen maken van alle entries die meer dan x procent afwijken van het gemiddelde van de waardes van de rest van die kolom.

In principe doet dat bash-script exact wat het moet doen, alleen duurt het eeuwen om uit te voeren, en is dit volgens mij binnen SQL met iets heel simpels op te lossen.

Voorstellen in een andere denk/oplossingrichting zijn ook welkom

[ Voor 36% gewijzigd door Verwijderd op 15-01-2015 20:48 ]

[Creepy]

Zet eerst eens een index (primary key) op groepnummer en het timestamp veld (dus 1 index op beide velden). Je kan nu veel sneller uit deze tabel selecteren op basis van groepsnummer. Dit zou al enorm moeten schelen in snelheid aangezien je dit steeds in je loop doet. Een primary key moet uniek zijn, vandaar dat je ook het timestamp veld mee moet nemen hierin.

Dan zit je nog met je queries per groep. Met een GROUP BY op groepnummer i.p.v. groepnummer op te nemen in je where (Programming FAQ - SQL - Group by) kan je met 1 query alle gemiddelden in 1 keer berekenen. Afhankelijk van hoe snel dat gaat kan je er voor kiezen om de gemiddelden weer weg te schrijven op wat performance winst te halen, maar dat zou ik dan in een losse tabel doen zodat je per groep maar 1 keer het gemiddelde hoeft op te slaan. Vergeet dan niet een primary key in te stellen (het groepsnummer)

[ Voor 5% gewijzigd door Creepy op 15-01-2015 21:35 ]

[TallManNL]

Bedenk wel dat je op deze manier bij iedere insert je gemiddelde op alle records voor die groep moet herberekenen. Dat kun je doen met een insert trigger maar je insert zal langzamer worden.

Als je veel insert en maar weinig select in de tabel kan het een stuk zinniger zijn je gemiddelde pas bij het ophalen te berekenen.

Gemiddelde ophalen in een select kan eenvoudig met (Tabel heb ik test genoemd)

SQL:

SELECT t.GroupNr, t.Value,aggr.avg 
from Test t
inner join (
    SELECT GroupNr, Avg(Value) as avg 
    from Test
    group by GroupNr) aggr on aggr.GroupNr=t.GroupNr

disclaimer: test gedaan op MSSQL, verschillen met MySql kunnen voorkomen

[ Voor 8% gewijzigd door TallManNL op 15-01-2015 22:06 ]

[Verwijderd]

Tallman:

Het gemiddelde ophalen werkt inderdaad, ik heb de timestamp er ook nog even bij opgehaald om te kijken van wanneer de data is:

| 3994 | 167 | 2015-01-13 14:07:31 | 172.994382022472 |
| 3994 | 167 | 2015-01-13 14:27:30 | 172.994382022472 |
| 3994 | 168 | 2015-01-13 14:47:31 | 172.994382022472 |
| 3994 | 168 | 2015-01-13 15:07:30 | 172.994382022472 |
| 3994 | 168 | 2015-01-13 15:27:30 | 172.994382022472 |
| 3994 | 169 | 2015-01-13 15:47:30 | 172.994382022472 |
| 3994 | 169 | 2015-01-13 16:07:30 | 172.994382022472 |
| 3994 | 170 | 2015-01-13 16:27:31 | 172.994382022472 |
| 3994 | 170 | 2015-01-13 16:47:31 | 172.994382022472 |


Laatste kolom geeft inderdaad het gemiddelde van alle entries van de 2e kolom.


Om het helemaal af te maken, zou ik eigenlijk een select statement moeten hebben die alleen de laatste 3 toegevoegde waardes weergeeft waarbij kolom2 ONDER het gemiddelde van de laatste kolom zit......

[Verwijderd]

Creepy schreef op donderdag 15 januari 2015 @ 21:34:
Zet eerst eens een index (primary key) op groepnummer en het timestamp veld (dus 1 index op beide velden). Je kan nu veel sneller uit deze tabel selecteren op basis van groepsnummer. Dit zou al enorm moeten schelen in snelheid aangezien je dit steeds in je loop doet. Een primary key moet uniek zijn, vandaar dat je ook het timestamp veld mee moet nemen hierin.

Dan zit je nog met je queries per groep. Met een GROUP BY op groepnummer i.p.v. groepnummer op te nemen in je where (Programming FAQ - SQL - Group by) kan je met 1 query alle gemiddelden in 1 keer berekenen. Afhankelijk van hoe snel dat gaat kan je er voor kiezen om de gemiddelden weer weg te schrijven op wat performance winst te halen, maar dat zou ik dan in een losse tabel doen zodat je per groep maar 1 keer het gemiddelde hoeft op te slaan. Vergeet dan niet een primary key in te stellen (het groepsnummer)

Ik heb de primary key toegevoegd in de table, met de combinatie groupnummer en timestamp als unieke waarde.

De waardes hoef ik niet persee weg te schrijven, zie posting hierboven.
Een select van de laatste 3 entries (dus meest recente tijdstip) waarbij de totaaltelling onder het gemiddelde valt zou al goed zijn. Hoef ik niet persee terug weg te schrijven.

[Verwijderd]

Okay, ik heb het (met jullie fijne hulp) redelijk voor elkaar.

deze select:

select distinct(tabel.groepnummer), avg from tabel inner join (select groepnummer, round(avg(totaal)) as avg from tabel group by groepnummer) aggr on aggr.groepnummer=tabel.groepnummer;

geeft mooi tabelletje met de groepnummers en de gemiddelden van de totalen:


| 3977 | 799 |
| 3979 | 321 |
| 3980 | 325 |
| 3981 | 274 |
| 3982 | 264 |
| 3984 | 128 |
| 3986 | 362 |

1 final question..

Hoe schrijf ik dit nu terug in de tabel ?
Dus een update-statement die met een loopje op alle rijen waar groepnummer=x is het gemiddelde y erin zet?

[TallManNL]

Je kunt ook updaten met een inner join

zoals hier wordt getoond

[Verwijderd]

Je zou er ook een view van kunnen maken. Feitelijk zou je dit gemiddelde niet op moeten slaan volgens de normalisatieregels aangezien het een procesgegeven is.

[aaitje]

Waarom wil je dat terugschrijven naar je tabel? Dan moet je alsnog bij iedere insert het gemiddelde herberekenen en een aantal records updaten met het nieuwe gemiddelde. Dat is niet erg efficiënt....

Zoals DefP ook aangeeft worden dat soort vluchtige gegevens meestal niet opgeslagen.

[ Voor 18% gewijzigd door aaitje op 15-01-2015 22:39 . Reden: verwijzing naar post defp toegevoegd ]

[Verwijderd]

Ik heb inmiddels gezien dat als ik het met een inner join terugschrijf dat hij bijna 4 minuten staat te harken, dus ik laat het hier maar bij.
Ik heb met de results van de gemiddelde-berekeningen query een 2e tabel aangemaakt en die roep ik er nu gewoon bij in bepaalde selects, dan kom ik er ook!

Allemaal superbedankt voor de hulp!