probleem met moving average

[nobloz]

query bewegend gemiddelde ?

Ik heb een (example) tabel met de volgende velden en inhoud

item datum prijs
item1 1/1/2009 10
item1 1/2/2009 11
item1 1/3/2009 12
item1 1/4/2009 11
item1 1/5/2009 10
item1 1/6/2009 10
item1 1/7/2009 10

Als ik maar met 1 item werk lukt het om een 3 dagen moving average te ceeren
SELECT
item,datum,volume,
(SELECT SUM(t2.volume) / COUNT(t2.volume)
FROM tradecount t2
WHERE DATEDIFF(t1.datum, t2.datum) BETWEEN 0 AND 2) AS 'avg3day'
FROM tradecount t1
ORDER BY t1.item,t1.datum,


item datum volume avg3day
item1 1/1/2009 10 10.0000
item1 1/2/2009 11 10.5000
item1 1/3/2009 12 11.0000
item1 1/4/2009 11 11.3333
item1 1/5/2009 10 11.0000
item1 1/6/2009 10 10.3333
item1 1/7/2009 10 10.0000



Als ik met meerder items werk, is er een probleempje
item1 heeft ineens een ander moving gemiddelde en items 2 en 3 in eens ook ???
ik krijg het niet voor elkaar om dit goed te doen

item datum volume avg3day
item1 1/7/2009 10 10.0000
item1 1/1/2009 10 10.0000
item1 1/4/2009 11 10.4444
item1 1/5/2009 10 10.3333
item1 1/2/2009 11 10.1667
item1 1/6/2009 10 10.1111
item1 1/3/2009 12 10.3333
item2 1/3/2009 10 10.3333
item2 1/7/2009 10 10.0000
item2 1/4/2009 10 10.4444
item2 1/1/2009 10 10.0000
item2 1/5/2009 10 10.3333
item2 1/2/2009 10 10.1667
item2 1/6/2009 10 10.1111
item3 1/4/2009 10 10.4444
item3 1/7/2009 10 10.0000
item3 1/1/2009 10 10.0000
item3 1/5/2009 10 10.3333
item3 1/2/2009 10 10.1667
item3 1/3/2009 10 10.3333
item3 1/6/2009 10 10.1111


In de database zitten duizenden items, dagelijks geupdate over meer dan een jaar.
wat ik uiteindelijk wil is kolommen met een week, maand, kwartaal en jaar moving average

item datum prijs avgweek avgmonth avgquarter avgyear


Kan er iemand een licht op laten schijnen ?

nobloz

 

[Frats]

Hm, ik weet niet of ik de query 100% gebruikt, maar als je die query die daar staat precies hetzelfde gebruikt met alle items is het probleem waarschijnlijk dat hij niet weet dat hij elk item apart moet nemen?

Kijk eens wat er gebeurd als je een 'GROUP BY t1.item' erin zet?

 

[nobloz]

SELECT
item,datum,volume,
(SELECT SUM(t2.volume) / COUNT(t2.volume)
FROM tradecount t2
WHERE DATEDIFF(t1.datum, t2.datum) BETWEEN 0 AND 2) AS 'avg3day'
FROM tradecount t1
GROUP BY t1.item
ORDER BY t1.datum

Als ik dat doe is dit de uitkomst.

item datum volume avg3day
item1 1/1/2009 10 10.0000
item2 1/1/2009 10 10.0000
item3 1/1/2009 10 10.0000

Ik had hier al mee gespeeld ...

 

[nobloz]

als je die query die daar staat precies hetzelfde gebruikt met alle items is het probleem waarschijnlijk dat hij niet weet dat hij elk item apart moet nemen?

Dat is inderdaad het probleem.
Hoe krijg ik het voor elkaar dat de berekeing over 1 item gaat en dan de volgende etc.
Heb wel een gehoord van tijdelijke tabellen, maar weet niet hoe ik dat moet doen.
Het ressultaat moet uiteindelijk toch in een tabel te recht komen.

kortom, voor elk item, doe de query en stop de resultaen in een nieuwe tabel.

Help ....

 

[Epic]

Je kunt ook de functie AVG() van MySQL zelf gebruiken. Maar los daarvan, om een gemiddelde te kunnen berekenen moet je op alle overigen velden die in je select staan een group by toevoegen. Wat me niet helemaal duidelijk is, is op basis van wat wil je een gemiddelde berekenen? Item + datum? In dat geval zou dit moeten werken:

SELECT t1.item
,      t1.datum
,      AVG(t1.volume) gemiddelde
FROM   tradecount t1
GROUP BY t1.item
,      t1.datum
ORDER BY t1.datum;

Wil je het gemiddelde per item, maar niet per datum en de datum wel laten zien dat wordt het al iets lastiger. Met een subquery kan dat en krijg je dit:

SELECT t2.item
,      t2.datum
,      t1.gemiddelde
FROM   tradecount t2
,    ( SELECT t.item
       ,      AVG(t.volume) gemiddelde
       FROM   tradecount t
       GROUP BY t.item
     ) t1
WHERE  t1.item = t2.item
ORDER BY t1.datum;

Hopelijk kom je hier een stapje verder mee. Succes!

 

[nobloz]

EPIC,

De eertse query geeft me de lijst die ik al heb, nl de oorsponkelijke lijst met het enige verschil dat deze per datum is gesorteerd in plaats van item en dan op datum.
"order by t1.item,t1.datum" heb ik de lijst weer zoals deze was, ofwel een query die infeite dus niets doet.

De tweede query krijg ik een foutmelding "1054 - unknown collumn 't1.datum' in 'order clause'" (ik heb mysql version 5.0.45) Maak ik daar "order by t2.datum" van werkt het wel. Uiteindelijk zal deze eerst per item en dan per datum moeten worden gesorteerd.
Dus "order by t2.item,t2.datum" en geeft me netjes het gemiddelde, maar niet een "moving average".

De vraag is nu, hoe krijg ik hier nu een "moving average"
en zijn we weer terug bij mijn eerste query waar ik een berekening maak
van items per dag en hun waarde, over een bepaalde periode, gedeeld door die periode.

note: elk item komt maar een keer per dag voor.

De uitkomst moet er zo uitzien:

item datum volume avg3day
item1 1/1/2009 10 10.0000 ->= (10)/1
item1 1/2/2009 11 10.5000 ->= (10+11)/2
item1 1/3/2009 12 11.0000 ->= (10+11+12)/3
item1 1/4/2009 11 11.3333 ->= (11+12+11)/3
item1 1/5/2009 10 11.0000 ->= (12+11+10)/3
item1 1/6/2009 10 10.3333 ->= (11+10+10)/3
item1 1/7/2009 10 10.0000 ->= (10+10+10)/3
item2 1/1/2009 10 10.0000
item2 1/2/2009 10 10.0000
item2 1/3/2009 10 10.0000
item2 1/4/2009 10 10.0000
item2 1/5/2009 10 10.0000
item2 1/5/2009 10 10.0000
item2 1/6/2009 10 10.0000
item3 1/1/2009 10 10.0000
item3 1/2/2009 10 10.0000
item3 1/3/2009 10 10.0000
item3 1/4/2009 10 10.0000
item3 1/5/2009 10 10.0000
item3 1/6/2009 10 10.0000
item3 1/7/2009 10 10.0000

Hoop dat de vraag nu wat duidelijker is.

Nobloz

 

[nobloz]

got the solution ...

SELECT
t1.item,t1.datum, volume,
( SELECT SUM(t2.volume) / COUNT(t2.volume)
FROM tradecount AS t2
WHERE item=t1.item AND DATEDIFF(t1.datum, t2.datum) BETWEEN 0 AND 2
) AS 'avg3day'
FROM tradecount AS t1
GROUP BY t1.item,t1.datum;